UDF for Vlookup function

[vandanavai]

Hi
I am new to VBA. I have attached one excel workbook. I have developed one Vlookup formula which I need to use very frequently in various workbooks. Copying this worksheet in various workbook is troublesome. Can anybody help me developing UDF for required Vlookup function.

Thanks in advance.

[davesexcel]

Not sure what you are asking,I am thinking you want to reference that one sheet to other sheets....


Here's the vlookup formula for your sheet

=VLOOKUP(D1,IndexValue,2,FALSE)

[vandanavai]

Sorry , But this is not what I am looking for. Actually I want Macro / VBA code for this function.

[Tristan]

Hi

Like DavesExcel, I think what you require is to reference the "IndexValue" range to the one worksheet where it's stored.

For demonstration purposes lets imagine the workbook with the "IndexValue" range is called Index.xls (inspired I know!). Let's also assume the range is on Sheet1 of that workbook and that the workbook itself is on your desktop.

Now, when you're writing your your lookup function in one of your other workbooks you'll need to tell Excel the location of the file which holds the "IndexValue" name. We established earlier that the "IndexValue" name was in the workbook Index.xls on your desktop so the formula you'd type into a cell will look something like this.

= VLOOKUP(D1,'C:\Documents and Settings\Installer\Desktop\Index.xls'!IndexValue,2)

Obviously when you write this you'll have to change the bits in italics so that it reflects your PC. (I'm guessing you haven't called your profile "Installer" but somehing much more exciting). Change it as needs be but make sure you keep those little quotes ' ' on either side of the filepath.

Now this works fine but, to be frank, it's a bugger to type out manually everytime. So how do we get around this? By creating a UDF. And to make it ultra neat we'll stick it into an Add-In.

In the VBA editor create a new module in the workbook that contains your sheet with the "IndexValue" range. Type the following code into the new module you've just created (very important this goes into a module and not any of the pre-created "Sheet" or "This workbook" code windows!!!).

//////////////////////////////////////////////////////////////////////////////////
Option Explicit

Function CheckIndex(ByVal vDate As Variant) As Variant

CheckIndex = WorksheetFunction.VLookup(vDate, Range(Application.Names _
("IndexValue")), 2)

End Function
//////////////////////////////////////////////////////////////////////////////////


So now we've got our new UDF into Excel. So far so funky.

Now for a couple of clever bits.
Still in the Visual Basic editor click on the "This Workbook" sheet in the folder tree. You should see a load of properties fill the properties box in the bottom left-hand pane.
Scroll down this list until you reach one called "Is Add-In". Next to it the value will say "False". Click on this value and change it to "True". Your workbook is now an Add-In.

Go back into Excel proper now. You'll want to save all these changes you've made so go to the Save As option and give it the new file type "Microsoft Excel Add-In" which is right at the very bottom of all the Save As types displayed when you open the arrow.

Now have a look at your desktop and there'll be this new file saved and its icon will look like the regualr Excel one except there'll be thi kinda magenta building block shape covering it. This is your workbook saved as an add-in.

Back into Excel we go. Go to Tools--> Add-Ins. This dialogue box shows all the available Add-Ins. Oh my God yours isn't there!! Don't panic. It's just in a location that Excel's not looking in so click the Browse button, find your desktop and select your .xla file. Excel's probably going to ask if you want to move this to your Add-In folder buried deep in the depths of the PC. I 'd say "Hell, why not?" and click OK. Now in the Add-In dialogue box your add-in has appeared. Make sure its checked and exit back to your workbook.

Now here's the test of faith. Close everything down. Come out of VBA, out of Excel back to your desktop. Now let's re-open Excel. Type in one of your dates into cell A1. now in cell B1 type your UDF:

=CheckIndex(A1)

Eh voila, if you've followed my instructions that index number should pop-up into the cell.

Well that's it I'm done.
Hope this all works out. God knows it's taken me long enough to type.
Let me know how it goes.

Tris

[Tristan]

OK....um..how embarrassing.

Right don't do any of the above because 1) it needs a little bit of revision and 2) it doesn't work anyway.

The problem lies in referring to the IndexValue name within the Add-In.
I'm going to post a message into the forum and ask if someone can solve this problem then I'll stick it back into the code.

Tris

[vandanavai]

Thanks for taking so much pain to type solution to my problem. Never mind it does't worked but best luck for next try.