4 Useful Excel Macros

BY JAIME CAMPBELL, CPA, MBA, CGMA, CIT, MCT, TIER ONE SERVICES

An Excel macro is a program that can automate tasks in Excel. Macros can best be used to automate tasks that don't require professional judgment, and they can be used in both nonprofit and forprofit settings. Learning the language of macros — Visual Basic for Applications (VBA) — saves time and reduces errors on recurring tasks.

Start with good principles of design: workbook structure, flexible cell references and the user experience.

The most efficient workbook structure has an input area for raw data, the macro that uses that data and the output. The result is a template which is reusable month after month, transferable to other clients and transferable to other practitioners.

Use named ranges in your VBA instead of cell references so the macro continues to work even if a row or column is inserted and the necessary data is no longer in those ranges. Refer to those range names in the VBA code such as Range(“Amount”) or Range(“TransactionDate”).

Best practices for the user experience include user controls, status bar updates and pop-ups. Controls such as command buttons are in the Developer tab. Use confirmation pop-ups such as MsgBox (“Your data is ready for import into the accounting system.”). For long, iterative macros, the status bar can be updated, for example, Application.StatusBar = CurrentRecordNumber & “ of ” & TotalRecords & “ complete.”

Following are four examples of useful macros.

1. ALLOCATING PAYROLL

Arrange the input area to accept imported or pasted information from a .csv file of the payroll report for the gross wages and other key figures for each employee. In a separate workbook tab, have a vertical listing of each employee with adjacent columns for the areas of percent allocation such as jobs, functional expenses or locations. These percentages can be hand-entered upon setup, or they can be formulas that read an export from a time-tracking system. In separate columns, multiply those percentages and the payroll figures.

Have the macro build from scratch a single entry with the correct debits and credits in the layout compatible with your upload tool. The macro will involve creating columns with the following information:

* Identical data in each row such as the transaction date

* Separate numerical data in each row such as the allocated payroll

* Custom text data in each row, such as the transaction memo with the employee’s name

2. IMPORTING TRANSACTIONS

When a financial institution doesn’t link with the accounting systems, sometimes the solution is to download a .csv and import into the accounting system.

Program the macro to copy and paste the columns of source data into the correct layout in order to be compatible with the upload tool and to change certain positive numbers to negative ones where needed for the disbursements. The macro can also be used to split up the database between cash receipts and cash disbursements, if that is required by the upload tool. The macro can export the results in .csv format and either prompt the user to select a location to save the file or detect the workbook location and place the .csv in the same folder.

3. SUMMARIZING E-COMMERCE TRANSACTIONS

In a high-volume environment, don't duplicate the revenue database in the accounting system. However, when the transactions are too complex to run a report in the e-commerce system and make a journal entry, a macro might be needed to prepare a daily summary of gross revenue, sales tax payable, shipping income, merchant service fees, discounts and returns.

Start with the data exported from the external system and, using worksheet functions, identify the appropriate General Ledger account for each transaction. Some Excel functions can look for keywords in the sales description, or the XLOOKUP function can be used to look up the price and identify the correct income account.

Program the macro to place the data onto the output tab in a layout compatible with the upload tool. It can summarize the transactions by account, by day.

4. MULTI-LOCATION REPORTING

Link an option button object to a macro in order to instantly show or hide columns containing reports for different locations. This is especially useful for presenting a proposed budget to a board of directors for a multi-location nonprofit. The macro can be a simple as two lines of code:

ActiveSheet.Range(“AllLocations”). EntireColumn.Hide

ActiveSheet.Range(“Lawrenceville”). EntireColumn.Show

Jaime Campbell, CPA, MBA, CGMA, CTT, MCT, is the chief financial officer of Tier One Services, a fractional CFO and outsourced accounting firm serving $1 million to $20 million organizations. She is a member of several NJCPA interest groups and can be reached at jcampbellcpa@tieroneservices.net.