Let’s say you have VBA code that handles a budget and a forecast. There is a cell B2 on the Input sheet that contains the word Budget or Forecast. Based on that cell the macro with do different things. You may need to test for Budget/Forecast a few times within the code. There is an easy and flexible way to handle this.
The code below (which is an image) will help demonstrate this technique
- Define your variables – always define your variables. Boolean means TRUE or FALSE. I use the prefix boo for Boolean. Some people use b or no prefixes at all. I like to use a standard prefix so that I know what type of data the variable should contain. If you don’t use a prefix you have to keep checking the Dim statements at the top to see the type of the variable. Apparently this is old school, but hey, I am old school.
- Capture whether cell B2 contains Budget. By comparing the cell to Budget you return TRUE if it is Budget or FALSE if it is anything else. I have enclosed the comparison in brackets to make it obvious what is happening. Having two equal signs within a formula can be confusing.
- Now whenever I need to know if Budget was selected I can use this short If statement. I can use this same If statement throughout the code whenever I need to do something differently between the budget and the forecast.
- You can do all the budget processes here.
- All the forecast processes here.
- I have used a msgbox to check if the code is working correctly.
- Always set your object variables to Nothing at the end of your code. This frees up memory. Object variables are coloured black in the Dim statement.
The beauty of this technique is that you are only checking the cell once. Every time you interact with the sheet it slows the macro down. Also, if you have multiple checks throughout your code then centralising the check into one variable at the start means it is easy to change if, for example, the cell reference changes.
In this case I would advise using a range name for the cell, and using that range name in the code.
Download the example file below.