Now that you know how to write functions in VBA and make them available to your spreadsheets, you are also aware that you can re-create any function already available in the Excel application. Although recreating Excel's functions would be a good way to improve your VBA programming skills, it's certainly not a practical use of your time. Why reinvent what's already been created for you? It would be nice if you could use Excel's application functions in your VBA code, as they are mostly complimentary, not repetitive, to VBA's set of functions. That way, if you need a specific function performed in your program that is not already included with VBA, you don't have to write it yourself.
Well, there is a method to use the Excel application functions, of course, and it is really quite simple.
result = Application.WorksheetFunction.Power(number, n)
Replacing the call to the PowerDB() function in the TestPower() sub procedure shown earlier with the line of code above will give the exact same result. The difference is that this code uses Excel's POWER() function and not the PowerDB() function. The syntax will be explained in detail in Chapter 5, "Basic Excel Objects," but you can probably guess what's happening from the names used in this line of code. The component Application.WorksheetFunction will return all functions available from the Excel application. From there it is a simple matter of adding on the name of the function and inserting the required parameters into the parentheses. Two more examples illustrate the use of the AVERAGE() and STDEV() functions from the Excel application.
myVar = Application.WorksheetFunction.Average(5, 7, 9) myVar2 = Application.WorksheetFunction.StDev(3, 7, 11)
The examples above will return the value 7 to the variable myVar and 4 to the variable myVar2.
Was this article helpful?