Antecedentes legales

More on Subs in Excel VBA

So far, all of your code has been written in Subs. It's time to take a closer look at them. After So far, all of your code has been written in Subs. It's time to take a closer look at them. After you've taken a closer look at how Subs work, we'll examine what a Function is, and you'll you've taken a closer look at how Subs work, we'll examine what a Function is, and you'll learn how to create your own.

learn how to create your own.

Subroutines

Subroutines

A Sub is a small chunk of code that you write to do a specific job. You can run this Sub by 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 b

 pressing F5 in the VBA Editor, you can run it by assigning the Sub to a butty assigning the Sub to a button on aon on a spreadsheet, and you can even run it from the menu bar at the top of the Editor. In fact, 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.

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. 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 Start with a new blank workbook. Open up your VBA Editor (you should know how to do this by now). Add the following Sub:

this by now). Add the following Sub:

Sub FirstCode( ) Sub FirstCode( )

Dim FormatCell As Integer Dim FormatCell As Integer FormatCell = ActiveCell.Value FormatCell = ActiveCell.Value If FormatCell < 20 Then If FormatCell < 20 Then With ActiveCell.Font With ActiveCell.Font .Bold = True .Bold = True .Name = "Arial" .Name = "Arial" .Size = "16" .Size = "16" End With End With End If End If End Sub End Sub

All the code does is to format a cell depending on the value of ActiveCell. If ActiveCell is All the code does is to format a cell depending on the value of ActiveCell. If ActiveCell is  below 20 then we change the fon

 below 20 then we change the font to bold, Arial, 16 points.t to bold, Arial, 16 points.

 Now this code is fine as it stan

 Now this code is fine as it stands. But suppose we wanted to use this sads. But suppose we wanted to use this same code again. Afterme code again. After all, we may to format cells further down the Sub, or from another Sub entirely. Wouldn't it be all, we may to format cells further down the Sub, or from another Sub entirely. Wouldn't it be  better if we placed the for

 better if we placed the formatting code in its own subroumatting code in its own subroutine? That way, we could call thetine? That way, we could call the formatting code into action whenever we needed it. We wouldn't have to duplicate code that formatting code into action whenever we needed it. We wouldn't have to duplicate code that we've already written.

we've already written.

Add another Sub to your code. Call it

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

So we have two Subs, one called

So we have two Subs, one called FirstCodeFirstCode and one called and one calledSecondCodeSecondCode. We could put a. We could put a  button on the spreads

 button on the spreadsheet and then attach thheet and then attach the macroe macroFirstCodeFirstCode to it. But this wouldn't be to it. But this wouldn't be much good, as the

much good, as theSecondCodeSecondCode is not being called into action. is not being called into action.

The way you activate one Sub from inside another is to simply type its name. Optionally, you The way you activate one Sub from inside another is to simply type its name. Optionally, you can add the word

can add the wordCallCall at the start of the line. Like this: at the start of the line. Like this:

Call SecondCode Call SecondCode

That's enough to active the Sub you want to call into action. VBA then executes the whole of 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.

the second Sub before returning to where it was.

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

Inside the If statement above, we have the call to

Inside the If statement above, we have the call toSecondSubSecondSub. Once VBA has executed all the. 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 code for the second sub it returns to where it was. It then drops down and executes the code for the rest of the

for the rest of theFirstCodeFirstCode sub. The next line that VBA executes, therefore, would be sub. The next line that VBA executes, therefore, would be the

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 theSub and End

Sub ofFirstCode. Press F5 on your keyboard to run yourFirstCode 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 arePublic 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 itPrivate. The way you make a Sub Private is by typing the wordPrivate before the word Sub:

Private Sub SecondCode( )

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