# Need help debugging user-defined function in VBA

• 2012-11-02, 07:42
CAV
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
' 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
• 2012-11-02, 08:22
RetiredGeek
Cav,

Welcome to the Lounge as a new poster! :cheers:

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. :cheers:
• 2012-11-02, 09:01
CAV
That was it!...you rock...thank you very much
• 2012-11-03, 02:18
Paul T
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