Subroutines

A Sub is a small chunk of code that you write to do a specific job. You can run this Sub by pressing F5 in the VBA Editor, you can run it by assigning the Sub to a button on a spreadsheet, and you can even run it from the menu bar at the top of the Editor. In fact, there's quite a lot of different ways you can run your Subs.

One other way to run a Sub is from another Sub. Let's see how.

Start with a new blank workbook. Open up your VBA Editor (you should know how to do this by now). Add the following Sub:

Sub FirstCode()

Dim FormatCell As Integer

FormatCell = ActiveCell.Value

If FormatCell < 20 Then

​ ​ With ActiveCell.Font

​ ​ .Bold = True

​ ​ .Name = "Arial"

​ ​ .Size = "16"

​ ​ End With

End If

End Sub

All the code does is to format a cell depending on the value of ActiveCell. If ActiveCell is below 20 then we change the font to bold, Arial, 16 points.

Now this code is fine as it stands. But suppose we wanted to use this same code again. After all, we may to format cells further down the Sub, or from another Sub entirely. Wouldn't it be better if we placed the formatting code in its own subroutine? That way, we could call the formatting code into action whenever we needed it. We wouldn't have to duplicate code that we've already written.

Add another Sub to your code. Call it SecondCode . Move the With Statement to your new Sub. Your coding window should now look like this:

So we have to Subs, one called FirstCode and one called SecondCode . We could put a button on the spreadsheet and then attached the macro FirstCode to it. But this wouldn't be much good, as the SecondCode is not being called into action.

The way you activate one Sub from inside another is to simply type its name. Optionally, you can add the word Call at the start of the line. Like this:

Call SecondCode

That's enough to active the Sub you want to call into action. VBA then executes the whole of the second Sub before returning to where it was.

Add that line to your first Sub and it will look like this:

Inside the If statement above, we have the call to SecondSub . Once VBA has executed all the code for the second sub it returns to where it was. It then drops down and executes the code for the rest of the FirstCode sub. The next line that VBA executes, therefore would be the End If line.

You can try it out at this stage. On your spreadsheet, type a number less than twenty into any cell. Now go back to your coding window. Click anywhere inside of the Sub and End Sub of FirstCode . Press F5 on your keyboard to run your FirstCode Sub. Now look at your spreadsheet again. You should see that the number in the cell has the new formatting.

We mentioned that the first Sub could be assigned to a button on a spreadsheet. To do so, you'd have to bring up the Assign Macro dialogue box.  If you did, you'd see that both Subs are on the list of Macros:

The reason they both show up is that by default they are Public Subs. This means that they can be seen just about everywhere from Excel. If you don't want a Sub showing up in the Assign Macro dialogue box then you can make it Private . The way you make a Sub Private is by typing the word Private before the word Sub:

Private Sub SecondCode()

Because it has the word Private at the start, this Sub won't show up in the Assign Macro dialogue box: