Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2012
    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...


    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

    Next i

    End Function

  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 RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 781 Times in 715 Posts

    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!


    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs

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

    CAV (2012-11-02)

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

  6. #4
    Platinum Lounger
    Join Date
    Dec 2009
    Thanked 231 Times in 219 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:
    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