Creating a UDF with the table array inside the code

[thecrazyjogger]

Hello Forum!

Summary: I want to create a UDF which will give me the result of a cell based on its value but I want the table array to be inside the UDF

Long story: My lookup table array looks like this:

ONE	TWO	THREE
1001	Alpha	Region1
1002	Bravo	Region1
1003	Charlie	Region2
1004	Delta	Region2
1005	Echo	Region3
1006	Foxtrot	Region1
1007	Golf	Region1
1008	Hotel	Region3

<tbody>
</tbody>

Currently, I have the UDF which VLOOKUPs the required data from the Personal workbook in Excel. What I want is that table range to be inside the UDF itself.
One way I was thinking about doing is below, but considering I have three tables with the smallest table of 30 rows, and the largest table of 210 rows, it will take a good amount of time. Hence I’m asking you guys

Function WHName(Dude As String)
Application.Volatile
 
COCC = (Dude)
If COCC= "1001" Then
        CCNumber = "Alpha"
ElseIf COCC= "1002" Then
        CCNumber = "Bravo"
ElseIf COCC= "1003" Then
        CCNumber = "Charlie"
ElseIf COCC= "1004" Then
        CCNumber = “Delta”
End If
 
WHName = CCNumber
 
End Function

There has to be a better way. Appreciate all ideas

 

[chrism216]

Hardcoding data into the function logic will inevitably lead to this. Unless you are willing to store your array as a resource somewhere, then there is no other way to do it.

One thing you could do is concatenate your data into a long string separated by commas, and then construct your arrays with the Split() function.

 

[thecrazyjogger]

Hardcoding data into the function logic will inevitably lead to this. Unless you are willing to store your array as a resource somewhere, then there is no other way to do it.

One thing you could do is concatenate your data into a long string separated by commas, and then construct your arrays with the Split() function.

Hey chrism216, Thank you for replying. Can you tell me how I can store it as a resource somewhere?