Category Archives: Excel VBA Tutorial

Excel VBA Tutorial 2

In Excel the steps needed to perform a series of actions can be recorded as a macro. In the previous lesson we looked at the before and after shots of the macro window.

Here is an image of the Standard VBA Toolbar, which you will see in all editors of VBA


It is beneficial to look at the code in the module that is generated from the macro recorder in order to see what syntax Excel VBA expects when you manually code, write instructions, for similar steps.

As we saw before, macros are stored in modules. A complete module may contain several sub procedures, each one performing a particular action. An advantage of coding your procedures manually is that you can organize them in a suitable arrangement.

A procedure can be either a function, property, or sub procedure.

A sub procedure follows the following structure:

Part Description
Sub This is the keyword that every sub procedure begins with.
SubProcedureName This is the name you give to the sub procedure, and the name that appears
in the Macro dialog box. It would benefit you to be meaningful with this name.
Arguments Additional instructions you are passing to the sub procedure. You receive these from the
variable values passed inside the parentheses. A sub procedure always includes open and close parentheses
even though it may not have any arguments.
Statements Instructions, commands, you record or write.
End Sub Every sub procedure ends with these keywords.

-Normally comments, or documentation, you add to the code are colored green, and begin with an apostrophe. It is
to your benefit to comment your code so you or someone else understands at a later time why you did what you did.

-Identions in your code is good practice and causes it to be more readable.

-Excel VBA automatically capitalizes keywords it recognizes. This is really helpful in order for you to see if you
spelled the word correctly or not.

Once you create a sub procedure, it will show up in your macro window (press Alt + F8) to show. Then select your macro’s name and click “Options” to assign a keyboard shortcut to the macro.

The video covers:

-Code Display
-Procedure Structure
-Sub Procedure Information
-Editting And Calling Subs

Trick: Create a keyboard shorcut for a macro by pressing these keys…”ALT + F8″ then click “Options”…enter the keyboard shortcut.

Here is the code stored in “Module1” produced by the macro recorder:

Questions? Comments About the Lesson? I want to help. Message me with the form below.

I consent to having this website store my submitted information so they can respond to my inquiry. See our privacy policy to learn more how we use data.