Creating functions in Excel VBA - User defined functions

Last Updated on January 11, 2023 by token

Creating VBA functions defined by user

Both Excel and VBA offer you a number of useful functions. But what if you would like to create a VBA function that you will use in Excel? VBA offers you possibility of creating your own functions. These are functions that source code you write yourselve. You can use them in both VBA code and in Excel spreadsheet.

The simplest user function – How to write a function in VBA Excel?

Let’s create a simple user function. Let’s create a new module in the VisualBasic Editor . The function will return the text “This is the result of a function” and does not require any arguments. The simplest example of this function is written here:

Function MyFunctionName()
 MyFunctionName = "This is function result"
End Function

The simplest user function - How to write a function in VBA Excel? — The simplest user function – How to write a function in VBA Excel?

As you can see, the simplest form of the user’s function looks like this:

Function  MyFunctionName([params])
 vba code
End Function

Note that the function arguments are not required. For example, the TODAY() spreadsheet function also has no arguments that we could enter from the datasheet. The most important thing to remember is the reference to the “MyFunctionName” variable in the code. This is the result returned by your VBA function.

The name of the variable corresponds to the function name. You can compare it to the Return statement in other programming languages. You can write the user functions in the same way as for any other function. You can use it both in the VBA code and in the Sheet after entering the name of your function.

Arguments and advanced function scheme

The function written above does not specify arguments or data types. As you know, both the arguments and the result returned by the functions have their data types. If you do not specify them, they will take the Variant data type . You can read more about the types of data in this part of the course: vba variant. A more advanced scheme of building your own functions in Excel VBA looks like this:

[function declaration] Function FunctionName([parameter1 as data_type, parameter2 as data_type...]) as return_data_type 
[vba code]
End Function

The level of declaration , that is declaring within what scope of the VBA project, your function works. If you want the function to be used in both the worksheet and any of the VBA code you write, the Public level is recommended here. If you want the function to work on the sheet and within the module – you use the Private level. You can learn more about the levels of declarations from this article: VBA variables.
You define the name of the arguments, so the values that the function will process. You need to also specify the types of argument data, otherwise the arguments will be the Variant type.
Finally, you should specify the data type for the value that will be returned by the function. If we do not do it automatically will be a Variant type . When you are creating large functions that engage the resources of your computer, it is recommended to specify data types.

Let’s create a new user function. Our function should accept two text arguments: first name and last name. The result of the function should be initials written in big letters created from arguments. The result itself should also return a value in the String data type. Let’s call our function ” INITIALS“. We declare the function at the Public level:

Public Function initials(firstName As String, lastName As String) As String
 initials = UCase(Left( firstName  , 1) & Left( lastName  , 1))
End Function

In our new function we have drawn the first letters of the first and last name using the VBA LEFT function. We combine the texts with the operator “&”. Then, using the VBA UCase function, we change the letters of initials to big letters.

VBA UDF - Arguments and advanced function scheme — VBA UDF – Arguments and advanced function scheme

Debugging functions – How do we debug functions in VBA?

To check if our function works correctly, we should test it. You can of course do it after writing a function. However, if we want to check at the level of the VBA code, if the code of our function does not contain programming errors, we should use debugging. In the VisualBasic Editor, select the Debug tab, and then the Complie VBAProject. Incorrectly written function will return an error, as in the screenshot: