VBA Sub Procedure

This tutorial will explain VBA Sub Procedures in Excel.

What are Sub Procedures?

Sub procedure are one of the major cornerstones of VBA. A Sub procedure does things. They perform actions such as formatting a table or creating a pivot table.  The majority of procedures written are Sub procedures. All macros are Sub procedures. A sub procedure begins with a Sub statement and ends with an End Sub statement. The procedure name is always followed by parentheses.

Sub Gridlines ()
  ActiveWindow.DisplayGridlines = False
End Sub

A Sub Procedure in VBA can be created by the Macro Recorder or directly in the Visual Basic Editor (VBE).

Creating a Sub Procedure with the Macro Recorder

In the Ribbon, select View > Macros > Record Macro.

OR

Developer > Visual Basic > Record Macro

Note: If you don’t see the Developer Ribbon, you’ll need to enable it.  You can learn how to do that here.

1) Type in the name for your macro, and then 2) Select where to store the macro. This can be in the Personal Macro workbook, the workbook you are currently editing or a new workbook entirely.

Once you have clicked OK, you can follow the steps that you want in your macro (for example bolding a cell, changing the color of the text, etc.), and then click the stop button at the bottom of the screen to stop recording the macro.

To view your macro, in the Ribbon, select View > Macros > View Macros.

OR

Developer > Visual Basic >Macros

Click on the Macro in the Macro name list, and then click on Edit.

This will  open the VBE and jump you into the VBA Code.

 

A VBA Project has now automatically been created for your workbook, and within this project, a module (Module 1) has also been created.  The Sub Procedure (macro) is contained within this new module on the right hand side.

Creating a Sub Procedure in the VBE Window

To create a new procedure, we first need to insert a module into our VBA Project or make sure you are clicked in the module in which you wish to store the procedure. To insert a new module into your code, click on the Insert option on the menu bar, and click Module. Or, click on the Insert Module button which you will find on the standard ribbon. Once you have selected your module, the easiest way to create a procedure is by typing directly into the Module Window. If you type the word Sub followed by the name of the procedure, the End Sub will be automatically added to the code for you. Alternatively, you can go to Insert > Procedure instead: The following dialog box will appear:

1. Type the name of your new procedure in the name box – this must start with a letter of the alphabet and can contain letters and number and be a maximum of 64 characters.
2. You can have a Sub procedure, a Function procedure or a Property procedure. (Properties are used in Class modules and set properties for ActiveX controls that you may have created). To create a Sub procedure, make sure that option is selected.
3. You can make the scope of the procedure either Public or Private. If the procedure is public (default), then it can be used by all the modules in the project while if the procedure is private, it will only be able to be used by this module.
4. You can declare local variables in this procedure as Statics (this is to do with the Scope of the variable and makes a local procedure level variable public to the entire module). We will not use this option.

When you have filled in all the relevant details, click on OK.

You then type your code between the Sub and End Sub statements.

Public Sub Test()
  ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
End Sub

The code above will switch off the gridlines in the active window if they are on, but if they are off, it will switch them on!  

Calling a Sub Procedure from Another Sub-Procedure

Often we write code that can then be used repetitively throughout the VBA Project. We might have a macro that format a cell for example – perhaps makes the text bold and red, and then in a different macro, we also want to format the cell, as well as do some other stuff to the cell. In the second procedure, we can CALL the first procedure, and then continue with our additional code.

Firstly, we create the first procedure:

Sub FormatCell ()
 ActiveCell.Font.Bold = True
 ActiveCell.Font.Color = vbRed
End Sub

Then, in a second procedure, we can refer to the first procedure to run that procedure as well.

Sub AdditionalFormatCell()
 'call first procedure
 FormatCell
 'add additional formatting
 ActiveCell.Font.Italic = True
 ActiveCell.Interior.Color = vbGreen
End Sub

So while the first procedure will make a cell bold and the text red, the second one will in addition to the first one, add italic and make the background of the cell green.

Adding an Argument to a Sub Procedure

We can further control how our code works by adding an argument or arguments to our Sub-Procedure.

Consider the following:

Our sub-procedure TestFormat, is calling the procedure AdditionalFormatCell. Although we have an argument for that procedure, we have marked it as Optional. An optional argument means that you do not have to pass the value to the procedure.

Due to the fact that we are not passing a value, the value of i will be zero – therefore the procedure FormatCell2 will be called instead of FormatCell. If we have passed a value to i – as long as that value was 1, then FormatCell would have been called instead.

Assigning a Button in Excel to a Sub Procedure

Once we have created a macro in Excel VBA, we can create a button on the worksheet to run the macro.  We need the Developer tab switched on to do this.

In the Ribbon, select Developer > Insert > Form Controls > Button.

Click and drag in the worksheet to create a button.  As soon as you release the mouse button, the assign macro dialog box will appear.  

Select the macro you wish to assign to the button, and click OK.

Right click on the button, and select Edit Text to change the text on the button.

Click on the button to run the macro.

Creating a Function to Return a Value

A Function procedure differs from a Sub Procedure in that it will return a value. It can have multiple arguments, and the value it returns can be defined by a data type (eg: Text, Number, Date etc).

Function ConvertWeight(dblAmt As Double, WeightType As Integer) As Double
  If WeightType = 1 Then
    ConvertWeight = dblAmt / 2.2
  Else
    ConvertWeight = dblAmt * 2.2
  End If
End Function

We can then create 2 Sub Procedures to use this function.  One to convert from pounds to kilos and the other from kilos to pounds.