06 - Functions


From the second lesson of this tutorial we have learned that the VBA code can be stored either in subroutines or in functions.

However, in all the examples presented so far we used only subroutines. Now this is about time to discuss functions in details.

Except for the subject of functions, we will come back to the topic of passing parameters to procedures or functions - this time we will focus on optional parameters.

We will also introduce a very helpful VBA editor feature - Immediate window - that offers yet another way to run macros and makes it possible to check the value of variables even if macro is running.

Basics of functions

The most important difference between procedures and functions is that a function can and should return some value or object.

Until now, we have been invoking only procedures which get some input parameters and make some calculations or processes based on those parameters but couldn't return any value back to the code that invoked them.

We have seen in the example from the previous lesson that procedure can change values of input parameters, however it is not the same as returning some value without modifying input parameters.

In order to make it clearer, let's get back to the above-mentioned example from the previous lesson intended to calculate net salary. The code of that example looked like below:
1
2
3
4
5
6
7
Sub earnings()
    Dim salary As Long

    salary = 2500
    Call taxDeduction(salary)

End Sub
1
2
3
Sub taxDeduction((salary As Long)
    salary = salary - (salary * 0.18)
End Sub

Let's recall how this macro works:
  • main procedure earnings contains one variable called salary,
  • value 2500 is assigned to the variable salary,
  • subroutine taxDeduction is invoked with variable salary given as a parameter,
  • in subroutine taxDeduction the value of salary variable is modified (tax is subtracted),
  • the code execution moves back to the subroutine earnings but the value of salary variable is already modified.

Now, let's assume we need two variables in our application, gross and net, and we want to assign a proper value to each of them. Below is the code with variable net added and rest of the code left unchanged (it is recommended to put a breakpoint in every single row of the code and check what happens with the values of variables when the code is running):
1
2
3
4
5
6
7
8
Sub earnings()
    Dim gross As Long
    Dim net As Long

    gross = 2500
    Call taxDeduction(gross)

End Sub
1
2
3
Sub taxDeduction(gross As Long)
    gross = gross - (gross * 0.18)
End Sub

In fact, this code is very similar to the previous one, the only difference being the additional variable and modified names of variables (gross and net, instead of salary).

In lines 5. and 6. value 2500 is assigned to the variable gross and then the subroutine taxDeduction is invoked with variable gross given as an input parameter. The taxDeduction subroutine has not been changed, so, as you remember from the previous example, it will modify the value of variable gross in such way that 18% of this value will be subtracted as a tax.

After the taxDeduction subroutine is finished, the code comes back to the main procedure earnings(), or, more precisely, to its End statement. At this moment both variables declared in this code have a value different from what was expected:
  • variable gross is equal to 2050 (it was initialized with the value 2500 but was later modified by taxDeduction procedure) | Expected value was 2500
  • variable net is equal to 0, since there is not even a single point in the code where this variable is assigned with any value | Expected value was 2050

One of the possible solutions for this issue is to modify taxDeduction procedure by adding second input parameter - net:
1
2
3
4
5
6
7
8
Sub earnings()
    Dim gross As Long
    Dim net As Long

    gross = 2500
    Call taxDeduction(gross, net)

End Sub
1
2
3
Sub taxDeduction(gross As Long, net As Long)
    net = gross - (gross * 0.18)
End Sub

The code above works and returns the correct results. However, it has several serious drawbacks that make it not very useful.

The most important one is that the current version of subroutine taxDeduction requires two input parameters, but in fact parameter net is redundant here. The only purpose of taxDeduction subroutine is to calculate net salary and to complete this task it needs only information about gross salary and tax rate. Since the tax rate is defined as constant value 0.18 in this procedure, gross salary is the only information required by this subroutine from outside.

What is the reason for passing variable net then?
As you remember, subroutines cannot return any value outside, but they can modify the value of input parameters. So, if we want to assign the value of calculations made by subroutine taxDeduction to the variable net we can make it only inside this subroutine and that is why we passed this variable as an input parameter.

Passing redundant variable to a procedure should be avoided because of performance reasons. Every time you pass a parameter to a procedure, it consumes a little bit of time and memory. Of course, if you invoke this subroutine only once there would be no visible performance losses. However, if you try to use taxDeduction procedure for a bigger set of data, i.e. when calculating net salary for 30 thousand people for 10 years, this procedure would be invoked hundreds of thousands of times and the time wasted because of redundant input parameter would be measured in seconds or even minutes, instead of microseconds.

Another drawback of the solution above is that this code is confusing for someone trying to read it. Most developers would have problem to find the point in this code where variable net changes its value from 0 to the proper net salary.

As you have seen in the example above, subroutines have some limitations. We can eliminate all of them by using functions.

Below is another version of the same macro, but this time a separate function is used for calculating net salary, instead of another subroutine:

1
2
3
4
5
6
7
8
Sub earnings()
    Dim gross As Long
    Dim net As Long

    gross = 2500
    net = salaryAfterTax(gross)

End Sub
1
2
3
Function salaryAfterTax(value As Long) As Long
    salaryAfterTax = value - (value * 0.18)
End Function

The subroutine taxDeduction doesn't exist any more. It has been replaced by the function salaryAfterTaxes.

Macro consists now of a main procedure called earnings and a function called salaryAfterTaxes.

There are two variables of the Long type declared in subroutine earnings: gross and net.

In the row 5. value 2500 is being assigned to the variable gross. At this point, the only thing left to do is to calculate net salary and assign this value to the variable net. We will complete this task by using the function salaryAfterTax shown in the code frame above.

You can imagine a function as a box. We provide it with required input data, it processes those data according to its algorithm and finally returns a single result (however, in fact it can be array or objects containing many values) that can be used further in the code, i.e. assigned to a variable or printed in an Excel worksheet.

In the next line of the code:
6
net = salaryAfterTax(gross)
the compiler is instructed to populate the variable net with the result returned by the function salaryAfterTaxes for the input parameter value equal to the current value of variable gross (2500 in our example).

Once a function is invoked in the code, compiler leaves the current procedure and moves to this function's body. It will get back to the invoking method right after the function is finished.

Let's look at how the single function is build and how it works.

As it was already mentioned, the keyword used for declaring function is Function. After that we need to give the name of the function (the rules for naming functions are the same as the rules for naming subroutines described earlier) - it should be descriptive and unique within the current code module.

After giving the name of the function we need to list in brackets all the input parameters required by this function along with their data types. Input parameters are separated from each other with commas.
If a function doesn't require any input parameter, empty brackets should be placed after the name of the function.

The last part of the function opening statement is the keyword As and the type of value returned by this function.

General form of function declaration looks like below:
 
Function functionName([arg1 As Type, ..., argN As Type]) [As ReturnedDataType]

If you don't specify what type of data is returned by a function, it will be considered by compiler as Variant type (and as it was already mentioned, for performance and maintenance reasons it is not a good practice to declare anything as Variant, unless there is no other option).

In the example above the function has been called salaryAfterTax. The name is descriptive so everyone knows at the first glance what is the purpose of this function.

The type of data returned by the function has been declared as Long, so all the results will be rounded to integer values.

The function requires only one input parameter of Long type - gross, and as we have found out earlier, this is the only information required to calculate net salary. No more redundant parameters, by using function we were able to get rid of them.

Be careful when declaring data types of input parameters and function result!

If you have declared a function with a single input parameter of numeric type (i.e. Integer, Long or Double), but when invoking this function you pass a text instead of a number, macro will not run and compiler will throw the following error: 'Run-time error 13: Type mismatch'.

When we were discussing the issue of invoking procedures or functions with parameters, it was told that all the input arguments can be used as regular variables declared in this procedure/function. If function salaryAfterTax takes a single input parameter value, it means there is already variable value in this function and we cannot declare another one with the same name.

In case of functions, the name of the function itself is also treated as additional variable. If the function above is called salaryAfterTax, we can refer to the variable with the same name within the body of this function, but we cannot declare another input parameter or variable with the same name, since it would cause Compile error: Duplicate declaration in current scope to be thrown when trying to run this macro.

There is another important thing you have to know about this specific variable named after the function itself. This is the most important variable in the whole function and the value assigned to this variable is treated as the result of the function itself.

As it was told before, a function is like a box - we put some input parameters into this box, close it for processing and after that we get a single output value. This output value is always equal to the final value of variable called the same as the name of the function.

So, in the example above the function will return the same value as variable salaryAfterTax has at the moment when the code leaves this function's body. That is why we assigned the result of subtracting 18% from gross salary to this variable in the second line of the function code - we just wanted the result of this operation to be the result of the function salaryAfterTax, so we needed to assign its result to the variable with the same name.

The next row is already End statement of the function, so the code will leave this function in the next line and the value of variable salaryAfterTax becomes the final result of the function with the same name.

Since the function has already finished working, the code execution gets back to the main procedure earnings, exactly to the row where it left it before, and assigns the result of the function obtained a moment before (2050) to the variable net.

Functions can be also invoked with the keyword Call, just like subroutines.

In such case, the function doesn't return any value or to be more precise, it does return the value but this value is not assigned to any variable and it is erased right after the code jumps to the next line.

This method of invoking can be usefule only for functions that make some additional things besides of returning values, i.e. saving the file on disk or displaying messages for user (this kind of functions are discussed in details in the next lesson).

Concluding our discussion about functions, let's think what is the point of using functions at all? Don't you think the function above would be easier to understand if it looks like that:
1
2
3
4
5
6
7
8
Sub earnings()
    Dim gross As Long
    Dim net As Long

    gross = 2500
    net = gross - (gross * 0.18)

End Sub

In the code above, all the calculations previously made by function salaryAfterTax have been put directly to the main procedure earnings.

It can seem that this solution is better since the code is much shorter and it should work slightly faster, because there is no need to invoke function and pass parameters to this function any more.

Indeed, it could be acceptable in such a simple macro. However, real applications are much more complex and consist usually of hundreds of procedures and functions.

Imagine that you would have twenty procedures in your application that need to calculate net salary at some stage. If you would like to put the code for calculating net salary directly in those procedures instead of creating a separate function for this task, you would have to repeat the same code twenty times.

In our example calculations made by the function salaryAfterTax are quite simple and take only one line of code, so the vision of repeating it, even twenty times, is not that terrifying. But remember that this is just the simplified model of calculating tax and the real model would be more complex and would take at least dozen lines of code.

If you use functions, the code for calculating net salary is included only once in your code, in a separate function, and any other procedure/function can access it by invoking this function with the proper set of input parameters - operation that takes only one line of code.

If you would like to avoid functions in your code, you would have to insert the same code into every procedure or function that need to calculate salary net.

Now, let's imagine the government has carried out some reforms and the method of calculating the tax payables has completely changed and you need to implement it in your application.

If you have been using functions and all the code responsible for calculating tax is located in a single function only, it will not be a serious problem - you just need to change the code of this function and all the procedures/functions invoking it will have access to the new code automatically.

On the other side, if you have avoided functions and the code for calculating tax is located in twenty different procedures, in order to make the application up-to-date and consistent you would have to find each part of the code responsible for calculating tax and replace it with the new code.

If you think any calculation or process could be used more than once in the application (or if there is a chance it could be useful in other applications) it is worth to create a separate function for this task.

A good practice is to create a separate module intended to be the library of useful functions. Every time you create a new function, think if it could be used in other applications and if your answer is positive, copy it to this library module.

When starting to work on a new VBA project, the first thing you should do is to copy this library module into the project to have access to all those functions you have created and tested before - there is no need to reinvent the wheel, if you have a function already developed there is no point to waste your time and do it again from scratch.

Using your own VBA functions in Excel worksheets

All the functions you develop in VBA editor can be used in a regular Excel worksheet..

Using your own function in Excel worksheet is very easy and it doesn't differ from using typical built-in Excel functions. All you need to do is type equal sign, the name of your function and all the necessary parameters.

The picture below shows example of using function salaryAfterTax in Excel worksheet.

Using VBA functions in Excel worksheets

RULES FOR USING USER DEFINED FUNCTIONS IN EXCEL WORKSHEETS

You can use in Excel worksheet all the functions from all the currently opened Excel workbooks. So, the function salaryAfterTax can be used not only in the file in which we created it, but also in any other Excel file, as long as its source file remains opened.

If you want to use a function from the other Excel file, you need to give the name of this file before the name of the functions.

Invoking function salaryAfterTax within the Excel file containing this function (text.xls):
 
=salaryAfterTax(5000)

Invoking function salaryAfterTax within any other Excel file:
 
=test.xls!salaryAfterTax(5000)

The exception to this are the VBA functions stored in installed Excel Add-Ins. They can be used in Excel worksheets without specifying full path to the source file even if they come from the other file.

In fact, Excel Add-In files (extension *.xla, *.xlax) are the most suitable ones when it comes to storing functions to be used in Excel worksheets.

Listed below are some advantages of Excel Add-In files as container for VBA functions:
  • Add-In files are being opened automatically when the Excel application is started so you don't have to bother to open them manually,
  • when referring a function from an Add-In file you don't need to add full path to the source file - the name of the function is enough,
  • Add-In files are invisible for users, since all their worksheets are hidden.

If you close a file containing VBA functions used in Excel worksheets from other files, errors like that will appear after recalculating the spreadsheets:

Errors caused by closing a file with VBA functions used in worksheets

Notice that the name of the file before the name of the function (text.xls!) has been automatically replaced with the full path to this file.

If you want to see the list of all currently available user functions, you need to open the Insert function window.

You can do it in two ways:
  • Clicking Insert function button on the Formulas ribbon tab

    Insert function button on the formula tab

  • Clicking fx icon on the Excel formula bar (bordered in red on the picture below):

    Opening Insert function window from Formula bar level

When Insert functions window appears on your screen, select User Defined category (bordered in red on the picture) and all the functions from all the open files will be listed below.

List of all user functions

One thing worth mentioning is that the list contains all the VBA functions: not only ones intended to be used in Excel worksheet, but also functions designed for internal use in VBA that should not be used in worksheets.

For better readability, it is a good practice to work out your own style of naming functions intended for worksheet usage. In the example below, all the functions intended for worksheets have been named in capital letters and can be easily distinguished from the rest of them.

That is all about the functions for now. Of course, it doesn't mean we will not use functions any more. On the contrary, we will use them more and more and you will see dozens of new functions during each of next lessons.

Optional parameters

In a few recent examples you have learned how to create and call functions or procedures with input parameters. As it was told in the previous lesson, if you try to call a function or procedure that requires input parameters, without specifying those parameters, the compiler will report error "" and macro will not even start.

However, it often happens that a function or procedure requires some input value only in specific cases, while in other cases this value would be completely redundant.

Let's assume that we need to develop function salaryAfterTax from the previous chapter in that way that it can take an additional parameter - taxFreeAmount. However, in some cases parameter taxFreeAmount will not be required, so it must be possible to invoke this function just like before, without specifying this argument.

Modify the function salaryAfterTax as shown below (new code is highlighted in red):
1
2
3
4
5
6
7
Function salaryAfterTax(baseSalary As Long, _
  Optional taxFreeAmount As Single) As Long
  Dim amountForTax As Single

  amountForTax = baseSalary - taxFreeAmount
  salaryAfterTax = baseSalary - (amountForTax * 0.18)
End Function

There is a new input parameter in the declaration of this function - taxFreeAmount.

What makes it different from any other parameter presented before is the keyword Optional before its name. This word suggests that this parameter can be specified when invoking this function, but it can be omitted as well and function will work without any issues.

According to the above, both statements below are correct:
 
netSalary = salaryAfterTax(5000)             'optional parameter taxFreeAmount is omitted
 
netSalary = salaryAfterTax(5000, 2542.24)    'optional parameter taxFreeAmount is specified (2542.24)

Let's get back to the previous version of the function salaryAfterTax (without optional parameter). In that version tax rate used to calculate net salary was hard-coded in the function and was always 18%. It was not possible to use other tax rate without modifying the code of the function.

Now, let's assume the tax rate is usually 18% but there are some specific cases when other rate is required. We need to modify the function so that it would be possible, depending on the issue, to explicitly give the specific value of tax rate or skip it and use the default value.

This function should look like that (new code is highlighted in red):
1
2
3
4
Function salaryAfterTax(baseSalary As Long, Optional taxRate As Single = 0.18) As Long

  salaryAfterTax = baseSalary - (baseSalary * taxRate)
End Function

In this version of the function, parameter taxRate is optional.

Optional parameters, as any other parameter, can be declared with the specific type of data and this limits the range of values that can be passed to this parameter. In the example above, optional parameter taxRate has been declared as a Single type.

Additionally, for each optional parameter we can define its default value that will be used in the case if this parameter is omitted when invoking the function. In our example the default value for optional parameter taxRate is 0.18, so every time we call this function without specifying the value for taxRate parameter, 0.18 will be used.

A single procedure or function can take up to 60 input parameters and it is not limited how many of them are optional - function can have only optional parameters, or none optional parameters at all.

When declaring input parameters for a function, all the optional parameters must be stated at the end of the parameter list!

The statement below is illegal and will cause compilation error:
 
Function salaryAfterTax(Optional taxRate As Single = 0.18, baseSalary As Long)
because an optional argument is declared before a mandatory argument.

Remember to specify mandatory parameters before optional ones. Actually, you don't even have to remember that, since compiler will remind you if you make a mistake - each function declaration with optional parameters stated before mandatory ones are automatically highlighted in red and macro cannot be compiled and run until this issue is fixed.

Immediate window

Immediate window can be used for the following purposes:
  • checking the value or state of variables,
  • calling subroutines,
  • checking the result of functions.

Immediate window is activated by pressing the key combination Ctrl + G. It looks like on the picture below (so as you can see it is not very complex):

Immediate window

Checking the value or state of a variable

By using Immediate window you can check the values of variables, no matter if macro is currently stopped at a breakpoint or if it is running.

Let's use the macro for calculating net salary we created earlier on this lesson, to present both cases.

Put a breakpoint at the last code line of earnings subroutine (End Sub) and run the macro.

When the macro hit the breakpoint and code execution is stopped, go to the Immediate window (you can do it by clicking mouse button inside this window or by pressing key combination Ctrl+G).).

If your cursor is already in the Immediate window, go to the new line and type question mark and the name of a variable which value you want to see:

Example of Immediate query.

After you press Enter on your keyboard, the result of your query is displayed in the row below. The cursor itself will be moved to the next row and it is ready for next queries.

This way of checking variables values can seem less convenient than hovering over the name of variable in the code pane that we described previously. However, the range of data that can be checked this way is much wider - you can check virtually all properties of the application and system. Some examples of queries have been shown in the table below:

VALUE TO BE CHECKEDQUERY
Active worksheet name?ActiveSheet.Name
Active workbook name?ActiveWorkbook.Name
Full path to the current Excel file?ThisWorkbook.Path
Current time?Time
Current date?Date
Current date and time?Now
Currently logged user?Application.UserName

With Immediate window you can also check the value of a variable without even setting any breakpoints and stopping the code flow.

Before we start discussing this subject, clear the content of your Immediate window to avoid being confused with too many query results.

Let's change the subroutine earnings in such way that it will display the current value of net variable twice during its execution: first time, just before assigning the result of function salaryAfterRax to this variable and the second time right after this value is assigned (values 0 and 2050 should be displayed in Immediate window).

Modify the code like shown below and remove the breakpoint you set earlier at the end of the procedure earnings.

1
2
3
4
5
6
7
8
9
10
Sub earnings()
    Dim gross As Long
    Dim net As Long

    pensjaBrutto = 2500
    Debug.Print net
    net = salaryAfterTax(gross)
    Debug.Print net

End Sub
1
2
3
Function salaryAfterTax(value As Long) As Long
    salaryAfterTax = value - (value * 0.18)
End Function

After you run the macro, the following values should appear in the Immediate window:

Immediate Debug.Print

so exactly the ones we have expected.

Generally, in order to see the current value of any variable during the macro is running, without having to stop it, you need to add the following statement in your code;
 
Debug.Print variableName

If you insert more than one Debug.Print statement in a single macro, it is a good practice to add some description to each of them in order to keep track what is represented by values printed in Immediate window.

In the example above you could add the following descriptions:
 
Debug.Print "The value of [net] variable before assigning the result of function [salaryAfterTax]: " & net
 
Debug.Print "The value of [net] variable after teh result of function [salaryAfterTax] has been assigned: " & net

With those descriptions appended, the output printed in Immediate window would look like that:

Debug.Print with description

Running macros

With the use of the Immediate window you can easily start macros.

All you need to do is write in your Immediate window the name of the procedure/function to be run (it can be preceded by the keyword Call) and press Enter.

If you type the following statement in your Immediate window
 
Call earnings
and press Enter, the subroutine earnings will be run.

Checking the results of functions

Another important role of the Immediate window is the possibility to check the results of functions.

If you would like to check the result of the function salaryAfterTax for gross salary equal to 5000, all you need to do is type
 
?salaryAfterTax(5000)
in the Immediate window and press Enter, and the result of 4100 will be displayed in the next line.

This way you can easily perform initial function tests by asking a few queries with different set of input parameters and checking if the expected results are being displayed in Immediate window.

When typing in Immediate window you can use Intellisense feature.

After you type first three letters of the function salaryAfterTax, press Ctrl+Space. This will make VBA editor auto-complete the name of this function (since there is no other function or subroutines starting with 'sal').

Remember to use Intellisense all the time, both in the regular VBA editor and in the Immediate window - it will boost your efficiency.