1 / 14

VBA (continued)

VBA (continued). DSC340 Mike Pangburn. C onsider 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.

indra
Download Presentation

VBA (continued)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. VBA (continued) DSC340 Mike Pangburn

  2. 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…

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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")

  8. 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)

  9. 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"

  10. 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

  11. 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?

  12. 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

  13. 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"

  14. 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

More Related