Feb 5 2007

Familiarizing With the Visual Basic Editor

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:

  1. 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.
  2. 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.

Macro Screenshot

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()”. 

Macro Description
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.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

4 Comments on this post

Trackbacks

  1. Using a For Loop in a Macro | Excel Hints wrote:

    [...] you’re new to macros, you might want to check out a previous post I made Familiarizing with the Visual Basic Editor and Use the Record Macro Feature.  Basically, the purpose of a macro in Excel is to provide you [...]

    October 12th, 2008 at 8:01 am
  2. Writing a Custom Function | Excel Hints wrote:

    [...] want a little  additional help with getting use to the VBA editor, check out this previous post on familiarizing with the Visual Basic editor.  A quick way to get to the VBA editor is to hit the Alt+F11 key sequence (Additional keyboard [...]

    February 22nd, 2009 at 1:29 pm
  3. Writing a Macro | Excel Hints wrote:

    [...] methods to open the VBA editor, as we discussed in Use the Record Macro Feature in Excel and Familiarizing With the Visual Basic Editor.  To quickly open the VBA Editor, hit Alt-F11 on your keyboard from Excel.  Then from the main [...]

    March 1st, 2009 at 10:32 pm
  1. Dhanumjaya said:

    nice usefull to teach my self as my knowledge great job ur doing

    December 30th, 2011 at 7:57 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives