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:
|Sub||This is the keyword that every sub procedure begins with.|
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.
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:
-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:
Private Sub CommandButton1_Click()
'This macro formats the header and total cells
ActiveCell.FormulaR1C1 = "Quarterly Sales Report"
.Name = "Century Schoolbook"
.Bold = True
.Italic = True
.Size = 14
.HorizontalAlignment = xlCenter
Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"
'Call this other procedure
ActiveCell.FormulaR1C1 = "Worldwide Motor Cars"
ActiveCell.FormulaR1C1 = "Subitted By:"
ActiveCell.FormulaR1C1 = "Date:"
Questions? Comments About the Lesson? I want to help. Message me with the form below.