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:

Sub SubProcedureName(arg1,arg2,etc)
	statement1
	statement2
End Sub

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:

Private Sub CommandButton1_Click()
    MyFormat
    
End Sub
Sub MyFormat()
    'This macro formats the header and total cells
    
    Range("A1").Select
    
    ActiveCell.FormulaR1C1 = "Quarterly Sales Report"
    
    With Selection.Font
        .Name = "Century Schoolbook"
        .Bold = True
        .Italic = True
        .Size = 14
        
    End With
    
    Range("A6:D6").Select
    
    With Selection
        .HorizontalAlignment = xlCenter
    End With
       
    
    Range("A6:D9").Select
    
    Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"
     
    Range("A1").Select
     
    'Call this other procedure
    FormatReportHeader
    
End Sub

Sub FormatReportHeader()



    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Worldwide Motor Cars"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Subitted By:"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Date:"
            
End Sub

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

[simple_contact_form]