Creating Excel VBA Macros

How to Create Excel VBA Macros.

Have you used a voice recording?  Creating Excel VBA Macros can be that easy.  The main benefit of a voice recording device is that everything you spoke once can be repeated over and over again without you physically speak again. Your voice is on the tape. You can even be heard without you being there. The Excel Macro Recorder feature is similar. The difference is that you store Excel actions with Excel VBA Macros instead of your voice. These actions can be repeated again and again without your physical intervention for each step in Excel. For example: Select a given data region, insert a table, and create a Pivot Table

  • Select a heading of a given table and apply a particular formatting
  • Delete all the empty sheets
  • Delete all sheets except the current one
  • Arrange cells in a particular layout
  • And more…

An audio tape is a set of recorded sounds. A Macro is a set of recorded instructions.

What Kind of Instructions Can Be in an Excel Macro?

Actions like: changing the color of a cell, renaming a sheet, creating a table, sorting, filtering, etc. On a voice recorder, to reproduce your voice you hit the Play button on the recording device; on the other hand, to reproduce a set of actions on Excel you run a Macro. Your voice is recorded on a magnetic tape or digital memory, while your Excel actions are recorded in a text file, specifically in an Excel VBA Macros Sub procedure inside a text window called a “Module”. There is a hierarchy within Excel:

  • Workbook – the Excel file that contains Excel formulas, data, and charts
    • Sheet – a tab, or section of the Excel Workbook
      • Range – a single cell or group of cells
  • Form – the graphical screen display for Excel instructions and data
  • Module – an Excel container that holds Excel VBA instructions
    • Subprocedure or Sub – a section of Excel VBA code dedicated to perform certain actions.  Contains, data, instructions, calls to functions.  Does not return data or status to the calling program.
    • Function – a series of VBA lines of code that perform a particular task, and return data or status codes to the calling routine.
    • Object – The target of instructions in the Excel Libraries.  A container for Properties, Methods, and Events
      • Method – An activity that Excel is being told to do.  Examples: insert a cell, delete a worksheet
      • Property – an attribute of the object.  Data or parameters passed to or from an Excel SUB or Function that give direction to how that code runs.  Examples: color, name, size, location.
      • Event – A trigger that directs when the object is to run.  Example, a keypress, or mouse click are events.

How do you Record Instructions in a Macro?

If you are using Excel 2007 or 2010

  • Go to: View>Record Macro>Macro name:>Ok
  • Or click the status bar Record icon.
  • The Excel 2007/2010 status bar also indicates that Excel is in recording mode…

On Excel 2003

  • Go to: Tools>Macro>Record New Macro
  • And specify the Macro configuration (name, description, etc.)…
  • After pressing OK, the status bar indicates that Excel is in recording mode…

While this mode is activated, EVERYTHING you do on Excel will be recorded. This is not as good as it might appear; the same way your voice recording device registers your hesitations and background noise, the Excel recorder captures all your mistakes and incidental actions.

Editing Recorded Macros

This creates the need to edit recorded actions in Macros. Keep in mind that while Excel is in recording mode, its behavior is normal, with one exception: in the background, Excel creates a Module (Module1, Module2, Module3, ModuleN) and a Sub procedure with the name you specify on the “Record Macro” dialog. By default: Macro1, Macro2, Macro3, MacroN.

The Excel VBA Editor is opened with Alt+F11.  Locate the workbook in the Explorer panel on the left.  Within that Workbook you will have Sheets, Modules, and Forms.  Your recorded macros will be within one of the modules in the Modules organization. Make the necessary changes to the macro in the VBA Editor.  You can run the subroutine from there, or simply make and save your changes, then run the macro from within the spreadsheet window.

Author unknown:

Edited by Rich Moyer

Rich Moyer is Principal Consultant(retired) of Spaho Consulting

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.