We help IT Professionals succeed at work.

# Excel UDF - looking up result within a table in the code

on
I have successfully made an excel UDF (user defined function) and turned it into an add-in.  I have made it in what is probably a very crude way so was looking for help and suggestions to make it cleaner and more simple.
The UDF converts a score in a test into a result (a distance) based upon a lookup table.  I have made it work by simply stringing together approximately 100 If lines. Is there a way to create a table inside the module/function and have the UDF do a lookup within the table?

See code:

Function YoYoL1Distance(Level As Single)

If Level = 5.1 Then YoYoL1Distance = 40
If Level = 8.1 Then YoYoL1Distance = 80
If Level = 11.1 Then YoYoL1Distance = 120
If Level = 11.2 Then YoYoL1Distance = 160
If Level = 12.1 Then YoYoL1Distance = 200
If Level = 12.2 Then YoYoL1Distance = 240
If Level = 12.3 Then YoYoL1Distance = 280
If Level = 13.1 Then YoYoL1Distance = 320
If Level = 13.2 Then YoYoL1Distance = 360
If Level = 13.3 Then YoYoL1Distance = 400
If Level = 13.4 Then YoYoL1Distance = 440
If Level = 14.1 Then YoYoL1Distance = 480
If Level = 14.2 Then YoYoL1Distance = 520
If Level = 14.3 Then YoYoL1Distance = 560
If Level = 14.4 Then YoYoL1Distance = 600
If Level = 14.5 Then YoYoL1Distance = 640
If Level = 14.6 Then YoYoL1Distance = 680
If Level = 14.7 Then YoYoL1Distance = 720
If Level = 14.8 Then YoYoL1Distance = 760
If Level = 15.1 Then YoYoL1Distance = 800
If Level = 15.2 Then YoYoL1Distance = 840
If Level = 15.3 Then YoYoL1Distance = 880
If Level = 15.4 Then YoYoL1Distance = 920
If Level = 15.5 Then YoYoL1Distance = 960
If Level = 15.6 Then YoYoL1Distance = 1000
If Level = 15.7 Then YoYoL1Distance = 1040
If Level = 15.8 Then YoYoL1Distance = 1080
If Level = 16.1 Then YoYoL1Distance = 1120
If Level = 16.2 Then YoYoL1Distance = 1160
If Level = 16.3 Then YoYoL1Distance = 1200
If Level = 16.4 Then YoYoL1Distance = 1240
If Level = 16.5 Then YoYoL1Distance = 1280

End Function
Comment
Watch Question

## View Solutions Only

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
You could do it all in the code or you could use a sheet in the add-in to store the table and simply look up the value from there using something like:
Function YoYoL1Distance(Level As Double)

YoYoL1Distance = application.vlookup(Level, thisworkbook.sheets(1).range("A1:B100"), 2, false)
End Function
for example.
CERTIFIED EXPERT
Commented:
Depends on what do you mean by "table inside a module"... because if you mean looking in a sheet, rorya already gave you an answer, but a "table inside VBA code" is not possible so I would asume you think of it more as an "array".

So, if you mean an "array inside the VBA module", now will depend on how do you want to define that array.

If you like to have the "table" as a simple "string" one ideea is this:

Function YoYoL1Distance2(Level As Single)
Dim sLevel As String
Dim aLevelInt As Variant
Dim nPos, nValue, nMark As Double

' Define string
sLevel = "5.1;40;8.1;80;11.1;120;"

nPos = InStr(1, sLevel, CStr(Level))
nMark = nPos + Len(CStr(Level)) + 1
nValue = Mid(sLevel, nMark, InStr(nMark, sLevel, ";") - nMark)

' If nothing found asume default value
If Val(nValue) > 0 Then
YoYoL1Distance2 = nValue
Else
YoYoL1Distance2 = 0
End If

End Function
or if you want an array by defining each element this could be another way:
Function YoYoL1Distance1(Level As Single)

' Define array
Dim aLevel(3, 2)

aLevel(1, 1) = 5.1
aLevel(1, 2) = 40

aLevel(2, 1) = 8.1
aLevel(2, 2) = 80

aLevel(3, 1) = 11.1
aLevel(3, 2) = 120

'Search the array
For i = LBound(aLevel) To UBound(aLevel)
If aLevel(i, 1) = Level Then
YoYoL1Distance = aLevel(i, 2)
Exit Function
End If
Next i

' If nothing found asume default value
YoYoL1Distance = 0

End Function

Commented:
Try this code:

Function YoYoL1Distance(lookup As Single) As Single

Dim Table(1, 32) As Variant

Table(0, 0) = 5.1
Table(1, 0) = 40
Table(0, 1) = 8.1
Table(1, 1) = 80
Table(0, 2) = 11.1
Table(1, 2) = 120
Table(0, 3) = 11.2
Table(1, 3) = 160
Table(0, 4) = 12.1
Table(1, 4) = 200
Table(0, 5) = 12.2
Table(1, 5) = 240
Table(0, 6) = 12.3
Table(1, 6) = 280
Table(0, 7) = 13.1
Table(1, 7) = 320
Table(0, 8) = 13.2
Table(1, 8) = 360
Table(0, 9) = 13.3
Table(1, 9) = 400
Table(0, 10) = 13.4
Table(1, 10) = 440
Table(0, 11) = 14.1
Table(1, 11) = 480
Table(0, 12) = 14.2
Table(1, 12) = 520
Table(0, 13) = 14.3
Table(1, 13) = 560
Table(0, 14) = 14.4
Table(1, 14) = 600
Table(0, 15) = 14.5
Table(1, 15) = 640
Table(0, 16) = 14.6
Table(1, 16) = 680
Table(0, 17) = 14.7
Table(1, 17) = 720
Table(0, 18) = 14.8
Table(1, 18) = 760
Table(0, 19) = 15.1
Table(1, 19) = 800
Table(0, 20) = 15.2
Table(1, 20) = 840
Table(0, 21) = 15.3
Table(1, 21) = 880
Table(0, 22) = 15.3
Table(1, 22) = 880
Table(0, 23) = 15.4
Table(1, 23) = 920
Table(0, 24) = 15.5
Table(1, 24) = 960
Table(0, 25) = 15.6
Table(1, 25) = 1000
Table(0, 26) = 15.7
Table(1, 26) = 1040
Table(0, 27) = 15.8
Table(1, 27) = 1080
Table(0, 28) = 16.1
Table(1, 28) = 1120
Table(0, 29) = 16.2
Table(1, 29) = 1160
Table(0, 30) = 16.3
Table(1, 30) = 1200
Table(0, 31) = 16.4
Table(1, 31) = 1240
Table(0, 32) = 16.5
Table(1, 32) = 1280

n = 0

Do Until n > 32

If Table(0, n) = lookup Then
YoYoL1Distance = Table(1, n)
End If

n = n + 1

Loop

End Function

Commented:
Thanks for the feedback and suggestions (apologies for going offline for 3 days after asking a question too)
I was looking for a way to make things a bit easier that writing 100+ if statements and i did not know i could simply attach a sheet to the add-in and reference to that (thanks rorya).  I will try this solution and use it in the future.  Thanks also to luconsta for some neat code and the 2 options.