Download
1 / 14
140 likes | 281 Vues
This tutorial demonstrates how to create an age calculator using VBA in Excel. It provides a step-by-step guide to determining a person's age in years, months, and days based on their date of birth. The example utilizes Google-sourced code for calculating the age and introduces a custom object to store the results. Additionally, it includes instructions for prompting the user to input their date of birth via an input box and reporting their age and eligibility for voting and drinking through message boxes.
E N D
VBA (continued) DSC340 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 Date1 and Date2 Dim Temp1 As Date Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1)) Y = Year(Date2) - Year(Date1) + (Temp1 > Date2) M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2)) D = Day(Date2) - Day(Date1) If D < 0 Then M = M - 1 D = Day(DateSerial(Year(Date2), Month(Date2) + 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 timeInYMD
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 timeInYMD‘ declares the new variable named result Set result = New timeInYMD‘ sets the var. equal to a new timeInYMD object result.Years = Y result.Months = M result.Days = D
My complete Age() function Function Age(Date1 As Date, Date2 As Date) As timeInYMD Dim Y, M, D As Integer Dim Temp1 As Date ' Math/code found on WWW Google search Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1)) Y = Year(Date2) - Year(Date1) + (Temp1 > Date2) M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2)) D = Day(Date2) - Day(Date1) If D < 0 Then M = M - 1 D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1 End If ' End of found code computing age in Y, M, D Dim result As timeInYMD Set result = New timeInYMD 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 = InputBox(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 timeInYMD object • So we can hold that result in our program using a timeInYMD object variable Dim yourAge As timeInYMD Set yourAge = Age(DOB, Date)
Report info to user in a MsgBox • This code will report the results back to the user Dim messageStart As String messageStart = "That would suggest you are " & yourAge.Years& " years, " & yourAge.Months & " months, and ” & yourAge.Days & " days old." MsgBoxmessageStart, vbInformation, "Age Calculator"
My complete subroutine Sub ProcessUserDOB() Dim DOB As Date DOB = InputBox(Prompt:="Your DOB please (e.g., 01/15/1988)",Title:="ENTER YOUR DOB", Default:="01/01/2001") Dim yourAge As timeInYMD Set yourAge = Age(DOB, Date) Dim messageStart As String messageStart = "That would suggest you are " & yourAge.Years & " years, " & yourAge.Months & " months, and ” & yourAge.Days& " days old.” MsgBoxmessageStart, vbInformation, "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 ReportDrinkingVotingStatus(yourAge) • 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 canDrink, canVote As Boolean If yourAge.Years >= 21 Then canDrink = True Else: canDrink = False End If If yourAge.Years >= 18 Then canVote = True Else: canVote = False End If
Report the results using a MsgBox If canDrink And canVote Then messageEnd = “Good news, that would mean you can drink and vote." Else: If canVote Then messageEnd = " You can't drink, but you can vote." Else: messageEnd = " Bummer, you can't drink or vote." End If End If MsgBoxmessageEnd, vbInformation, "Age Calculator"
The complete voting/drinking sub Sub ReportDrinkingVotingStatus(yourAge As timeInYMD) Dim messageEnd As String Dim canDrink, canVote As Boolean If yourAge.Years >= 21 Then canDrink = True Else: canDrink = False End If If yourAge.Years >= 18 Then canVote = True Else: canVote = False End If If canDrink And canVote Then messageEnd = " Good news, that would mean you can drink and vote." Else: If canVote Then messageEnd = " You can't drink, but you can vote." Else: messageEnd = " Bummer, you can't drink or vote." End If End If MsgBoxmessageEnd, vbInformation, "Age Calculator" End Sub
