VBA custom function, Expected List Separator or )

Hello, so I've never written a custom function before and I'm doing something that should be quite simple.

I'm taking the VLOOKUP function, and creating a new function that does the *same* exact thing, except it simplifies the typing process. Take a look.

Please Login or Register  to view this content.

The goal is just to have a shorter function name, and to not have to type the quotation marks with the asterisks, I only won't need to type FALSE at the end of it every time. Thinking on it now I may even try to default the range as well.

Either way, it doesn't seem to like my commas but I'm not sure why. the VLOOKUP function is working perfectly fine in my excel sheet when typed like this (in the cell that is): "=VLOOKUP("*itemname*",A1:D26,4,FALSE)" so I'm not sure why that isn't translating nicely to the function.

I think this is covered but this is my try:

Please Login or Register  to view this content.

The code I ended up going with was this, and it works fine

Please Login or Register  to view this content.

I decided that I actually want to make the range and column static as well. I tried a few different things but none of them have worked so far. This was my last attempt but it just returns 0 all the time.

Please Login or Register  to view this content.

Thoughts?
Thanks so much!

Hi,

<issing the correct range notation

Please Login or Register  to view this content.

Thanks

Ahhhhh, I see.
Great, thanks to all who contributed!

It's some solid advice but what doesn't work for me is just the length of the formula. The workbook is personal, by which I mean it's not for work or a client or anything like that, and the way I have it set up I don't foresee ever needing to change those other values (and if I do, I can effectively change it for *every* cell containing my UDF by making a slight edit to the code, rather than changing every cell containing one of these -- which is going to be a lot).

I totally get what mike says. We think we have all under control but later on a few months from now something starts failing and we cannot find what its and we already forgotten that we made that range hard coded. by the way your VLOOKUP formula is pretty small, I wonder if you will add more to it.

Good Luck