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:

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.

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.

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.

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.

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.

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

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

  

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

  

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.

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.

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.

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):

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:

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:

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.

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

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:

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.

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.