In VBA, the best way to debug a user defined function (UDF) is by writing a temporary Sub procedure that calls your function and then step into the Sub procedure by pressing F8 key.
Example :
User Defined Function (UDF)
Let's say you have the following function. This function calculates a grade based on the values of these scores. It takes two parameters: "score" and "score1".
Function grade(score As Single, score1 As Single) As String If score >= 75 And score1 >= 75 Then grade = "A" ElseIf score > 50 And score1 > 50 Then grade = "B" Else grade = "C" End If End Function
Step 1: Temporary Sub Procedure
You create a Sub procedure within the same module where your function is defined. This Sub procedure will call your UDF with specific inputs. By using a Sub procedure, you can easily run and debug your function.
Sub test()
MsgBox grade(Range("A2").Value, Range("B2").Value)
End Sub
Step 2: Step Into (F8 Key)
Position your cursor before Sub test() and then press F8 key to step into the Sub Procedure.
Perfect and simple solution. Thanks. Gary
ReplyDeleteThanks - this was helpful!
ReplyDeleteThanks. It works.
ReplyDeleteStepping into my function as described works, but calling it from the spreadsheet throws a #Name? error. Confused on why it would work if I step into it but not when I call it from a cell?
ReplyDelete