Excel VBA : How to debug a user defined function (UDF)

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 :

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"
         grade = "C"
         End If
End Function
Temporary Sub Procedure -

Sub test()
MsgBox grade(Range("A2").Value, Range("B2").Value)
End Sub

Next Step : Position your cursor before Sub test() and then press F8 key to step into the Sub Procedure.
Related Posts
About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 8 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Telecom and Human Resource.

4 Responses to "Excel VBA : How to debug a user defined function (UDF)"
  1. Perfect and simple solution. Thanks. Gary

  2. Thanks - this was helpful!

  3. Stepping 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?


Next → ← Prev
Love this Post? Spread the Word!