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)]