Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first module of each course. Start Free
The article below showcases eight VBA routines for Excel reporting, which you can start using immediately to supercharge your Excel reporting tasks. VBA’s been around for as long as most of us care to remember, and there are certain tasks where using VBA still rules.
The routines below cover a range of functionalities, from custom shortcuts and formulas to formatting and PDF creation. The powerful routines were written to give you a real sense of the types of tasks where Excel VBA remains the best tool to use.
VBA routines for creating reports:
VBA routines for publishing reports:
Category: Report Creation, Formatting
VBA is particularly helpful in applying bespoke formatting to your reports, especially when it’s being applied to a set of selected cells that you can specify each time. You can even assign a shortcut to the macro if you perform the action often.
A particularly common example is where there is a set of grouped headers that need unmerging and then filling down. Sure, you can do it manually each time, but it becomes time-consuming if you got eight columns worth of grouped headers. Using the routine will save hours of work in the long term. It even works on more than one column at once.
The Unmerge VBA routine modifies the cell and range properties of the selected area to apply the given formula or value to all the cells within the merged cell range.
Category: Report Creation, Formatting
So, you’ve just written a formula and need to copy it down through 2,000 rows. Now, we all know about the Fill button on the bottom right of the selection box, but wouldn’t it be nice if there were a keyboard shortcut that achieves the same result?
The Fill VBA routine uses range and cell selection to give you fill-down functionality with a single keyboard shortcut. I recommend you assign the Ctrl+D and Ctrl+R shortcuts to replace the default fill shortcuts.
Category: Report Creation, Custom Formulas
If you ever wished Excel came with more formulas, then you’re in luck – you can create your own! User-Defined Functions (UDFs) allow you to create functions in VBA that you can then use in your own Excel worksheets. Functions are very similar to formulas; they pass in a set of inputs and return an answer.
The VBA function acts like a worksheet formula and returns various properties of the selected data values as a text string. The example below gives our reader an indication of mean, median, and mode, which can be useful as dynamic text at the bottom of a graph.
Category: Report Publishing, Formatting
You’ve spent a bit of time tidying up your data. You’ve got approximately 10,000 rows and 15 columns. Some values are legitimately blank. In other cases, you’ve got completely blank rows that you need to get rid of before publishing your data.
The VBA routine will look through the range that you’ve selected and use the CountA worksheet function to find and delete any rows that are completely blank. The row must be totally blank, even outside of the selection. Rows above or below the selected range are not affected.
Category: Report Publishing, Presentation
So you’ve created a report that includes 5 tabs, one for each store in the business. You’ve been playing around with a few of them while you’ve been working on a few things. However, you need to ensure that they’re all in the correct order for simplicity before publishing the report.
The VBA routine will sort all of the SELECTED sheets alphabetically using the Move method of the worksheet object. By keeping it specific to SELECTED sheets, it allows you to keep a few title sheets at the front if you wish.
Category: Report Publishing, Presentation
Sometimes you need to hide sheets in your workbook, but you know that certain colleagues will inevitably unhide them. What if you can really hide them!? It turns out there’s an option “Very Hidden” in VBA, which isn’t normally accessible as an option in the Excel menu.
The VBA routine allows you to easily set your sheets to “Very Hidden” using the visibility property of the worksheet object. The second routine I’ve included allows you to unhide all the sheets at once instead of doing them one by one. If you have a lot of hidden sheets, consider first whether you actually need them.
Category: Report Publishing, Exporting
You’ve probably worked on a project where each sheet represents a different entity, person, location, or region. All are essentially the same but with different audiences. In such a case, you may want to export each sheet separately so that you can send each one to its respective audience.
The VBA routine will loop through the selected sheets using a For Next loop and export each one as a PDF using the Export method of the worksheet object. It also uses a folder picker to let the user decide where to save the PDFs. The routine can come in really handy when working with files and folders in VBA!
Category: Report Publishing, Exporting
Need all your financial charts in PowerPoint? Use the ChartsToPPT routine to help you export everything to PowerPoint in a single click. While the concept is simple, you can also modify the routine to output certain charts to a specific bookmark in your presentation.
The VBA routine uses an application object to create a new PowerPoint presentation in the PowerPoint application. A new slide is created using the “Add” method, and then each chart is added to its own slide using the CopyPaste method.
Thank you for reading CFI’s guide to Top 8 VBA Routines for Excel Reporting. To keep learning and advancing your career, the following resources will be helpful:
To master the art of Excel, check out CFI’s Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
to take your career to the next level and move up the ladder!
Get Certified for Business Intelligence (BIDA®)Develop analytical superpowers by learning how to use programming and data analytics tools such as VBA, Python, Tableau, Power BI, Power Query, and more.
