Top 8 VBA Routines for Excel Reporting

What is VBA?

VBA, an acronym for Visual Basics for Application, is a programming language developed by Microsoft for Office applications such as MS Excel, MS Word, MS Access, etc.

It is a human-readable and editable programming language that allows users to provide sets of instructions to Excel, known as a program. These instructions can automatically execute simple and complex tasks in Excel.

By grouping these instructions or commands, users can create macros, which are executable sets of instructions that can be run repeatedly to perform a particular task. Using VBA helps to access functions beyond those available by default in Excel and other Microsoft Office applications.

VBA also helps in creating user-defined functions and automates computer processes. We can analyze large amounts of data using VBA, creating and maintaining complex financial models.

For example, suppose we have a financial model for Nike Inc. It is possible to auto-populate most numbers on the template with just a button click using VBA macros.

Hence, VBA makes our life easy in many ways while working in Excel. It helps to access functions beyond the in-built functions in Microsoft Office applications. It also helps to create user-defined functions, hence customizing our tasks in Excel.

Most programmers in VBA use VBA macros, which are sets of statements the user can run multiple times. Doing this shows macros' flexible nature and potential usefulness for various tasks.

VBA Routines Useful in Excel Reporting

In this section, we will discuss the eight most useful VBA routines used in Excel that can increase the usability and efficiency of Excel VBA.

Here, the term routine refers to a set of actions followed regularly. VBA programmers use these VBA routines regularly to supercharge their task of Excel reporting.

These VBA routines serve many functionalities like custom shortcuts, formatting, and PDF creation. In addition, these VBA routines give a real sense of the tasks in which Excel VBA is still the best tool.

Excel reporting involves collecting and presenting data obtained from various sources visually and intuitively on a single Excel worksheet.

Excel reporting is a great way to store, analyze, and present data using charts and graphs, making it easier for our audience and clients to understand the data.

VBA Routines for Creating Reports

Creating reports in Excel usually involves fetching, organizing, analyzing, and presenting data in a structured manner.

VBA (Visual Basic for Applications) can be a powerful tool to automate this process, especially when reports need to be generated frequently.

Three routines in VBA help in creating Excel reports. The three VBA routines for creating reports are as follows:

Unmerging the Selected cells and Filling the values down

This routine allows users to apply custom formatting to Excel reports using VBA, particularly when dealing with a specific set of cells that must be formatted repeatedly.

The routine helps when we have a set of headers grouped based on some conditions, and the user wants to unmerge the data and fill it downward. Doing it manually every time will be time-consuming and inefficient, particularly when dealing with many grouped headers.

We can save a lot of time by using this routine. It also helps in unmerging more than one column at the same time. Let us take a practical example to demonstrate the applications of this routine.

Suppose we have the marks of three students in Science, Economics, and History given in an Excel worksheet. The data is grouped into three groups for the three students. The data looks as illustrated below:

We can merge different cells manually by going to the Home tab > Merge and Center drop-down > Merge Cells. To unmerge the cells, click on the Unmerge Cells option below the Merge Cells option.

However, this process would be very tiring and time-consuming if there were many columns to be grouped. So instead, we can use the Unmerge VBA routine to unmerge the cells.

This routine modifies the cell properties of a selected area on the Excel worksheet to apply any formula or value to all the cells within the merged cell range.

The VBA code to execute the Unmerge routine during Excel reporting is as follows:

If we select any merged cell and run the code, we see that the particular cell is unmerged. Go to Run > Run Sub/Userform to run a code in VBA.

Similarly, by running the code for all the merged cells, we can unmerge them as shown:

The Unmerge routine in VBA to unmerge cells in an Excel report is very efficient and saves time.

Improving the Fill Handle Using Keyboard Shortcuts

Suppose we have written a formula and want to copy it down through many rows. Of course, we can manually copy and paste the formula in all the rows if there are only five or ten rows.

But what if there are 200 or 500 rows? In this case, manually copying and pasting the formula would be very time-consuming.

Excel provides the Fill option to streamline this process. To do so, click on Home > Fill as shown below:

To further optimize this process, a keyboard shortcut can be created to achieve the same result. For example, the FILL VBA routine uses the range and cell selection to give the fill-down functionality with a single keyboard shortcut.

The VBA code to create a keyboard shortcut for the FILL option in Excel is as follows:

In the above code, we have created a keyboard shortcut for the Fill Down option. Similarly, we can write the code to produce a Fill right option.

Custom Formulas with VBA

The previous two VBA routines belonged to the category of formatting. This routine belongs to the Custom Formulas category. All three are VBA routines for the creation of reports.

This routine allows us to create our formulas and functions. In addition, user-defined functions help us create our functions in VBA that we can use in our Excel worksheets.

In Excel, there is little difference between functions and formulas. Both perform computations on input values and return an output. A VBA function is similar to a worksheet formula and returns various properties of the data values as a text string.

The code below gives the user and the client insights into the data's mean, median, and mode dynamically displayed at the bottom of the graph.

VBA Routines for Publishing Reports

Publishing reports from Excel to various formats or destinations often involves exporting the data to a specific format, saving it to a location, or even sending it via email.

Using VBA, you can automate these tasks to make the publishing process more efficient. There are 5 VBA routines for publishing reports in Excel. They are as described below.

Deleting the Completely Blank Rows in the Worksheet

Suppose you have arranged the entire data on your Excel worksheet. There are 6,000 rows and 11 columns in your worksheet. You notice that in some places, there are completely blank rows that have to be removed before presenting the data to your client.

Manually removing so many blank spaces is a tiring and time-consuming task. In such cases, we can use the VBA routine, which observes the selected range of cells and uses the CountA function to find and delete blank rows.

The CountA function ensures that only completely blank rows, even beyond the selected range, are deleted. The code for this VBA routine is as follows:

The above code first checks if some range of cells in the worksheet is selected. If it is not selected, a message box will display “No Range Selected.”

 On the other hand, if a range is selected, we iterate through all the rows in the range, find the rows that are completely blank using the CountA function, and delete them.

Sorting the Selected Sheets Alphabetically

This VBA routine helps to enhance the presentation of Excel reports. For simplicity, all the tabs in a report must be in the correct order.

This VBA routine sorts all the selected sheets alphabetically using the MOVE function of the worksheet object. The code to execute this VBA routine is as follows:

Here the variable SColl stores the collection of all the selected sheets, the worksheet is stored in variable work, and SCount stores the number of sheets that need to be sorted.

Setting the selected sheets to “Very Hidden”

Sometimes we might want to hide sheets in our workbooks. We can do this using the Hide & Unhide option in the Home tab of the Excel worksheet. But anybody viewing our report can easily unhide it. Can we hide the sheets so that no one except the user can see them?

Yes, we can, by using this VBA routine which helps to set our sheets to “Very Hidden” using the Visibility property of the Excel worksheet object. Unfortunately, this option is not available in the Excel menu bar.

We have stored the Excel worksheet in the variable w in this code. Then, we iterated through all the selected sheets and changed their visibility to “Very Hidden.”

VBA provides another routine, just the reverse of the previous one. This routine serves the purpose of unhiding all the sheets at once without doing it one by one.

In the above code, we store the worksheet in variable w. We then iterate through the entire worksheet and change their visibility to “Visible.”

Exporting each Sheet into a Separate PDF Report

Sometimes we might have to work on an Excel project in which each sheet represents different entities, people, or information. They are mostly the same but with different target audiences.

In such cases, it may be beneficial to export each sheet separately so we can send each sheet to its respective target audience.

This VBA routine iterates through the selected sheets using a For Next loop and exports each sheet as a separate PDF report using the Export method of the worksheet object.

This routine also provides a folder picker for the user to decide where to save the PDFs. The code to execute this VBA routine is as follows:

The ExportAsFixedFormat is a method of the Worksheets Object which allows us to export the selected sheets in a specified format. We keep the type as xlTypePDF, specifying that the sheets must be exported as PDF files.

The OpenAfterPublish is set to False so that the files are not automatically opened as the macro execution is complete.

Exporting all charts in a PowerPoint Workbook

VBA also allows exporting all our financial charts in a PowerPoint worksheet. We can do so by using the ChartsToPPT routine in VBA. Using this, we can export everything to Powerpoint in a single click.

This VBA routine creates a new Powerpoint presentation using an application object. It then creates new slides using the Add method and adds each chart to a separate slide using the CopyPaste method. The VBA code to execute this routine is as follows:

So these are 3 VBA routines for creating reports and 5 VBA routines for publishing reports, a total of 8 VBA routines extensively used while working in VBA.

Researched and authored by Devang Shekhar | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• VBA Cell References
• VBA Do Loop
• VBA in Excel
• VBA Methods
• VBA Variable Types