• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Debugging custom vba function

I

infinitedrifter

New Member
I'm having trouble with a custom function that pulls an amount from a worksheet based on factors entered on the main sheet (age, zip, etc). I've updated these on several different workbooks(we have one for each state) before with no problem but this week none of the ones I've added seem to work. I'm afraid I'm just making the same mistake in the coding on each one & being that I'm a beginner w/ VBA (more accustomed to using formulas) I just haven't found the error.


Here's an example of one of the function codes:


[pre]<br />
Function Constitution_RATE_PLAN(ByRef zip As Range, ByRef Gender As Range, ByRef Tobacco As Range, ByRef Age As Long, ByRef pay_method As Range, ByRef rate_plan As Range) As Double </p>
<p> 'Initially we need to determine the rate plan that was selected.<br />
Dim rate_str As String </p>
<p> rate_str = "Constitution_" & replace_spaces(Trim(rate_plan), "_") </p>
<p> 'Payment method factor.<br />
Dim pay_factor As Double </p>
<p> Select Case pay_method<br />
Case "Annually"<br />
pay_factor = 1<br />
Case "SemiAnnually"<br />
pay_factor = 0.52<br />
Case "Quarterly"<br />
pay_factor = 0.265<br />
Case "Monthly"<br />
pay_factor = 0.085<br />
End Select </p>
<p> 'Determine gender pay factor.<br />
rate_str = rate_str & "_" & Gender </p>
<p> 'Determine tobacco factor.<br />
rate_str = rate_str & "_" & Tobacco </p>
<p> 'Determine the area factor.<br />
Dim Area_range1 As Range, Area_range2 As Range </p>
<p> Set Area_range1 = Constitution.Range("Constitution_Area_1")<br />
'Set Area_range2 = Constitution.Range("Constitution_Area_2")</p>
<p> 'Now we search the range for the appropriate column.<br />
If factor_search(Area_range1, CLng(Left(Trim(zip), 3))) Then<br />
rate_str = rate_str & "_" & 1<br />
Else: rate_str = rate_str & "_" & 2<br />
End If </p>
<p> 'Now we find the plan.<br />
Dim current_plan As Range<br />
Set current_plan = Constitution.Range(rate_str)<br />
'Now we need to find the rate according to the age and the factors found above.<br />
Dim age_row As Long </p>
<p> If Age <= 64 Then<br />
age_row = 1<br />
Else: age_row = Age_search(Constitution.Range("Constitution_Age"), Age)<br />
End If </p>
<p> Constitution_RATE_PLAN = current_plan.Cells(age_row, 1) * pay_factor </p>
<p>End Function<br />



On the main sheet the result is found using this code:


<br />
=If(ISERROR(Constitution_RATE_PLAN(Household_Discounts, Zip_Code,Gender,Tobacco,Age,Payment_Method,Plan_Option1)),"N/A",uOO_RATE_PLAN(Household_Discounts, Zip_Code,Gender,Tobacco,Age,Payment_Method,Plan_Option1))<br />
[/pre]


Uploaded here: https://docs.google.com/leaf?id=0B9_U_YkSNyAtYjUxMzc4ZTAtNWZhOS00MTA4LTg2YzYtYTRlZGQxMTg1YTYz&hl=en_US

- The (Household_Discounts, Zip_Code,Gender,Tobacco,Age,Payment_Method,Plan_Option1) parts are all in working order because they return correct results for the already existing functions (we have 1 for each company/spreadsheet, i.e. instead of "Constitution_Rate_Plan" it's "AARP_Rate_Plan")

- The Constitution spreadsheet is organized with named ranges which allow the Function to search for the variables entered on the first sheet & return the correct rate. I suspect this is where the error is happening, somewhere in the Define Area or Age portion but I haven't been able to find a solution. I hope this will reveal to someone smarter than I what I'm doing wrong.


- I know IFERROR is more efficient but the original code was created by someone else, probably in an earlier version of Excel & I haven't gotten around to cleaning it up.


Thanks for any insight you might be able to provide. I think if I stare at this any more my brain will explode.
 
Hey, so I downloaded the file your linked to and I don't see any problems on my side. Could you be more specific on the errors that are popping up?
 
It just returns N/A on the main sheet (the proposal page). It should return the applicable rate based on age/zip/etc like the other functions.
 
Hi infinitedrifter,

One problem seems to be in the factor_search() function called by the above code. When the above code calls factor_search, code execution never returns fro the factor_search call! Possibly due to the code generating an error and something in all the workbook code preventing an error dialog from pooing up and alerting to the error.


The other place where there may be a problem is in the text of the AREA 1 RATES on the Constitution worksheet, which is the text that factor_search is comparing to. That text looks perfectly reasonable, however the difference with Constitution Life is that you are using just the first 3 digits of the zip code for comparison. If you type "30000-39999" into that cell, your worksheet calculates values for constitution Plan F -- only Plan G shows N/A.


As far as debugging strategy goes,

Use the "Watches" pane to monitor the value of variables, and step through your code while it's executing. You can do that by setting a breakpoint (F9), then use the option on the Debug menu, right click menu, or their shortcut keys (listed on the debug menu) to step through your code one line at a time, or to "run to cursor" and stop at another point in your code. You can see if your Watches are behaving as expected... and when stepping one line at a time, easily see if your If/Then constructs and the like are causing the lines you expect to execute. In the "Watches" pane, besides just monitor varibales, you can monitor calculated expresssions, and you can set it to automatically stop code execution when a certain expression is true.


It took me a bit in the case of your workbook to figure out what was happening, since it is totally unexpected for code execution to stop without returning from a function call... I knew something strange was up when I set a breakpoint after that call in your procedure, and execution never "breaked" there... indicating code execution never reached that point.


At first look, I didn't isolate what might be causing the other function to fail, although maybe it's logic may not be designed for 3-digit number handling somewhere.


If I notice anything else, I'll let you know.


Asa
 
Hi again,

ok, found the culprit.


The problem is not the code -- but the data in the AREA 1 RATES cell on the Constitution sheet (Constitution_Area_1 range).

It contains:

300‐303, 305, 311, 399
Click to expand...

which looks fine, but the dash between "300" and "303" is not a normal minus sign/dash. I didn't track down exactly what character it is.. perhaps a unicode short dash or something.. in any case, the factor_search() function searches for a regular minus sign, and doesn't find it.


Editing the cell in question and typing the dash/minus over again fixes your formula on my machine.


The line in the code that raises an error and causes the function to fail (along with the Constitution_RATE_PLAN function) is:

[pre]
Code: 
If zip = CLng(str1(i)) Then
[/pre]
because CLng() will raise a Type Mismatch error if the variable passed to it cannot be converted to a long integer, or in this case, to any number. str1(i) contains "300-303" since the "-" was never parsed properly due to the strange character representation.


Asa
 
Asa:

First, thank you a million times for confirming I'm not crazy. I've been tweaking that code for a week & I was sure it looked okay. I can't believe something as simple as a dash is responsible for nearly driving me insane! The info on the Constitution sheet is usually copied or exported from a pdf so it completely makes sense that it would be some weird character.


Second, thanks for the tips on debugging. I've been trying to set up breakpoints & step through the code to figure out where the error was but I couldn't really find any good instructions online (and it's not as easy as F9 debugging a regular formula like I'm used to). I have another sheet that is returning N/A & the dash doesn't seem to be the culprit so your tips will definitely help me (hopefully) find the error myself.


I can't thank you enough.
 
I hope you will do me the favor of giving me another tip on debugging...

I set up some watches (any command that was rate_str = ) on another similar file & experimented with setting the breakpoint. When I set it at

Code: 
rate_str = rate_str & "_" & Tobacco

the watch values are False (no matter what the actual value is, i.e. tobacco is false whether you select yes or no, same for gender) except for age_row which is "Object required" - assume because it hasn't yet executed due to the breakpoint.


If I *don't* set a breakpoint or set it after

Set Area_range1 = United.Range("United_Area_1")

the watch values are all "Out of Context".


So I just want to make sure I'm interpreting these results correctly. Based on the change to Out of Context when I remove the breakpoint...this means something is going wrong again at the Area_range portion of the code? Shouldn't the value for rate_str & "_" & Tobacco return True if Tobacco is set to "yes"? If it *is* the area_range portion of the code that is going wrong, what would be the best way to get the value it is returning incorrectly since it doesn't seem to execute?


Thanks for any tips you might have; I really appreciate you taking the time to help me out.


Here's the file:

https://docs.google.com/leaf?id=0B9_U_YkSNyAtOWYwYjBiNjItZDQ2NC00ODc0LThjYzgtNmZkZjU1NzViODM3&hl=en_US
 
Hi infinitedrifter... My pleasure to help you. Your gratitude is reward enough.


I can't delve into the file right now (I can look at it later if you are still stuck), but based on what you've said, some comments:

rate_str = rate_str & "_" & Tobacco
Click to expand...

will never be True; it is a text string. It's value will be something like "F_Yes" (I don't recall what the expected values of rate_str and Tobacco really are, just guessing).


If you want to stop execution when Tobacco is Yes, use

[pre]
Code: 
Tobacco="Yes"
or to stop when rate_str = "F_Yes":

[pre][code]rate_str="F_Yes"
The watch values are "Out of context" when either the code is done executing (which could be due to an error causing execution to stop, or just normal completion), or some code is executing but the procedure that you set the watches in is not.  In this case it sounds like an error is likely, as you say.


I would set a breakpoint before where you suspect the problem is, then use F8 or the debug menu to step through your code one line at a time.  If code execution doesn't continue to the next line at some point, then there was an error.


I'm not very experience with UDF function used in cell formulas, so I was a little surprised by the behavior of not getting a popup message with the code error, but I guess that makes sense during worksheet calculation -- the error will just show up as an error in the cell.  It makes debugging a little harder though.


Another debugging tip is you can add lines to your code like this:

[pre][code]Debug.Print "message"
Debug.Print variable
[/pre]
Those lines will output text to the "Immediate" pane in the VB Editor, and can give you info about what is happening more quickly than all the code stepping sometimes.

For example, you could add lines that simply indicate where in the code it is, and you can tell then looking at the immediate window what the flow of execution was. You can also display the value of variables as the code executes and you can then review the history in the Immediate pane.


Another tip with Watches is you can right-click any variable, or highlight and right-click and formula or expression in your code and select "Add Watch".


Another handy thing is right-clicking any procedure name, object name, variable, or constant name hit "View Definition" to bring up the declaration or procedure into view.


While code execution is paused, due to a breakpoint or otherwise, you can enter visual basic commands into the Immediate pane and they are interpreted in the context of your executing code, just as if the lines had been inserted at that point in your procedure. You can check the values of variable and expressions right in the immediate pane, which for a one-time check can sometimes be faster than using Watches. Just use a command like:

Debug.Print rate_str[/code][/pre]
or the shorthand version:

? rate_str[/code][/pre]
You can also modify variables and see how your code responds to the changes.


Asa


Asa
 
Wow, this info is awesome.

Ok, using the immediate window `? rate_str' shows the correct value right up until

[pre]
Code: 
Dim Area_range1 As Range, Area_range2 As Range

Set Area_range1 = United.Range("United_Area_1")
Set Area_range2 = United.Range("United_Area_2")

'Now we search the range for the appropriate column.
If factor_search(Area_range1, CLng(Left(Trim(zip), 3))) Then
rate_str = rate_str & "_" & 1
ElseIf factor_search(Area_range2, CLng(Left(Trim(zip), 3))) Then
rate_str = rate_str & "_" & 2
Else: rate_str = rate_str & "_" & 3
End If[/pre]

(I have a breakpoint at  [code]Set Area_range1 = United.Range("United_Area_1")
)


The watch for United_Area_1 returns a value of "expression not defined in context" which according to what I could glean from a google search, can be an error or can be a normal message depending on how you've set up the watch module.


I'm still not great at stepping through the code w/ F8 and using it to pinpoint where the code is broken because it just skips back to the beginning of the first module. But when I go through either stepping into or stepping over it always stops at Set Area_range1 = United.Range("United_Area_1")[/code] & does not move forward to the next line...so this means that maybe "expression not defined in context" is an error and it has to do with how United_Area_1 is defined?
 
When it skips back to the beginning, I think that's where the code is broken.


It skips back because it errored out, and then the function was called again by the next cell in the worksheet for calculation.


F8 is "step into" which continues your code as usual, for one line. "Step over" skips the highlighted line and does not execute it.


If the Set line is failing, it could be due to that range not being defined, or the worksheet being referenced improperly.

In immediate window, try
Code: 
? United.Name to verify that [code]United is referencing your worksheet.  This style of reference is using the "Code name" for the worksheet instead of the name you see on the tab at the bottom of the worksheet view.  They are usually the same but not necessarily.  You could replace [code]United.Range in that command with [code]Worksheets("United").Range to use the displayed name of the worksheet.


If ? united.name
works, try ? united.range("United_Area_1").address[/code] to verify that the that the range part is finding that named range...


The watch for United_Area_1 won't work, because you can only watch expressions that are valid in VBA. A named range isn't valid in VBA -- not naked like that. You can watch united.range("United_Area_1")[/code] or Worksheets("United").Range("United_Area_1")[/code]
 
Amazing!
Code: 
? united.name
gave an error. The sheet was named "United National" with the object name as "United_National" so when I changed both to just "United" it worked.


Thanks a million not just for helping me solve this problem but teaching me what to look for so I can solve future issues. Seriously, the info you've provided is better than anything I've found after scouring the web.


THANK YOU!
 
Awesome! It's often easier in the moment to just give someone the exact solution they need in the moment, or on the other end, just have someone else solve your problem for you.. I'm glad I was able to teach you some new skills in this arena!

Asa
 
By the way, I see I mispoke and gave some misinfo earlier when I said

rate_str = rate_str & "_" & Tobacco
Click to expand...

will never be True; it is a text string. It's value will be something like "F_Yes" (I don't recall what the expected values of rate_str and Tobacco really are, just guessing).
Click to expand...

If you type
Code: 
rate_str = rate_str & "_" & Tobacco as a Watch expression, it will never be true, but not for the reason I listed.  It will never be true because nothing can be [i]itself[/i] plus something else.


If [code]rate_str was "F_Yes" then for that Watch expression to be true, [code]rate_str = "F_Yes_Yes" would have to be true while [code]rate_str = "F_Yes"
as established (evaluating each variable on the right side of that expression and typing the value that VBA would find when interpreting your expression).


rate_str = rate_str & "_" & Tobacco[/code]

is a perfectly valid declarative statement in code, since it is not a logical test like it is in the Watch window. The equivalent code would be:

debug.print rate_str = rate_str & "_" & Tobacco[/code]


the declarative statement is an abbreviated version of the traditional BASIC command

Let rate_str = rate_str & "_" & Tobacco[/code]

for setting a variable's value.


Asa
 
I fixed 5 sheets in like 1/2 an hour thanks to your info! After puzzling over the code for a week, it *was* a little vindicating to find that the error wasn't in my code but in the data itself.


Ok, that makes sense. I was confused that the watch value didn't change depending on whether was Tobacco was Yes or No. But I get what you mean about the logical test making it impossible to interpret as a watch.


I'm about to start learning VBA so I can convert a bunch of other spreadsheets that have a ton of formula calculations to hopefully speed up the calc rate & I feel like now I have a good foundation - at least to figure out why it's not working ;)


Thanks again!
 
You must log in or register to reply here.
Back
Top