Excel Lessons Online

Finally, you have mastered the art of creating simple macros in Excel and they are working great. You are getting your work done quickly and efficiently. But, now your company has expanded and with this growth, you have had to add extra data to your reports and steps, so now you need to edit your wonderful macros in order to make your work life manageable again.

You don’t want to recreate the macros, just add new information and steps. But the bigger problem you face is that to edit the macros in Excel you need to use another program called Visual Basic Application, and you don’t know anything about it. Don’t be intimidated by this program which uses code to create and edit macros because you don’t need to be a computer language wiz or techie to use it. You just need to know some basics about the program itself to get you started.

In this tutorial, I will show you how to easily edit an existing macro in Excel using Visual Basic Editor. We will not cover all the functionalities, but try to see this tutorial as just an introduction and have patience, because with practice you will be able to fully understand and use Visual Basic Editor within Excel to its full capacity.

In the scenario for this tutorial, you still work for the restaurant chain Happy Tummies as the lead Training Specialist. Because of the company’s expansion five new training managers have been hired, and you need to add this information to an existing macro that you created which runs the names of all the existing training managers. You use this macro constantly to create training reports.

Let’s Get Started!

1. Go to Start>Programs>Microsoft Excel to open the application. Go to File>New and open a new spreadsheet. Then go to Window>Unhide to bring the personal macro workbook forward – while you work with Excel this macro workbook remains hidden but once you need to edit a macro you need to unhide it to be able to work with it.

2. Select “PERSONAL.XLS” and click OK to open the personal macro workbook.

3. Now, we’ll access the macros already created. Go to Tools>Macros and select Macros.

4. Now you can see all the macros already created and stored in the personal macro workbook. For the purposes of this tutorial, we’ll need to update the macro called “ht” – this is the macro that has the names of the training managers recorded. Select “ht” and click “edit” to edit the macro.

5. In this step, you get a glimpse of the Microsoft Visual Basic Application. You will notice three distinctive windows:
1. The Project Window located to the upper left hand side, which shows you the files currently open.
2. The Properties Window located right below the Project window to the lower left hand corner, which lists the properties of the macros.
3. The Code window, located to the right hand side of the screen. In the code window is where you edit the actual code of the macros and the one that we will concentrate for the remainder of this tutorial.

Please Note: Before moving forward, we need to go over some basic elements within Visual Basic so that it is easier for you to use this new application. First, Visual Basic is a type of computer language where you use computer code to tell the program what you want it to do.

You need to become familiar with the basic elements of the language and its structure. Let’s compare, for example, the English language to Visual Basic. The English language is made up of words or nouns, and when these nouns do something, we call it a verb, and when we want to describe a characteristic of a noun, we use adjectives. The syntax of Visual Basic is very similar, for example, in VBA there are things called “objects” (like nouns in English) and when these things do something, they use a “method” (like verbs in English) and when describing these things you use “properties” (like adjectives in English).

All code starts with “Sub MACRO NAME ()” and ends with “End Sub.” When you need an object to do something or follow a “method” you type a period right in between the object and the method – object.method. When you need an object to have a specific characteristic or “property” you type a period in between the object and the property – object.property.

Let’s quickly go over these elements in the below example. You will notice that the code for macro “ht” begins with “Sub ht ()” then you have the description of the macro right below it, training managers monthly report (you can type a description when recording the macro for the first time), then you see in the next line, the keyboard shortcut – so in essence, the first couple of lines of code give you a quick summary of the macro.

6. In this example, when recording the original macro, I entered the names of the training managers. The first name was “Mary Smith” and this is why you see the reference “ActiveCell.FormulaR1C1= “Mary Smith.” Visual Basic added this code after I entered the first name, then in the second line of code you see “Range(“A2”).Select” because range is a thing that is doing something or following a method – the method is that range A2 was selected and the name “Mary Smith” was entered. For the purposes of this tutorial, I will show how easy it can be to edit this type of code without really knowing much about code. We are going to copy and paste five times an existing piece of code with the instructions and edit accordingly. So, go ahead and copy the last two lines of code - ActiveCell.FormulaR1C1= “Mike Temples,” Range(“A6”). Now paste it five times right below it. It should look like the image below.

7. Next, replace the name “Mike Temple” with the five new Training Managers – Marlene Evans, Jose Gonzalez, Melvin McCall, JR Fallon and Peter Johnson. Enter A7, A8, A9, A10 and A11 in the Range(“A6”).Select piece of code. It should look like the image below.

8. Now go to File>Save PERSONAL.XLS and save the macro changes within the personal macro workbook. Then go to File>Close and Return to Microsoft Excel to close the macro within Visual Basic.

9. Finally, we are going to test it, now that you are back in Excel, go to File>New to open a new spreadsheet. Press the control key and the “h” key on your keyboard to test the shortcut for the macro. The new names should appear along with the old ones. Please remember that you can also run the macro by going to Tools>Macro>Macros and selecting the macro and clicking the “run” button.

Congrats! you actually worked on some code and learned how to edit macros. Anything new like a computer program or language can be intimidating especially if you don’t work with it on daily basis and don’t have much time to play with it. And many times, we would much rather continue doing things the long way than find shortcuts, but when you take a little time to learn something that seemed hard, you usually find it is not so hard and can really help you get things done easily and quickly. Remember, this tutorial is an introduction to Visual Basic within Excel and does not cover all the elements, capabilities or functions of Visual Basic; but I will soon be posting more in depth tutorials related to macros that will help you understand the Microsoft Visual Basic application better.

If this tutorial has helped you, please consider donating.

3 Comments

Comments RSS TrackBack Identifier URI

Leave a comment