Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2012
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Need help debugging user-defined function in VBA

    I need help debugging the following function. I am a beginner and fear I am making an obvious mistake. Nothing I read online is helpful. What would you recommend? Recommendations on how to run the debugger are welcome...

    Thx

    Function Vest(HrsRng As Range, VReq, VYrDef, BkYrDef, PermBkDef) As Integer
    ' Read in:
    ' HrsRng is a column of cells listing annual hours per Plan Year
    ' VReq is the vesting service requirement in years to become first vested: e.g. "5" ("3" if using graded vesting)
    ' VYrDef is the hours requirement in a year to get a year of vesting: e.g. 1000
    ' BkYrDef is the hours threshold for a break year: e.g. 500
    ' PermBkDef is the requirement for a permanent break in years: e.g. 5
    '
    ' Used in function
    ' VYr is tallying Vesting service
    ' BkYr is tallying Break Years

    Dim i As Integer
    Dim VYr As Double
    Dim BkYr As Double

    'initialize variables
    VYr = 0
    BkYr = 0
    Vest = 0

    'Tally Vesting and Break buckets, test for vesting and permanent break
    For i = 1 To HrsRng.Rows.Count

    'Don't start tallying until member has established participation (as defined by working more than your BkYrDef)
    If (HrsRng.Cells(i, 1) < BkYrDef) Then GoTo LastLine

    'Else start tallying
    If (HrsRng.Cells(i, 1) >= VYrDef) Then Yr = VYr + 1
    If (HrsRng.Cells(i, 1) < BkYrDef) Then BkYr = BkYr + 1
    If (BkYr >= PermBkDef) Then VYr = 0
    If (VYr >= VReq) Then Vest = 1

    LastLine:
    Next i

    End Function

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,498
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Cav,

    Welcome to the Lounge as a new poster!

    The obvious mistake I can see is in this line: If (HrsRng.Cells(i, 1) >= VYrDef) Then Yr = VYr + 1
    Which should read: If (HrsRng.Cells(i, 1) >= VYrDef) Then VYr = VYr + 1.

    Some general suggestions.

    1. Set Options Explicit at the top of your module. This would have caught this error for you.
    2. Declare all variables including thise in your function definition, e.g. BkYrDef as Integer, etc.
    3. Use coding standards to identify variable type, e.g. change BkYrDef to iBkYrDef to idicate it is an integer.

    Also, in complex formulas like this it is very useful to post a workbook file with sample data and expected results for us to work with in solving the problem.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    CAV (2012-11-02)

  4. #3
    New Lounger
    Join Date
    Nov 2012
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That was it!...you rock...thank you very much

  5. #4
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,846
    Thanks
    7
    Thanked 253 Times in 238 Posts
    From a programming perspective you shouldn't use a "GoTo". It makes code harder to read and can make it difficult to debug. You should also use indenting to visually break the sections up. Then it's easy to see what happens after a particular "if" statement etc.
    Something like this:
    Code:
    For i = 1 To HrsRng.Rows.Count
    	If (HrsRng.Cells(i, 1) >= BkYrDef) Then
    		'start tallying
    		If (HrsRng.Cells(i, 1) >= VYrDef) Then VYr = VYr + 1
    		If (HrsRng.Cells(i, 1) < BkYrDef) Then BkYr = BkYr + 1
    		If (BkYr >= PermBkDef) Then VYr = 0
    		If (VYr >= VReq) Then Vest = 1
    	End If
    Next i
    cheers, Paul

Posting Permissions

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