Excel VBA Macro Tutorial: Excel's Event

An event handler procedure is a specially named procedure that's executed when a specific event occurs. Following are examples of types of events that Excel can recognize:

• A workbook is opened or closed
• A worksheet is activated or deactivated
• An object is clicked
• A worksheet is changed
• A workbook is saved
• A new workbook is created
• A window is activaed or deactivated
• A window is resized
• A new worksheet is added

Excel's events can be classified as the following:

• Workbook events. Events that occur for a particular workbook. Examples for this events are Open, Close, and BeforeSave.
• Worksheet events. Events that occur for a particular worksheet. Examples include Change, and SelectionChange.
• Chart events. Events that occur for a particular chart. Examples include Select.
• Application events. Events that occur for a particular the application (Excel itself).
• UserForm events. Events that occur for a particular UserForm or an object that contained on the UserForm. For example Click event.
• Events not associated with objects. For examples OnTime and OnKey events.

There is a strict rule we must follow when naming event handler procedures, the name must be in the form of objectname_eventname. For example, the CommandButton control has the Click event, for a CommandButton whose name is cmdButton1, the event handler procedure must be named cmdButton1_Click.

Event-handling procedures should be placed in the correct location. If the procedure is placed in the wrong location, it does not respond to its event even though it is named properly. Here are some guidelines:

• Event procedures for a user form (and its controls) should always go in the user
  form module itself.
• Event procedures for a workbook, worksheet, or chart should always be placed in
  the project associated with the workbook.
• If the object and the event can be found in the object and event list at the top of
  the editing window, it is all right to place the procedure in the current module.
• Never place event procedures in a code module (those project modules listed under
  the Modules node in the Project window).

Following example will puts word "Excel VBA Macro" when worksheet activated:

Private Sub Worksheet_Activate()
  ActiveSheet.Cells(1, 1).Value = "Excel VBA Macro"
End Sub

Related post: Excel VBA Macro Tutorial: Sub procedure

Excel VBA Macro Tutorial: Looping

What is looping? In simple term, looping is a process of repeating tasks. There are three types of loops, For-Next, Do-Loop, and While-Wend. Which we will use depends on the objectives and conditions.


For-Next loops

Repeats a group of statements a specified number of times.

Syntax:

For counter = start To end [Step step]
  [statements]
  [Exit  For]
  [statements]
Next [counter]

The following example, we will puts number words "Excel VBA Macro" to range A1:A10 in active sheet.

Sub putWords()
  For myNum = 1 To 10
     ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
  Next myNum
End Sub

Do-Loop loops

Repeats a block of statements while a condition is True or until a condition becomes True.

Syntax:

Do [{While | Until}  condition]
  [statements]
  [Exit  Do]
  [statements]
Loop

Or, you can use this syntax:

Do
  [statements]
  [Exit  Do]
  [statements]
Loop [{While | Until} condition]

The output of this examples exactly same as example above.

Sub putWords2()
  myNum = 0
  Do
     myNum = myNum + 1
     ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
  Loop While myNum <>
End Sub  

Sub putWords3()   
   myNum = 0
   Do Until myNum = 10
      myNum = myNum + 1
      ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"   
   Loop
End Sub

While-Wend loops

Executes a series of statements as long as a given condition is True.

Syntax:

While condition
  [statements]
Wend

Example:

Sub putWords4()
  myNum = 0
  While myNum < 10
     myNum = myNum + 1
     ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
  Wend
End Sub 

Excel VBA Macro Example: Copying a range

In this Excel VBA Macro Example section, I will show you how to copy a range from C4:E4 to G10:H10.


Sub CopyRange()
   Sheets("Sheet1").Range("C4:E4").Copy _
     Destination:=Sheets("Sheet1").Range("G10")
End Sub

If Destination argument is omitted, Microsoft Excel copies the range to the Clipboard.

If you only want to copies value of the range (simulate copy paste specials value), you may use the following code:

Sub CopyPasteValue()
   Sheets("Sheet1").Range("C4:E4").Copy
   Sheets("Sheet1").Range("G10").PasteSpecial _
     Paste:=xlPasteValues
End Sub

VBA Macro Excel Tutorial: Function Procedure

The difference between Sub procedure and Function procedure is Function procedure usually return a single value or an array. Function procedures can be used in two ways:

• As part of an expression in VBA Macro Excel procedure.
• In formulas that you create in a worksheet.

The following is a custom function named DiscountPrice. This function returns the discount price as currency.


Function DiscountPrice(Price as Currency) as Currency
   DiscountPrice = Price * 0.95
End Function


Here's an example how to use the function in a formula:

=DiscountPrice(200)

And this is an example how to use the function in a VBA Macro Excel procedure:

Sub DiscountIt()
   inputPrice = InputBox("Enter price: ")
   MsgBox "New price: " & DiscountPrice(inputPrice)
End Sub

VBA Macro Excel Tutorial: Sub Procedure

A procedure is a series of VBA Macro Excel statements that resides in a VBA module. Sub procedure syntax:

[Private | Public] [Static] Sub name ([arglist])
[instructions]
[Exit Sub]
[instructions]
End Sub

Private (optional) indicates that the procedure is accessible only to other procedures in the same module.

Public (optional) indicates that the procedure is accessible to any procedures in any modules.

Static (optional) indicates that the procedure's variables are preserved when the procedure ends.

Sub (required) indicates the beginning of a procedure.

instructions (optional) represents valid VBA code.

Exit Sub (optional) a statement that forces an immediate exit from the procedure.

End Sub (required) indicates the end of a procedure.


Scope of Sub procedure

A procedure with a Private scope only accessible by procedures in the same module, whereas procedures with a Public scope can be accessed by any procedure in any module. By default, every procedure is a Public procedure.


Executing Sub procedures

There are many ways to execute Sub procedure:

• With the Run -> Run Sub/UserForm command. Or by pressing the F5 shortcut key.
• Form Excel's macro dialog box, which you can open by clicking Tools -> Macro -> Macros. Or by pressing the Alt+F8 shortcut key to access the Macro dialog box.
• By clicking a button or shape on a worksheet. You must have the procedure assigned to the button first.
• Form another procedure you write. To call a Sub procedure from another procedure you must type the name of the procedure and include arguments values, if any.
• When an event occurs. For example, when you open a workbook, saving a workbook, or closing a workbook, etc.
• From a toolbar button.

Related posts:

• Excel VBA Macro Tutorial: Function Procedure
• Excel VBA Macro Tutorial: Controlling Execution
• Find A Specific Text Within Range And Return Its Row Position
• Check Whether Workbook Is Already Open Or Not

---
If you like posts in this blog, you can to support me :)

VBA Macro Excel Tutorial: Controlling Execution

This section introduces a few of the more common programming constructs that are used to control the flow of execution: If-Then construct, Select Case construct, and For-Next loops.

The If-Then construct
One of the most important control structures in VBA Macro Excel is the If-Then construct. The basic syntax of the If-Then structure is as follows:

If condition Then true_statements [Else false_statements]

Following is the example:

Sub Hello()
   inputName = InputBox("Enter your name: ")
   If inputName = "" then
     MsgBox "Hello, Anonymous."
   Else
     MsgBox "Hello, " & inputName & "."
End Sub


The Select Case construct
The Select Case construct is useful for choosing among two or more options. The syntax for Select Case is as follows:

Select Case testexpression
   [Case expressionlist-n
     [instructions-n]]
   [Case Else
     [default_instructions]]
End Select

Following example is an alternative to If-Then-Else:

Sub Hello()
   inputName = InputBox("Enter your name: ")
     Select Case inputName = ""
       MsgBox "Hello, Anonymous."
     Case Else
       MsgBox "Hello, " & inputName & "."
End Sub

This is another example of Select Case with three options:

Sub TheNumber()
   inputName = InputBox("Enter number (0-9): ")
     Select Case 0
       MsgBox "Zero Number"
     Case 1, 3, 5, 7, 9
       MsgBox "Odd Number"
     Case 2, 4, 6, 8
       MsgBox "Even Number"
     Case Else Exit Sub
End Sub


For-Next loops
You can use a For-Next loop to process a series of items. Following is an example of a For-Next loop:

Sub SumNumber()
   Total = 0
   For Num = 1 To 10
     Total = Total + (Num)
   Next Num
   MsgBox Total
End Sub

VBA Macro Excel Tutorial: Range Objects

Range object is the heart of VBA Macro Excel programming, because much of the work you do in VBA Macro Excel involves ranges and cells in worksheets. A range object consists of a single cell or a range of cells on a single worksheet. There are three ways of referring to range objects:

• Range property
• Cells property
• Offset property

The Range property
The Range property has two syntaxes:

object.Range(cell)
object.Range(cell1, cell2)

Following example puts words "VBA Macro Excel" into a range A1:E1 on active sheet of the active workbook:

ActiveSheet.Range("A1:E1").Value = "VBA Macro Excel"

The next example produce the same result as the preceding example:

ActiveSheet.Range("A1", "E1").Value = "VBA Macro Excel"


The Cells property
The Cells property has three syntaxes:

object.Cells(rowIndex, columnIndex)
object.Cells(rowIndex)
object.Cells

This following example puts the value 5 into cell B5 on Sheet1 of the active workbook:

Sheets("Sheet1").Cells(5, 2).Value = 5

The second syntax of the Cells method uses a single argument that can range from 1 to 16,777,216 (256 columns x 65,536 rows). The cells are numbered from A1 continuing right then down to the next row.

For example, to enters value 2 into cells B2 of active sheet:

ActiveSheet.Cells(258).Value = 2

The third syntax for the Cells property returns all cells on the referenced worksheet. The following example will erase all cells value and its format:

ActiveSheet.Cells.Clear


The Offset property
The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The Offset property syntax is follow:

object.Offset(rowOffset, columnOffset)

The following example puts words "VBA Macro Excel" into the cell above the active cell:

ActiveCell.Offset(-1, 0).Value = "VBA Macro Excel"