Excel Macros: Sample Files - dummies

Excel Macros: Sample Files

By Michael Alexander

Each macro in Excel Macros For Dummies book has an associated sample file that enables you to see the macro working and to review the code. You can use the sample files also to copy and paste the code into your environment (as opposed to typing each macro from scratch). In general terms, you open the sample file associated with the macro, go to Visual Basic Editor (by pressing Alt+F11), and copy the code. Then you go to your workbook, open Visual Basic Editor, and paste the code in the appropriate location.

You can download the sample Excel Macros files here.

If a macro is not working for you, most likely a component of the macro needs to be changed. Pay special attention to range addresses, directory names, and any other hard-coded names.

Keep the following things to keep in mind while working with these macros:

  • Any file that contains a macro must have the .xlsm file extension.

    Beginning with Excel 2007, Excel workbooks were given the standard .xlsx file extension. Files with the .xlsx extension cannot contain macros. If your workbook contains macros and you then save that workbook as an .xlsx file, your macros are removed automatically. Excel warns you that macro content will be disabled when saving a workbook with macros as an .xlsx file.

    If you want to retain the macros, you must save your file as an Excel macro-enabled workbook. This gives your file an .xlsm extension.

  • Excel will not run macros until they are enabled.

    When you open a workbook that contains macros in Excel 2010 or later, you see a yellow bar message under the ribbon stating that macros (active content) have been disabled. You must click the Enable option in the yellow bar in order to use the macro.

  • You cannot undo macro actions.

    When working in Excel, you can often undo the actions you’ve taken because Excel keeps a log (called the undo stack) recording your last 100 actions. However, running a macro automatically destroys the undo stack, so you can’t undo the actions you take in a macro.

  • You need to tweak the macros to fit your workbook.

    Many of the macros reference example sheet names and ranges that you may not have in your workbook. Be sure to replace references such as Sheet 1 or Range(“A1”) with the sheet names and cell addresses you are working with in your own workbooks.

    If the macro uses a directory, you must edit the macro to reference your target directory. For instance, in the macro example that prints all workbooks in a directory, the macro points to the C:Temp directory. Before using this macro, you must change it so that it references the directory that contains your workbooks.