TOP
# VBA-Lesson 14.1. Using Excel functions

## VBA Functions

## EXCEL Functions

## WorksheetFunction Example

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.

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:

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

- 1. What is VBA, basic concepts.
- 2. Debugger (Debugger)
- 3. Working with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function
Google Translate - Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
Fuzzy Lookup Function- Function GETSUBSTR
- Free extension "YouLibreCalc"
- Professional Extension "YLC Utilities"