VBA continued DSC 340 Mike Pangburn Consider a

VBA (continued) DSC 340 Mike Pangburn

Consider a quite different example You need to compute someone’s exact age. The math is actually somewhat complicated and we don’t have time/energy/experience to figure it out, so we perform a Google search on VBA code and calculating someone’s age. Here’s what I found…

VBA age calculator from WWW ‘ Determines time in Years (Y), Months (M), and Days (D) between Date 1 and Date 2 Dim Temp 1 As Date Temp 1 = Date. Serial(Year(Date 2), Month(Date 1), Day(Date 1)) Y = Year(Date 2) - Year(Date 1) + (Temp 1 > Date 2) M = Month(Date 2) - Month(Date 1) - (12 * (Temp 1 > Date 2)) D = Day(Date 2) - Day(Date 1) If D < 0 Then M=M-1 D = Day(Date. Serial(Year(Date 2), Month(Date 2) + 1, 0)) + D + 1 End If

Store Y, M, D info in an object I’d like a “date object” to store the 3 date parts (Y, M, and D) There is no such built-in object type (i. e. , a “class”) in VBA So, I can create one, which I decided to call time. In. YMD

Using my new class Assuming I have used the WWW code I found to calculate Y, M, and D, I want to create an object from my new class Here is what that code would look like in VBA syntax Dim result As time. In. YMD Set result = New time. In. YMD result. Years = Y result. Months = M result. Days = D ‘ declares the new variable named result ‘ sets the var. equal to a new time. In. YMD object

My complete Age() function Function Age(Date 1 As Date, Date 2 As Date) As time. In. YMD Dim Y, M, D As Integer Dim Temp 1 As Date ' Math/code found on WWW Google search Temp 1 = Date. Serial(Year(Date 2), Month(Date 1), Day(Date 1)) Y = Year(Date 2) - Year(Date 1) + (Temp 1 > Date 2) M = Month(Date 2) - Month(Date 1) - (12 * (Temp 1 > Date 2)) D = Day(Date 2) - Day(Date 1) If D < 0 Then M=M-1 D = Day(Date. Serial(Year(Date 2), Month(Date 2) + 1, 0)) + D + 1 End If ' End of found code computing age in Y, M, D Dim result As time. In. YMD Set result = New time. In. YMD result. Years = Y result. Months = M result. Days = D Set Age = result End Function

How do we get the user’s DOB? We could get the user’s DOB (date of birth) by having it typed into a worksheet cell Let’s see how we can do it with a dialog box Again, we can do a Google WWW search to find some example VBA code for an input box window. Here’s what I found: Dim DOB As Date DOB = Input. Box(Prompt: ="Your DOB please", Title: ="ENTER DOB", Default: ="01/01/2001")

Once we have their DOB, call Age() After the user types their DOB into the dialog box, we compute their age via our Age() function, which returns a time. In. YMD object So we can hold that result in our program using a time. In. YMD object variable Dim your. Age As time. In. YMD Set your. Age = Age(DOB, Date)

Report info to user in a Msg. Box This code will report the results back to the user Dim message. Start As String message. Start = "That would suggest you are " & your. Age. Years & " years, " & your. Age. Months & " months, and ” & your. Age. Days & " days old. " Msg. Box message. Start, vb. Information, "Age Calculator"

My complete subroutine Sub Process. User. DOB() Dim DOB As Date DOB = Input. Box(Prompt: ="Your DOB please (e. g. , 01/15/1988)", Title: ="ENTER YOUR DOB", Default: ="01/01/2001") Dim your. Age As time. In. YMD Set your. Age = Age(DOB, Date) Dim message. Start As String message. Start = "That would suggest you are " & your. Age. Years & " years, " & your. Age. Months & " months, and ” & your. Age. Days & " days old. ” Msg. Box message. Start, vb. Information, "Age Calculator” End Sub

Let’s call another sub to give voting/drinking info. Let’s write another sub() We will call it at the bottom of the prior sub by adding the line: Call Report. Drinking. Voting. Status(your. Age) This new sub will take the person’s age and determine: Is the person of legal drinking age? Is the person of legal voting age?

Figuring out the voting/drinking Qs Is the age eligible for voting or drinking? Dim can. Drink, can. Vote As Boolean If your. Age. Years >= 21 Then can. Drink = True Else: can. Drink = False End If If your. Age. Years >= 18 Then can. Vote = True Else: can. Vote = False End If

Report the results using a Msg. Box If can. Drink And can. Vote Then message. End = “Good news, that would mean you can drink and vote. " Else: If can. Vote Then message. End = " You can't drink, but you can vote. " Else: message. End = " Bummer, you can't drink or vote. " End If Msg. Box message. End, vb. Information, "Age Calculator"

The complete voting/drinking sub Sub Report. Drinking. Voting. Status(your. Age As time. In. YMD) Dim message. End As String Dim can. Drink, can. Vote As Boolean If your. Age. Years >= 21 Then can. Drink = True Else: can. Drink = False End If If your. Age. Years >= 18 Then can. Vote = True Else: can. Vote = False End If If can. Drink And can. Vote Then message. End = " Good news, that would mean you can drink and vote. " Else: If can. Vote Then message. End = " You can't drink, but you can vote. " Else: message. End = " Bummer, you can't drink or vote. " End If Msg. Box message. End, vb. Information, "Age Calculator" End Sub