As a VBA developer, it’s important not to lose sight of the broader Excel application environment. VBA will always be part of Excel — an application containing its own interface, features, and functionality. This post will provide an overview of 10 important non-VBA concepts and features important for VBA developers to understand.
This is the final post within the Getting Started with VBA series. While our primary focus has been on VBA, it’s important to recognize VBA as feature (or layer) of Excel. VBA can support and automate content within a spreadsheet, not replace it. If you want to write a sand-alone program, download Visual Studio (it’s free) and learn vb.net (it’s similar to VBA). However, if you want something that is accessible and familiar, supplementing a spreadsheet with VBA is the way to go.
With that, I’m closing the Getting Started in VBA series with a post on non-VBA concepts important to incorporate into your VBA project. If a non-VBA topic has previously been written about on this site, I link to it along with a brief description. For topics that are not discussed on this site, I link to some of my other favorite Excel sites and blogs.
When it comes to interacting with a spreadsheet, the Range object is the most important tool at your disposal. One fatal flaw with the Range object relates to the use of its address. A Range’s address is a property that refers to one or more cells’ position on the Excel grid, such as A1 or A1:B2. This property is not a fixed attribute, so when a cell’s position changes—such as a row being added above it—its address will change as well. When a cell’s address changes, its corresponding reference within the Range object (in the VBA editor) will not change. In this situation, if you are referencing a value from cell A2, you might be referencing an empty range if a row was added above it.
This is where Named Ranges come in. By assigning a name to a to a cell, the name will move with the cell, even if that cell’s address changes. This is useful in a few ways. First, if you reference the name in VBA, you will not need to update the reference if cells move within a worksheet. Second, if want to relocate the named cell(s) in the worksheet, you can do a simple cut and paste — the named reference will follow. Finally, you can include multiple cells in a single range, creating an object that VBA can treat as an array.
If you find yourself frequently relying on named ranges, check out our free Name Manager Plus add-in.
Without a doubt, calculation speed is often the most overlooked factor for even the most experienced Excel users and developers. Typically, the first step in creating a report is to create a simple visualization. This includes where the data will be located, the inputs the user will provide, and where the data is stored. As you build the report and add data, you begin to notice long calculation times for complex formulas. The report may provide amazing information and metrics, but users will quickly become annoyed, especially if the report relies on many user-inputs and updates.
What’s causing this slowness? It could be many factors, but one likely culprit may be volatile formulas. A volatile formula is a formula which re-evaluates whenever a single change occurs (even an unrelated change) in a worksheet. These recalculations use up system resources and lead to calculation delays.
Most Accountants and Finance professionals are very familiar with the SUMIF formula. In 2007, Microsoft added an even more powerful formula: SUMIFS. Unlike SUMIF, which will only permit one criteria to be searched, SUMIFS permits any number of criteria. Note of caution — this can slow down worksheets if used in extremely large datasets, or with too many criteria. I find myself relying on SUMIFS for any dynamic reports, incorporating it with budgets IDs in dropdown lists, the chart of accounts, or any other metric that relies on varying criteria.
Possibly the most famous formula in Excel. Any dynamic report in Excel will likely have a few cells with a VLOOKUP formula. This is very useful for consolidating and joining tabular data, as well as providing key details on dashboards. Oftentimes, VLOOKUP can take the place of VBA scripts that attempt to move data around a worksheet.
While VLOOKUP is one of the most popular formulas, it’s also one of the most error prone. Many wasted hours and headaches result from this moody formula. To help understand it, read an in-depth post on VLOOKUP at the Excel Trick site. You might also be interested in our custom formula, VLOOKUPLEFT.
The IF formula is a simple conditional statement that supports dynamic content within a worksheet. In some situations, you may want to present a warning message if certain thresholds are met, or maybe a tax rate needs to change depending on a state selected. The IF formula is typically the easiest way to do this.
You may have built a VBA project to support or manipulate data in a dashboard. While dashboards may seem easy to implement—throw together a few charts, form elements, and data inputs—there are important components to consider. In another great post by Excel Off the Grid, fundamentals of dashboard model building are explained.
It’s important to provide users with a straightforward and intuitive means of interacting with your VBA procedures. While the macro editor does permit the assignment of keyboard shortcuts to macros, how to use the shortcuts may not be clear to the end-user. Excel offers form controls to make your macro interface mimic that of other windows programs. The site Chandoo provides a comprehensive overview of Excel’s Form Controls.
Nothing is more appealing than proper cell formats. Related information and data should look consistent — including the same number of decimal spaces, same currency signs, and making colors/highlights appealing. The Gnome project has a post detailing many options for formatting cells.
Protecting a Sheet
It’s very likely that your VBA procedures will also be supported by formulas within Excel. It’s important to make sure users do not inadvertently change those formulas. This can be accomplished by protecting a sheet. Within specific sheets, data input cells can accept inputs, while formula cells will remain locked. We wrote about protecting sheet layout and formulas back in October 2016.
Dynamic content can provide users will immediate and valuable feedback. By defining thresholds for key data points, or displaying numbers with “heat-map” colors, users will understand the numbers before reading them. You can read about how to implement conditional formatting in a October 2016 post.
This concludes our series on Getting Started with VBA. The posts within the series should provide you with a solid foundation to code like a professional in VBA. For a comprehensive overview of all the topics discussed in the series, as well as summarized code snippets, you can view our Series Highlights and VBA Cheat Sheet.
As always, if you found the content of this series useful, please consider subscribing or following us on social media.