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.

Function cvl(name, range, column)

Dim name As String
Dim range As range
Dim column As Integer

cvl(name, range, column) = application.VLOOKUP("*"&name&"*,"&range&","&column&",FALSE)"

End Function

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:

Public Function cvl(ByVal name As Variant, ByVal rRange As range, ByVal column As Integer, Optional ByVal match As Boolean = False) As Variant

cvl = WorksheetFunction.VLookup("*" & name & "*", rRange, column, match)

End Function

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

Function cvl(aName As String, myRange As range, myColumn As Integer) As Variant

cvl = Application.VLookup("*" & aName & "*", myRange, myColumn, False)

End Function

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.

Function cvl(aName As String) As Variant

cvl = Application.VLookup("*" & aName & "*", range(A1, D40), 4, False)

End Function

Thoughts?
Thanks so much!

Hi,

<issing the correct range notation

Function cvl(aName As String) As Variant

cvl = Application.VLookup("*" & aName & "*", range("A1:D40"), 4, False)

End Function

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