Passing arguments to VB Function
 

Hi All,

I'm new to this so I hope I get it right in order to get some help.

=Look(A13,D13,(A13),3)*E13
This is the formula entered into a cell expecting a specific value to
be returned to it. The formula calls the VB code listed below. This
would work great except the 2nd reference to A13(enclosed in
paranthensis only for emphasis) in the formula sends the argument to
the function with quotes around it. (This would be sent to the tbl
argument in the function). This argument is actually a table name in
the workbook...that string located in the A13 cell. The quotation
marks prevent the function from working properly...if I eliminate the
quotes, the function works fine.

I could use a concatenated if statement but it becomes unwieldy due to
the number of cells I would have to enter it into. Any suggestions for
eliminating the quotation marks for the tbl argument or maybe a better
way of doing this.

Function Look(style, size, tbl, coll)
Select Case UCase(style)
Case "DH"
Look = Application.VLookup(size, tbl, coll)
Case "LT2"
Look = Application.VLookup(size, tbl, coll)
Case "CSE2"
Look = Application.VLookup(size, tbl, coll)
Case Else
Look = Evaluate("NA()")
End Select
End Function

Thanks for any help you can give me.
Tom

Passing arguments to VB Function
 

Hi Tom,

A couple of things. You should declare what type of variable you are
passing in your formula. Also, you can combine the Case (first 3) as they
do the same thing. Example ..

Function Look(rngStyle As Range, rngSize As Range, rngTbl As Range, lngColl
As Long)
Select Case UCase(style)
Case "DH", "LT2", "CSE2"
Look = Application.WorksheetFunction.VLookup(rngSize, rngTbl,
lngColl)
Case Else
Look = Evaluate("NA()")
End Select
End Function

Can you provide a sample or two of what your function looks like and what it
returns? Also post what data is in the referenced cells.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Tom" wrote in message
ups.com...
Hi All,

I'm new to this so I hope I get it right in order to get some help.

=Look(A13,D13,(A13),3)*E13
This is the formula entered into a cell expecting a specific value to
be returned to it. The formula calls the VB code listed below. This
would work great except the 2nd reference to A13(enclosed in
paranthensis only for emphasis) in the formula sends the argument to
the function with quotes around it. (This would be sent to the tbl
argument in the function). This argument is actually a table name in
the workbook...that string located in the A13 cell. The quotation
marks prevent the function from working properly...if I eliminate the
quotes, the function works fine.

I could use a concatenated if statement but it becomes unwieldy due to
the number of cells I would have to enter it into. Any suggestions for
eliminating the quotation marks for the tbl argument or maybe a better
way of doing this.

Function Look(style, size, tbl, coll)
Select Case UCase(style)
Case "DH"
Look = Application.VLookup(size, tbl, coll)
Case "LT2"
Look = Application.VLookup(size, tbl, coll)
Case "CSE2"
Look = Application.VLookup(size, tbl, coll)
Case Else
Look = Evaluate("NA()")
End Select
End Function

Thanks for any help you can give me.
Tom