How to call a VBA procedure?
As you have already learned from previous lessons whole code performing tasks must be placed in procedures (Sub) or functions (Function). I have also mentioned that each task should be placed in separate procedure. You also got to know several ways of executing macros manually.
Let's assume now that you need to write a macro performing ten different tasks. According to the rule of separating tasks mentioned above you should create ten different procedures in this case. You should admit that executing each out of ten procedures manually wouldn't be too comfortable – you would have to start the first procedure, wait until it stops and then launch the second one, wait until it stops and so on.
Fortunately Visual Basic enables automatic execution of needed procedures one by one.
To show you how automatic execution of procedures works we will employ known example of displaying another exponentials of two and three. To make it a little bit harder let's assume that the macro should also display another exponentials of four and five.
To stick with the rule of separating particular tasks from each other we should create separate procedure
for each output number. In total we must write four different procedures:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub powersOfTwo()
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, 1) = power
power = power * 2
Worksheets("Sheet1").Cells(2, 1) = power
power = power * 2
Worksheets("Sheet1").Cells(3, 1) = power
power = power * 2
Worksheets("Sheet1").Cells(4, 1) = power
power = power * 2
Worksheets("Sheet1").Cells(5, 1) = power
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub powersOfThree()
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, 2) = power
power = power * 3
Worksheets("Sheet1").Cells(2, 2) = power
power = power * 3
Worksheets("Sheet1").Cells(3, 2) = power
power = power * 3
Worksheets("Sheet1").Cells(4, 2) = power
power = power * 3
Worksheets("Sheet1").Cells(5, 2) = power
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub powersOfFour()
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, 3) = power
power = power * 4
Worksheets("Sheet1").Cells(2, 3) = power
power = power * 4
Worksheets("Sheet1").Cells(3, 3) = power
power = power * 4
Worksheets("Sheet1").Cells(4, 3) = power
power = power * 4
Worksheets("Sheet1").Cells(5, 3) = power
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub powersOfFive()
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, 4) = power
power = power * 5
Worksheets("Sheet1").Cells(2, 4) = power
power = power * 5
Worksheets("Sheet1").Cells(3, 4) = power
power = power * 5
Worksheets("Sheet1").Cells(4, 4) = power
power = power * 5
Worksheets("Sheet1").Cells(5, 4) = power
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub powersOfTwo()
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, 1) = power
power = power * 2
Worksheets("Sheet1").Cells(2, 1) = power
power = power * 2
Worksheets("Sheet1").Cells(3, 1) = power
power = power * 2
Worksheets("Sheet1").Cells(4, 1) = power
power = power * 2
Worksheets("Sheet1").Cells(5, 1) = power
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub powersOfThree()
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, 2) = power
power = power * 3
Worksheets("Sheet1").Cells(2, 2) = power
power = power * 3
Worksheets("Sheet1").Cells(3, 2) = power
power = power * 3
Worksheets("Sheet1").Cells(4, 2) = power
power = power * 3
Worksheets("Sheet1").Cells(5, 2) = power
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub powersOfFour()
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, 3) = power
power = power * 4
Worksheets("Sheet1").Cells(2, 3) = power
power = power * 4
Worksheets("Sheet1").Cells(3, 3) = power
power = power * 4
Worksheets("Sheet1").Cells(4, 3) = power
power = power * 4
Worksheets("Sheet1").Cells(5, 3) = power
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub powersOfFive()
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, 4) = power
power = power * 5
Worksheets("Sheet1").Cells(2, 4) = power
power = power * 5
Worksheets("Sheet1").Cells(3, 4) = power
power = power * 5
Worksheets("Sheet1").Cells(4, 4) = power
power = power * 5
Worksheets("Sheet1").Cells(5, 4) = power
End Sub
Note that variable power is being declared anew in each procedure.
This is because variable declared by the key word Dim works only inside the procedure it was declared in. Later during the course you will get to know other ways of declaring variables that enable them to be visible in many procedures simultaneously.
If you wanted to run all of these procedures manually right now it would take some time – you would have to hover your mouse over each of them, press F5 (or execute them by other method mentioned in the course section devoted to executing macros), wait until it ends, move to another and so on. In short – waste of time and energy.
Below you can find additional procedure whose only task is to run each of four written procedures one by one what shortens the process of executing out four macros. Now only one procedure is needed to execute four other.
1
2
3
4
5
6
Sub callOtherProcedures()
Call powersOfTwo
Call powersOfThree
Call powersOfFour
Call powersOfFive
End Sub
As you can easily guess by looking at the code above, calling some procedure from the inside of the other procedure is done by writing the key word Call and the name of the procedure that is being called.
When calling procedures you can omit the key word Call and write only the name of the procedure.
However for the clarity of the code it is recommended to apply the instruction Call every time and it is going to be this way during this tutorial.
Apart from the clarity of the code another advantage of applying the instruction Call is the possibility to easily find all usages of a given procedure.
How to pass parameters to a procedure?
If you look to the procedures discussed above displaying exponentials of particular numbers you will notice that all of them are almost identical. The only difference is the number that is being raised to the power and the index of column in which results are displayed.
That is why the same pieces of code repeat over and over again which we should categorically avoid by creating generic procedures.
Below you can find few fragments of code from the example above that repeat in each of four procedures:
Dim power As Long
power = 1
power = power * _
Worksheets("Sheet1").Cells(2, _) = power
Dim power As Long
power = 1
power = power * _
Worksheets("Sheet1").Cells(2, _) = power
In this case a perfect solution seems to be to write a generic procedure in which we would change only the number that is being raised to the power and the number of column with data and the whole rest would be common.
Such general procedure for displaying powers is shown in the frame below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub printPowers(number As Byte, columnIndex As Byte)
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(2, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(3, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(4, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(5, columnIndex) = power
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub printPowers(number As Byte, columnIndex As Byte)
Dim power As Long
power = 1
Worksheets("Sheet1").Cells(1, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(2, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(3, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(4, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(5, columnIndex) = power
End Sub
Now, erase from the VBA editor all four procedures that you have previously made and replace them with the procedure above
printPowers. Procedures from the previous example were used only to show the idea of calling procedures.
Basically in such situations we should write a generic procedure such as procedure printPowers presented above.
Lets now try to execute procedure callOtherProcedures which starts all of the remaining procedures.
Of course this procedure is not going to work! The reason is that the subroutines called by this procedure had already been deleted.
If macro is trying to call a procedure that does not exist, editor will display the following error: Compile error: Sub of Function not defined.
Before executing the macro in a new form the procedure callOtherProcedures must be adjusted
to the new conditions. It should look now like this:
1
2
3
4
5
6
Sub callOtherProcedures()
Call printPowers(2, 1)
Call printPowers(3, 2)
Call printPowers(4, 3)
Call printPowers(5, 4)
End Sub
1
2
3
4
5
6
Sub callOtherProcedures()
Call printPowers(2, 1)
Call printPowers(3, 2)
Call printPowers(4, 3)
Call printPowers(5, 4)
End Sub
Now there should be no problems and after executing the macro expected results should be displayed in worksheet Sheet1.
Note that the code got much shorter after replacing each of the particular procedures with one generic procedure.
In the modified version of printPowers procedure new elements of VBA syntax were used
that need to be explained.
In the first row of the procedure it must be open by the keyword Sub.
Sub printPowers(number As Byte, columnIndex As Byte)
Sub printPowers(number As Byte, columnIndex As Byte)You know this part very well, since it has been discussed a lot of times during this course.
However, apart from the traditional opening of the procedure there is one additional element involved - input arguments, which were defined in the bracket next to the name of the procedure.
The opening row of the procedure with input arguments has the following general form:
Sub procedureName(arg1 As type,
arg2 As type...,
argN As type)
Sub procedureName(arg1 As type,
arg2 As type...,
argN As type)
In the example described above it has been defined that the procedure printPowers
has got two input arguments:
-
argument
numberof Byte type (number from range 0-255), that stands for a number that we will raise to the power, -
argument
columnIndexof Byte type (number from range 0-255), which is going to determine the index of column in which the results will be displayed.
So right now after each execution of that procedure, after keyword Call and its name you will have to define two values of those arguments in the bracket.
Declaring input arguments means that later on user will have to define values for these arguments to execute the procedure.
If you are trying to execute the procedure requiring arguments without defining them, the editor will display the following error: Compile error: Argument nor optional.
Giving a variable as an argument in the opening row of the procedure is equal to normal declaration of that variable in the procedure (more info for declaring variables). It means that in this procedure you cannot declare another variable with the same name.
In the second row of printPowers procedure variable power of a Long
type was declared. At this moment three variables have already been declared in this procedure: potega
and two other variables declared as entry arguments in the opening row – number and columnIndex.
In the 4. row of the code, initial value 1 is being assigned to the
variable power. In the next line this value is displayed in the worksheet Sheet1
on the intersect of first row and the column with index given as columnIndex argument.
In the row 7. value of variable power is multiplied by value
of number argument, which is defined while calling the procedure.
During the rest part of the procedure everything is done according to the scenario known from the previous examples.
Procedure callOtherProcedures has also been changed.
Instead of calling four different procedures, as it used to happen before, it calls the same
procedure (printPowers) four times changing only the values of input arguments assigned to them.
So for example in the row 2 procedure
printPowers is being called with input arguments 2 and 1:
2
Call printPowers(2,1)
which means that the number that we raise to the power is 2 and the results will be displayed in
the first column of worksheet Sheet1.
2Call printPowers(2,1)
Similarly in the 3. row procedure printPowers
is being called which raises number 3 to the power and displays the results in the second column, and so on.
In the example above input arguments given to the procedure printPowers remain unaltered while
this procedure is being executed. However, as you will see in the next example, it is possible to modify the
value of entry arguments inside the procedure.
Modify now printPowers procedure so that it looks as below (new rows are highlighted in red):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub printPowers(number As Byte, columnIndex As Byte)
Dim power As Long
number = 2
power = 1
Worksheets("Sheet1").Cells(1, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(2, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(3, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(4, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(5, columnIndex) = power
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub printPowers(number As Byte, columnIndex As Byte)
Dim power As Long
number = 2
power = 1
Worksheets("Sheet1").Cells(1, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(2, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(3, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(4, columnIndex) = power
power = power * number
Worksheets("Sheet1").Cells(5, columnIndex) = power
End Sub
As you can see in the row 4 we assigned value 2 to the
argument number.
In such situation regardless of the value of argument number
declared while calling the procedure, it will always raise number 2 to the power.
It is worth to mention another important issue related to the calling procedures with arguments.
Paste the following code to the editor:
1
2
3
4
5
6
7
8
Sub earnings()
Dim salary As Long
salary = 2500
Call deductTax(salary)
Cells(1, 1) = salary 'This is net salary
End Sub
1
2
3
4
Sub deductTax(base As Long)
base = base - (base * 0.15)
'another calculation can be made here
End Sub
Procedure earnings is the main procedure and it calls deductTax
subprocedure while being executed.
Below there is a detailed analysis of what is going to happen after running this code.
In the second row of earnings procedure variable salary has been declared
and in the next line of the code value 2500 is assigned to it.
In the row 5 procedure deductTax is being called, which requires
the numeric argument base to be passed (to deduct a tax you must know the gross salary).
Variable salary, still holding value 2500, is being passed as this argument.
Paste the following code to the editor:
1
2
3
4
5
6
7
8
Sub earnings()
Dim salary As Long
salary = 2500
Call deductTax(salary)
Cells(1, 1) = salary 'This is net salary
End Sub
1
2
3
4
Sub deductTax(base As Long)
base = base - (base * 0.15)
'another calculation can be made here
End Sub
1
2
3
4
5
6
7
8
Sub earnings()
Dim salary As Long
salary = 2500
Call deductTax(salary)
Cells(1, 1) = salary 'This is net salary
End Sub
1
2
3
4
Sub deductTax(base As Long)
base = base - (base * 0.15)
'another calculation can be made here
End Sub
Procedure earnings is the main procedure and it calls deductTax
subprocedure while being executed.
Below there is a detailed analysis of what is going to happen after running this code.
In the second row of earnings procedure variable salary has been declared
and in the next line of the code value 2500 is assigned to it.
In the row 5 procedure deductTax is being called, which requires
the numeric argument base to be passed (to deduct a tax you must know the gross salary).
Variable salary, still holding value 2500, is being passed as this argument.
Once the deductTax procedure is called, execution of the whole macro moves to this procedure
and just after finishing it goes back to earnings procedure, exactly to the point where it left.
At this point the compiler is in deductTax procedure in which one variable is declared (base).
It was not declared with the keyword Dim , but it appears as an argument in the opening row of the procedure
which is equal to its declaration as mentioned a few paragraphs above.
Note that when the procedure deductTax is called, we did not pass 2500 literally as an entry argument base,
but salary variable itself (which had a value of 2500).
This apparently irrelevant difference has in fact a huge impact on how the macro works.
In the second row of deductTax procedure, 15% of value is deducted from
the value of base argument (so it will represent the value of 2125 now).
Since the variable salary has been given as an argument base,
they refer now to the same part of program.
So if the value of argument base changed to 2125, the value of variable salary
also changed to 2125, since they are in fact one and the same.
If it was not this way after deducting the tax from the base, which is represented by salary variable,
salary would remain unchanged. Unfortunately such beautiful things do not happen even in programming.
Then in deductTax procedure other operations can happen which were described only by a comment in
the rows 3-4 because they are irrelevant to the issue discussed.
It is important however what happens after the macro goes back to earnings procedure.
The macro goes back to it once it finishes deductTax procedure and it runs further
actions starting from the next row (in this case from the row 7).
Variable salary does not represent the value of 2500 any longer, but 2125 instead
(because this value has been assigned to salary variable inside the deductTax
procedure where this variable had been passed as an argument).
In the row 6 the value of variable salary is printed to the
worksheet Sheet1, so you can run this macro and see that value 2125 is displayed in the cell A1.
Summing up the above-described example: if a variable is transferred as an argument to the called procedure, it could have quite different value after going back to the base procedure.
At this point we need to go back again to the issue of using a Call keyword while calling procedures.
As it was mentioned before the procedure can be called by writing only the name of it, without adding a Call keyword before it.
In case the procedure has no input arguments both methods do not differ.
The row of calling the procedure should look like this:
Call calledProcedure
or this:
calledProcedure
Call calledProcedure
calledProcedure
However, if the procedure to be called requires any entry arguments, one rule must be remembered: if you omit Call keyword do not place the arguments’ values in a bracket but simply write them after the name of the procedure and separate them with comas.
Below you can find examples how to invoke a procedure with arguments by using Call keyword and without it:
deductTax salary, tax
deductTax salary, tax
Bear in mind that using Call keyword is a much better solution, since it helps to keep order and transparency inside the code and it also makes it easy to find the invokations of procedures afterwards.
Breaking macro from running
When writing longer program you will often need to stop its performance at some point to make sure whether all of the commands work properly till a specific moment or to check the current value of the variables.
To do this you can set so-called toogle breakpoints. If you set a toogle breakpoint in a macro, compiler will always suspend its execution when it reach the point where this breakpoint is set.
Breakpoints are indicated in the Visual Basic editor by highlighting the whole row of code in brown and placing a brown dot on the left side of the row.
Setting a breakpoint is very simple and it can be done in several ways:
-
the fastest way is to place the cursor in a row where you want to set the breakpoint and then press F9 on your keyboard.
This is the most comfortable method since it does not require pulling hands out of keyboard.
- the second way is to set in the row in which a breakpoint is to be placed and then click the icon of white hand on the
Edit toolbar (bordered in red in the picture below).
- the third way is by clicking on the grey stripe on the left side of the code editor
(bordered in red in the picture below), at the level of a row where a breakpoint is to be placed.
One of the biggest advantages of breaking the macro while it is working is the possibility to check the current value of variables.
In the picture below you can see a print screen of macro deducting the tax from the previous example.
In this macro the breakpoint was set in the row 6 of the earnings
procedure just before printing out the value of salary variable to the worksheet.
While making this print screen the cursor was focused on salary word in the second line
of the code and that is why a yellow frame presenting the current value of that variable appeared
(unfortunately while making print screen cursor vanishes and that is why you cannot see it in this picture).
To present this feature the piece of code from the other line than the breakpoint was intentionally chosen to show you that after pausing the macro you can see not only the values from the row at which macro stopped but also from other rows.
Another advantage is that you can watch not only declared variables but much more – for instance you can as well set the cursor over Cells(1,1) command (which as you already know from the previous parts of this course refers to the A1 cell) to check the current value of A1 cell.
Pausing the macro is thereby a very useful feature which can make creating applications easier.
You cannot set breakpoints in empty rows and in rows containing only variable declaration.
Translation:
Artur Krawczyński