Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Arguments in Excell function | Excel Discussion (Misc queries) | |||
Passing arguments into function to bring data array from closed wb | Excel Programming | |||
passing reference arguments to VBA function | Excel Programming | |||
Passing Arguments | Excel Programming | |||
Passing arguments to a sub routine... | Excel Programming |