Many excel users might not be aware of the subtle distinction between Excel Worksheet functions and VBA functions. Majority of Excel’s capability is constructed using Visual Basics for Applications (VBA). VBA is a programming language that contains a fairly rich set of built in Functions; lets call these functions VBA Functions. Using the aforementioned VBA Functions, developers of Excel meticulously create hundreds of functions for their software, that could be keyed into the Formula Bar. These functions are called Excel Functions or Worksheet functions.
VBA functions cannot be accessed from Excel’s Formula Bar. However, some basic Excel Functions and VBA functions have the exact same name and construction; and when used in the Formula Bar, the user is essentially calling the excel function and not the VBA function. Surprisingly, there are some Functions that are available in the VBA, that were not adopted by Excel. However, a VBA function could be accessed from Excel, by wrapping it in a user defined function (UDF).
- A very famous example is the StrReverse() function. . Click here to see how to import StrReverse() into Excel.
- Another classic example is the Split() function. Click here to see how you can use this function to get the Nth word in a string.
Similarly, Excel functions cannot be used in Visual Basics directly. Fortunately, they can be accessed from the WorksheetFunction object, retrieved by the WorksheetFunction property of the Application (Excel) object. Click here to read more about using Application.WorksheetFunction.