Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Request help in learningVB (VB 6.3 function procedure)

    I am gradually trying to learn how to use VB/VBA, especially with respect to Excel & Access. I am using a couple of books recommended to me previously by Lounge members.

    Currently, I am trying to develop a macro/function procedure that will prompt worksheet users for data that would provide needed info for the function procedure/macro and place result of the procedure in a specific cell of the worksheet. The worksheet is one we use to gather certain traffic collision statistics including drivers' ages.

    I would like the worksheet user to be prompted for a driver's date of birth then use that data to compute the driver's age in years and enter it in that cell. The worksheet has three (3) cells for different drivers' ages. Attached is a file containing what I have come up with so far. However, I cannot get it to return a value in years, and to enter the value in the cell. Also, how do I get the prompt to work when I click on one of the Driver's Age cells? The procedure will run when I try it in VB Editor but returns a result of something like 68,754. Suspecting the result was the number of days difference between the date of birth and the current date ("Now") I divided that number by 365 and came up with 106 and a rather long decimal figure.

    Your assistance will be greatly appreciated.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Request help in learningVB (VB 6.3 function procedure)

    <P ID="edit" class=small>(Edited by JohnBF on 06-Feb-06 17:40. )</P>Learn about and turn on "Option Explicit" in the VBE under Tools | Options | Require variable declaration.

    Your code as posted is failing because the Inputbox return is placed in a variable called "getdob" which is never subsequently used. Change "getdob" to "dob".

    EDIT: you might also want to look into the Datediff VB6 function:

    age = DateDiff("yyyy", dob, Now)
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  4. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Request help in learningVB (VB 6.3 function procedure)

    I appreciate that you want to learn VBA, but one aspect of that is to learn when to use VBA and when not to. Worksheet functions are one of the strengths of Excel, and they are much more convenient for what you want than VBA.
    Say that the user enters a driver's date of birth in cell B2. The following formula will calculate the driver's age in years:

    =DATEDIF(B2,TODAY(),"y")

    For what it's worth, here is a function to calculate the difference in whole years between two dates:

    Function AgeYears(d1 As Date, d2 As Date) As Integer
    AgeYears = Year(d2) - Year(d1) + (Month(d2) < Month(d1) Or _
    (Month(d2) = Month(d1) And Day(d2) < Day(d1)))
    End Function

    More complicated, as you see. You could use it as follows:
    Right-click the sheet tab and select View Code from the popup menu.
    Select Worksheet from the dropdown list in the upper left of the module window.
    Make the code look like this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oCell As Range
    Dim dob As String
    If Not Intersect(Target, Range("B2:B4")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("B2:B4"))
    dob = InputBox("Enter date of birth")
    If IsDate(dob) Then
    oCell = AgeYears(CDate(dob), Date)
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

    Change B2:B4 to the range of 3 cells where you want the drivers' ages.
    All this for something that Excel can do with a simple formula!

  5. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Request help in learningVB (VB 6.3 function procedure)

    Thank you very much for the info. I'll give it a try. I thought the "get" portion of that statement was part of the syntax (I read it in one of the books I've been using). Thank you for setting me straight.

  6. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Request help in learningVB (VB 6.3 function procedure)

    Thank you for replying to my post. I was under the impression that the language used in macros/function statements was, or was close to, VB/VBA, and so I'd be well served by learning at least a little VB/VBA. It seems that such may not be the case. Your examples certainly drive home your point about the ease of Excel functions vs. developing routines in VB/VBA.

    As an aside, I have looked into the DateDif statement in the past but kept getting stuck on how to prompt users for the input (drivers' dates of birth). I will definitely re-visit Excel functions.

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Request help in learningVB (VB 6.3 function procedure)

    > I was under the impression that the language used in macros/function statements was, or was close to, VB/VBA

    Yes, VBA is the macro language used in all Office applications.

  8. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Request help in learningVB (VB 6.3 function procedure)

    A quick note - 1. the Datediff() function in VBA has different syntax (and different spelling), than the Excel Datedif() function. Go figure! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> 2. You might also want to check out the Isdate() VB function to use to test that a valid date has been entered.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  9. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Request help in learningVB (VB 6.3 function procedure)

    Just to add <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth to what Hans said... while VB/VBA is the language of "external" macros and functions, the built-in worksheet functions in Excel are much more efficient than running "custom" VBA macros. In essence, if you can do it with a built-in function, then choose that option in preference to a VBA/macro routine. A telling example is illustrated in Excel VBA Loops: Correct/Efficient Uses of Excel Loops. Do, For Each and While Loops.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •