5 Things CPAs Should Know about Excel Macros
- Macros are programming code that you can add to your Excel spreadsheets to automate repetitive tasks. Macros can be as simple as a single line of code to carry out a task, such as typing your company’s name. Other macros interact with accounting programs, download data from the Internet, or collect information from users via custom forms—you’re often limited only by your imagination.
- You don’t need to know anything about programming. Excel’s Macro Recorder feature makes it easy to create your own macros. Think of it as Excel’s version of a camcorder, where you click Record and have the actions you carry out transformed into programming code that you can play back over and over. This allows you to automate simple tasks such as cleaning up a text file that you download from a web site.
- Many of the tools that you use for macros in Excel 2007 and 2010 reside on a hidden Developer tab. In Excel 2007, click the Show the Developer tab checkbox on the first Excel Options window. In Excel 2010, right-click on the ribbon, choose Customize the Ribbon, and then click the checkbox for the Developer tab. You’ll find the Macro Recorder button on the Developer tab, as well as a Visual Basic button that enables you to get behind-the-scenes to your macros in the Visual Basic Editor.
- You don’t have to create your own macros. Programming isn’t for everyone, but knowing that it’s possible to have a tool that carries out a repetitive task dozens, hundreds, or even thousands of times can be a huge time saver for your company. You can hire an expert to create a macro-enabled spreadsheet that allows you to accomplish your task with the click of a button.
- The best way to learn about macros in Excel is to have a project in mind that you wish to automate. Many of Excel’s features seem to fall into the category of “why would I want to know how to do that”, but given the proper context you have that “Oh! I get it!” moment.
This information courtesy of CPE Link instructor and Excel expert David Ringstrom, CPA.
by Sue Anderson - Based on 30 years of experience in continuing education for accountants. Currently program director for online CPE provider, CPE Link. Formerly with the California CPA Education Foundation managing key operational areas including marketing, program development, and distance learning.