Loader

VBA Starter Kit – Section 3 – Subroutines, Functions, Attaching Macros, Public vs. Private

Subroutines

Now that you know a bit about VBA, let’s talk about some code!  Generally, code is divided into two categories.  Subroutines & Functions. Subroutines (sub) are pieces of code you write to do a specific task.  For example:

Sub FirstMacro()
If ActiveCell.Value > 100 Then
ActiveCell.Font.Bold = True
End If
End Sub

This sub utilizes an if statement to check if the active cell has a value greater than 100.  If it does, it formats the cell to be bold. You can run this code from within the code editor, but it usually makes more sense to attach your code to objects so they can be initiated from within the worksheet interface.  There are a couple of ways to do this. For the first, you need to ensure you have the “Developer” tab on your command ribbon. If you don’t have the developer tab already visible, follow these steps to make it visible.

 

  1. Click “File” in the upper-left corner.
  2. Select “Options” near the bottom on the left-hand side.
  3. In the Excel Options popup, select “Customize Ribbon”
  4. In the right-hand column, the “Developer” box should be checked.
  5. Click “OK”
VBA Subroutines

Under the Developer tab on your command ribbon, there are options to insert various form controls and ActiveX controls.  You’ll want to stick with form controls for your subroutines in the beginning. Also, note that ActiveX controls do not work on Excel for Mac OS. In the example below, I’ve inserted a Button and attached the VBA code we just wrote to it by right-clicking and selecting “assign macro”.

 

VBA Starter Kit - Subroutines

 

You can also assign VBA code to shapes and images within Excel. This allows you customize your user experience a little more. The process is the same. Insert shape, then assign the macro.

VBA Starter Kit - Subroutines

Functions

VBA functions are wonderful when you need to run a task that returns a result. You might write a function that finds the last row or column in spreadsheet and call it when you need it to get that information or you might use it check a mathematical function. Generally, you write functions to perform an action and return a result that your sub isn’t designed to do.  This helps optimize your code. For example, this function will return true if the active cell value multiplied by 10 is greater than 100. Otherwise, it will return false.

Function GT100(val As Double) As Boolean
If val * 10 > 100 Then
GT100 = True
Else
GT100 = False
End If
End Function

The first difference you might observe is the “val As Double” in the parentheses.  This allows us to pass an input “val” to the function and then perform some calculation with it. The formula is expecting to receive an input of the “Double” data type and will error if the value passed is not a double or cannot be implicitly converted to a double data type.

Lastly, functions cannot be assigned to buttons like subroutines can.  They can be used in two ways.  The first is to call them from within your subroutine.  Let’s change our first subroutine up a bit to demonstrate what I mean.

Sub SecondMacro()
If GT100(ActiveCell.Value) = True Then
ActiveCell.Font.Bold = True
End If
End Sub

In this sub, we are passing the active cell’s value to the function and, if the function returns a result of “True”, making the cell’s format bold.  Let’s see this in action.

vba starter kit - functions

The other way to access functions you create is by typing them into Excel like you would any other excel function. Check out this post for more information on that!

 

The last piece I want to cover in this section is the difference between public and private declarations. When you create a functions or subroutines using VBA, it defaults to “public”.  What does this mean? It means that your sub or function can be called across modules without an issue.  You can create a module for all your functions and, if they’re public, they can be called by any sub in any module as well as be used in Excel like a normal function. If you set it to private however, it can only be referenced by subroutines within that module.  As far as other modules are concerned, that function doesn’t exist.  In the example file, I’ve created a second module to demo this.

In Module2, the function & sub are set to private.  However, they still work correctly with each other because they are both members of the same module.  But, you see that “FourthMacro” fails because ThirdMacro doesn’t exist. As I said, as far as outside modules are concerned, private VBA functions or subroutines do not exist. On smaller projects, you are probably working in one or two modules and won’t need to worry about this very much. When working on larger projects though, it’s worth thinking about when you start your build.

Help-Desk