Creating functions in Excel VBA – User functions

Creating own function defined by user

Both Excel and VBA offer us a number of useful functions. But what if we would like to create a function that we will use in Excel? The possibility of creating so-called user functions. These are functions whose source code we write ourselves. We 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 is to return the text “This is the result of a function” and does not require arguments. The simplest example of such a function is given below:

As you can see, the simplest form of the user’s functions is as follows:

Note that the presence of arguments is not mandatory. For example, the TODAY  spreadsheet function () also has no arguments that we could enter from the datasheet. The most important thing to remember from our schematic is the reference to the “MyFunction” variable in the instructions. This is the result returned by our function. The name of the variable corresponds to the function name. We can compare it to the Return statement in other programming languages. We use the user functions in the same way as for any other function. We can use it both in the VBA code and in the Sheet after entering the name of our function.

 

Arguments and advanced function diagram

The above function diagram does not specify arguments or data types. As we 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 . A more advanced diagram of building your own functions in Excel VBA looks as follows:

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

Let’s create a new function now. Our function should accept two text arguments: name and surname. The result of the function should be initials written in large 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 :

 

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

Debugging functions – How do I 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 below screenshot.

 

Creating your own VBA functions – Tasks (You can enter the solution in a comment)

  1. Write a function that changes the PESEL number to date of birth. You can check the terms of this feature in this article .
  2. Create a function that counts the days remaining until the end of the current year.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: