Use VBA Function in Excel Formula

Visual Basic for Application (VBA) is a fantastic tool for automating work in Excel. It allows us to program our work and customize it to our needs.

In VBA, we can create our functions and use them in our workbook to return a certain value or array of values. In the example below, we will do just that.

Insert VBA Function

The first thing that we need to do is to open the VBA. We do that by clicking ALT + F11, and then clicking on the Insert >> Module on the window that appears:

Graphical user interface, application, Word

Description automatically generated

In the module, we will insert the following code:

This is what the code looks like in the module itself:

Graphical user interface, text, application, email

Description automatically generated

This function is made to extract the numbers from our string. The first thing that we do is to declare a function, give it a name, define its parameters, and define what type the function will return.

Our function will be called “ExtractNumbers”, and will have one parameter (cell) that will be in a form of a string, and the function itself will return a number.

Next, we declare variables res as String, i and StrLength as Integer. StrLength will be equal to the number of characters in our cell.

Then we get to the For Next Loop:

This loop goes through every character in our cell and checks if the character is a number. It saves all the numeric characters that are then stored as the result of our function.

Using VBA Function in Workbook

Now that we have our function, the next step is to use it in real-life experience. We will make a list of random addresses in our worksheet:


Description automatically generated

These addresses are different in terms that we have numbers in various positions (at the beginning of the text, in the middle, or at the end).

Next to column A, we will search for numbers in our cells with the defined function.

We will simply insert it as any other formula, calling it by its name. So this is what we will insert in cell B2:

We will expand the formula to the end of the list, and these are the results we will end up with:

Graphical user interface, application, table, Excel

Description automatically generated

If we had numbers in different places in one cell, they would all get extracted.

We can also combine this formula with different formulas in Excel. When created, there is no difference between this formula and every other pre-existing formula in Excel.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba