Saving Valuable Time with Office Macros - Part 2

Robin (Rob) Mayes
Senior software Engineer
LTEC/ATPI PhD student
University of North Texas (UNT)

 Last month the CTE blog published “Saving Valuable Time with Office Macros Part I”.  Topics covered were how to record and save a VBA Macro, how to find your VBA Macro, and how to execute your VBA Macro.  If you are interested in automating your office processes please read or review the previous month’s blog.  Here is our blogs second and the advanced list of competencies you can teach yourself about Microsoft Office VBA Macros.

1.       How delete, re-record, or edit your VBA Macro

2.       How to attach it to a menu ribbon (menu or button)

3.       How to create forms (advanced work)

As with most computer work, tasks go easy as long as you do not vary from the path.  However, life always gives us choices and diversions.  Just like life, building and using VBA Macros are fraught with options and enhancements leading to needed reworks and corrective modifications.  This blog entry will explain the next level of VBA Macro usage and some basic VBA programming competencies.

In older versions of Office the Macro menu is under Tools Menu.  In the new versions it is under the View Tab Strip (Ribbon Bar).  We will be covering the newer version of office in these blogs.  The Macros menu selections include “View Macros, Record Macro, and Pause Recording.   The first part of these two blogs covered the Record Macro function with the supporting Pause Recording selections.  Let us review what recording a VBA Macro is.   If the Record Macro is turned ON every menu you select, button you push, and keystroke you type is recorded.  It is recorded in the Visual Basic for Application (VBA) language.  If you examine the written steps the Macro recorder has written, you may find yourself very quickly learning to program.

However, first you have to learn to manage your VBA Macros.  A Macro can be saved in the particular document you are working with or in the document Template file.  If the Macro is recorded in the Template file your will always have access to the Macro.  If it has been saved in a particular document then you will only have access to that Macro when you are working with that particular document.   As default Word saves Macros in the DOTX (template file).  Excel saves Macros in the XLSX (worksheet file).   While PowerPoint supports VBA Macros, I am not sure for what I would use that feature.  If you really want to learn the VBA language and how database work, Access is an excellent program to learn all programming competencies.

To find your list of Macros select View Macros.   A window appears that displays a list of all available Macros.  Select a Macro and push the Delete button.  That will remove a Macro.  Select a Macro and push the Edit button.  You are now looking at a section of the Macro storage file that stores the Macro you selected.   Look at the text and compare it to the steps you completed while recording you Macro.  While some of the code may seem cryptic most of it is easy to see what is going on.   VBA was not written for “Rocket Scientists”.   Microsoft wanted to make it as easy to understand while attempting to include full Office functionality. 

If you want to see how “Search and Replace” is programmed, record a macro where you actually use the “Search and Replace” feature.  In the code you can change the values in the typed commands and search and/or replace for something else.  You just moved to the next level….you are now a “Programmer”.   You can actually save this program with a different name from the original Macro and “away you go”.  If you find a function in a Macro and you want to add it to your new Macro, just copy and paste it.  Then Save it and Run it to see if it works as planned.

Now that you are creating VBA Macros like crazy, let’s figure out how to create a new ribbon and tab to start up (Run) your VBA Macros.   Using the File tab select the Options menu on the left.  A Windows Dialog box will appear and then select Customize Ribbon.   Next, at the top of the left list box using the dropdown box, select Macros.  You should see all of the VBA Macros listed below.  Next, using the list box on the right and either select an existing ribbon or make a new one (your best choice).  To make a new Ribbon, push New Tab.   You can move it up and down in the list with the arrow keys on the far right.  There is also a rename button to give your tab and groups new names.  After you have your ribbon tab and groups started, move a selected VBA Macro to the selected group by pushing the right arrow button between the two list boxes.  If you decide you just really messed up your menu, push the Reset Customization button the restore the ribbon bars to their original states.   This will not delete the VBA Macros, just remove any links to them and restore the ribbon bars.  Push the OK button to finalize your changes and then test your new Ribbon Bar buttons.

Our last competency is gaining an understanding VBA forms.  VBA forms are windows you can create and then call from your VBA Macro.  Forms are important if you want your Macro to communicate with you and based upon your response to take a different direction.  Message box are simple forms used to tell the user something, give them a change to responds, or actually type a reply to a question.  More complex forms are created using a drag and drop tool that allows you to added buttons, text boxes, list boxes, and a lot more to a form. 

When you macro comes to a line typed like this:

 Msgbox(“Your First Form”) 

Guess what happens?

Now that you are curious, you can search online for samples of code for many VBA Macro features.  The more you learn, the more you will understand about the more complex coding examples.   While the VBA Macros between the various Office applications will appear similar, there will be differences based upon the different types of documents.

If you have enjoyed these two blogs, let us know and I can continue into more advanced VBA Macro territory.