Final Exam Review for ISOM 3230 Business Applications Programming


Willis WAN, Chun Yu

22 May 2018

Information Systems

Programming

VBA

ISOM

IS

ISOM3230

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

TypesExplanation
Compile Error
and
Syntax Error
Compile errors are recognized by the compiler as being illegal.
Syntax error will be immediately highlighted in red and VBA editor will pop up a message box.
Runtime ErrorOccurs during execution of code, and cause the code to stop running.
Logical ErrorsCauses the macro to perform unexpected actions or return an incorrect result.

Option Explicit

Explicit: Need to declare variables before use

1
2
3
4
5
6
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

Data TypeDefault Value
String""
BooleanFALSE
Date12:00:00 AM December 30, 1899
Integer0
Double0
VariantEmpty → "" or 0

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.

Common PracticeData Type
strNameString
intNum1Integer
dblTotalDouble
blnValueBoolean
datDobDate

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:

ConstantValue
vbOKOnly0
vbOKCancel1
vbAbortRetryIgnore2
vbYesNoCancel3
vbYesNo4
vbRetryCancel5
$\dots$$\dots$

Return Value:

ConstantValue
vbOK1
vbCancel2
vbAbort3
vbRetry4
vbIgnore5
vbYes6
vbNo7

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])

ValueTypes Inputted
0A formula
1A number
2A string
4Boolean
8A cell reference
16An error value, such as # N/A
64An array of values

Advantages

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

Macro

Rules

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

Note

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

Method NameDescription
AddCreate a new workbook and activate it.
CloseClose all open workbooks.
OpenOpen a workbook.
Properties NameDescription
CountReturn a Long value that represents the number of objects in the collection.
ItemReturn a single workbook object from the collection.

Workbook Object

Method NameDescription
ActivateActivate the first window associated with the workbook.
CloseClose the workbook.
SaveSave changes to the workbook.
Properties NameDescription
ActiveSheetReturn the active sheet in the active workbook or in the specified window or workbook. Return Nothing if no sheet is active.
NameReturn a String value that stores the workbook name.
NamesReturn a Name collection (named range) that include all the names in the workbook.
SheetsReturn a Sheets collection that represents all the sheets in the workbook.
WorksheetsReturn a Sheets collection that represents all the sheets in the workbook.

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

Method NameDescription
AddCreate a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet.
CopyCopy the sheet to another location in the workbook.
DeleteDelete the object.
MoveMove the sheet to another location in the workbook.
SelectSelect the worksheet.
Properties NameDescription
CountReturn a Long value that represent the number of objects in the collection.
ItemReturn a single worksheet object.

Worksheet Object

Method NameDescription
ActivateMake the current sheet the active sheet.
CopyCopy the sheet to another location in the workbook.
DeleteDelete the object.
SelectSelect the object.
Properties NameDescription
CellsReturn a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).
ColumnsReturns a Range object that represents all the columns on the worksheet.
NameReturn or sets a String value representing the name of the worksheet.
RangeReturn a Range object that represents a cell or a range of cells.
RowsReturn a Range object that represents all the rows on the worksheets.

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 A1throughB5.

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

Creating Range objects using [ ] and Named Range

[A1:B5], [MyRange]

Range - Property and Method

PropertyMethod
.Areas.Activate
.Cells.Autofill
.Columns.Borderaround
.Count.Calculate
.Font.ClearContents
.Formula.Copy
.Interior.Delete
.Item.Find
.Name.Insert
.Offset.Merge
.Range.Run
.Rows.Select
.Value.Sort

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

TaskStatic ArrayDynamic Array
DeclareDim arr(0 To 5) As LongDim arr() As Long
Dim arr As Variant
Set SizeSee Declare aboveReDim arr(0 To 5)As Variant
Increase size (keep existing data)Dynamic OnlyReDimPreserve arr(0 To 6)
Set valuesarr(1) = 22arr(1) = 22
Receive valuestotal = arr(1)total = arr(1)
First positionLBound(arr)LBound(arr)
Last positionUbound(arr)Ubound(arr)
Read all items(1D)For i = LBound(arr) To UBound(arr)
Next i

Or
For i = LBound(arr,1) To UBound(arr,1)
Next i
For i = LBound(arr) To UBound(arr)
Next i

Or
For i = LBound(arr,1) To UBound(arr,1)
Next i
Read all items(2D)For i = LBound(arr,1) To UBound(arr,1)
  For j = LBound(arr,2) To UBound(arr,2)
  Next j
Next i
For i = LBound(arr,1) To UBound(arr,1)
  For j = LBound(arr,2) To UBound(arr,2)
  Next j
Next i
Read all itemsDim item As Variant
For Each item In arr
Next item</td>
Dim item As Variant
For Each item In arr
Next item</td> </tr>
Pass to SubSub MySub(ByRef arr() As String)Sub MySub(ByRef arr() As String)
Return from FunctionFunction GetArray() As Long()
    Dim arr(0 To 5) As Long
    GetArray = arr
End Function
Function GetArray() As Long()
    Dim arr() As Long
    GetArray = arr
End Function
Receive from FunctionDynamic onlyDim arr() As Long
Arr = GetArray()
Erase arrayErase arr
*Resets all values to default
Erase arr
*Deletes array
String to arrayDynamic onlyDim arr As Variant
arr = Split("James:Earl:Jones",":")
Array to stringDim sName As String
sName = Join(arr, ":")
Dim sName As String
sName = Join(arr, ":")
Fill with valuesDynamic onlyDim arr As Variant
arr = Array("John", "Hazel", "Fred")
Range to ArrayDynamic onlyDim arr As Variant
arr = Range("A1:D2")
Array to RangeSame as dynamic Dim arr As Variant
Range("A5:D6") = arr

About the Author

Willis WAN, Chun Yu

A graduate in BBA(Information Systems) of HKUST. Tech Enthusiast, Teacher, Learner.

Copyright © 2020 All Rights Reserved