Quick PasteSpecial: Recording, Examining, and Testing VBA Macro Code

The objective of this post is to introduce the powerful programming language used in Microsoft application called VBA.  More specifically this post will introduce the “macro recorder” that can replay steps taken while the recorder is on.  The recorder is a great way to familiarize oneself with VBA code.  However, further post will build on the understanding of the VBA language which can be used in conjunction with the recorder to create useful programs.  These programs can quickly and efficiently complete repetitive task without the risk of human error, besides the risk that is present in writing a program with errors.  Once a developer has an error free program it can be leveraged time and time again to do anything that a person can do with a mouse and keyboard but with all the advantages of having a program execute the steps instead of a person.

The steps below will convert selected formulas to their current values.

RECORDING THE MACRO

The following formulas were typed into an excel spreadsheet,

which yield the following values.

One useful set of procedures that Excel users is to select a range of cells with formulas and then use the past special command to change the selected range to values.  The following steps outline how to record these steps in an Excel macro.

1.  Select the range of cells that contain the formulas.

2. View – > Macro – > Record New Macro.

3. Enter the name for the macro, enter shift+C as the shortcut key combo, and click OK.

4. Do a “copy and paste special” on the values like you normally would on Excel.

5. View – > Macros – > Stop Recording.

The steps above created an Excel VBA macro.  One can test the macros by creating new formulas on the sheet, selecting them, and pressing the Ctrl+Shift+C shortcut specified in the steps above.

VIEWING AND MODIFYING THE MACRO

The steps above actually recorded some Excel VBA code in the form of a macros.  One can see and modify this code by following these steps:

1)  Hit Alt+F11 to bring up the Visual Basic Editor (VBE).

2) Select the project that corresponds to the workbook in which you recorded the macro, if you haven’s saved the workbook it should be in this location called Module 1:

3. Click on the “Module 1” icon above to get the see the Excel VBA macros.

The code created a macro called “ConvertFormulas” which takes no inputs.  The text in green is commented out with an apostrophe.  Comments can include some useful information, but for the sake of the program they are ignored.  Next, the second line of code copies whatever cells are selected.  The following three lines of code executes  the paste special commands with the numerous options.  Finally the last line of code before the “End Sub” line deselects the selected data.

Next, a line of code will be inserted that will prompt the user to confirm that they in fact want to run this macros, before the program executes and steps.  This is a great way to ensure macros are not accidentally run or that an incorrect program is not called by the user.  Finally, the last step will be to add the name of the author and date to the macros was created to the program.

The code prompting the user is inserted right before the program starts and outputs this message box before executing the ConvertFormulas subroutine.

Subsequent post will examine the syntax more thoroughly and develop more complex problems.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s