Excel Functions in Macros

RiverKing 

You can use the WorksheetFunction object for this. There are two ways to use it:

1) Explicitly. For example:

MsgBox WorksheetFunction.Sum(Range("A2:A11"))

MsgBox WorksheetFunction.VLookup(Range("A2").Value, Range("D2:E100"), 2, False)

Please note that we have to use the VBA way of referring to ranges.

2) Implicitly, via the Application object:

MsgBox Application.Sum(Range("A2:A11"))

MsgBox Application.VLookup(Range("A2").Value, Range("D2:E100"), 2, False)

These two methods work slightly differently: if the corresponding formula would return an error (for example because VLOOKUP cannot find a match), WorksheetFunction will throw a runtime error. You can use an error handler to catch the error. But using Application, the VBA function will return an error:

Dim Result As Variant

Variant = Application.VLookup(Range("A2").Value, Range("D2:E100"), 2, False)

If IsError(Variant) Then ...

Alternatively, you can use the Evaluate function:

MsgBox Evaluate("VLOOKUP(A2,D2:E100,2,FALSE)")

or even

MsgBox [VLOOKUP(A2,D2:E100,2,FALSE)]