This was my revision notes when I prepared for the final exam of ISOM 3230 Business Application Programming. This is transcribed from my old website so the formatting might be off. The content might not be updated as well.
This is served as a reference only, and I have no intention to update this note.

Types of Error

Option Explicit

Explicit: Need to declare variables before use

Option Explicit

Sub test()
    Dim height As Double
    height = 1.8
End Sub

Comment

' This is a comment.
REM This is also a comment.

Colon and Underscore

Colon (:) is used to combine two lines of code together.

Range("A1").Value = "Date" : Range("B1").Value = "Stock Code"

Underscore ( _ ) can be used to break down a single line statement into two lines.

Range("A1").Value = _
"Date"

Data Type in VBA

In programming, each data is associated with a data type. The data can be represented as variables and/or constants.

' Declare as Variable
Dim height As Double
' Declare as Constant
Constant pi As Integer = 3.1416

Variant variables are initialized to empty. An empty variable is represented by a zero in a numeric context and a zero-length string ("") in a string context.

Naming Convention

A subroutine and a variable cannot start with a digit, cannot be a reserved words, and cannot have space in between.

Object, Property, and Method

The dot operator (.) is used to refer the object’s property or method.

Object Variable

It is a common practice that we use an object variable to refer to a group of data cells, and then apply changes to the variable directly.

But when we assign the data cells to the object variable, we need to use the Set keyword.

Sub setObjectVariable()
	Dim myRange as Range
	Set myRange = Range("A1:D5")
	myRange.Value = 100
End Sub

Input and Output - Overview

Output

MsgBox "TestMsgBox", vbOkOnly, "Caption"

Debug.Print "Test - Debug.Print"

Range("A1").Value = "Test Range"

Input

Dim strInput As String

strInput = InputBox("Text", "Caption")
strInput = Range("A1").Value

MsgBox

Buttons:

Return Value:

InputBox

InputBox(prompt[, title][, default][, xpos][, ypos][, helpfile][, context])

The value entered by the user will be returned if the user clicks OK or presses the ENTER key. If the user clicks Cancel, a zero-length string is returned.

Application.InputBox

Application.InputBox(prompt[, title][, default][, left][, top][, helpfile][, helpContextID][, type])

Advantages

• Ask for number an make sure the input is actually numeric before accepting it.
• Allow the user to input an array of values
• Get a Range object using Application.InputBox

Debug.Print

All outputs using Debug.Print will directly go to Immediate Window.

Usages of immediate window

1. Using Debug.Print in VBA programmes
2. Printing values of properties - using ?
3. Executing instructions - typing the instruction directly.

Method Bracket

Rules

1. When the return value of a method is used immediately, brackets are required.
2. When the return value of la method is NOT used, brackets are NOT required.

Note

• Method is either Sub or Function
• Opening bracket is the one right after the name of a method, and there is no space in between.

Macro

Rules

1. Must be a Sub
2. Nothing in the Sub bracket.

Note

• Only macro can be a starting point of a VBA programme.
• Only macro can be assigned to buttons

Excel Objects - Introduction

Objects come in Collection

Each object (e.g. Workbook) is an individual item belongs to a collection (e.g. Workbooks) of similar objects.

Objects contain Properties

Two objects share the same list of properties, even though they have different values for the properties - they belong to the same class of object.

A collection has its own set of properties.

Objects contain Methods

The activities an object can do are called methods. Objects that belong to the same class can all do the same methods. The list of methods that belong to a particular collection object is usually different from the list of the methods that belong to the individual items in it.

Methods and Properties

Methods can Modify Properties

Most methods can change properties, but the method doesn’t remember what it did.

Properties can Involve Actions

Setting a property usually involves some kinds of actions.

Common Excel Object

Workbooks, Workbook, Worksheets, Worksheet, Sheets, Range.

Workbooks is a collection of all Workbook objects. A Workbook object represents an Excel workbook, i.e. .xlsx or .xlsm file.

Worksheets is a collection of Worksheet object. A Worksheet object represents a worksheet.

Sheets object represents a collection of Excel worksheets and Chart sheets.

Range object represents a range of data cells.

Workbooks Collection

This collection holds all open workbooks in Excel.

It contains methods and properties to add, remove, and count all workbooks.

It contains a property to retrieve a particular workbook object.

Workbooks Object

Workbook Object

Worksheets Collection

Similar to the Workbooks collection, this collection holds all the worksheets in current workbook.

Sometimes, Sheets collection can also refer to all worksheets in the same workbook. In addition, it contains both chart sheets and worksheets.

Worksheets Objects

Worksheet Object

Named Argument

Workbooks.Open "Book1.xls",,,,"pswd"

is equivalent to

Workbooks.Open Filename:="Book1.xls", Password:="pswd"

Creating Range objects using Range

Range("A1","B5") means cells A1 through B5.

Range("A:A"), Range("1:1")

Creating Range objects using [ ] and Named Range

[A1:B5], [MyRange]

Range - Property and Method

Range Count

	sub counter()
		dim count as Integer
		Range("A3", Range("A3").End(xlDown)).Select
		count = Selection.Count
	End Sub

Useful Methods and Properties

Workbooks.Open(fileName) => Workbook
Workbooks.Close
Workbooks.Add => Workbook

Workbooks.Count => Long
Workbooks.Item(index) => Workbook
Workbooks(index) => Number

Workbook.Activate
Workbook.Close(SaveChanges, FileName)
Workbook.Save

Workbook.ActiveSheet => Worksheet
Workbook.Name => String
Workbook.Names => Names
Workbook.Sheets => Sheets (including Charts)
Workbook.Worksheets => Sheets

Worksheets.Add(Before, After, Count, Type) => Worksheet / Chart / Macro
Worksheets.Copy(Before, After)
Worksheets.Delete
'Note a workbook must have at least one visible worksheet
Worksheets.Move(Before, After)
Worksheets.Select(Replace)

Worksheets.Count => Long
Worksheets.Item(Index) => Worksheet
Worksheets(Index) => Worksheet

Worksheet.Activate
Worksheet.Copy(Before, After)
Worksheet.Delete => Boolean
Worksheet.Select(Replace)

Worksheet.Cells => Range
Worksheet.Columns => Range (Need to Activate Sheet)
Worksheet.Name => String
Worksheet.Range(Cell1, Cell2) => Range
Worksheet.Rows => Range

Range.Activate => Variant
Range.Clear => Variant
Range.ClearContents => Variant
Range.Copy(Destination) => Variant
Range.Select => Variant

Range.Column => Long
Range.Columns => Range
Range.End(Direction) => Range
Range.Font => Font
Range.Formula => Variant
Range.Offset(RowOffset, ColumnOffset) => Range
Range.Range(Cell1, Cell2) => Range
Range.Row => Long
Range.Rows => Row
Range.Interior => Interior

Font.Bold => Variant / Boolean
Font.Color => Variant
Font.Italic => Boolean
Font.Name => Variant / String

Interior.Color => Variant

Validation Link

With Range("e5").Validation 
	.Add Type:=xlValidateWholeNumber, _ 
	AlertStyle:= xlValidAlertStop, _ 
	Operator:=xlBetween, Formula1:="5", Formula2:="10" 
	.InputTitle = "Integers" 
	.ErrorTitle = "Integers" 
	.InputMessage = "Enter an integer from five to ten" 
	.ErrorMessage = "You must enter a number from five to ten"  
End With

Array Manipulation