CODEWITHSUNDEEP
excelvba
Kevin Pooley
Kevin Pooley

Reputation: 99

How do I use an array element as an argument in a UDF?

This function works exactly as intended, but if I replace 'Rank' with 'Merc(98)' as an argument I receive an error message, Compile error: Expected: ). What am I doing wrong?

UDF to lookup DMs on Skills_Tables_DMs table
  Function RankDMs(WhichTable, Rank)
    RankDMs = WorksheetFunction.HLookup(WhichTable, (Range("Skills_Tables_DMs")), (Rank + 1), False)
  End Function

Upvotes: 0

Views: 75

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

If the below doesn't help then you're going to need to either explain in more detail what you're trying to do or (better) include more of the relevant code.

Sub Tester()
'...
Dim rv
'...
    rv = RankDMs(someValueHere, 5)
    'or
    rv = RankDMs(someValueHere, Merc(98))
'...
End Sub


Function RankDMs(WhichTable, Rank)
    RankDMs = WorksheetFunction.HLookup(WhichTable, _
                  Range("Skills_Tables_DMs"), (Rank + 1), False)
End Function

Note I changed this:

(Range("Skills_Tables_DMs"))

to:

Range("Skills_Tables_DMs")

Though it's not really critical in this case (since either form will work), you should note that the two are not equivalent: in the first case the extra parentheses cause an evaluation of the Value of the range, resulting in a Variant array, but the second results in a Range object. You can see this in the Immediate pane of the VB editor by comparing these two:

? typeName((Range("A1:A5")))   ' >> Variant()
? typeName(Range("A1:A5"))     ' >> Range

Upvotes: 1

Related Questions