Passing Values to a Sub

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 (two lines of code):

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 (all on oneline):

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.