VBA-Lesson 14.1. Using Excel functions

VBA Functions

VBA has many functions that you can use in your designs.

In this course we have used some of them, like IsNumeric functions, Year, Split, Join, Array, Date, Chr etc.

You can find a list of all main VBA functions (with an example of how to use each function) in the VBA Functions section.

It is also possible to use the usual EXCEL functions in the VBA code, which we will see later in this lesson.

EXCEL Functions

To add the Excel function to the VBA code, type WorksheetFunction and put a dot ("."), after which a list of functions will appear:

Select any Excel function you are interested in, for example COUNTBLANK, and fill in its arguments:

WorksheetFunction Example

In the following example of the VBA macro, we count the number of empty cells in the range "A1:D8", store the resulting value in a variable, and then display it in a dialog box:

Sub test()
     var_test = WorksheetFunction.CountBlank(Range("A1:D8"))
     MsgBox var_test
End Sub