Last year, in some desperation, I asked for tips debugging Excel VBA functions (as opposed to procedures). To the main thrust of my question, I received no replies (makes me wonder about Woody constantly plugging how helpful people here are).
Well, last weekend, I sat down and spent an afternoon on my problem, and I actually managed to find some answers. I thought I'd share these with everyone here, in case anyone was having the same problems as me. Naturally, if I've made any mistakes, corrections are welcome.
Tips for Debugging Excel VBA Functions
Debugging Excel VBA Functions is quite different from debugging other types of VB/VBA code. Basically, this difference can be traced to one thing: data types. Why? Because you have no control over what types Excel delivers to the function as parameters, nor what it expects the return value to be.
Here's my suggestions how to deal with this:
1. Create a VBA procedure that calls your function with a set of fixed parameters. Don't worry about displaying the return value. This procedure will be used to debug the internal workings of the function.
2. Use the function in one cell in your workbook, with the parameters you want it to deal with. Don't create more than one (at least, not too many more), as they'll all stop at any breakpoints, in turn. This cell will be used to debug the passing of data to and from the function from a worksheet.
3. Open the Locals window, and keep it open.
4. Set a breakpoint near the start of your function. Move this around as you require.
5. Run the procedure you created to test the function. Use F8 to step through the function from your breakpoint.
6. Once it's working the way you want, go to your worksheet and reevaluate the function (this can be done a number of ways, such as F2 followed by Enter). See if the parameters and return value still work. If not, it's probably a data type conflict.
The idea is to watch the types of variables in the Locals window and see if they match what you're expecting. If not, you may need to change how you use data or force it to another data type (type conversion functions in Help). Variants can sometimes be tricky, but look particularly at the Value field. For Ranges, you should be able to dig down to an array of the individual values, though I don't think there's any property explicitly stating the extents of the range as cell locations.
BONUS: For those who have read this far, I figured out how to return an array from a user defined function. Simply build the array, then assign it to the return variable, ensuring its type is specified as Variant (if at all). Talk about easy! In the worksheet, you can then create a multi-cell array formula which will show the first array element in the first cell, the second array element in the second cell, etc. Extremely useful, and quite undocumented (AFAIK).