Excel Visual Basic Question -Using Vlookup in a user defined function?

Question

I need to create a user defined function in excel based on many conditional lookups. My problem is that I don't know the syntax to imbed a vlookup statement in VBA.

For example, let's say you are given the following the following

Variable state = FL

In a tab named 'stateftr' the following table exists in range A:1 to B:5

AL 1.05
FL 1.08
GA 1.10
TN 1.15
SC 1.20

What is the syntax to do a lookup on the state and return the factor in a new variable called stateftr.

Thanks for your help,
Michael

Answer 1

There is an object in VBA of Excel called "WorksheetFunction"

Once you type it, you will have access to most of the functions in Insert > Function list

In you Function code area, add this line:

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4])

Arg1, etc are the same arguments in VLOOKUP (Excel version), they should be constants or variables.

Example:

Var1 = WorksheetFunction.VLookup( "FL", Worksheets( "Sheet1").range( A1:B5), 2, TRUE)

Use this line in any of XL VBA code

So, if you know the VLOOKUP syntax in Excel, you will know it here, it has the same arguments



Enjoy my profile, I am the VBAXLMan

Answer 2

User defined functions are macros, so they must run when the user computer security level is medium or low (Concern that) That was issue number 1 Issue number 2 will be the pathes for these files, the end-user must put them all in the same folder, (not only same folder) but also in the same path. Means if your files were in C:\Work1 folder, the end-user must put them also in C:\work1 folder. The function itself can be called frm any file (once the file with the UDF is opened), but the links will have some diffeculties in linking values I worked alot with these issues. So if you have only UDFs in one file and made sure that the user will open it, it will be no problem, but the links between cell values may face this issue. I can help you here if you are interesting and show you how to manage these, mail me here Enjoy my profile, I am the VBAXLMan

Answer 3

vlookup({Cell where variable state is},stateftr!A1:B5,2)



also, your data must be sorted in the column you are looking at, so your states should be in order. SC and TN switched.