Solved: How to use Excel Functions from VBA.

[surya prakash]

Hi,
I am wondering if it is possible to use Excel Functions from VBA..

thanks

[rory]

Yes - check out the help (or Intellisense) for Application.WorksheetFunction. You can also use Application.Evaluate to evaluate formulas.

[Bob Phillips]

There are two ways to invoke worksheet functions in VBA, either using the function as a property of the WorksheetFunction property, or as property of the Application object. As an example, the SUM function can be called with Application.SUM, or with Worksheetfunction.SUM.

Application was how worksheet functions were invoked prior to Excel 97, and has been retained for compatibility. Although both work, there are some nuances in the way in which they work.

? WorksheetFunction,and Application.WorksheetFunction, supports the "auto list members" option, whereas Application alone does not.
? Not all worksheet functions are exposed to VBA. Functions not exposed by the WorksheetFunction object usually have a VBA equivalent (e.g., Left, Year), but they do not necessarily work in exactly the same way.
? Functions within Add-ins, such as the Analysis Toolpak, cannot be called with Application or WorksheetFunction.
? WorksheetFunction is faster than Application, by an order of circa 20%.
? Errors are handled differently. When a function is called directly using Application, such as Application.VLookup, the result is a variant containing an error. When called directly using WorksheetFunction, for example WorksheetFunction.VLookup, the function will raise an error. Both can be managed, but in different ways



Dim res As Variant
res = Application.VLookup(1, Range("A1:B10"), 2, False)

res = WorksheetFunction.VLookup(1, Range("A1:B10"), 2, False)


Here, the VLOOKUP function is being invoked to lookup a value that does not exist in the range A1:B10. The first method, Application.Vlookup, returns an error to the variable, whereas the second method raises an error.
Application calls can also be trapped using the IsError statement



If IsError(Application.VLookup(1, Range("A1:B10"), 2, False)) Then
Debug.Print "error"
End If


In a similar manner, WorksheetFunction calls can be wrapped in error handling code to trap the errors.



On Error Resume Next
res = WorksheetFunction.VLookup(1, Range("A1:B10"), 2, False)
On Error GoTo 0
Debug.Print res


In this instance, res will be an empty variable.

[surya prakash]

thanks a ton all of you...