Top 8 VBA Routines for Excel Reporting

Top 8 VBA Routines for Excel Reporting

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, 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:

• Unmerge selected cells and fill values down
• Improved fill down handle keyboard shortcut
• Custom formulas with VBA

VBA routines for publishing reports:

• Delete completely blank rows in the selection
• Sort selected sheets alphabetically
• Set selected sheets to “Very Hidden”
• Export each sheet into a separate PDF Report
• Export all charts to a PPT workbook

1. Unmerge selected cells and fill values down

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.

2. Improved fill down handle keyboard shortcut

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.

3. Custom Formulas with VBA

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; 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.

4. Delete Completely Blank Rows in the Selection

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 uses 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.

5. Sort Selected Sheets Alphabetically

Category: Report Publishing, Presentation

So you’ve created a report, which 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.

6. Set Selected Sheets to “Very Hidden”

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 got a lot of hidden sheets, consider first whether you actually need them.

7. Export each Sheet into a Separate PDF Report

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!

8. Export All Charts to a PPT Workbook

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 used the “Add” method, and then each chart is added to its own slide using the CopyPaste methods.

Related Readings

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:

• How to Debug Code (VBA)
• Tips for Writing VBA in Excel
• Types of Errors in VBA for Excel
• VBA Methods
• See all Excel resources