Debugging Excel Functions

[PStepanas]

Dear all,

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).

Happy programming,

Paulius

[Gary Frieder]

Hi Paulius,

Thanks for sharing this interesting work.

The fact that no one previously answered your question was probably due more to the fact that no one here had a useful answer, than anything else. Maybe Woody needs to make available some "I Stumped the Lounge" T Shirts we could give out.<g>

Glad you were able dig up some information that no one else could, and have shared it - everybody benefits.

Regards,
Gary

[chrisgreaves]

I echo Gary's comments.

I am amazed at how often I can ask a question that has stumped me and get an answer. You may have hit on the one-in-a-hundred stumper staright off. (That's a hidden invitation to post more queries).

[LegareColeman]

Last year was the old lounge, and there were not nearly as many helpful people around. I don't think you will have the same experience here in the NEW lounge. My experience is that there are two prime reasons for questions not getting answered. One is simply that no one knows the answer. The second is that the person asking the question phrased it in too general a way. For example, someone might ask, "Can someone tell me how to debug a VBA function." That question could easily be the basis for a book, and can not be easily answered in a venue like the forums. I don't remember seeing your original question, and all of the archives from the old forum were lost in the transition, so I can't guess why you didn't get an answer. However, if you read through the threads here, I think you will see that the vast majority of questions at least get someone to try to help.

You have some good information in your message. However, I have to disagree with a couple of things that you say. You do have complete control over what type of data is delivered to your function and you also have complete control over what type it returns. Here is an example:

<pre>Public Function GetTodayPlus(iDays As Integer) As String
GetTodayPlus = Format(Date + iDays, "dd-mmm-yyyy")
End Function
</pre>


In that function, the type of the parameter iDays is guaranteed to be an integer, and the return value is guaranteed to be a string. If you don't specify the type you want, then Excel will default to variant, in which case you have chosen to give up control over the types.

[PStepanas]

Thanks for the responses. They were certainly encouraging, and make me think I should repost some of my Outlook queries. I particularly liked the idea of an "I Stumped the Lounge" t-shirt, not so much for the article of clothing, rather because of the positive, helpful attitude it reflects. Thanks for restoring my confidence in the Lounge.

LegareColeman wrote:
>You have some good information in your message. However,
>I have to disagree with a couple of things that you say.
>You do have complete control over what type of data is
>delivered to your function and you also have complete
>control over what type it returns.

and proceeded with an example of using the As keyword to force the data type of parameters and return value.

I agree with you, as written. However, what actually happened to me was a bit more complicated than what I indicated.

I was actually using the Application.Match() and Offset() functions to pick a value out of a table. What I didn't realise was that Offset works differently in VBA and that I should have been using Cells(). So my return value was a Range rather than a String, and simply casting it to String didn't help. In fact, Excel had no problem with this when returning the value to the worksheet, as it simply truncated the Range to its first cell (the correct answer). But when I tried to take the result of this function and feed it back to the function (it was a recursive system spread across two functions), an error naturally occurred. This is what made it so confusing.

This is what I meant by not having control over Excel's data types. Basically, there are some things Excel does when returning a value to a worksheet (such as truncating to the first cell) and reading data from worksheets (such as reading all cell values as Strings in some cases), that are confusing to the novice.

The reason I now know what's going on is through discovering the Locals window, since all the types and intermediate values are shown.

It all seems so much simpler now!

I hope that the posting at the top of this thread might give any other novices the initial direction they need to get on top of this issue quickly.

Paulius