Familiarizing With the Visual Basic Editor
- 4 Comment
In this post, I’m am going to start off with just showing you the parts of a basic macro and how you can get to the editor. To see this feature for your self the first thing you want to do is record a macro. Try typing some text in a cell, maybe some copying and pasting, anything. Next were going to take a look at the code and see what happened.
Getting to the VBA Editor
After you record you macro and hit stop, the next step is to take a look at it and see what’s been recorded. There are 2 ways to get to the Visual Basic editor:
- Select Tools–>Macro–>Macros and a box will pop up with a list of the macros you have available. Click on the macro you would like to edit and hit “Edit” on the right of the box. Your macro will show up in the editor.
- Hit alt-F11 from your spreadsheet. This will pull up the Visual Basic editor with a blank window. Using the project window on the left, open up the “Modules” folder and inside there click on your macro. Your macro should now be showing in the window.
Over the next few posts, I will go into greater detail about some of the basic features that can be recorded by the macro and some of the features that need to be created manually. Check out the screenshot below that highlight some of the important areas of the Visual Basic Editor that you need to be familiar with to use the macro section of Excel.
Name of Your Macro
As you can see in the screenshot above, the basic macro is made up of three parts, its name, description and code. Every macro must have the keyword “Sub” before whatever you decide to name your macro and open and close parenthesis after. If you wanted to name your macro “Count_Rows”, then the first line on your macro will be “Sub Count_Rows()”.
The 2nd section is just a description of what the macro will be doing. You’ll notice that the description is in green because it is a comment section. Any part of a macro that is commented out will be ignored when the macro is actually run, so you can type anything you want there. To comment out a section, all you need to do is add a single quote before the line you want to comment ( looks like this –> ‘ ). You are then able to comment on anything you would like. Comments can be put anywhere within the macro code.
Code for the Macro
Finally, the 3rd section of the macro is the actual code. This is what the macro will be doing when you tell it to run. Anything you type in the section will be attempted to be run by the macro, so if you type something incorrectly that the macro doesn’t understand, it will give you an error to debug. Over the next few posts we will be looking at some commands we can give the visual basic editor to make it do what we want.
End of Your Macro
After the code is complete you have to end the macro with the line “End Sub”. This just lets the macro know that it has reached the end of the executable code and its job is complete.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.