Excel VBA Basic Tutorial 1

This page contains the 1st lesson on the Excel VBA Basic Tutorial series.  It covers topics in creating and managing array and understanding the VBA decision and loop structures.  Beginners in VBA programming are encouraged to go through the prior lessons in this series if they had not already done so.  This document contains information about the following topics.
 Microsoft Support site or the Excel VBA Help section on your computer contains comprehensive examples on most the issues covered on this page.  For more information, please refer to them.

Creating Your First Macro   Microsoft Support
 
In this sub section, we will show you how to create your first macro (VBA program).   We will use the world classic "Hello World!" example.  To create the example, please follow the following steps:
1.  Open Visual Basic Editor by go to Tools...Macro...Visual Basic Editor or just simply press the [Alt] and [F11] keys at the same time.
 





2. In the Insert menu on top of the Visual Basic Editor, select Module to open the Module window (code window).




3.  In the Module window, type the following:
Sub showMessage()
    MsgBox "Hello World!"
End Sub




4. Click the Run  button,, press [F5], or go to Run..Run Sub/UserForm to run the program




5.  The message box pops up with the "Hello World!" greeting.



This is your first VBA programmer.



Recording Your First Macro

Recording a Macro

Macrosoft Excel has a build-in macro recorder that translates your actions into VBA macro commands.  After you recorded the macro, you will be able to see the layout and syntax.  Before you record or write a macro, plan the steps and commands you want the macro to perform.  Every actions that you take during the recording of the macro will be recorded - including the correction that you made.

In this example, we will record a macro that sets the cell background color to light yellow.  To record the macro, follow the steps below:
1.  Select Record New Macro... under Tools...Macro




2.  In the Record Macro dailog box, type "SetBackgroundColor" in the Macro Name textbox to set the macro name.  Leave all other option by default then click the Ok button.  This will start the macro recording.





3.  In the Background Color Panel, select the Ligth Yellow color box.  This action will set the background of the current cell (A1) in light yellow color.





4.  To stop the macro recording, click the Stop button (the navy blue rectangle) on the Macro Recorder toolbar.
Now you have recorded a macro that set cell background to light yellow.

See the Recorded Syntax

The recorded macro is ready for use.  Before we run the marco, let's look into the syntax.
1. To load the Visual Basic Editor, press [Alt] and [F11] at the same time.  (Remember from our prior lesson?)  The Visual Basic Editor comes up.





2.  Expand the Modules folder in the Project Explorer by clicking on the plus (+) sign.


3.  Double click the Module1 folder to see the sub routine (marco).



As the figure shows, the name of the sub routine is "SetBackgroundColor".   The color index for the light yellow is 36.  The background pattern is soild.


Run the Recorded Macro

In our prior example, we created the "Hello World!" marco.  We ran the macro within the Visual Basic Editor.  This time we will run the recorded macro in the worksheet.
1.  On any worksheet, select from D3 to E6.
2.  Run the recorded macro by select Tools...Macro...Macros... or press [Alt] and [F8] at the same time.



3.  The Macro dailog box displayed.  Since there is only one macro in the module, by default the only macro, SetBackgroundColor is selected.  Click the Run botton to run the macro. 
4.   Cells D3 to E6 now have light yellow background color.





Modules and Procedures

Modules and Procedures and Their Scope

A module is a container for procedures as shown in our prior examples.  A procedure is a unit of code enclosed either between the Sub and End Sub statement or between the Function and End Function statements.  

The following sub procedure (or sub routine) print the current date and time on cell C1:
Sub ShowTime()
    Range("C1") = Now()
End Sub
The following function sum up two numbers:
Function sumNo(x, y)
     sumNo = x + y
End Function
Procedures in Visual Basic can have either private or public scope.  A procedure with private scope is only accessible to the other procedures in the same module; a procedure with public scope is accessible to all procedures in in every module in the workbook in which the procedure is declared, and in all workbooks that contain a reference to that workbook.  By default, procedures has public scope.

Here are examples of defining the scope for procedure. 

Public Sub ShowTime()
    Range("C1") = Now()
End Sub

Private Sub ShowTime()
    Range("C1") = Now()
End Sub

Calling Sub Procedures and Function Procedures

There are two ways to call a sub procedure.  The following example shows how a sub procedure can be called by other sub procedures. 
Sub z(a)
    MsgBox a
End Sub

Sub x()
    Call z("ABC")
End Sub

Sub y()
    z "ABC"
End Sub

Sub z procedure takes an argument (a) and display the argument value ("ABC") in a message box.  Running either Sub x or Sub y will yield the same result.

The following example calls a function procedure from a sub procedure.
Sub ShowSum()
     msgbox sumNo(3,5)
End Sub

Function sumNo(x, y)
     sumNo = x + y
End Function

The ShowSum sub procedure calls the sumNo function and returns an "8" in a message box.

If there are procedures with duplicate names in different modules, you must need to include a module qualifier before the procedure name when calling the procedure. 

For example:
Module1.ShowSum



Passing Argument by Reference or by Value

If you pass an argument by reference when calling a procedure, the procedure access to the actual variable in memory.  As a result, the variable's value can be changed by the procedure.  Passing by reference is the default in VBA.  If you do not explicitly specify to pass an argurment by value, VBA will pass it by reference.  The following two statements yield the same outcome. 
Sub AddNo(ByRef x as integer)
Sub AddNo(x as integer)

Here is an example to show the by reference behavior.  The sub procedure, TestPassing 1 calls AddNo1 by reference and display "60" (50 + 10) on the message box.
Sub TestPassing1()
    Dim y As Integer
    y = 50
    AddNo1 y
    MsgBox y
End Sub

Sub AddNo1(ByRef x As Integer)
    x = x + 10
End Sub
The following example shows the by value behavior.  The sub procedure, TestPassing 2 calls AddNo2 by value and display "50" on the message box.
Sub TestPassing2()
    Dim y As Integer
    y = 50
    AddNo2 y
    MsgBox y
End Sub

Sub AddNo2(ByVal x As Integer)
    x = x + 10
End Sub