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:
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.
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”.
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 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.
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.
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.
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.
 |
Tickets |