1.8 Resize
Home and Learn - Free Excel VBA Course
7.2 Passing values to a Sub
In the previous lesson, you saw how to set up and Call a Sub. In this lesson, you'll see how to hand values over to your subroutines. So open up the code you wrote last time and we'll make a start.
The code for our second Sub is this:
With ActiveCell.Font
.Bold = True
.Name = "Arial"
.Size = "16"
End With
So we're changing three values of the Font in the ActiveCell: Bold, Name and Size. However, these are all hard-coded to values of True, Arial and 16. It would be much better if we could replace these hard-coded values with variables:
.Bold = BoldValue
.Name = NameValue
.Size = SizeValue
We could then put whatever we liked in them. This would make the code much more reusable.
The question is, how do we put values in these variables? We could do it like this:
Dim BoldValue As Boolean
Dim NameValue As String
Dim SizeValue As Variant
BoldValue = True
NameValue = "Arial"
SizeValue = "16"
But this wouldn't make our code reusable at all. In fact, it's just a longer version of what we already have.
That's where the round brackets come in at the start of the Sub. The round brackets of a Sub are not just there for show. They allow you to pass values over to your subroutines. This means that the line that calls one Sub can pass values to another Sub.
When setting up a Sub that accepts values, you place variable names between the round brackets:
Sub SecondCode(BoldValue, NameValue, SizeValue)
End Sub
Each variable is separated by a comma. You can pass no variables, one variable, or as many as you need.
As well as specifying the variable names, you can specify a variable type, as well:
Sub SecondCode(BoldValue As Boolean, NameValue As String)
End Sub
If you miss out the "As Variable_Type" then the variables are treated As Variant. Notice that you don't need the Dim word anymore.
But this is just setting up the variable. They have nothing in them yet. To place something in these variables, you do so on the calling line.
Call SecondCode(True, "Arial")
Each value you place between the round brackets is known as an argument (or sometimes a parameter). The arguments must match. So if you have set up your Sub line to accept two arguments then you must pass two arguments in on the calling line, otherwise you'll get an error. The order of the arguments must match, as well. If the first variable is a Boolean, then you can't pass in a value of "Arial". Likewise, if the second argument is a String, then you can't pass in a number. Unless, that is, you set each variable up as Variants by missing off As String, As Boolean between the round brackets of the Sub line.
To clear that up, if our Sub is this:
Sub SecondCode(BoldValue As Boolean, NameValue As String)
Then this Calling line will get you an error:
Call SecondCode("value", "Arial")
But this won't
Call SecondCode(True, "Arial")
The error is because we have the text "value" as the first argument. The Sub says that BoldValue should be a Boolean, however. Boolean values can either be True or False, so VBA will give you a "Type Mismatch" error. (You can have 1 or 0 for your Booleans, though. A value of 1 means True and a value of 0 means False. You can even put the True and False in quote marks, and in any case you like: upper, lower, or a mix.)
In the Sub where your calling line is, you can set up variables, place values in the variables, and then type the variable names between the round brackets. Like this:
Dim boolValue As Boolean
Dim strText As String
boolValue = True
strText = "Arial"
Call SecondCode(boolValue, strText )
Now, we have variable names between the round brackets of the calling line. VBA will pass over to the SecondCode Sub whatever is inside of these variables. It will transfer the values to the new variables names:
Sub SecondCode(BoldValue As Boolean, NameValue As String)
So the value in boolValue will get transferred to the BoldValue variable, and the value in strText will get transferred to the NameValue variable.
Once you have values in the new variables between the round brackets of your Sub, you can do something with these variables.
To see how that works, let's now amend our SecondCode Sub to accept variables.
Change the first line of your code to this:
Private Sub SecondCode(BoldValue As Boolean, NameValue As String, SizeValue)
Here, we've set three arguments between the round brackets of SecondCode. The first is a Boolean value called BoldValue, and the second is a String value called NameValue. Notice that the third argument is just the variable name itself, SizeValue. Because we haven't specified a type, like As Boolean or As String then the SizeValue variable is a Variant, meaning it can accept just about any kind of value. With a font size, the value can be just a number, like 22. But it can also be a number surrounded by quotes, like "22". Either will work with font size.
To actually do something with these new variables, change your With Statement to this:
With ActiveCell.Font
.Bold = BoldValue
.Name = NameValue
.Size = SizeValue
End With
Last time we had .Bold = True. This time, the value after the equal sign is a variable name. So whatever value is inside of BoldValue will be used for the Bold property of Font. Similarly, the value in NameValue will be used as the Font Name, and the value in SizeValue will be used as the Font Size.
The point about doing it this way is that the code is much more reusable. We can now call this Sub from just about anywhere. The only thing we have to do is pass in the correct values when we call it into action.
Amend the calling line from FirstCode to this:
Call SecondCode(True, "Arial", 22)
This passes three values over to our SecondCode sub and its variables between round brackets: a value of True for the Bold font, a value of "Arial" for the font name, and a value of 22 for the font size.
Your coding window should look like this, though:
Test it out. Enter a value below 20 into any cell of your spreadsheet. Go back to your coding window. Click anywhere inside of the FirstCode sub. Then press F5 to run your code. Go back to your spreadsheet and you should find that the number in your ActiveCell has been formatted to bold, Arial, 22 points.
Now add an Else part to the If Statement in FirstCode:
Else
Call SecondCode(False, "Times", 26)
FirstCode should now look like this:
This time, the values we're handing over to SecondCode are False, "Times", and 26. Notice that we don't have to change the SecondCode Sub at all. It has been set up to be reusable, so the only thing we need do is to call it into action - and that can be from anywhere.
Exit Sub
Sometimes, it's useful to exit a subroutine early. For example, you can have some error checking at the start of a Sub. If a value not what you want, you can bail out. The way you do this is with the words Exit Sub. Here's an example:
Notice that the first line is:
Dim FormatCell As Variant
So we've changed the variable type from As Integer to As Variant. We've changed it because in the real world, the value from ActiveCell could be anything. The user may enter text, for example, instead of numbers. We're going to be testing for this.
The code that tests the value of the FormatCell variable is in an If Statement. This one:
If Not IsNumeric(FormatCell) Then
MsgBox ("This needs to be a number - Exiting Now")
Exit Sub
End If
To test if something is a number you can use the inbuilt function IsNumeric. Between the round brackets of IsNumeric you type whatever it is you want to test. In this case, we're testing the FormatCell variable. Before the IsNumeric function we have Not. We could have done this instead, though:
If IsNumeric(FormatCell) = False Then
The result is the same: If the value in FormatCell is not a number then the code for the If Statement will get executed. The code that gets executed is this:
MsgBox ("This needs to be a number - Exiting Now")
Exit Sub
First, we display a message box for the user. The second line is Exit Sub. On meeting this line, VBA bails out of the subroutine. Any lines below that won't get executed.
You should always try to add as much error checking code as you can. Then use Exit Sub so that your macros don't crash.
In the next lesson, you'll learn how to customize your message box in Excel VBA.
Next Lesson: 7.3 VBA Message Boxes >