Creating a Table Template with Excel VBA

Let’s consider another example that automates the work of a commercial company. Suppose, for example, you are the manager of a company called “Boundless Horizons LLC”, and you need to create a monthly expense tracking table. We will create and then edit a macro that allows you to quickly generate a template for a simple tabular report.

To create an unformatted expense tracking table template, perform the following steps:

1. Start the macro recorder by going to the Developer tab and clicking Record Macro in the Code group.
2. In the Record Macro window, enter the macro name Create_Simple_Table_Template, and in the Store macro in dropdown, select This Workbook.
3. Go to cell B1 and enter the word Expenses.
4. Fill in cells A1…A7 as shown in Fig. 1.21.
5. Then, select cell B7 and enter the formula =SUM(B2:B6).
6. Select column A, go to the Home tab, in the Cells group click the Format dropdown, and choose AutoFit Column Width.
7. Repeat the previous step for column B: select column B, go to the Home tab, in the Cells group click Format, and choose AutoFit Column Width.
8. Place the cell pointer in B2.
9. Stop the macro recorder by going to the Developer tab and clicking Stop Recording in the Code group.

As a result, the following macro is recorded in the standard module.

Macro to create the report template

Sub Create_Simple_Table_Template()
' Create_Simple_Table_Template Macro
    ActiveCell.FormulaR1C1 = "Expense Item"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Expenses"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Telephone"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Rent"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Depreciation"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "Insurance"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "Salary"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
    Columns("A:A").Select
    Selection.Columns.AutoFit
    Columns("B:B").Select
    Selection.Columns.AutoFit
    Range("B2").Select
End Sub

So, the macro has been recorded, and now let’s discuss how its code can be simplified to create a report template.

The first fifteen lines of the macro enter text into the selected worksheet cells. Starting from the second line, paired instructions are used for input. Therefore, instead of:

Range("B1").Select
ActiveCell.FormulaR1C1 = "Expenses"

it is more reasonable to write:

Range("B1").Value = "Expenses"

Similarly, the very first instruction of the macro can be simplified to:

Range("A1").Value = "Expense Item"

The next two instructions enter the formula =SUM(B2:B6) into cell B7. In the macro, this formula is recorded in the relative R1C1 format (commonly used in VBA programs):

Range("B7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"

It is reasonable to replace these two instructions with a single one, using the familiar A1 reference style and the formula in the local language:

Range("B7").FormulaLocal = "=СУММ(B2:B6)"

The next four instructions automatically adjust the column widths for columns A and B so that all text fits:

Columns("A:A").Select
Selection.Columns.AutoFit
Columns("B:B").Select
Selection.Columns.AutoFit

This code can be shortened to just two instructions:

Columns("A:A").AutoFit
Columns("B:B").AutoFit

The last instruction sets the cell pointer to B2, which we will leave unchanged.

It is also worth noting that it would be convenient for the user if the worksheet name matched the month for which the report is created. Therefore, it makes sense to add instructions that display a dialog box asking whether the user wants to rename the sheet. If the user agrees, they enter the new sheet name in the input box and press OK, and the procedure automatically renames the sheet.

Thus, the final version of the report template procedure consists of only a few instructions but, importantly, is much more functional than the macro it was based on.

We also recommend, to reinforce the material, creating a macro that formats the table template according to your preferences and a macro that clears the worksheet of any existing data and formatting applied by the formatting macro.