Top 8 VBA Routines for Excel Reporting

Supercharge your Excel reporting tasks!

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.

 

Top 10 VBA Routines for Excel Reporting

 

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 if you got eight columns worth of grouped headers, it becomes time-consuming. 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.

 

Unmerge Selected Cells - Sample Table

 

Top 10 VBA Routines for Excel Reporting - Unmerge Selected Cells

 

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.

 

Top 10 VBA Routines for Excel Reporting - Improved Fill Down Handle

 

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.

 

Top 10 VBA Routines for Excel Reporting - Custom Formulas with VBA

 

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.

 

Top 10 VBA Routines for Excel Reporting - Delete Completely Blank Rows in the Selection


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 you publish 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.

 

Top 10 VBA Routines for Excel Reporting - Sort Selected Sheets Alphabetically


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!? 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.

 

Set Selected Sheets to "Very Hidden"


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!

 

Exporting into a PDF Report 1

Exporting into a PDF Report 2


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.

 

Export All Charts to a PPT Workbook

 

Related Readings

Thank you for reading CFI’s Top 8 VBA Routines for Excel Reporting. CFI offers the Certified Banking & Credit Analyst (CBCA)™ certification program for those looking to take their careers to the next level. 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

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE 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.

 

Launch CFI’s Free Excel Course now

to take your career to the next level and move up the ladder!