Secrets to Using Excel Automation to Reduce Errors and Repetition

Using Excel’s automation tools, you can accomplish a variety of steps - like importing CSV files, adding headers, moving columns, formatting text, and more - with a single click.

Additionally, if you go on vacation, you could send a spreadsheet to a fellow employee who can do the same thing without having to reinvent the wheel. More advanced versions of automation allow you to add custom logic, so that the automated process can take different steps based on the results of prior steps.

While there is some overlap, there are three main ways to automate tasks in Excel:

• Macros: A sequence of actions that have been recorded and can be reused. They can be user-generated or received from other users or downloaded.

• Visual Basic: A programming language built into Microsoft Office products. Visual Basic (also knowns as Visual Basic for Applications or VBA) allows a user to to choose and modify pre-selected pieces of code via a graphical user interface, called Visual Basic Editor (VBE). Macros are saved in VBA, and can be viewed and edited in the VBE.

• Add-ins: Third-party programs that can be downloaded and connected to Excel. Add-ins (sometimes referred to as plug-ins) can be created in Visual Basic, or in other programming languages.

Some common uses for automation include the following:

• Adding standard formatting cells, ranges, rows, columns, or worksheets

• Saving a worksheet in a particular format (like PDF) with a particular file name format (e.g., DailySalesReportxxxxxxxx, where the x’s are replaced with the current date)

• Moving columns or rows into a certain order

• Creating and formatting a chart based on data in a worksheet

• Creating and formatting a table

• Creating UserForms

Benefits of Using Excel Automation

In addition to saving time and reducing the effort expended, there are advantages to automating tasks in Excel. These include the following:
Standardized document formats and consistent results

• Error reduction

• Better reporting

• No programming knowledge required (if macros or add-ins are used)

• Integration with apps such as Microsoft Word and PowerPoint

• Reduction in brain drain due to staff turnover

• Ability to get work done when someone is out of the office

Jon Peltier is President and Owner of Peltier Technical Services, a company that provides commercial and custom solutions for Microsoft Excel that enhance information presentation, integrate with other applications in Microsoft's Office suite, and save valuable time through automation.

Peltier explains, “Automation means it’s done the same way every time and it's done a lot faster. I won’t say that Visual Basic programs don't have any errors, but you don't have the kind of errors that you get with a user who is bored out of his mind doing the same stuff time and again.”

 

Challenges to Using Macros for Automation

There are a few issues to be aware of when using macros and VBA code, but that doesn’t mean it’s not worth the initial investment in time to make Excel automation work for you. Keep the following in mind when using macros:

• They can do things you don’t expect (such as deleting data), so ensure you understand how they work.

• They can be malicious, so it’s imperative to verify that you can trust the source of the macro or code.

• Not all macros will work correctly on all platforms, and some platforms (such as browser-based versions of Excel or Excel on mobile devices) don’t run macros at all.

Saving a workbook as a template does not save the macros. In order to save a macro in a standard template, you’ll need to save it as a macro-enabled template, which has the extension XLSM.

First, decide which steps you want your macros to perform. The next step is to record yourself performing the selected steps. The last step is to run the macro and verify that it performs as expected. Follow the tutorial below to learn how to perform each of these steps in Excel.

Recording a Macro

Macros are created by using the macro recorder. The macro recorder remembers a series of steps a user performs, which can be repeated whenever needed. It’s easy to create macros in this way because users just need to perform actions that they already know how to do. Open an Excel workbook that you want to automate to get started.

1. From the Tools menu, click Macro, click Record New Macro.

2. Click in the box under Macro name in the pop-up window, and type a name for your Macro.

3. Choose where to store the macro from the dropdown menu. There are three options; choose one that fits your needs:

• This Workbook: The macro is only available in the current workbook.

• New Workbook: A new workbook opens and the macro is only available in that workbook.

• Personal Workbook File: This allows a user to use a macro from any Excel workbook opened on that same computer. These files will have the extension .xlsb.

If desired, enter a shortcut key (more on this later). If you enter a letter that’s used by the OS, you’ll get a politely-worded message to change it. Click OK.

4. The recording indicator appears below the navigation buttons.

5. Perform the steps you want to automate, and click the macro recording indicator when done. It’ll change appearance and look like this.

From this point forward, you can also click this button to start recording a new macro.

Running a Macro

Once a macro is recorded, you’ll want to run it. Excel provides a few different ways to run your macros. Use this list to decide which method is right for you:

• Menu: Click Tools, click Macro, click Macros…, then click the title of the macro. In Windows, simultaneously press alt+F8 to bring up this option.

   
  

Select the macro you want and click Run. If you don’t see your macro, it may be stored in a different workbook.

• Keyboard Shortcut: If a shortcut was created when you first named the macro, you can run the macro by keying that shortcut. The key combination for Windows is Control+Shift+ the letter you chose; for Mac, it is Option+Command+ the letter you chose.

  • You can also add a shortcut after the fact. Simply click Tools, click Macro, click Macros…, click the title of the macro, and then click Options.

• Worksheet Button: You can add a button to the spreadsheet that can be assigned to run a macro. If you send the spreadsheet to another person, they can easily see how to run the macro. To add a button:

  • Click the Developer tab (if the Developer tab is not showing, here are the instructions to add it).

  • Click Button. A button will appear on your spreadsheet (the first one will be called Button1, etc.). Click on it, and assign the desired macro.

     
    
    

  • Double click on the button to edit its display name.

     
    
• Toolbar Button: For quick access, you can add a button to a toolbar. You can find instructions for this task here.

Visual Basic is more complicated to use than macros because it requires some knowledge of the Basic programming language. When macros are created, Excel saves them as Visual Basic; existing macros can be edited in the Visual Basic Editor (VBE), which is the interface in Excel.

Add-ins are third-party programs that can be downloaded and connected to Excel. Some examples are AutomateExcel, HelpSystems Automate, Kloudio, Analytics Canvas, Insight Software, PowerOLAP, and Domo. They are also available from companies like Peltier Technical Services, Inc..

If you have programming chops or know someone who does, add-ins that interact with Excel can be developed in languages such as Python, SQL, C#, M, Java, C++, and D. These can be customized to do many things.

The creation of reports can be automated via any of the available methods. If it’s a simple report, it can be done with a macro. For more advanced reports, some VBA coding or an add-in will be required.

For reports, steps like opening a source file, removing unneeded data, formatting cells, adding titles, and adding the current date are all doable.

Dashboards can also be automated via any of the available methods. For a dashboard, steps like accessing a source file, copying data from cells in the source file to cells in the dashboard, and updating the date and time are all feasible.

You can use Visual Basic to automate tasks in Excel. Visual Basic gives users more control over the steps than do macros; however, users need to have knowledge of the programming language. You can find more information on how to get started with Visual Basic here.

If you want to add data in an Excel worksheet to a Word document, and keep the data update in the Word document as it changes in the worksheet, you simply need to copy and paste.

When you paste the Excel cells into the Word document, click on the Paste Options button in Word, and select one of the options that say Link to Excel. As long as the documents are on the same computer, the Word document will automatically update as the Excel data changes.

For more complicated data sets, use Visual Basic or other programming languages to open a Word document and input Excel data into the document. Instructions for this are beyond the scope of an introductory-level article, but they can be found online for anyone looking for a challenge.

Like any other skill, education and practice are key. Find a resource that explains what you are trying to learn, and then practice, practice, practice. Macros are fairly easy to learn on the job, especially for a series of simple steps. You can also get a manual, find an online tutorial, or take a class.

Peltier advises, “I would suggest that people take a project they really think would benefit from automation and just start working on it. Start out with a small piece of it, like opening the file and copying the data in the right worksheet. Figure out how to automate that, then figure out the next step, and find a few web sites where they have a lot of good examples and just go back to them. But do it with that specific project in mind so that your motivation is there; when you finish you'll have something that'll save you an hour a day or more.”

Visual Basic coding is more difficult, but can be learned the same way. There are also many tutorials online and in manuals that can help you learn how to use VBA. Or, if you’re a learn-by-doing person, record some macros and look at the code to figure out how it works. There are also classes available.”

To view the code for a particular macro, click Tools, click Macro…, click the name of the macro,  and click Edit.

Says Peltier, “I started learning it about 20 years ago by recording my own macros and then seeing what code was recorded, then saying, ‘Ok, I want it to work a little bit differently than what I recorded.’ So you go and you play around with that. There is a wealth of information about Visual Basic for Excel on the internet. People end up reading examples on my site or other sites like Stack Overflow, or tMrExcel.com to find all kinds of stuff.”

An automation server (sometimes called an automation component) is a subtype of automation add-ins that are built into Excel. An automation server is an application that opens programmable objects (aka automation objects) in other applications (aka automation clients).

Developers use automation add-ins as a library of functions that can be called from cells in a worksheet. There are two ways to control an automation server: late binding (the objects are not declared until run-time) and and early binding (the objects are declared during design). Early binding is considered better because the application knows in advance what’s coming.

The Excel Object Model is each entity in Excel, which has properties that can be called on or changed via add-ins, VBA, or macros. Objects include an entire workbook, a worksheet, a row, a column, a range of cells, an individual cell, a pivot table, a chart, and the active cell or range of cells.

Some common properties are Name, Value, Row Number, Column Number, if it’s protected, if it contains a formula, and its display properties.

Code can reference the properties of an entity and use that to make a change. For example, it can look at the value of a cell and if it’s less than a target, it can change the color of the text in that cell to red. Code can also change the properties of an entity. For example, you can rename a worksheet from the default (e.g. Sheet1) to something else (e.g. February 2018 Expenses).

Dashboards and reports aren’t the only tasks you can automate in Excel. In fact, there are a variety of other things that can be automated as well. The following list of actions can be automated in Excel — use it to get ideas of how you can implement automation in your own workflow.

Enhancing Data Readability

• Create a chart

• Create and format tables

• Create a table of contents

• Create a cover page

• Display a UserForm

• Expand pivot table data

• View locations that are listed in spreadsheet as points on a map

Manipulating Data

• Delete rows that meet certain criteria (e.g., blank or negative values)

• Schedule obtaining data from other sources and insert it in a cell

Editing and formatting

• Format text

• Format cells

• Insert a formula

• Add headers to a column

• Open a document (e.g. csv file, xlsx file)

• Remove duplicate rows

• Add or remove sheets

• Convert a vector (i.e. a row) to a matrix

• Convert a matrix to a vector

• Copy a cell’s format

• Select the contents of a cell and copy it to other cells

Notifications

• Send an email when a cell value hits a certain threshold

• Send an email whenever a particular macro is run

Other

• Return the column or row of a cell based on its contents (i.e., the cell with value 5309 is in column AA)

• Show the formula in a cell

• Find a word in a string that meets criteria (e.g., 10 characters, starts with L, etc,)

• Find the last used cell

• Save and close all open workbooks

• Manage data sets larger than excel size limits

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change. 

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed. 

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.