Repair

Standard excel functions in vba. VBA Operators and Built-in Functions. Creating a function in VBA with one argument

With VBA, you can create a custom function that can be used in worksheets just like regular functions.

This is useful when existing Excel features are not enough. In such cases, you can create your own user-defined function (UDF) to meet your specific needs.

In this tutorial, I will talk about creating and using custom functions in VBA.

What is a function procedure in VBA?

A Function procedure is VBA code that performs calculations and returns a value (or an array of values).

Using the Function procedure, you can create a function that you can use in a worksheet (like any regular Excel function such as SUM or VLOOKUP).

Once you have created a Function procedure using VBA, you can use it in three ways:

As a formula in a worksheet, where it can take arguments as input and return a value or an array of values.
As part of your VBA routine code or other function code.
In conditional formatting

Although the worksheet already has over 450 built-in Excel functions, you may need a custom function if:

Built-in functions can't do what you want to do. In this case, you can create a custom function based on your requirements.
Built-in functions can get the job done, but the formula is long and complicated. In this case you can create a custom function that is easy to read and use

Note that custom functions created using VBA can be significantly slower than built-in functions. Hence, they are best suited for situations where you cannot get the result using built-in functions.

Function vs Subroutine in VBA

A "Sub" allows you to execute a set of code while a "Function" returns a value (or an array of values).

For example, if you have a list of numbers (both positive and negative) and you want to identify the negative numbers, here's what you can do with a function and a subroutine.

The routine can go through every cell in a range and can highlight all the cells that have a negative value in it. In this case, the routine ends up changing the properties of the range object (by changing the color of the cells).

With a custom function, you can use it on a single column and it can return TRUE if the value in the cell is negative and FALSE if it is positive. With a function you cannot change the properties of an object. This means that you cannot change the color of a cell using the function itself (however, you can do this using conditional formatting with a custom function).

When you create a user-defined function (UDF) using VBA, you can use the function in a worksheet just like any other function. I'll talk more about this in the Different Ways to Use UDFs in Excel section.

Creating a Simple User-Defined Function in VBA

Let me create a simple user defined function in VBA and show you how it works.

The code below creates a function that extracts the numeric parts from an alphanumeric string.

Function GetNumeric(CellRef As String) as Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function

If you have the above code in a module, you can use this function in a workbook.

Below is how this function, GetNumeric, can be used in Excel.

Now, before I tell you how this function is created in VBA and how it works, you need to know a few things:

When you create a function in VBA, it becomes available throughout the workbook, just like any other regular function.
When you enter a function name followed by an equal sign, Excel will show you the function name in the list of matching functions. In the example above, when I entered =Get, Excel showed me a list that had my custom function.

I think this is a good example where you can use VBA to create an easy to use function in Excel. You can do the same with a formula (as shown in this tutorial), but it gets complicated and difficult to understand. With this UDF you only need to pass one argument and you will get the result.

Anatomy of a User-Defined Function in VBA

In the above section, I gave you the code and showed you how the UDF function works in a worksheet.

Now let's dive in and see how this function is created. You should place the below code in a module in VB Editor. I cover this topic in the section

Function GetNumeric(CellRef As String) as Long " This function retrieves the numeric part from a string Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function

The first line of code begins with the word "Function".

This word tells VBA that our code is a function (not a subroutine). The word Function is followed by the name of the function - GetNumeric. This is the name we will use on the sheet to use this function.

The function name must not contain spaces. Also, you can't name a function if it conflicts with the name of a cell reference. For example, you can't name the function ABC123 because it also refers to a cell in the Excel worksheet.
You should not give your function the same name as an existing function. If you do this, Excel will give preference to the built-in function.
You can use underlining if you want to separate words. For example, Get_Numeric is a valid name

The function name is followed by some arguments in parentheses. These are the arguments that our function needs from the user. These are like the arguments we have to provide to Excel's built-in functions. For example, the COUNTIF function has two arguments (range and criteria).

Arguments must be specified in parentheses.

In our example there is only one argument - CellRef.

It is also useful to specify what argument the function expects. In this example, since we will be passing a cell reference to the function, we can specify the argument as a type of "Range". If you don't specify a data type, VBA will treat it as an option (meaning you can use any data type).

If you have more than one argument, you can specify the same in parentheses - separated by commas. Later in this tutorial we will see how to use multiple arguments in a custom function.

Note that the function is specified as a "String" data type. This will tell VBA that the result of the formula will have a String data type.

Here I can use a numeric data type (such as Long or Double), but this will limit the range of numbers returned. If I have a string of 20 numbers long that I need to extract from a common string, declaring the function as Long or Double will throw an error (since the number will be out of range). So I saved the function output data type as String.

The second line of code - green, which begins with an apostrophe - is a comment. When reading the code, VBA ignores this line. You can use this to add description or details about the code.

The third line of code declares the StringLength variable as an Integer data type. This is a variable in which we store the value of the string length, which is analyzed using the formula.

The fourth line declares the Result variable as a String data type. This is the variable where we will extract numbers from an alphanumeric string.

The fifth line assigns the length of the string in the input argument to the variable "StringLength". Note that "CellRef" refers to the argument that will be provided by the user when using the formula in a worksheet (or when using it in VBA - which we will see later in this tutorial).

The sixth, seventh and eighth lines are part of the For Next loop. The loop is executed as many times as there are characters in the input argument. This number is specified by the LEN function and assigned to the "StringLength" variable.

So the loop goes from "1 to Stringlength".

Inside the loop, the IF statement examines each character in the string and, if it is numeric, adds that numeric character to the Result variable. To do this, it uses the MID function in VBA.

The second last line of code assigns the result value to the function. It is this line of code that ensures that the function returns the value "Result" back to the cell (where it is called from).

The last line of code is End Function. This is a required line of code that tells VBA that the function code ends here.

The above code explains the different parts of a typical user-defined function created in VBA. In the following sections, we will delve deeper into these elements and also see different ways to perform a VBA function in Excel.

Arguments in a custom function in VBA

In the examples above, where we created a custom function to get the numeric part of an alphanumeric string (GetNumeric), the function was designed to receive a single argument.

In this section, I'll cover how to create no-argument functions for functions that take multiple arguments (both required and optional).

Creating a function in VBA without any arguments

In the Excel worksheet, we have several functions that take no arguments (eg RAND, TODAY, NOW).

These functions do not depend on input arguments. For example, the TODAY function returns the current date, and the RAND function returns a random number between 0 and 1.

You can create the same function in VBA.

Below is the code that will give you the filename. It does not accept any arguments because the result to be returned does not depend on any argument.

The above code defines the result of the function as a String data type (we want the filename as the result, which is a string).

This function assigns the value "ThisWorkbook.Name" to the function, which is returned when the function is used on a worksheet.

If the file was saved it returns the name with the file extension, otherwise it just gives the name.

There is one problem with the above, though.

If the file name changes, it will not be automatically updated. Typically the function is updated when the input arguments change. But since there are no arguments to this function, the function does not recalculate (even if you change the name of the workbook, close it, and then open it again).

If you wish, you can force the recalculation using the keyboard shortcut - Control + Alt + F9.

To make the formula recalculate whenever there is a change in the worksheet, you need a line of code to go with it.

The code below causes the function to recalculate whenever a change occurs in the worksheet (just like other similar worksheet functions such as the TODAY or RAND function).

Function WorkbookName() As String Application.Volatile True WorkbookName = ThisWorkbook.Name End Function

Now if you change the workbook name, this function will update whenever there is any change in the table or when you open that workbook again.

Creating a function in VBA with one argument

In one of the sections above, we already saw how to create a function that takes only one argument (the GetNumeric function described above).

Let's create another simple function that takes only one argument.

The function created with the code below converts reference text to upper case. Now we already have a function for this in Excel, and this function just shows you how it works. If you need to do this, it's better to use the built-in UPPER function.

Function ConvertToUpperCase(CellRef As Range) ConvertToUpperCase = UCase(CellRef) End Function

This function uses the UCase function in VBA to change the value of the CellRef variable. It then assigns the value to the ConvertToUpperCase function.

Since this function takes an argument, we don't need to use the Application.Volatile part here. As soon as the argument changes, the function will automatically update.

Creating a Function in VBA with Multiple Arguments

Just like worksheet functions, you can create functions in VBA that take multiple arguments.

The code below will create a function that will extract the text before the specified delimiter. It takes two arguments - a cell reference with a text string and a separator.

Function GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

When you need to use more than one argument in a user-defined function, you can have all the arguments in parentheses, separated by a comma.

Note that for each argument you can specify a data type. In the above example, "CellRef" was declared as a Range data type and "Delim" was declared as a String data type. If you don't specify a data type, VBA considers it to be a variant data.

When you use the above function in a worksheet, you need to provide a cell reference that has the text as the first argument and the double quoted character(s) as the double quote.

It then checks the position of the delimiter using the INSTR function in VBA. This position is then used to extract all characters before the delimiter (using the LEFT function).

Finally, it assigns the result to the function.

This formula is far from perfect. For example, if you enter a delimiter that is not found in the text, it will throw an error. Now you can use the IFERROR function on the worksheet to get rid of the errors, or you can use the code below which returns the entire text when it can't find the separator.

Function GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition< 0 Then DelimPosition = Len(CellRef) Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

We can further optimize this feature.

If you enter text (of which you want to extract the part before the delimiter) directly into the functions, it will result in an error. Come on... try it!

This happens when we have specified "CellRef" as the range data type.

Or if you want the separator to be in the cell and use a cell reference instead of hard coding it in the formula, you can't do that with the code above. This is because Delim was declared as a string data type.

If you want the function to have the flexibility to accept direct text input or cell references from the user, you need to remove the data type declaration. This will create argument as an alternative data type that can accept arguments of any type and process them.

The code below will do this:

Function GetDataBeforeDelimiter(CellRef, Delim) As String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition< 0 Then DelimPosition = Len(CellRef) Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

Creating a Function in VBA with Optional Arguments

Excel has many features, some of which are optional.

For example, the legendary VLOOKUP function has 3 required arguments and one optional argument.

The optional argument, as the name suggests, is optional. If you don't supply one of the required arguments, your function will give you an error, but if you don't supply the optional argument, your function will work.

But optional arguments are not useless. They allow you to choose from a range of options.

For example, in the VLOOKUP function, if you do not specify a fourth argument, VLOOKUP performs a rough match, and if you specify the last argument as FALSE (or 0), then it performs an exact match.

Remember that optional arguments must always come after all required arguments. You cannot have additional arguments at the beginning.

Now let's see how to create a function in VBA with optional arguments.

Function with only optional argument

As far as I know, there is no built-in function that takes only optional arguments (I could be wrong, but I can't think of any such function).

But we can create one with VBA.

Below is the code for a function that will give you the current date in the format dd-mm-yyyy if you do not enter any arguments (i.e. leave this field blank), and in the format "dd mmmm, yyyy" if you enter something - or as an argument (i.e., anything so that the argument is not empty).

Function CurrDate(Optional fmt As Variant) Dim Result If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") Else CurrDate = Format(Date, "dd mmmm, yyyy") End If End Function

Note that the above function uses IsMissing to check if the argument is missing or not. To use the IsMissing function, the optional argument must be a variant data type.

The above function works no matter what you enter as an argument. In the code, we only check whether the optional argument is specified or not.

You can make this more reliable by taking only certain values as arguments and showing an error otherwise (as shown in the code below).

Function CurrDate(Optional fmt As Variant) Dim Result If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") ElseIf fmt = 1 Then CurrDate = Format(Date, "dd mmmm, yyyy") Else CurrDate = CVErr(xlErrValue) End If End Function

The above code creates a function that displays the date in the format "dd-mm-yyyy" if no argument is given, and in the format "dd mmmm, yyyy" if the argument is 1. In all other cases, an error is thrown.

Function with required and optional arguments

We've already seen code that extracts the numeric part from a string.

Now let's look at a similar example that takes both required and optional arguments.

The code below creates a function that extracts the text portion from a string. If the optional argument is TRUE, it gives the result in upper case, and if the optional argument is FALSE or omitted, it gives the result as is.

Function GetText(CellRef As Range, Optional TextCase = False) As String Dim StringLength As Integer Dim Result As String StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i If TextCase = True Then Result = UCase(Result) GetText = Result End Function

Note that in the above code, we have initialized the value of "TextCase" to False (see the parentheses on the first line).

By doing this, we ensured that the optional argument starts with the default value, which is FALSE. If the user specifies the value as TRUE, the function returns uppercase text, and if the user specifies the optional argument as FALSE or skips it, then the returned text remains as is.

Creating a function in VBA with an array as argument

So far we've seen examples of creating a function with optional/required arguments, where those arguments were a single value.

You can also create a function that can take an array as an argument. There are many functions in Excel worksheet functions that take array arguments such as SUM, VLOOKUP, SUMIF, COUNTIF, etc.

Below is the code that creates a function that gives the sum of all even numbers in a specified range of cells.

Function AddEven(CellRef as Range) Dim Cell As Range For Each Cell In CellRef If IsNumeric(Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Result + Cell.Value End If End If Next Cell AddEven = Result End Function

You can use this function in a worksheet and specify a range of cells that take numbers as an argument. The function will return one value - the sum of all even numbers (as shown below).

In the above function, instead of a single value, we have provided an array (A1:A10). For this to work, you need to make sure that your argument data type can accept an array.

In the above code, I have specified the CellRef argument as Range (which can take an array as input). You can also use a variant data type here.

The code has a For Each loop that goes through each cell and checks whether the number is not. If it is not, nothing happens and it moves to the next cell. If it is a number, it checks whether it is even or not (using the MOD function).

At the end, all even numbers are added and the sum is returned back to the function.

Creating a function with an indefinite number of arguments

When creating some functions in VBA, you may not know the exact number of arguments the user wants to provide. Therefore, you need to create a function that can take as many arguments as needed and use them to return a result.

An example of such a worksheet function is the SUM function. You can provide multiple arguments (like this):

= SUM(A1, A2: A4, B1: B20)

The above function will add values to all these arguments. Also note that this can be a single cell or an array of cells.

You can create such a function in VBA by specifying the last argument (or the only argument) as optional. Additionally, this optional argument must be preceded by the keyword "ParamArray".

ParamArray is a modifier that allows you to take as many arguments as you want. Note that using the word ParamArray before an argument makes the argument optional. However, you don't need to use the word "Optional" here.

Now let's create a function that can take an arbitrary number of arguments and will add all the numbers to the given arguments:

Function AddArguments(ParamArray arglist() As Variant) For Each arg In arglist AddArguments = AddArguments + arg Next arg End Function

The above function can take any number of arguments and add those arguments to produce the result.

Note that you can only use one value, cell reference, boolean, or expression as an argument. You cannot provide an array as an argument. For example, if one of your arguments is D8:D10, this formula will give you an error.

If you want to use both arguments from multiple cells, you need to use the following code:

Function AddArguments(ParamArray arglist() As Variant) For Each arg In arglist For Each Cell In arg AddArguments = AddArguments + Cell Next Cell Next arg End Function

Note that this formula works with multiple cells and array references, however it cannot handle hard-coded values or expressions. You can create a more robust function by checking and handling these conditions, but that's not the goal.

The goal here is to show you how ParamArray works so that you can allow an indefinite number of arguments in a function. If you need a better function than the one created in the code above, use the SUM function on the worksheet.

Creating a function that returns an array

So far we have seen functions that return a single value.

With VBA, you can create a function that returns a variant containing an entire array of values.

Array formulas are also available as built-in functions in Excel sheets. If you are familiar with array formulas in Excel, you know that they are entered using the Control + Shift + Enter keys (not just Enter). You can read more about array formulas here. If you don't know array formulas, don't worry, keep reading.

Let's create a formula that returns an array of three numbers (1,2,3).

The code below will do this.

Function ThreeNumbers() As Variant Dim NumberValue(1 To 3) NumberValue(1) = 1 NumberValue(2) = 2 NumberValue(3) = 3 ThreeNumbers = NumberValue End Function

In the above code, we have specified the ThreeNumbers function as an option. This allows it to contain an array of values.

The NumberValue variable is declared as an array of 3 elements. It contains three values and assigns it to the Three Numbers function.

You can use this function in a worksheet. Type this function and press Control+Shift+Enter (hold Control and Shift and then press Enter).

When you do this it will return 1 in the cell, but in reality it contains all three values. To check this, use the following formula:

=MAX(ThreeNumbers())

Use the above function with Control + Shift + Enter. You'll notice that the result is now 3 because those are the largest values in the array returned by the Max function, which gets three numbers as a result of our custom function, ThreeNumbers.

You can use the same technique to create a function that returns an array of month names, as shown in the code below:

Function Months() As Variant Dim MonthName(1 To 12) MonthName(1) = "January" MonthName(2) = "February" MonthName(3) = "March" MonthName(4) = "April" MonthName(5) = "May" MonthName(6) = "June" MonthName(7) = "July" MonthName(8) = "August" MonthName(9) = "September" MonthName(10) = "October" MonthName(11) = "November " MonthName(12) = "December" Months = MonthName End Function

Now, when you enter the =Months() function in an Excel worksheet and use Control + Shift + Enter, it will return the entire array of month names. Note that you only see January in the cell because it is the first value in the array. This does not mean that the array returns only one value.

To show you the fact that it returns all the values, do this - select the cell with the formula, go to the formula bar, select the entire formula and press F9. This will show you all the values that the function returns.

You can use this by using the INDEX formula below to get a list of all the month names in one go.

=INDEX(Months(),ROW())

So the same code where we create the Months function will become shorter as shown below:

Function Months() As Variant Months = Array("January", "February", "March", "April", "May", "June", _ "July", "August", "September", "October" , "November", "December") End Function

The above function uses the Array function to assign values directly to this function.

Note that all the functions created above return a horizontal array of values. This means that if you select 12 horizontal cells (say A1:L1) and enter the formula =Months() into cell A1, you will get all the month names.

But what if you want these values in a vertical range of cells.

You can do this using the TRANSPOSE formula in the worksheet.

Simply select 12 vertical cells (adjacent) and enter the formula below.

Understanding the scope of a custom function in Excel

A function can have two scopes - Public or Private.

Shared scope means that the function is available to all sheets in the workbook, and to all procedures (auxiliary and functional) in all modules in the workbook. This is useful when you want to call a function from a subroutine (we'll see how this is done in the next section).
Private scope means that a function is only available in the module in which it exists. You cannot use it in other modules. You won't see it in the list of functions on the worksheet either. For example, if your function name is "Months()" and you enter the function in Excel (after the = sign), it will not display the function name to you. However, you can still use it if you enter the formula name

If you don't specify anything, the function is public by default.

Below is the function which is a private function:

Private Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

You can use this function in subroutines and procedures in the same modules, but you cannot use it in other modules. This feature will also not appear on the worksheet.

The code below will make this function public. This will also appear on the sheet.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

Different Ways to Use a User Defined Function in Excel

Once you create a custom function in VBA, you can use it in different ways.

Let's first look at how to use functions in a worksheet.

Using Custom Functions in Worksheets

We have already seen examples of using a function created in VBA in a worksheet.

All you have to do is enter the function name and it will show up in intellisense.

Note that in order for a function to appear on the worksheet, it must be a Public function (as described in the section above).

You can also use the Insert Function dialog box to insert a custom function (using the steps below). This will only work for public functions.

Go to the Data tab.
Click Insert Function.

In the Insert Function dialog box, select User Defined as the category. This option only appears when you have a function in the VB editor (and a Public function).

Select a function from the list of all publicly available custom functions.
Click OK

The above steps will insert the function into the worksheet. It also displays the Function Arguments dialog box, which will give you detailed information about the arguments and the result.

You can use a user-defined function just like any other function in Excel. This also means you can use it with other built-in Excel features. For example. The formula below will give the workbook title in uppercase:

=UPPER(WorkbookName())

Using User Defined Functions in VBA Procedures and Functions

Once you have created a function, you can use it in other subprocedures.

If a function is Public, it can be used in any procedure in the same or another module. If it is Private, it can only be used in the same module.

Below is a function that returns the name of the workbook.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

The procedure below calls the function and then displays the name in a message box.

Sub ShowWorkbookName() MsgBox WorkbookName End Sub

You can also call a function from another function.

In the codes below, the first code returns the workbook name and the second returns the uppercase name by calling the first function.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function Function WorkbookNameinUpper() WorkbookNameinUpper = UCase(WorkbookName) End Function

Calling a custom function from other workbooks

If you have a function in a workbook, you can call that function in other workbooks.

There are several ways to do this:

Creating an add-in
Save function in personal macro
Link to a function from another workbook.

Creating an add-in

By creating and installing the add-in, you get a custom feature that is available in all workbooks.

Let's say you created a custom function - "GetNumeric" and want it to be in all workbooks. To do this, create a new workbook and place the function code in a module of this new workbook.

Go to the File tab and click Save As.

In the Save As dialog box, change the Save As type to .xlam. The name you assign to the file will be the name of your add-in. In this example, the file is saved with the name GetNumeric.
- You will notice that the path to the file where it is saved changes automatically. You can use the default or change it if you want.

Open a new Excel workbook and go to the Developer tab.
Select the Excel Add-ins option.

In the Add-Ins dialog box, browse and locate the saved file and click OK.

The add-on has now been activated.

You can now use custom functions in all workbooks.

Saving a function in a personal macro workbook

A personal macro workbook is a hidden workbook on your system that opens every time you open Excel.

This is a place where you can store macros and access them from any workbook. This is a great place to store those macros that you want to use frequently.

By default, your Excel does not have a personal macro workbook. You need to create one by recording a macro and saving it in your personal macro book.

Link to a function from another book

While the first two methods (creating an add-in and using a personal macro workbook) will work in all situations, if you want to reference a function from another workbook, that workbook must be open.

Let's say you have a workbook named "Formula Workbook" and it has a function called "GetNumeric".

To use this function in another workbook (while the workbook with the formula is open), you can use the following formula:

=’Workbook with Formula’!GetNumeric(A1)

The above formula will use the custom function in the Workbook with Formula file and give you the result.

Please note that because the book name contains spaces, it must be enclosed in single quotes.

Using the escape statement from VBA

If you want to exit a function while code is executing, you can do so using the Exit Function statement.

The code below extracts the first three numeric characters from an alphanumeric text string. Once it receives three characters, the function exits and returns the result.

Function GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric(Mid(CellRef, i, 1)) Then J = J + 1 Result = Result & Mid(CellRef, i, 1) GetNumericFirstThree = Result End If Next i End Function

The above function checks the number of numeric characters and when it gets 3 numeric characters, it exits the function in the next loop.

Debugging a custom function

There are several methods you can use when debugging a user-defined function in VBA:

Debugging a custom function using a message box

Use a function to show a message box with a specific value.

The value displayed may be based on what you want to check. For example, if you want to check whether the code is running or not, any message will work, and if you want to check whether the loops are running or not, you can display a specific value or loop counter.

Debugging a custom function by setting a breakpoint

Set a breakpoint so you can go step by step through each line. To set a breakpoint, select the line you want and press F9 or click on the gray vertical area that is to the left of the lines of code. Either of these methods would insert a breakpoint (you'll see a red dot in the gray area).

Once you set a breakpoint and execute the function, it goes up to the breakpoint line and then stops. You can now view the code using the F8 key. Press F8 once to move to the next line in the code.

Debugging a custom function using Debug.Print in code

You can use the Debug.Print statement in your code to get the values of specified variables/arguments in the immediate window.

For example, in the code below, I used Debug.Print to get the value of two variables - "j" and "Result".

Function GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric(Mid(CellRef, i, 1)) Then J = J + 1 Result = Result & Mid(CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = Result End If Next i End Function

When this code is executed, it displays the following in the immediate window.

Excel Built-in Functions vs VBA Custom Function

There are several strong advantages to using Excel's built-in functions over custom functions created in VBA.

Built-in functions are much faster than VBA functions.
When you create a report/dashboard using VBA functions and send it to a client/colleague, they don't have to worry about whether macros are enabled or not. In some cases, clients/customers get scared when they see a yellow bar warning (which simply asks them to enable macros).
With Excel's built-in features, you don't have to worry about file extensions. If you have macros or user defined functions in the workbook, you need to save them in .xlsm format

While there are many good reasons to use Excel's built-in functions, in some cases it is better to use a custom function.

It's better to use a custom function if your inline formula is huge and complex. This becomes even more relevant when you need someone else to update your formulas. For example, if you have a huge formula consisting of many different functions, even changing a cell reference can be tedious and error-prone. Instead, you can create a custom function that only takes one or two arguments and does all the backend heavy lifting.
When you need to do something that cannot be done by Excel's built-in functions. An example of this would be when you want to extract all numeric characters from a string. In such cases, the benefits of using the gar custom function outweigh its disadvantages.

Where to put the VBA code for a custom function

When you create a custom function, you must place the code in the code window for the workbook in which you want to use the function.

Below are instructions for placing the code for the "GetNumeric" function in the workbook.

Most Microsoft Excel worksheet functions can be used in Visual Basic code (list of these functions: List of Worksheet Functions Available to Visual Basic)
Some Excel functions in Visual Basic are simply not needed. For example, instead of the function Concatenate(CONCATENATE) Visual Basic code uses the operator to concatenate text & .

Calling a Worksheet Function from Visual Basic

In Visual Basic, Microsoft Excel worksheet functions are accessed through the object WorksheetFunction.

For example, the function used here is Min to determine the smallest value in a range of cells:

Sub UseFunction() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:C10") answer = Application.WorksheetFunction.Min(myRange) MsgBox answer End Sub

Here, the variable myRange is first declared as an object of type Range, and is then assigned the cell range A1:C10 in Sheet1. The variable answer is assigned the result of applying the function Min to myRange . The resulting answer value is displayed in the message box.

A range of cells must be declared as an object Range!

For example, in a formula in an Excel table cell
=MATCH(9,A1:A10,0)
and in Visual Basic code -

Sub FindFirst() myVar = Application.WorksheetFunction.Match(9, Worksheets(1).Range("A1:A10"), 0) MsgBox myVar End Sub

Visual Basic Functions (no prefix WorksheetFunction) may have the same name as an Excel function, but work differently. For example, Application.WorksheetFunction.Log and Log return different values.

Inserting a worksheet function into a cell

To insert a function into a cell, you need to assign it to a property Formula corresponding object Range.

For example:

Sub InsertFormula() Worksheets("Sheet1").Range("A1:B3").Formula = "=RAND()" End Sub

The result of the RAND function (it returns a random number) is assigned to the Formula property of the range of cells A1:B3 in Sheet1 in the active Excel workbook.

(Using Microsoft Excel Worksheet Functions in Visual Basic Office 2003)

Function Name ([list_ arguments])
[instructions]
[Name = expression]
[instructions]
[Name = expression]
End Function

A value is always assigned to a function name at least once, and usually when the function has finished executing. To create a custom function, start by creating a VBA module (you can also use an existing module). Enter the keyword Function, followed by the name of the function and a list of its arguments (if any) in parentheses. You can also declare the data type of the value that the function returns using the As keyword (this is optional, but recommended). Paste the VBA code that performs the required actions and ensure that the required value is assigned to the procedure variable corresponding to the function name at least once in the function body. The function ends with the End Function statement.

Function names follow the same rules as . If you plan to use a function in a worksheet formula, make sure the name is not in the form of a cell address. Also, avoid giving functions names that match the names of Excel built-in functions. If the scope of the function is not specified, then Public is assumed by default. Functions declared as Private do not appear in the dialog Function Wizard.

The function can be called in one of the following ways:

call it from another procedure;
include it in the worksheet formula;
include in conditional formatting formula;
call it in the VBE debug window ( Immediate). This method is usually used during the testing phase (Fig. 3).

Rice. 3. Calling a function in the debug window

Unlike procedures, functions do not appear in a dialog box Macro(menu Developer –> Code –> Macros; or Alt+F8).

Function Arguments

Arguments can be represented by variables (including arrays), constants, character data, or expressions. Some functions have no arguments. Functions have both required and optional arguments.

Functions without arguments

Excel has several built-in functions that have no arguments, such as RAND, TODAY, TDATE. It's easy to create similar custom functions. For example:

Function User()
" Returns the username
User = Application.UserName
End Function

When you enter the formula =User(), the cell returns the name of the current user (Fig. 4). Please note that when using a function without an argument, you must include empty parentheses in a worksheet formula.

Rice. 4. Formula =User() returns the name of the current user

User-defined functions behave like built-in Excel functions. Typically, a user-defined function is recalculated when needed, i.e. if one of the function arguments changes. However, you can recalculate functions more often. The function is recalculated when any cell changes if the operator is added to the procedure

Application.Volatile True

The Volatile method of the Application object has one argument (True or False). If the function is highlighted as volatile(changeable), it is recalculated whenever any cell in the worksheet changes. When you use the False argument of the Volatile method, the function is recalculated only when one of its arguments changes as a result of the recalculation.

Excel has a built-in RAND function. But I didn't really like that the random numbers changed every time the worksheet was recalculated. Therefore, I developed a function that returns random numbers that do not change when formulas are recalculated. To do this, I used the built-in VBA function Rnd:

Function StaticRand()
" Returns a random number that does not change when formulas are recalculated
StaticRand = Rnd()
End Function

The values obtained using this formula never change. But the user still has the option of forcing the formula to be recalculated using a key combination .

Function with one argument

Rice. 5. Commission table

There are several ways to calculate commissions. For example, using the following formula (if sales volume is placed in cell D1):

IF(AND(D1>=0;D1<=9999,99);D1*0,08;ЕСЛИ(И(D1>=10000;D1<=19999,99);D1*0,105; ЕСЛИ(И(D1>=20000;D1<=39999,99);D1*0,12;ЕСЛИ(D1>=40000;D1*0.14))))

This formula fails for several reasons. Firstly, it is complex, it is not easy to type, and subsequently edit. Secondly, the values are strictly defined in the formula, which makes it difficult to change. It is much better to use VLOOKUP (Fig. 6).

Rice. 6. Using the VLOOKUP function to calculate commissions

Even better (no need to use a lookup table) to create a custom function:

Function Commission(Sales)
Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
" Calculating commissions on sales
Select Case Sales
Case 0 To 9999.99: Commission = Sales * Tier1
Case 10000 To 19999.99: Commission = Sales * Tier2
Case 20000 To 39999.99: Commission = Sales * Tier3
Case Is >= 40000: Commission = Sales * Tier4
End Select
End Function

Once entered into a VBA module, this function can be used in a formula in a worksheet or called from other VBA procedures. Typing the following formula into a cell will return the result 3000:

Commission(B2)

Function DoubleCell()
DoubleCell = Range(" Al ") * 2
End Function

Although this function works, in some cases it produces incorrect results. The reason is that Excel's calculation engine does not take into account ranges that are not passed as arguments. As a result, sometimes all associated values are not calculated before a function returns a value. You should also write a DoubleCell function that takes the value of cell A1 as its argument.

Function DoubleCell(cell)
DoubleCell = cell * 2
End Function

Function with two arguments

Let's imagine that the manager discussed above implements a new policy designed to reduce employee turnover: the total amount of commissions payable increases by 1% for each year that the employee works for the company. Let's change the custom function Commission so that it takes two arguments. The new argument represents the number of years the employee has worked for the company. Let's call this new function Commission2:

Function Commission2(Sales, Years) As Single
" Calculation of sales commissions based on
"length of experience
Commission2 = Commission(Sales) + _
(Commission(Sales) * Years / 100)
End Function

Function with Array Argument

Functions can take one or more arrays as arguments, process that array(s), and return a single value. The function below takes an array as an argument and returns the sum of its elements.

Function SumArray(List) As Double
Dim Item As Variant
SumArray = 0
For Each Item In List
If WorksheetFunction.IsNumber(Item) Then _
SumArray = SumArray + Item
Next Item
End Function

Excel's ISNUMBER function checks whether each element is a number before adding it to the overall integer. Adding this simple data validation operator eliminates type mismatch errors when attempting to perform an arithmetic operation on a string.

Function with optional arguments

Many built-in Excel functions have optional arguments. An example is the LEFT function, which returns characters from the left edge of a string. It has the following syntax:

LEWSIM( text, number of characters)

The first argument is required, unlike the second. If the second argument is not specified, Excel assumes the value 1.

Custom functions developed in VBA can also have optional arguments. You specify an optional argument if you enter the Optional keyword before the argument name. In an argument list, optional arguments are specified after all required ones. For example:

Function User2(Optional Uppercase As Variant)
If IsMissing(Uppercase) Then Uppercase = False
User2 = Application.UserName
If Uppercase Then User2 = UCase(User2)
End Function

If the argument is False or omitted, then the username is returned without any changes. If the function argument is True, then the user name is returned in uppercase characters (using the VBA function Ucase). Pay attention to the first function statement - it contains the IsMissing VBA function, which determines the presence of an argument. If there is no argument, the operator sets the Uppercase variable to False (the default).

VBA function returning an array

VBA contains a very useful function called Array. It returns a Variant data type that contains an array (that is, multiple values). If you are not familiar with array formulas in Excel, I suggest starting with . The array formula is entered into the cell after clicking . Excel adds parentheses around a formula to indicate that it is an array formula.

The MonthNames function is a simple example of using the Array function in a user-defined function.

Function MonthNames()
MonthNames = Array(" January ", " February ", " March ", _
"April", "May", "June", "July", "August", _
" September October November December "
End Function

The MonthNames function returns a horizontal array of month names. In your worksheet, select 12 cells, enter the formula =MonthNames() and click . If you need to generate a vertical array of month names, select the vertical range, enter the formula =TRANSP(MonthNames()) and click .

Function returning an error value

xlErrDivO (for error #DIV/0!);
xlErrNA (for error #N/A);
xlErrName (for error #NAME?);
xlErrNull (for #NULL error!);
xlErrNum (for error #NUMBER!);
xlErrRef (for #REF error!);
xlErrValue (for #VALUE!) error.

Below is the converted RemoveVowels function (see example at the beginning). The If-Then construct is used to perform an alternative action when the argument is not text. This function calls the Excel ITEXT function, which determines whether the argument contains text. If the cell contains text, then the function returns the normal result. If the cell does not contain text (or is empty), then the function returns the error #VALUE!

Function RemoveVowels3(txt) As Variant
" Removes all vowels from the Txt argument
" Returns a #VALUE! error if the argument is not a string
Dim i As Long
RemoveVowels3 = " "
If Application.WorksheetFunction.IsText(txt) Then
For i = 1 To Len(txt)
If Not UCase(Mid(txt, i, 1)) Like " " Then
RemoveVowels3 = RemoveVowels3 & Mid(txt, i, 1)
End If
Next i
Else
RemoveVowels3 = CVErr(xlErrValue)
End If
End Function

Note that the data type for the function's return value has been changed. Since the function can return something other than a string, the data type has been changed to Variant.

Function with an indefinite number of arguments

It is possible to create custom functions that have an indefinite number of arguments. Use an array as the last (or only) argument and prefix it with the ParamArray keyword (ParamArray refers only to the last argument in a procedure's argument list. It is always a Variant data type and is always an optional argument). The following function returns the sum of all arguments, which can be either a single value (cell) or a range.

Function SimpleSum(ParamArray arglist() As Variant) As Double
Dim cell As Range
Dim arg As Variant
For Each arg In arglist
For Each cell In arg
SimpleSum = SimpleSum + cell
Next cell
Next arg
End Function

Debugging functions

When you use a formula in a worksheet to test a function, errors that occur during execution are not displayed in the familiar message dialog box. The formula simply returns the error value (#VALUE!). Fortunately, this is not a big problem when debugging functions, since there are always several workarounds.

Place a MsgBox function in important places to control the values of individual variables.
Test the function by calling it from a procedure rather than from a worksheet formula. Errors during execution are displayed as usual.
Determine the stopping point in the function and walk through the function step by step. In this case, you can use all standard debugging tools. To add a breakpoint, place the cursor in the statement where you choose to pause execution and select the command Debug –> Toggle Breakpoint (Debugging –> Stop point) or click .
Use one or more temporary Print statements in your program to display values in a window Immediate VBA editor. For example, to monitor a value that changes cyclically, use the following method:

Rice. 7. Use the debug window to display the results when executing the function

In this case, the values of two variables, Ch and i, are displayed in the debug window ( Immediate) whenever the Debug.Print statement is encountered in the program. Place the cursor anywhere in the Test() procedure and press F5. In Fig. Figure 7 shows the result when the function takes the TusconArizona argument.

Using the MacroOptions Method

You can use the Application object's MacroOptions method to include functions that you develop as part of Excel's built-in functions. This method allows you to:

add a description of the function (starting from Excel 2010;
indicate the function category;
add a description of the function arguments.

Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim FuncCat As Long
Dim Arg1Desc As String, Arg2Desc As String
FuncName = "Draw"
FuncDesc = "Random Range Cell Contents"
FuncCat = 5" Links and Arrays
Arg1Desc = "The range that contains the values"
Arg2Desc = " (optional) If False or missing, _
the Rnd function is not recalculated. "
Arg2Desc = Arg2Desc & "If True, the Rnd function is recalculated"
Arg2Desc = Arg2Desc & "for any change on the sheet."
Application.MacroOptions_
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=FuncCat, _
ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc)
End Sub

In Fig. 8 shows dialog boxes Function Wizard And Function Arguments after executing the DescribeFunction() procedure.

Rice. 8. View of dialog boxes Function Wizard And Function Arguments for a custom function

The DescribeFunction() procedure should only be called once. Once it is called, the information associated with the function is stored in the workbook. But if you modified the procedure, call it again.

If you do not specify a function category using the MacroOptions method, the custom worksheet function appears in the category User defined dialog box Function Wizard. The table (Fig. 9) lists the category numbers that can be used as values for the Category argument of the MacroOptions method. Please note that some of these categories (10 to 13) do not normally appear in the dialog box Function Wizard. If you assign one of the user functions to a similar category, it will appear in the dialog box.

Using add-ons to store custom functions

Optionally, you can save frequently used custom functions in an add-in file. The main advantage of this approach is that functions can be applied in formulas without a file name specifier. Let's say you have a custom function ZapSpaces; it is stored in the Myfuncs.xlsm file. To apply it to a formula in a different workbook (other than Myfuncs.xlsm), you must enter the following formula: =Myfuncs.xlsm!ZapSpaces(A1:C12).

If you create an add-in based on the Myfuncs.xlsm file and the add-in is loaded in the current Excel session, you can skip the link to the file by entering the following formula: =ZapSpaces(A1:C12). Creating add-ons will be discussed separately.

A potential issue that may arise from using add-ins to store custom functions is the dependency of the workbook on the add-ins file. If you give a workbook to a co-worker, be sure to also give a copy of the add-in that contains the required functionality.

Using Windows API Functions

VBA can borrow methods from other files that have nothing to do with Excel or VBA, such as DLL (Dynamic Link Library) files that are used by Windows and other programs. As a result, VBA can perform operations that, without borrowed methods, would be beyond the capabilities of the language.

Windows API (Application Programming Interface) is a set of functions available to programmers in the Windows environment. When you call a Windows function from VBA, you are accessing the Windows API. Many Windows resources used by Windows programmers can be obtained from DLL files, which store programs and functions that are connected at runtime rather than at compile time.

Before a Windows API function can be used, it must be declared at the top of the program module. If the software module is not a standard VBA module (i.e. a module for UserForm, Sheet or This book), then the API function must be declared as Private.

Declaring an API function has some complexity - the function must be declared as precisely as possible. The declaration statement tells VBA the following:

what API function are you using;
in which library the API function is located;
API function arguments.

Once declared, the API function can be used in a VBA program.

Let's look at an example of an API function that displays the name of a Windows folder (this task is sometimes impossible to accomplish using standard VBA statements). First, let's declare the API function:

Declare PtrSafe Function GetWindowsDirectoryA Lib "kernel32" _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long

This function, which has two arguments, returns the name of the folder in which the Windows operating system is installed. After calling this function, the path to the Windows folder will be stored in the lpBuffer variable, and the length of the path string will be stored in the nSize variable.

The following example displays the result in a message box:

Sub ShowWindowsDir()
Dim WinPath As String * 255
Dim WinDir As String
WinPath = Space(255)
WinDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
MsgBox WinDir, vbInformation, "Windows Directory"
End Sub

While the ShowWindowsDir procedure is running, a message box appears indicating the location of the Windows folder.

Sometimes you need to create a wrapper for API functions. In other words, you will create your own function that uses the API function. This approach greatly simplifies the use of the API function. Below is an example of such a VBA function:

Function WindowsDir() As String
"Windows folder name
Dim WinPath As String * 255
WinPath = Space(255)
WindowsDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
End Function

After declaring this function, you can call it from another procedure: MsgBox WindowsDir(). You can also use this function in a worksheet formula: =WindowsDir().

Attention! Don't be surprised by system crashes when using Windows API functions in VBA. Save your work before testing.

Determining the state of a key

Let's say you've written a VBA macro that will be executed from a toolbar button. This macro needs to be executed differently if the user holds down the key after clicking the button . To know about a key press , you can use the GetKeyState API function. The GetKeyState function reports whether a specific key is pressed. The function has one argument, nVirtKey, which represents the key code of the key you are interested in.

Below is a program that detects that a key was pressed during the Button_Click event procedure. . Please note that to determine the state of a key a constant (taking a hexadecimal value) is used, which is then used as an argument to the GetKeyState function. If GetKeyState returns a value less than 0, it means the key is pressed; otherwise the key not pressed. A similar check can be done for the Ctrl and Alt keys (Fig. 10).

Rice. 10. Checking the Shift, Ctrl and Alt keystrokes

The VBA function code can be found in the attached Excel file

Working with Windows API functions can be quite complex. Many programming books list API function declaration statements with corresponding examples. Typically, you can simply copy the declaration expressions and use the functions without understanding them. Most Excel VBA programmers view API functions as a panacea for solving most problems. On the Internet you will find hundreds of completely reliable examples that you can copy and paste into your own program.

The text file contains Windows API declarations and constants. You can open this file in a text editor and copy the corresponding declarations into the VBA module.

Based on materials from the book. – M: Dialectics, 2013. – P. 287–323.

A function written in VBA is code that performs a calculation and returns a value (or an array of values). Once you create a function, you can use it in three ways:

As a formula in a worksheet, where it can take arguments and return values.
As part of your VBA routine. In a Sub procedure or inside other functions.
In the conditional formatting rules.

Although Excel already contains more than 450 built-in functions, they are also sometimes lacking. Sometimes built-in functions can't do what you want to do. Sometimes, to achieve a result, you need to create a huge and complex formula that is not understandable to others. In this case, you can create a custom function that is easy to read and use.

Note that user-defined functions created with VBA are generally significantly slower than built-in functions. Therefore, they are best suited for situations where you cannot get the result using built-in functions or there is not much computation and the performance penalty is not critical.

Not only you, but also your colleagues can use the built-in functions. The functions you write will appear alongside others in the Function Wizard dialog box. You may be intimidated by the process of creating functions, but I hasten to assure you that it is quite simple.

What is the difference between a procedure (Sub) and a function (Function)?

The main difference is that a procedure (sub) is used to execute a set of commands, and is not intended, unlike a function (function), to return a value (or an array of values).

To demonstrate, let's give an example. For example, there is a series of numbers from 1 to 100 and you need to separate the even ones from the odd ones.

Using the (sub) procedure, you can, for example, go through the cells and highlight the odd ones using a fill. And the function can be used in the adjacent column and it will return TRUE or FALSE depending on whether the value is even or not. Those. you won't be able to change the fill color using a function in the worksheet.

Creating a Simple User-Defined Function in VBA

Let's create a simple custom function in VBA and see how it all works.

Below is the code for a function that leaves only numbers from the text, discarding letter values.

Function Numbers(Text As String) As Long Dim i As Long Dim result As String For i = 1 To Len(Text) If IsNumeric(Mid(Text, i, 1)) Then result = result & Mid(Text, i, 1 ) Next Numbers = CLng(result) End Function

For everything to work for you, you need to paste this code into the book module. If you don’t know how to do this, then start with the article How to record a macro in Excel.

Now let's see how the function works, let's try to use it on a sheet:

Before analyzing the function itself, let’s note 2 pleasant moments that appeared after its creation:

It became available, like any other built-in function (we’ll tell you how to create a hidden function later).
When you enter the "=" sign and start typing the name of the function, Excel displays all matches and shows not only the built-in functions, but also the custom ones.

Let's analyze the function step by step

Now let's take a deep dive and see how this feature was created. The function starts with the line

Function Numbers(Text As String) As Long

Word Function speaks about the beginning of the function, followed by its name, in our case Numbers.

The function name cannot contain spaces. Also, you can't call a function if it collides with a cell reference name. For example, you cannot name the function ABC123 because that name also refers to a cell in the Excel worksheet.
You should not give your function the same name as an existing function. If you do this, Excel will give preference to the built-in function.
You can use the underscore character if you want to separate words. For example, Suma in cuirsive is a valid name.

After the name, the function arguments are described in parentheses. Similar to built-in Excel functions. In our case, the only argument used is Text. After the name of the argument we indicated As String, this means that our argument is a text value or a cell reference containing a text value. If you don't specify a data type, VBA will treat it as Variant(which means you can use any data type, VBA will figure it out for you).

Last part of first line As Long specifies the data type that the function returns. In our case, the function will return integer values. This is also not necessary.

The second and third lines of the function declare additional internal variables that we will use.

Dim i As Long Dim result As String

Variable i we will use it to enumerate characters. A variable result to store the intermediate result of a function.

The function's task is to go through all the characters in the Text variable and save only those that are numbers. Therefore, we will start the loop from 1 to the last character.

For i = 1 To Len(Text)

Len is a function that determines the number of characters.

The main line of the function is checking whether the next character of the text is a number and, if so, storing it in the result variable

If IsNumeric(Mid(Text, i, 1)) Then result = result & Mid(Text, i, 1)

For this we need a function IsNumeric- she returns True if the text is a number and False otherwise.

Function Mid takes from the Text argument i th character (meaning 1 , indicates that the function Mid takes only 1 character)/

Function Next- closes the loop For everything is clear here.

Numbers = CLng(result)

With this line we convert the text variable result, which contains all the digits of the argument Text, to a numeric value. And we say what result our function should output Numbers.

The last line of code is End Function. This is a required line of code that tells VBA that the function code ends here.

The above code describes the different parts of a typical user-defined function created in VBA. In future articles, we'll look at these elements in more detail, as well as look at different ways to perform a VBA function in Excel.

Almost all the program code of VBA modules is contained in procedures of two types: Sub (subroutines) and Function (functions). The main task of a Function procedure is to calculate some value and return it to the point at which the function procedure is called.

Function procedure syntax:

Function Function_Name(arguments As) As

Function_Name = Return_Value

End Function

Function procedures can be used in a variety of expressions.

Example 1

For example, the simplest procedure-function Function:

Function F1(x As Currency) As Currency

Function F1(x) can be used in further calculations (module code). A procedure of type Function can only be executed by calling it from another procedure. To do this, the calling procedure must assign the name of this F1(x) to some variable.

Example 2

For example, Function F1(x) can be used in the MySub() procedure by naming the variable "y" F1(x).

Dim y As Single ‘Declaration of variable y

y = F1 (9) ‘Determine F1 (x) for the value x=9

Debug.Print y ‘Print values in the Immediate window

Function F1 (x As Single) As Single

F1 = x^10 ‘Return value of x to the power of 10

Here Function F1(x)=$x^(10)$ for the value $ x=9$ returns the value $3.486785E+09$ to the calling procedure MySub(). If a function return value or variable that is used in a VBA procedure does not have a declared data type, the default data type will be Variant.

VBA uses both Function procedures and built-in functions.

Built-in functions consist of two parts: a name (identifier) and arguments. Built-in functions are ready-made VBA formulas that perform certain actions on expressions and return some value at the point of their call.

Functions return a result value instead of their name, which is used in further calculations. Typically, functions require arguments, which are written in parentheses separated by commas. But some functions do not require arguments. For example, the Now() function, which requires no arguments, returns the current system date and time. Functions can be used to create new expressions or functions.

How to insert a function into a program text? To use a function in expressions, you must enter its name in a VBA statement. To call a built-in function that requires no arguments, just enter its name (for example, Now) into the module program code:

Sub MyDate()

Dim TD ‘Declare a TD variable

TD = Now ‘Determine the current system date and time

Debug.Print TD ‘Print values in the Immediate window

To call a function that requires entering one or more arguments, you must enter its name on the right side of the assignment operator with the parameters (argument values) enclosed in parentheses. For example, to call the built-in Function Log (N) with one variable N in a procedure of type Sub, the variable Log_N is assigned the name of the function Log (50) with an argument value of 50.

Sub Natural logarithm()

Dim LogN ‘Declare the variable LogN

Debug.Print LogN ‘Print values in the Immediate window

Note 1

Here, the built-in function Log(N) for an argument value of 50 returns the value 3.91202300542815 at the point of calling Log(50) of the calling procedure "Sub Natural_logarithm()". A function can be called either using a separate VBA statement or by placing its name with a list of argument values (parameters) in a formula or expression in a VBA program. VBA uses function nesting to shorten notation by allowing one function call to be specified as an argument to another function. In this case, the return value of the first function is used as an argument for the next function.

VBA has a large set of built-in functions and routines that make programming easier, which can be divided into the following categories:

mathematical;
type checking functions;
string processing functions;
formatting function;
format conversion functions;
date and time functions.

Mathematical functions

Mathematical functions include:

Abs (x) - modulus of argument $x$;
Cos(x) - cosine of argument $x$;
Exp(x) - raising the base of the natural logarithm to the power $x$;
Log(x) - natural logarithm of argument $x$;
Rnd - random number from the interval;
Sin(x) - sine of argument $x$;
Sqr(x) - square root of $x$;
Atn(x) - arctangent of $x$;
Tan(x) – tangent of $x$;
Sgn(x) – sign of $x$.

Fix(x) and Int(x) both functions discard the fractional part of a number and return an integer value. The difference between these functions is for negative argument values. Int(x) returns the nearest negative integer less than or equal to x, and Fix(x) returns the nearest negative integer greater than or equal to x.

Type checking functions

Here are the functions that determine what type a variable is:

IsArray(x) – the function checks whether the variable is an array;
IsDate(x) determines whether the variable is a date;
IsError(x) determines whether the variable is an error code;
IsNull(x) determines whether the variable is an empty value;
IsNumeric(x) determines whether the variable is a numeric value;
IsObject(x) determines whether a variable is an object.

Format function

The formatting function returns a Variant (String) value containing an expression formatted according to the function syntax:

Format(Expression[,Format [,First day of the week[,First Week of the Year]]]),where:

Expression – a required argument (any valid expression – a combination of keywords, operators, variables and constants, the result of which is a string, number or object);
Format is an optional parameter (any valid named or user-defined format expression).

If a $ sign is added to the function name, then the function returns a value of type String., then the function returns a value of type String.

When creating your own number format, you can use the following characters:

0 – reserves the digital bit position. Displays a digit or zero. If the number being formatted has any digit at that digit position where there is a 0 in the format string, the function displays that digit; if not, then a zero is displayed at that position;
"#" - the action of this symbol is similar to the action of 0 with the only difference that insignificant zeros are not displayed;
. – reserves the position of the decimal separator, determines how many digits need to be displayed to the left and right of the decimal point;
% - reserves the percentage display of a number;
. – separates hundreds from thousands.

Format conversion functions

These include:

Val(string) – returns the numbers contained in the string as a numeric value of the appropriate type.
Str(number) – returns a Variant (String) value that is a string representation of a number.

In addition to the Val and Str functions, there are a number of functions for converting expression types.

String processing functions

Among the many functions are the following:

Chr(code) – converts ASCII code into a string. For example, Chr(10) performs a new line, Chr(13) performs a carriage return;
Mid(string, pos[,length]) – returns a substring of a string containing the specified number of characters, where String is the string expression from which the substring is extracted;
Pos – the position of the character in the String from which the desired substring begins;
Length – number of returned substring characters;
Len(string) – returns the number of characters in a string.