A little bit different tutorial on how I build UDF's

I will start by mentioning this help file https://support.office.com/en-us/art...2-B6748AE8231F as a decent introduction to user-defined functions, because this is probably not going to be a complete tutorial. I have wondered if beginners to UDF's could use a different introduction to strategies for building UDF's that goes beyond the usual "here's what a UDF here's -- here are some examples -- now go to it."

One definition -- I am using the term User-Defined-Function (UDF) here to refer to VBA function procedures that are intended to be called from a spreadsheet cell.

Some of the old hands will probably scratch their head, but I will often start a UDF with something mind numbingly simple:

Please Login or Register  to view this content.

Then go into the spreadsheet, enter =quadratic(), and I should get 5 back. As nearly meaningless as this is, I often start a UDF this way just to get things started. Especially as a beginner, this allowed me to see and test how to get a result from the UDF back to the spreadsheet. If something doesn't work, I can work on debugging why I cannot call a simple UDF and get the expected answer (5) back.

The next step will be to add an argument to the function, so I can make certain I can feed input into the function. UDF's should not interact with the spreadsheet directly. Any information they need should come through the argument list, and result should be sent back to the spreadsheet through the function name. Usually at this stage, I will have the function parrot back the input, just to see that it works.

I also find that this is a good time to start practicing debugging strategies, so I will either set a breakpoint on the function statement, so VBA will enter debug mode as soon as the function is executed, or put a Stop statement at the top of the function. This is important when coding UDF's because it is difficult to execute a UDF directly from the VBE the way one might initiate a Sub procedure. The Debug->step into command does not have a way to specify parameters for the arguments, so this strategy for initiating the function fails. Also, since one expect to call a UDF from a spreadsheet, calling it from the spreadsheet while developing and debugging allows that element to be part of your debugging.

Please Login or Register  to view this content.

I will have to edit the function call to reflect the argument =quadratic(5) and then, maybe, =quadratic(A2) where I enter 5 into A2 (since I will almost always use other cells to hold the parameters I want to pass to the UDF). Then, when VBA goes into debug mode with the stop statement highlighted, I check the locals window to make sure it is visible and that all the expected variables (at this point a and quadratic) are present. I can step through the function with F8 and follow everything, making sure the function executes correctly.

As simple as these two steps are, I find them important. From those earliest computer science classes in middle school, computer programs/procedures perform three operations -- input, output, and processing. These first two steps allow me to establish and test the essential input and output parts of the UDF.

Having established that I can send data to the UDF and receive the result from the UDF, then I can start worrying about the more interesting processing part. Let's say I want a UDF to calculate the quadratic formula (I hope you all remember this from your algebra classes. If you have forgotten: http://purplemath.com/modules/quadform.htm ). Having established that I can send input to the function and get output from the function, here's how I would proceed:
1) I will start by setting up three arguments for my function.
2) Compute the discriminant and store it in a variable.
3) Test the discriminant to see if there are 2, 1, or 0 real roots.
a) If I see that there are 0 real roots, I will either have the function return an error, or I will have a branch of the code ready to handle the computation in complex number space.
b) Decide which root I should return
4) Compute the root from the quadratic formula
5) Send result to Excel and end.
6) At any point in this development, I may choose to stop code, set the function name to the most recent result, then execute the function to test and debug what I have done to that point. One intermediate point might look like:

Please Login or Register  to view this content.

Stopping coding at this point and going through a "debug" phase, I can test the decision tree here with different values of a b and c and make sure that the decision tree is working as I expect it to, before going on with the function.

The final function may look something like

Please Login or Register  to view this content.

Before deleting the stop statement, I will step through the function for several different inputs to make sure the function seems to be working correctly. I will try to have these test cases cover a wide range of scenarios. What happens if I send a problem where the discriminant is less than 0? What happens if I send 0 for a? What happens if I send non-numeric data? Are there other scenarios I need to test and make sure the function handles them correctly?

If I wanted to further develop the function, I might include an optional argument for choosing which root to return [(-b+sqr(disc)) or (-b-sqr(disc)]. As I noted, I could also include algorithms to handle the computation using complex numbers when disc<0. I could also have more involved error checking. At this point, I will also go back and dimension variables that I have not yet dimensioned (such as disc). At somewhere along the way, after I am satisfied that the function works, I will clear breakpoints and delete stop statements. At that point, I will hopefully be comfortable stating that the UDF is ready for normal use.

Basically, the strategy is to start with something very simple (for the beginning beginner, that might be something like what I started with here), then gradually make the procedure more complex. Stopping to check along the way to see how everything is working and debugging problems as they crop up. My hope is that this little essay will help someone getting started with coding UDF's to see some basic, strategies for creating a UDF.

thanks for sharing!