Solved

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

Posted on 2012-03-13
4
174 Views
Last Modified: 2012-03-18
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
0
Comment
Question by:JohnNZExcel
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 200 total points
ID: 37713954
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

Open in new window

for example.
0
 
LVL 14

Assisted Solution

by:luconsta
luconsta earned 150 total points
ID: 37714149
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

Open in new window

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

Open in new window

0
 

Expert Comment

by:ivowater
ID: 37714362
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

Open in new window

0
 

Author Comment

by:JohnNZExcel
ID: 37734298
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.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now