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:
VB:
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
'Initially we need to determine the rate plan that was selected.
Dim rate_str As String
rate_str = "Constitution_" & replace_spaces(Trim(rate_plan), "_")
'Payment method factor.
Dim pay_factor As Double
Select Case pay_method
Case "Annually"
pay_factor = 1
Case "SemiAnnually"
pay_factor = 0.52
Case "Quarterly"
pay_factor = 0.265
Case "Monthly"
pay_factor = 0.085
End Select
'Determine gender pay factor.
rate_str = rate_str & "_" & Gender
'Determine tobacco factor.
rate_str = rate_str & "_" & Tobacco
'Determine the area factor.
Dim Area_range1 As Range, Area_range2 As Range
Set Area_range1 = Constitution.Range("Constitution_Area_1")
'Set Area_range2 = Constitution.Range("Constitution_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
Else: rate_str = rate_str & "_" & 2
End If
'Now we find the plan.
Dim current_plan As Range
Set current_plan = Constitution.Range(rate_str)
'Now we need to find the rate according to the age and the factors found above.
Dim age_row As Long
If Age <= 64 Then
age_row = 1
Else: age_row = Age_search(Constitution.Range("Constitution_Age"), Age)
End If
Constitution_RATE_PLAN = current_plan.Cells(age_row, 1) * pay_factor
End Function
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
On the main sheet the result is found using this code:
VB:
=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))
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
- 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 can't attach the workbook with the main page as it's over 500k & too large, so I've uploaded a copy of the Constitution sheet & another sheet that is working correctly. I think this is where the error is happening rather than the main page so 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.
