ISOM3230Business Applications

Programming

VBA Basic

2

Learning Outcomes

apply programming concepts to solve business problems

describe the logic and flows of given programs predict the output of a program write programs with common programming

practices identify and fix logical and run-time errors in

programs

3

Outline1. Developer Tab 16. Operator

2. Enable Macro 17. Operator Precedence

3. Trust Center 18. Sub and Function

4. VBA Editor + Module 19. Passing Value

5. Immediate Window 20. If… End If

6. Comment 21. Select Case

7. Debug.Print 22. For… Next Loop

8. MsgBox 23. Do While/Until… Loop

9. InputBox 24. Do … Loop While/ Until

10. Data Type 25. For Each… Next

11. Constant 26. Exit Do/ Exit For

12. Declaring Variable 27. Array

13. Converting Data Type 28. Object Statement

14. Option Explicit 29. Excel Environment

15. Scope of Variable 28. Reference

Sub and Function4

Debug.Print

Developer Tab

Enable Macro

Trust Center

VBA Editor + Module

Immediate Window

Passing Value

MsgBox

InputBox

Constant

Converting Data Type

Declare & Assign

Option Explicit

Scope of Variable

Object Variable

Operator

Operator Precedence

If … End If

Select Case

For … Next Loop

Do While/Until … Loop

Do … Loop While/ Until

For Each … Next

Exit Do/ Exit For

Option Base

Color Constant

Comment, _, and Main

Data Type

En

viro

nmen

t +

Ba

sic

In/

outp

utV

aria

ble

Array Initialization

Redim + Preserve

Lbound + Ubound

Dynamic Array

Array

O bj

Con

di.

Sta

t.It

erat

ion

Arr

ay

Application.InputBox

Monitor a Variable

Ref

.

6-14

15-17

18-23

24-28

29-32

33

34-37

38-43

44-49

58-64Op

era

tor

Met

hod

Object Statement

Excel Environment57

50-56

End

5

6

Developer Tab

7

Enable Macro

8

Trust Center (1)

8

Trust Center (2)

9

10

VBA Editor + ModuleAlt+F11

11

Immediate Window

Press Ctrl+G to open the Immediate Window

12

Use of Immediate Window (1)

Tips 1You may use Immediate Window to execute statements. For example, after typing "Worksheets.Add" and then press Enter, a new worksheet will be created.

13

Use of Immediate Window (2)

Tips 2You may use Immediate Window to show the result value of a variable or expression.Type "? Expression" and press Enter, then the window will show the result of the expression

14

Comment, _, and Main Subroutine

Remarks: • ’ and Rem are used to insert comments into a program• _ is used to indicate that the current statement is not yet ended

and the next line is part of the current statement• The Naming Rules in VBA are the same as VB

- A new VBA program MUST BE stored in a NEW subroutine (or known as Main Subroutine) which is created by programmers - The Name of the subroutine MUST BE a unique and valid name

15

Debug.Print

16

MsgBox

Remark: Messagebox.Show() is not available in VBA

17

InputBox

Remark: OK returns a StringCancel returns Nothing

InputBox(Prompt, Title, Default )

18

Data Type

DataType Default Value

Boolean False

Integer 0

Double 0

Date 12:00:00 AM December 30, 1899

String “”

Variant Empty

Remark: Variant is similar to the Object data type in VB

19

ConstantDeclaring a constant

_ is used here

20

Declaring Variable and Assigning Value

Must be separated

21

Converting Data Type

Remarks:All of the following are available in VBACBool()CDbl()CStr()CInt()CDate()

Build-in Constant

22

Option Explicit

23

Scope of VariablePublic Level(Public)

Module Level(Dim, Private)

Global Variable

Local Variable

24

Operator (1)

Arithmetic OperatorOperator Example Result

Exponential ^ 6 ^ 4 1296

Multiplication * 6 * 4 24

Division / 6 / 4 1.5

Integral Division \ 6 \ 4 1

Remainder Mod 6 Mod 4 2

Addition + 6 + 4 10

Subtraction - 6 - 4 2

25

Operator (2) Comparison Operator

Operator Example Result

Smaller than < 6 < 4 False

Smaller or Equal to <= 6 <= 4 False

Greater than > 6 > 4 True

Greater or Equal to >= 6 >= 4 True

Equal to = 6 = 4 False

Not equal to <> 6 <> 4 True

Object comparison Is objA Is objB True/False

String pattern comparison Like “UST“ Like "*me*" True/False

When comparing objects, Is must be used. So objA = objB Runtime Error

26

Operator (3)

String Operator

Logic (or Boolean) Operator

Operator Example Result

String Connection & "ISOM" & "3230" "ISOM3230"

String Connection + "ISOM" + "3230" "ISOM3230"

"ISOM" + 3230 (Error)

Operator Example Result

Logical conjunction And 6 > 4 And 8 = 9 False

Logical disjunction Or 6 > 4 Or 8 = 9 True

Logical negation Not Not 6 > 4 False

27

Operator Precedence (1)

Precedence Rules Operators are evaluated by following the

precedence order table (next slide) Parentheses can override the order of

precedence and the left associativity Operators with equal precedence are evaluated

left to right in the order in which they appear in the expression

Operator Precedence (2)Type from Highest to Lowest

Parentheses ()

Arithmetic and

Concatenation

^

- (Negation)

* /

\

Mod

+ - (Add and subtract)

&

Comparison =, <>, <, <=,>,>=, Like, Is

Logical

Not

And

Or

Xor

Eqv

Imp

http://msdn.microsoft.com/en-us/library/gg278455.aspxa + = 1 ‘ Error in VBA 28

29

Sub and Function (1)

30

Sub and Function (2)

Sub test() Dim msg As Integer msg = MsgBox("Test 1", vbOKOnly) ' OK msg = msgbox "Test 2", vbOkOnly ' Error MsgBox "Test 3", vbOKOnly ' OK MsgBox("Test 4", vbOKOnly) ' ErrorEnd Sub

When the return value of a function will be used immediately, brackets MUST be used.

31

Passing Value to Method (1)

Remark:= is to assign a value to a specific argument:ArgumentName := Value

ArgumentName bArgumentName a

32

Passing Value to Method (2)

33

Object VariableClass nameObject variable name

Must use Set

Range object

Reset the object

Range object .Value property

34

If-Then

35

If-Then-Else

36

If-Then-Elseif

37

Select Case

38

For … Next Loop

The counter value i must be declared outside For loop

39

Do While … Loop

Background color

Color value

Range Object

Remark: Cells( y , x ) Where x, and y are coordinates of a worksheet

Cells() will return a Range Object

40

Do Until … Loop

Month(Date) A function returns an Integer specifying whole number between 1 and 12, inclusive, representing the month of the year.

41

Do … Loop While / Until

Remark: Cells( y , x ) Where x can be a letter, instead of an integer

42

For Each … Next

Update

Remark: - The Range Object covers data cells from B2 to D7- Range( ”B2” , ”D7” ) same as Range( ”B2:D7” )

43

Exit Do / Exit For

44

Array

Remark: - Range( ”B2” ) same as Range( ”B2” ).Value

Index starts from 0

45

Option Base

The default array lower bound index will become 1

If you also specify the lower bound index during declaration:

Dim Arr(2 To 5) As String

Then Option Base will NOT change the lower bound index in this array

Option Base 1 / Option Base 0

46

Array InitializationMust use Variant

MUST use Array keyword

This also works

47

Redim + Preserve

48

LBound + UBound

Calculating the array size: UBound(ArrayName) - LBound(ArrayName) + 1

49

Dynamic Array

Remark: Range( ”A1” ).End(xlDown).Row - Count the total number of valid data starting from A1- An empty cell contains invalid data

50

Object and Dot (.) Operator To access a property or method of an object, use the dot

(.) operator between the object name (left operand) and the property / method name (right operand)

Workbooks.CountDot Operator

ObjectEither Property (Variable)or Method

• Sub()• Function()

51

Object and Dot (.) Operator

Object.Property

Object.Method

Object.Sub()

Object.Function()

52

A complicated example

Range("B4").End(xlup).SelectRange("B4").End(xlup).Select

Object .SelectObject .MethodCannot be determined:Maybe Sub() or Function()

This Object can be generated from either Property (Object Variable) or Function (Returning Object).

Object variable: Objvar

Dim Objvar as New Human(where Human is a class)

Function’s Returning Object:

Function TestFunc() as Human Return New HumanEnd Function

53

Model Answer:Range("B4").End(xlUp).Select

54

Range("B4").End(xlUp).Select

XlDirection DescriptionxlDown Down

xlToLeft To left

xlToRight To right

xlUp Up

Returning ObjectOne parameter type is

XIDirection

1

2

3

4

55

Range.Select

1

2

3

4What is the data type of Return Value?

Mistake!!!

56

Range.Select

Workbook, Worksheet, and Range Objects

a range of one cell

a range of 5 cells

current sheet tab

Workbook Object

Worksheet Object

Range Object

58

Reference

59

Color Constant

Color constants: http://msdn.microsoft.com/en-us/library/gg278810.aspx

Miscellaneous Constantshttp://msdn.microsoft.com/en-us/library/gg278800.aspx

For reference

Constant Description Constant Description Constant Description Constant Description

vbBlack Black vbBlue Blue vbGreen Green vbCyan Cyan

vbRed Red vbMagenta Magenta vbYellow Yellow vbWhite White

Constant Description

vbCrLf Carriage return–linefeed combination

vbNewLine Platform-specific new line character; whichever is appropriate for current platform

vbTab Tab character

60

Application.InputBox with specific parameters

Application.InputBox(Prompt := Message, _ Title := Caption, _ Type := DataType)

For reference

61

Monitor a variable (1)

Step 1Highlight the variable you want to monitor, right click and then select “Add Watch”

For reference

62

Monitor a variable (2)

Step 2Select the Watch Type, and then press OK

For reference

63

Monitor a variable (3)

Step 3The Watch Window will be shown. Click on the grey bar near your code to set a breakpoint. The program will pause once it reaches the breakpoint.

For reference

64

Monitor a variable (4)

Step 4Press F5 to run the Sub. The program will pause at the breakpoint. The current value of the variable you watched will be shown on the Watch Window. You may also expand the content of the variable.

For reference