UDF Error Output

[dan_l]

I need a good way to achieve this.


I've got a UDF that looks at a cell and evaluates the parts of the string against a couple of vlookup tables. Under good circumstances, the results of the look up are run through a formula. In the event that one or more of the parts of the string is bad, I'd like the output to instead tell the user which parts are bad.


Here's the idea. This isn't even the 'live' code, it's just a paired down example I'm screwing with:

[pre]

Dim opt1, opt2, opt3, opt4 As String
Dim c1, c2, c3, c4 As Integer

opt1 = Left(sim, 2)
opt2 = Mid(sim, 3, 2)
opt3 = Mid(sim, 5, 2)
opt4 = Right(sim, 2)

c1 = Application.WorksheetFunction.VLookup(opt1, [comp1], 2, False)
c2 = Application.WorksheetFunction.VLookup(opt2, [comp2], 2, False)
c3 = Application.WorksheetFunction.VLookup(opt3, [comp3], 2, False)
c4 = Application.WorksheetFunction.VLookup(opt4, [comp4], 2, False)

simresult = c1+c2+c3+c4

I think maybe I could create a variable called string and do it with if/then


so 


dim error as string
dim errorcount as int

error = ""
errorcount = 0
if c1 = "" then
error = switch(errorcount = 0, error & "Error 1",errorcount=>1,error & "/" & "Error 1")
end if
if c2 = "" then
error = switch(errorcount = 0, error & "Error 2",errorcount=>1,error & "/" & "Error 2")
end if

[/pre]
So on/so forth. But what would be the best way to do this?

 

[NARAYANK991]

Hi ,


My opinion is that a UDF which tries to do too many functions is not a good construct. Ideally any function should do one thing , do that one thing completely , and do it well.


Since you can nest functions , having multiple functions in one formula is not an issue.


In your case , if the function is doing a split of the input string into its component parts , and those component parts are being used to lookup into several tables , wouldn't it be better to combine the split and lookup of each individual component together into one function ? With this combination , you would have 4 different functions , each of which could have its own error handlers.


Speaking of error handlers , the errors could come at the time of the the split or at the time of lookup ; splitting the input string into 4 parts presupposes that the input string is going to be the right length or that it will have valid characters in all its individual parts ; is this a valid assumption ?


Errors at the time of lookup need to be handled differently , but do you intend taking simresult = c1+c2+c3+c4 before checking for errors or after ?


Narayan

 

[dan_l]

Hmm.


Are you saying break up the UDF's? Or are you saying just go break out the mega formula?

 

[NARAYANK991]

Hi ,


I suggest that you have 4 UDFs ; one clubbing together opt1 and c1 ; the second clubbing together opt2 and c2 , and so on.


The error handling in each UDF would be similar ; opt1 , opt2 ,... would probably have to be checked for not being blank ( in case the input string does not have the required number of characters ) and also for containing valid characters ( probably only alphabets or numbers or alphanumeric ... ).


Once the lookup values opt1 , opt2 ,... are validated , then the error handling can check for the VLOOKUP return value.


Once all the 4 UDFs return valid values , your main procedure can do the combination :


simresult = c1+c2+c3+c4


and the rest of the processing.


Narayan

 

[dan_l]

So would i have to do all the error handling in the top level function?

[pre]

Dim opt1, opt2, opt3, opt4 As String
Dim c1, c2, c3, c4 As Integer

opt1 = Left(sim, 2)
opt2 = Mid(sim, 3, 2)
opt3 = Mid(sim, 5, 2)
opt4 = Right(sim, 2)

c1 = to comp1calc(opt1)

simresult = c1+c2+c3+c4
function comp1calc(opt1 as string)
dim somevariable as integer

somevariable = vlookup(yada,yada,yada)

if somevariable = 0 then
comp1calc = "Error 1"
else comp1calc = somevariable
end if
end function

[/pre]