Learn how to debug custom functions in Excel

As you already know how to create UDFs (and, I hope, you’ve also tried applying them in your Excel), let’s dig a bit deeper and see what can be done in case your user defined function is not working.

In order to solve some issues when creating a custom function, you will most likely need to run a debug. Then you can be sure that the function is working correctly.

We will explore the following debugging techniques:

• Place the MsgBox function in important places
• Determine stopping points and perform step by step
• Debugging using the Debug.Print operator
• Call a function from a procedure

When you create a custom function, there is always a possibility that you will make a mistake. Custom functions are usually quite complex. And they don't always start working correctly right away. The formula may return an incorrect result or the #VALUE! error. Unlike standard Excel functions, you will not see any other messages.

Is there a way to go through a custom function step by step to check how each of its statements works? Sure! Debugging is used for this.

I will offer you several ways to debug your custom function so that you can choose the one that works for you.

As an example, we use the custom function GetMaxBetween from one of our previous articles which calculates the maximum number in the specified range of values:

Function GetMaxBetween(rngCells As Range, MinNum, MaxNum) Dim NumRange As Range Dim vMax Dim arrNums() Dim i As Integer ReDim arrNums(rngCells.Count) For Each NumRange In rngCells vMax = NumRange Select Case vMax Case MinNum 0.01 To MaxNum - 0.01 arrNums(i) = vMax i = i 1 Case Else GetMaxBetween = 0 End Select Next NumRange GetMaxBetween = WorksheetFunction.Max(arrNums) End Function

The function arguments are the range of cells where numbers are written, as well as the upper and lower limit of values.

Place the MsgBox function in important places

In order to monitor the execution of calculations, you can display the values of the most important variables on the screen in the right places. This can be done using pop-up dialog boxes.

MsgBox is a dialog box that you can use to show some kind of message to the user.

MsgBox’s syntax is similar to other VBA functions:

MsgBox(prompt [, buttons] [, title] [, helpfile, context])

prompt is a required argument. It contains the message that you see in the dialog box. It can also be used to display the values ​​of individual variables.

All the other arguments are optional.

[buttons] - determines which buttons and icons are displayed in the MsgBox. For example, if we use the option vbOkOnly, then only the OK button will be displayed. Even if you missed this argument, this button is used by default.

[title] - here you can specify the title of the message box.

Let's switch from words to practice and start debugging. To display the message, add the following line to the code of the GetMaxBetween user-defined function before the Case Else operator:

MsgBox vMax,, "Count -" & i

Here is what we’ll get in the result:

Function GetMaxBetween(rngCells As Range, MinNum, MaxNum) Dim NumRange As Range Dim vMax Dim arrNums() Dim i As Integer ReDim arrNums(rngCells.Count) For Each NumRange In rngCells vMax = NumRange Select Case vMax Case MinNum 0.01 To MaxNum - 0.01 arrNums(i) = vMax i = i 1 MsgBox vMax,, "Count -" & i Case Else GetMaxBetween = 0 End Select Next NumRange GetMaxBetween = WorksheetFunction.Max(arrNums) End Function

Using the vMax variable in the dialog box, we will see which numbers meet the criteria for selection, so that we can select the largest of them. With the expression "Count -" & I in the title bar, we indicate how many numbers we have already selected to determine the maximum value. The counter will be increased with each new value.

Once we have our UDF set, we apply the formula below to the date range:

= GetMaxBetween (A1:A6,10,50)

After the Enter button is pressed, you will see a message as in the screenshot below:

This is the first number in the range A1: A6 that meets the criteria: greater than 10 but less than 50.

After you click OK, a second message appears with the number 14. The rest of the numbers do not match the selection criteria. Therefore, the function exits and returns the largest of the two values, 17.

The MsgBox function can be used in the most important places in your custom function to control how the values ​​of individual variables change. Message boxes can be very useful when you have a large function and a lot of computation. In this case, it will be easy for you to determine in which part of the code the error occurs.

Determine stopping points and perform step by step

You can add breakpoints to the code of your function where the code execution will stop. So you can follow the calculation process step by step. In doing so, you can see how the values ​​of the variables change.

To add a breakpoint, place the cursor on the line containing the statement where you choose to pause. Then right-click and select Debug -> Toggle Breakpoint or just press F9. You can also click in the desired place on the vertical gray area to the left of the function code.

A red circle will appear, as you can see in the screenshot below. The line of code where the calculation will be stopped is highlighted in red.

Now, the VBA editor window will be opened when the function is running. The cursor will be positioned at the point where you stopped.

If you hover your mouse cursor over any of the variables in the function code, you can see their current value:

Press F5 to continue the calculation.

Note. After the breakpoint, you can start tracking the progress of the calculations step by step. If you press the F8 button, only one next line of the VBA code will be executed. The yellow line with an arrow will also move to the last executed code position.

Since the execution of the function is paused again, you can view the current values ​​of all the variables of the function using the mouse cursor.

The next press of F8 will take us one step forward. So you can press F8 till the end of the calculation. Or press F5 to continue the calculation until the next breakpoint.

If an error occurs, the cursor will be stopped at the point in the code where the error occurred. And you will also see a pop-up error message. This makes it easy to determine the cause of the problem.

The breakpoints you specify will be applied until you close the file. When you reopen it, you will need to set them again. Not the most convenient method, don’t you think?

However, this problem can be solved. Insert a Stop statement into the function code at the necessary points, and you can stop the program execution in the same way as when using breakpoints.

When VBA encounters a Stop statement, it will stop program execution and wait for your action. Check the values ​​of the variables, then press F5 to continue.

Or press F8 to fulfill the function step-by-step as described above.

The Stop statement is part of the program and therefore is not deleted, as is the case with a breakpoint. When you're done debugging, remove it yourself. Or turn it into a comment by preceding it with a single quote (').

Debugging using the Debug.Print operator

You can place the Debug.Print in the function code in the right place. This is useful for checking the values ​​of variables that are cyclically changing.

You can see an example of Debug.Print’s performance on the screenshot below.

Statement Debug.Print i, vMax prints values and their ordinal numbers.

In the Immediate window you see two numbers (17 and 14) from the selected range, which corresponds to the set limits and among which the maximum will be selected. Digits 1 and 2 mean that the function has completed 2 cycles in which the numbers were selected. We see the values ​​of the most important variables, as we did earlier with MsgBox. But this did not stop the function.

Call a function from a procedure

You can call a user defined function not from a cell in the worksheet, but from a procedure. In this case, all errors will be shown in the Visual Basic Editor window.

Here is how you can call the user-defined function GetMaxBerween from a procedure:

Sub Test() Dim x x = GetMaxBetween(Range ("A1:A6"), 10, 50) MsgBox(x) End Sub

Position the cursor anywhere in the code and press F5. If there is no error in the function, you will see a pop-up window with the calculation result.

In case of an error, you will see a corresponding message in the VBA editor. The calculation will be stopped and the line of code in which the error occurred will be highlighted in yellow. You can easily identify where and why the error occurred.

That's all. Now you have created your own add-in, added it to Excel and you can use the UDF in it. If you want to use more UDFs, just write the code in the add-in module in the VBA editor and save it.

That's it for today. We've covered different ways to debug custom functions and learned how to use them in your workbook. We really hope you find these guidelines helpful. If you have any questions, write in the comments to this article.

The above is the detailed content of Learn how to debug custom functions in Excel. For more information, please follow other related articles on the PHP Chinese website!