Excel Macros, No Programmer Needed

In this article

Macros are a time saver.  Repetitive tasks can be automated using macros in Excel.  Need to format a cell and apply that formatting to multiple cells; a macro can save you some time.  Have a special report that is printed each month?  Use a macro to automate the printing process.  Do you or your company have standard, boilerplate, text that must be entered in to specific cells?  A macro can automatically place that text for you.  Any task that you do time after time can be reduced to a macro.  A macro will eliminate any errors in the steps of a repetitive process.  The added benefit will be that each step in the macro will run without error.

A Bad Rap

I think macros have gotten a bad rap.  As soon as students at my college hear the word “macro”, they immediately decide that it is going to be a difficult topic.  I cannot completely blame them, I have read a number of Excel books and online Excel blogs that make macros sound too much like programming.  Excel macros are usually presented prior to VBA (Visual Basic for Applications) a programming language.  This gives the impression that macros are just an introduction to VBA.

It can be said that macros can be used as an introduction to VBA programming.  When a macro is created, VBA code is automatically created.  If you choose to look at or edit the code, it will familiarize you with VBA.  Editing the code that macros create would be an excellent introduction to VBA programming.

In this blog I will present the non-programming side of macros.  Macros can be created without you having to write any programming code.  You will find that you do not need to become a programmer in order to benefit from the time saving features of macros.

The Connection between Macros and VBA

According to Microsoft; “a macro is a series of commands and functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) that are stored in a Microsoft Visual Basic module (module: A collection of declarations, statements, and procedures stored together as one named unit. There are two types of modules: standard modules and class modules.) and can be run whenever you need to perform the task.”   There they are; the words “Visual Basic”, a programming language.  So, if a macro is a series of commands and functions in a programming language’s module, therefore you have to be a programmer to use macros.  A programmer designs, writes, tests, debugs, and maintains the source code of computer programs.  A series of Excel commands and functions stored in a module does not sound like the duties of a programmer.

At this point, one of my students will point out that Microsoft uses the term; VBA Macros.  The student, with a broad smile of satisfaction, will say; “see Loretta, you have to be a VBA programmer to use macros”.  I answer; yes, you can use VBA programming code to automate processes that manipulate worksheets in Excel.  The macros I am referring to do not require you to write programming code.  The keystrokes and mouse clicks you will use will be automatically generated into VBA code for you.

A Simple Macro from Keystrokes and Mouse Clicks

To create an Excel macro, you will use the “macro recorder”.  Think of a tape recorder and how it records audio.  The macro recorder records keystrokes and mouse clicks that you perform in Excel.  The steps to creating a simple macro are not complicated.  The first step is to add an additional tab to the Excel Ribbon that will allow you to access macros.

1)    The “Developer” tab must be visible on the Excel Ribbon.

a)    Choose “File”  –>  “Options”

b)    In the Options dialog box, select “Customize Ribbon”

c)    In the list on the right, place a check mark next to “Developer”

d)    Click “OK” and return to Excel

Create a macro that will place a name into any cell in Excel when you press the CTRL+m shortcut keys.

2)    From the “Developer” tab, in the “Code” category, click “Record Macro”.

The following “Record Macro” dialog window will appear.  You will need to name the new macro; I called it “FirstName”.  Macro names can include letters, numbers and the underscore character (_). Spaces or special characters are not allowed in macro names.  In addition, macro names must begin with a letter.

Assign a key after the Ctrl+, in the example the key is the letter m.  Plus you can create a description for the new macro, which is optional.  Click “OK” to start recording your new “FirstName” macro.

 

a)    In the active cell, C3 in this example, type in your first name; i.e., Lynn.

b)    Press the <Enter> key after typing in the name.

c)    Stop the macro recorder by clicking, “Stop recording”

d)    Cell C3 now contains the name, “Lynn”, and the active cell is now cell C4

The macro called “FirstName” has recorded the following keystrokes:

• L
• y
• n
• n
• <Enter>

Testing the New Macro

The keyboard combination of CTRL+m, control key and the letter “m”, is the shortcut key that will active or start the macro.

To test out the “FirstName” macro, click on cell A8.  Run the macro by pressing CRTL+m, the first name “Lynn” will be placed in cell A8 and the cursor will have moved to cell A9.  You could move to any cell within that workbook.  Activate the macro and the name “Lynn” will be entered with the cursor one cell beneath it.

Behind the scenes, Excel has automatically written VBA code that has saved and plays back your keystrokes in “FirstName”.  Thankfully, you do not have to worry about the technicalities involved in creating and running a macro.

Shortcut Keys to Avoid

The shortcut keys for the “FirstName” macro were CTRL+m.  When creating macros, be careful of the shortcut keys that you might use.  Excel has some built in shortcut keys that are already assigned to various tasks.  If you assign any of these built in shortcut keys, Excel will over write its built in task and run your macro instead.

Here is the list of built in shortcut keys that Excel uses.  You would never want to override these common shortcut keys.  These key combinations are also used in all of the other Microsoft Office applications; Word, Access, PowerPoint, Publisher, Outlook and OneNote.

• CTRL+c  (copy)
• CTRL+n  (new)
• CTRL+o  (open)
• CTRL+p  (print)
• CTRL+s  (save)
• CTRL+x  (cut)
• CTRL+z  (undo)
• CTRL+y  (redo/repeat)
• CTRL+v  (paste)

If you want to use one of the above built in shortcut keys, the best solution is to add the SHIFT key before the letter.  Instead of CTRL+x for cut, you would assign CTRL+SHIFT+x.

Updating the Loan Amortization Schedule with Macro

The “Loan Amortization Schedule” can be found in the BiggerPockets FilePlace, https://www.biggerpockets.com/files/user/oakdragon/file/loan-amortization-schedule.  We will place a clickable button that will clear the; Loan Amount, Term in Years, Interest Rate and Payments per Year values to zero.  Any time that the “Reset” button is clicked will cause the macro to reset the four input values on the schedule.

1)    Open the Loan Amortization Schedule in Excel.

2)    In the “Developer” tab, in the “Code” category, click the “Record Macro” option.  The Record Macro dialog window will appear.  Use the word, “Reset” as the new macro name.  Use the letter “r” as the Ctrl+ shortcut keys.  Then click “OK” to start recording keystrokes.

3)    Enter a zero in cells C3 through C6 and then press the Enter key.  Go back to the “Code” category in the “Developer” tab and click the “Stop Recording” option.  The “Reset” macro has been created.  The next step is to place a form button on the schedule and assign the “Reset” macro to the button.

4)    In the “Controls” category of the “Developer” tab, click the “Insert” option and click the Form Button.  The Form Button will be the first control and looks like a small rectangular button.

 

5)    Your mouse cursor will change to a small crosshair.  Starting at upper left corner of cell E4, click and drag your cursor to the lower right corner of cell E5 to draw out the button.  The new button will have a “Button 1” caption and an “Assign Macro” window will open.  In the window, click the word Reset and then click “OK”.  Your previous macro is now assigned to the button.

6)    Change the button caption from “Button 1” to “Reset”.  Place your mouse cursor inside of the button next to the “B” of “Button 1” and delete the caption and type in “Reset”.  Do not click the ENTER key.  Just click on any empty cell to finish renaming the button.

Time to Test the Reset Button

Enter some values into the four input cells on the Schedule.  The Loan Amortization Schedule will fill with the resulting values.  Now for the test, click your “Reset” button.  The four input values have been reset and you are ready to enter new values.

You have now created a useful “Reset” button that makes the Loan Amortization Schedule easier to use.  All this was accomplished without writing a single line of programming code.

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.