VBA Quick Review

1
VBA Quick Review

• Subroutine (sub - end sub)
• DIM to declare/define variables, plus various
  types
• Assignment statement
• MsgBox, InputBox() standard routines
• Intro to objects, especially Range
• Use SET to copy one object to another

2
Yet to come

• If-Then statements
• Select-Case statements
• For-Next loop
• Do loops
• Local versus global variables
• Arrays
• For Each loops

3
Yet to come

• Function subroutines
• User Forms
• Error handling
• Using Solver from within VBA
• Comprehensive application example

4
IF-THEN form
If condition Then statement Example If Income gt
50000 Then TaxRate 0.33 Simplest form of
If-Then is followed by a single program statement
5
Conditional Expressionsin IF-THEN statements
If Income gt 50000 And Deductionsgt5 Then TaxRate
0.28 Can also use keyword or (and other logical
operators) The NOT logical operator DIM
DataEntered As Boolean DataEntered False IF
Not DataEntered THEN go get the data Not
operator changes FALSE to True, or True to False
6
Conditional Expressions

• Conditions in the IF statement are arithmetic
  expressions
• that evaluate to a logical condition of true or
  false.
• Expressions are evaluated using algebraic laws of
  hierarchy
• , / come before , -, come before gt, lt, , lt,
  gt , ltgt
• which come before logical operators AND , OR.
• 353 evaluates to 18, not 83 or 24.
• 353 gt 10 evaluates to TRUE
• 3 53 gt 30 evaluates to FALSE
• Use of parenthesis may make expressions easier to
  read

7
IF-THEN-END IF Form
IF condition THEN Multiple statements END
IF Example If NumberOrdered gt 500 Then
PricePerUnit 1.20 Reorder True Inventory
Inventory - NumberOrdered End If
8
IF-THEN within IF-THEN
IF condition THEN IF condition THEN
statements End IF End If Example If
NumberOrdered gt 500 Then If NumberOrdered lt
Inventory Then PricePerUnit 1.20
Reorder True Inventory Inventory -
NumberOrdered End if End if
9
IF-THEN ELSE form
IF condition THEN Statements1 ELSE
Statements 2 END IF If NumberOrdered lt100 Then
Price 1.50 Else Price 2.00 End if
10
Nested IF-THEN ELSE form
IF condition1 THEN If condition2 THEN If
condition3 THEN Statements3 ELSE
statements3a ELSE statements2 ELSE
statements1 END IF
11
IF-THEN-ELSEIF form
IF condition1 THEN Statements1 ELSEIF
condition2 THEN Statements 2 ELSEIF condition3
THEN Statements 3 ELSE optional Statements
4 END IF
12
Select Case
When lots of IF statements get complex and
confusing And youd like to check one value
against many alternatives Select Case variable
Case Value1 Statements1 Case Value2
Statements2 Case Value3 Statements3 Case
Else optional Other Statements End Select
13
Select Case Example(from text)
Select Case ProductIndex Case Is lt3
UnitPrice 1.2 UnitCost Case 4 to 6
UnitPrice 1.3 UnitCost Case 7
UnitPrice 1.4 UnitCost Case Else
UnitPrice 1.1 UnitCost End Select
14
Rules for Case Expressions
Case expression Example Case 9 Case expression
To expression Example Case 10 To 20 Case Is
comparison-operator Examples Case Is lt13 Case Is
gt20
Case expression, expression, Example Case
10, 11, 12, 13 Case 10 to 20, 30 to 40
15
Review

• Subroutine
• Variables, types of data, use of DIM
• Assignment of values to variables
• Use of Range(), Range().Cells()
• IF-THEN conditional statement
• Select-Case conditional statement

16
Today

• For-Next Loop
• Use of Range() within loops
• Exit For statement
• Do-Loops

17
For loop
For Loop is used to repeatedly execute a
sequence of program statements a specific number
of times.
For I 1 To 10 MsgBox "I" I Next i

• Sets I to 1
• Executes the MsgBox statement
• At Next I, it increments I by 1, and then tests
  to see
• if I is less than the ending value of 10. If it
  is less, then it
• jumps back and executes the content of the loop
  again.

18
For with STEP keyword
What if you want to increment I by something
other than 1? For I 0 to 9 Step 2 Next
I I counts from 0 to 2 to 4 to 6 to 8 to 10 and
stops Counting downwards from 10 down to 1. For
I 10 to 1 Step -1 Next I
19
For-Next Syntax
For counter startvalue To endvalue STEP
increment statements Next counter The
brackets indicate that this item is optional
and may be omitted.
20
Using For with Range()
Sub demo() Dim I As Integer ' Highlight
the range of cells to make the work area visible
Range("B5B15").Select ' Now, set each
value of the range to a value of 1 to 10. For I
1 To 10 Range("B5B15").Cells(i) I
MsgBox "I" i Next I End Sub
21
Range().Cells(row,column)
Sub demo() Dim i As Integer ' Highlight
the range of cells to make the work area visible
Range("B5B15").Select ' Now, set each
value of the range to a value of 1 to 10. For i
1 To 10 Range("B5C15").Cells(i, 1) i
Range("B5C15").Cells(i, 2) i i MsgBox
"I" i Next i End Sub
22
Example Range().Offset()
For i 0 To 9 ' Copy cell in column A10 I to
b20 I Range("b20").Offset(i).Value
Range("A10").Offset(i).Value Next i
23
Nested For loops
For I 1 to 2 For J 1 to 3 MsgBox I
I , and J J Next J Next I (Demo in
RangeDemo.xls)
24
EXIT FOR
FoundItem False For I 1 to 100
statements If Range(A10).Offset(I).Value 50
Then FoundItem True Exit For End
If more statements Next I statements
25
Do Loops

• Use For loops when you wish to repeat a group of
  program statements a known number of times
• For I1 to 10 Next I
• Use Do loops to repeat program statementsuntil
  or while some condition is true (but you dont
  knowhow many times it needs to repeat)
• Example
• Valid False
• Do Until Valid
• Password InputBox(Enter a valid
  password)
• Valid CheckPassword(Password) True if
  okay
• Loop

26
Do Loop Types
Condition test at top of Do loop 1 Do Until
condition-is-true program statements
Loop 2 Do While condition -is-true
program statements Loop
27
Do loop types
Test condition at bottom of Do loop 3 Do
program statements Loop Until
condition-is-true 4 Do program
statements Loop While condition -is-true
28
1 Do Until Loop
1 Do Until condition-is-true
program statements Loop
At entry, VBA will test to see if the condition
is true or false. If false, VBA will execute the
program statements inside the Do Loop, thengo
back to the start of the loop and test the
condition again. If the condition is false, the
loop repeats. If true, the the program execution
jumps to the first statement after the Loop
keyword.
29
Do Loop Examples
See Week10Demo.xls spreadsheet file
30
Infinite Loops
Do loops will always execute until the test
condition is met If the test condition is never
met, the program will never leave the loop. When
the program gets stuck in a loop this is an
infinite loop If this happens Press Ctrl-Break
to stop your program
31
Next Topics

• Final Project (Briefly)
• Do loops
• Subroutine declarations and parameters
• Function Subroutines
• Arrays
• UserForms
• Solver usage
• Possibly other topics Error handling,
  collections, help, files

32
More about subroutines

• Subroutine parameters
• Calling subroutines
• Subroutine declarations
• Calling subroutines from other subroutines
• Breaks large problems into smaller, simpler
• problems
• Easier to fix defects
• Easier to make updates/changes
• Reuses (hopefully) already tested code
• Reduces your work when you can re-use

33
Subroutine Parameters
Subroutines without parameters
GetInputData() Subroutines with parameters
ChangeRecords(A1B10) With parameters, data is
passed into the subroutine where it is used or
manipulated within the subroutine This lets us
use one routine, over and over, with different
options ChangeRecords(C10C20)
ChangeRecords(D1E10)
34
Subroutine Parameters 2
The data that you can pass to a subroutine
include Variables Constants Strings and string
constants Objects (e.g. Range object) Arrays
(more on this later)
35
Subroutine Parameters 3

• Subroutine declaration defines parameters,
• also known as arguments
• Many procedures require no parameters
• Some use a fixed number of parameters
• Some can use optional parameters

36
Subroutine Parameters 4
Sub Main() Dim TotalIncome As Single, Tax As
Single TotalIncome 100 Tax 0 Call
ComputeTax(TotalIncome, Tax) MsgBox "Total
Income " TotalIncome ", Tax " Tax End
Sub Sub ComputeTax(TaxableIncome, TaxToPay)
TaxToPay TaxableIncome 0.33 End Sub
37
Subroutine Parameters 4a
Nested subroutine calls Subroutines can call
subroutines, which call subroutines Sub
Main() Call SubA() Main calls SubA() Sub
SubA() Call SubB() SubA, in turn, calls
SubB() End Sub Sub SubB() SubB, in turn,
calls SubC() Call SubC() End Sub Returns
back to SubA() Sub SubC() SubC() runs and
returns back to SubB() End Sub
38
Subroutine Parameters 4b
Subroutines can call themselves Not recommended
unless you know what you are doing. Can run out
of memory, crash program. Should not be needed
for anything we do in this class. Used to solve
problems recursively Example 5! 5 4 3
2 1 or factorial(X) X factorial(X-1)
39
Subroutine Parameters 5
Subroutines provide for efficient code
re-use Sub Main() Dim Total As Single, Tax As
Single Total 100 Call ComputeTax(Total,
Tax) MsgBox "Tax " Tax Output 33
Total 200 Call ComputeTax(Total, Tax)
MsgBox "Tax " Tax Output 66 End Sub
Reuses the code
40
Subroutine Parameters 6
Sub Main() Dim TotalIncome As Single, Tax As
Single TotalIncome 100 Tax 0 Call
ComputeTax(TotalIncome, Tax) MsgBox "Total
Income " TotalIncome ", Tax " Tax End
Sub Sub ComputeTax(ByVal TaxableIncome, ByVal
TaxToPay) TaxToPay TaxableIncome 0.33 End
Sub
Now, outputs 0, not 33 Why? Uses Pass by
value instead of Pass by reference
41
Subroutine Parameters 7
Normal parameter passing method is pass by
reference Subroutine can then change the
original value and the caller will see the
changes If you wish the caller to retain the
original value, then, use pass by value with
ByVal (ByRef is the opposite of ByVal)
42
Subroutine Parameters 8
Can specify the data type for parameters Sub
ComputeTax(Income As Single) Income Income
1.33 End Sub Can specify multiple
parameters Sub ComputeTax(Income As Single,
TaxRate As Single) Income Income
TaxRate End Sub
43
Subroutine Parameters 9
Can specify multiple parameters Sub
ComputeTax(Income, ByVal TaxRate) Income
Income TaxRate End Sub Income is ByRef
(default), and TaxRate is ByVal If Income is
changed, the caller will see the change If
TaxRate is changed, the caller will NOT see the
change
44
Subroutine Parameters 10
Pass data as arguments/parameters to
subroutine using global variables (less
preferred) Dim TaxRate As Single Sub Main() Dim
Total As Single Total 100 TaxRate 1.33
Call ComputeTax(Total) MsgBox "TotalIncome "
Total End Sub Sub ComputeTax(Income As Single)
Income Income TaxRate End Sub
Its preferable to use parameters than to pass
values in global variables. This is an example of
how not to pass values!
45
Subroutine Exit Sub
If you wish to exit a subroutine without
executing all the statements, use Exit
Sub Example Dim TaxRate As Single Sub Main()
Dim Total As Single Total 100 TaxRate 0
... If TaxRate 0 Then Exit Sub Call
ComputeTax(Total, TaxRate) MsgBox "Tax Amount
" Total End Sub
46
Private Subroutines

• Normally, all subroutines are public and can
  becalled by other subroutines in any module.
• Only public subroutines appear in the Macros
  dialog box
• You can create private subroutines that can be
  calledby procedures only within the module where
  theyare defined.
• Private sub scan_list()
• ...
• End sub
• If subroutines are called only within a module,
  good
• programming practice is to declare them as
  private
• You will not be graded on this in this class!

47
Static Subroutines

• Remember
• Local variables lose their values at subroutine
  exit
• Static variables retain their values at exit
  defined as
• Static Counter As Integer
• You can make all the variables in a subroutine
  STATIC by
• placing the STATIC keyword before the Sub
  keyword, as
• static sub MySub()
• dim x as integer, y as integer, z as integer
• end sub

48
Optional Subroutine Parameters
sub ComputeTax (Income As Single, Optional
TaxRate, Optional Credit) If
IsMissing(TaxRate) Then TaxRate 0.15 If
IsMissing(Credit) Then Credit 0 Tax Income
TaxRate Credit MsgBox "Tax" Tax ",
Credit" Credit End sub Optional parameters
must be Variant (means no type) All parameters
after first optional parameter must also be
optional
49
Optional parameters example
Call ComputeTax( 1000) Call ComputeTax( 1000,
.33 ) Call ComputeTax ( 1000, .39, -15) Call
ComputeTax(1000, , -15)
50
Local versus Global variables
Dim Tax As Single . Sub Main() Dim TotalIncome
As Single TotalIncome 100 Tax 0 Call
ComputeTax(TotalIncome) MsgBox "Total Income
" TotalIncome ", Tax " Tax End Sub Sub
ComputeTax(TaxableIncome) Tax TaxableIncome
0.33 End Sub
51
Function subroutines Defined
Subroutines are like a standalone command that
does its job and exits A Function Subroutine
does its job, but returns a single value for use
in an arithmetic calculation. Examples of
function subroutines are existing Excel
functions Sum(A1A10) Average(A1A10) Max(A1A1
0) Each performs a function, and returns a value
52
Function subroutines in expressions and formulas
Function subroutines return a value, so they
can be used in any arithmetic expression in your
VBA programs Y 13 Cubed(X) Can be called
from formulas you enter into a spreadsheet sum(A
1A10) 13Cubed(C10) User written functions
are added to the function list (see Excel demo)
53
Function Example 1
Function Cubed(X As Single) As Single Cubed X
X X End Function
54
Function subroutine Example
EXAMPLE Function Reverse(InputString) As
String ' Returns the input string, with
characters reversed Dim I As Integer,
StringLength As Integer Reverse ""
StringLength Len(InputString) For I
StringLength To 1 Step -1 Reverse Reverse
Mid(InputString, I, 1) Next I End
Function Demo in Excel, note use of range or
string input
55
Function subroutine declarations
Function Declarations Public/Private Static
Function name (parameters) As type name
expression End Function Public, Private and
Static are optional As Type is optional (VBA will
otherwise use variant) Use Exit Function to
exit a function before reaching the end
56
Function without parameters
Functions can be defined without
parameters Function InputSales() InputSales
InputBox("Enter Sales Total ") End Function
TotalSales InputSales() Useful for moving
complex input boxes out of the way
57
Function with 1 parameter
Function with one parameter or argument Function
RandomNum(MaxSize) Returns a random number
from 0 to MaxSize RandomNum Rnd(0)
MaxSize End Function RandomNum(100) returns
random value 0 to 100 RandomNum(5000) returns
random value 0 to 5000
58
Function with 1 parameter
Function with one parameter or argument Function
SalesCommission(TotalSales) Select Case
TotalSales Case 0 To 9999.99
SalesCommission TotalSales 0.08 Case 1000
To 19999.99 SalesCommission TotalSales
0.105 Case 20000 To 39999.99
SalesCommission TotalSales 0.12 Case Is
gt 40000 SalesCommission TotalSales
0.14 End Select End Function
59
Function with 2 parameters
Function Calculate_Area (Base, Height)
Calculate_Area Base Height End
function or Function Calculate_Area (Base as
Integer,
Height as Integer) Calculate_Area Base
Height End function
60
Reminder of Areas To Study

• Writing basic subroutines with parameters
• Defining variables, including arrays
• Distinguishing between local and global
  variables
• Using the IF-THEN statement forms
• Using Select Case
• Using the Range() object to access spreadsheet
  cells
• Using For-Next loops, be familiar with the
  Do-loop forms
• UserForms

61
Homework Reading
Chapter 7, Section 7.7 Do loops Chapter 10 up
through 10.5 (variables and subroutines) Chapter
10, 10.6 Function subroutines Optional Chapter
13, Debugging up through 13.3
62
Arrays
An array is a collection of data Think of a range
of cells as an array of data Weve seen how you
can use Range() to read and write data to
worksheet cells What if you want to store the
data internally, and not in the
worksheet? Solution Use an array variable
63
Defining an Array Variable
Dim X(10) As Single Sets aside an array X,
having 10 values X(0), X(1), X(2), X(3)
X(9) Note that arrays always start at zero.
Since this is confusing, you can tell Visual
Basic to start arrays at 1 (or any value), using
the Option Base statement at the top of each
module. Option Base 1 means that the array will
be X(1) to X(10)
64
Redimensioning An Array
You can change the size of an array after it
was defined with DIM, by using REDIM You might
do this because less data was entered than was
originally specified. Example Dim
EmployeeNames(100) As String ReDim
EmployeeNames(NumEmployees) BUT big problem .
65
Redimensioning An Array
Problem When you REDIM an array, all of the
existing values stored in the array are
erased! Solution Redim PRESERVE
EmployeeNames(NumEmployees)
66
For Each
For Each is the last loop example See
Week10demo.xls ForEachDemo2 and 3
67
Homework 9-30 points
Part 1 Reading Chapter 9, Arrays Section 7.6
For Each Part 2 Programming problems 9a, 9b,
9c described on next slides a. Arrays b.
Subroutines c. Arrays and function
subroutines Okay to work with other students
to discuss approaches to solving the problems (I
encourage this), but each student needs to turn
in their own assignment and is expected to write
their own program routines.
68
Homework 9, problem (a)(same data as HW 8)
Shipment Weight And Cost Table Weight Cost 10
0 15 200 20 300 25 400 30 500 35 60
0 40 700 45 800 50 900 55
69
Homework 9 a
Array practice Implement problem 1 on Page 144
of the text. Remember to include Option Base 1
at the top of your module containing your
subroutines.
70
Homework 9b
Subroutines practice Implement problem 1 on
page 167
71
Homework 9c
Arrays and Function subroutines with
parameters Declare two global arrays, Weight()
and Cost() to hold the values from the HW8
lookup table. Initialize the array values to
those from the table. Create at least 2
subroutines One as the main routine to prompt
for input and display the result. The other
should be a function with one parameter (the
weight to look for in the Weight() array), and it
should return the corresponding cost. Use any
method weve discussed to search through
the array (for-next, do-loops, select case). In
the main routine, use InputBox() to obtain the
shipment weight. Then use the function subroutine
to to find the cost. Display the result in a
MsgBox.
72
UserForms
Dialog boxes and user interfaces User Forms are
the official name for VB dialog boxes Usually
written in shortened from as UserForms Creating
and editing UserForms (lab work today) Window
Events Linking dialog box elements to program
subroutines Input/output through dialog
boxes Text Chapter 12
73
UserForms Overview
The elements or controls of a dialog box
74
UserForm Editor
75
Dialog Box ActiveX Controls
76
Setting Properties of Controls
77
Linking Controls To Program

• Whenever the user does something, an event
  occurs
• Clicks on a button
• Clicks on a checkbox
• Clicks on radio button
• Moves the mouse
• Drags the mouse (move while pressing the mouse
  button)
• Events link dialog box controls to program
  subroutines
• called event handlers
• (there are zillions of events but only need to
  use a few)

78
Okay Button Example
When the button named OkButton is clicked,
VBA will automatically call a routine named
Sub_OkButton_Click() The event name
assigned by VBA The name you gave your
Okay button control
Private Sub_OkButton_Click() .. This is where
you write code to collect the data .. that was
entered in the dialog box End Sub
79
UserForm Excel Demoand Lab Practice Exercise
Chapter 12, section 12.3.
80
Initializing UserForm Fields
Double click somewhere on the form (not on a
control) OR, right-click on selected UserForm in
project window, and then choose View Code Then,
select the Initialize event Then, write code to
copy values into the form controls Example
UsernameBox.Value Ed See Example 12.2 (page
202 in text)
81
Getting Values of From Dialog
In the Okay button event for click, reference
the controls value fields, and copy the values
to local variables UserName UsernameBox.Value P
roduct1Option Product1CheckBox.Value
82
Topics

• Built-in functions commonly used in business
  applications
• String functions and formatting
• Dates and Times
• Formatting Data for output
• Error handling
• If time, listbox and reference edit controls
• Homework 10

83
String Functions
Strings can be in length from 0 to 255
characters VBA-provided String
functions Len(string) tells you the length of the
string Len(ABCDEF) is 6 Chr(numeric
expression) used to create a single
character NewLine Chr(13) The internal code for
the newline character is 13. This converts the
numeric value 13 into a character string
value. When used in MsgBox, it causes output to
begin on the next line in the output box.
84
String Functions 1
Removing blank or space characters Example
UserName InputBox(Enter your name)
UserName Trim(UserName) removes extra spaces
from the beginning and end of the enter name. If
the user had typed, Enter your name John
ltentergt the extra spaces would be trimmed to
convert John to just John
85
String Functions 2
Related string functions LTRIM(string) removes
extra spaces from the beginning or left-side of
the string only. RTRIM(string) removes extra
spaces from the end or right-side of the string
only. TRIM(string) removes spaces from both sides
86
String Functions 3
Converting upper case and lower case
text Example InputValue InputBox(Select YES
or NO) Select YES or NO yes sets InputValue
to yes To test the input value you could
write, If InputValue yes or InputValueYES
Then Better approach If UCase(InputValue)
YES Then ...
87
String Functions 4
Similarly, you can convert all characters to
lower case InputValue LCase(InputValue) would
convert YES to yes, or Yes to yes.
88
String Functions 5
Extracting part of a character string S
ABCDEFGHJIJKLMNOPQRSTUVWXYZ Left ( S, 3) is
ABC Right (S, 3) is XYZ Mid (S, 4, 3) is DEF
89
String Functions 6
Searching for one string inside another Instr()
looks for pattern inside another string, and
returns the position where it was
found InStr(starting_position, SearchWithin,
ForThis, Compare) The first and last parameters
can be leftout, yielding the simpler InStr(
SearchWithin, ForThis )
90
String Functions 7
S ABCDEFGHJIJKLMNOPQRSTUVWXYZ InStr( S,
MNO ) returns 13, since the MNO begins at the
13th character position in S. This function is
often used together with LEFT() and RIGHT(). For
example UserName Bob Smith BlankPosition
Instr( UserName, ) FirstName Left
(UserName, BlankPosition - 1) LastName
Right(UserName, Len(UserName) - BlankPosition)
91
String Functions 8
InStrs first and last parameters StartingPosition
indicates where the search should begin. The
last parameter, Compare, can be 0 or 1. 0 means
to find an exact match, while 1 means to ignore
the case of the text (lower case treated same as
upper case). The default is 0. Example SThe
quick brown fox jumped over the lazy dog
Instr(S, The) returns 1 but Instr(4, S,
The) returns 0 because The does not match
the at the end of the sentence.
92
String Functions 9
Example SThe quick brown fox jumped over the
lazy dog Instr(S, The) returns 1 but
Instr(4, S, The) returns 0 because The does
not match the at the end of the
sentence. Instead, probably want to use
Instr(4, S, The, 1) which returns 33, since it
ignores the case in the pattern match.
93
Dates
Reference the Date keyword or the Time keyword to
get current date and time Examples Dim S as
String S Date MsgBox S displays 11/29/01
Dim MyTime, MyDate, MyStr MyTime
170423 MyDate January 27, 1993
94
Time
Example Dim S as String S Time MsgBox
S displays 22953 PM
95
Formatting Data 1

• Formatting numbers, dates and time for program
  output
• Examples
• You want currency values to display as 43.00
• You want the date to display as Wednesday,
  11/28/01
• You want the time to display as 012030 AM
• The easiest way to format output is to use the
  Format() function
• Format(numeric expression, format selection)
• format selection can be VBA standard formats,
• or, can be a custom, user defined format.

96
Formatting Data 2
Some examples to illustrate the idea s
Format(4363.14159, ".") produces 4353.14,
always with two decimal digits. s
Format(4363.14159, ",.") says to insert ,
between the 1000s digits, producing 4,353.14.
97
Standard Formats
General NumberDisplay number with no thousand
separator. Currency Display number with
thousand separator, if appropriate display
two digits to the right of the decimal separator.
Fixed Display at least one digit to the left
and two digits to the right of the decimal
separator. Standard Display number with
thousand separator, at least one digit to the
left and two digits to the right of the decimal
separator. Percent Display number multiplied by
100 with a percent sign () appended to the
right always display two digits to the right
of the decimal separator. Scientific Use
standard scientific notation. Yes/No Display No
if number is 0 otherwise, display
Yes. True/False Display False if number is 0
otherwise, display True. On/Off Display Off if
number is 0 otherwise, display On. Long
Date System date format, Wednesday,
11/28/01 Long Time System time format
98
Standard Format Examples
Format (13) is just 13 (no formatting) Format
(4361.456, Currency) is 4,361.45 Format
(34545098, Standard) is 34,545,098.00 Format
(.05, Percent) is 5.00 Format ( 0,
True/False) is False Format ( 1, True/False)
is True non-zero is true Format ( 0, Yes/No)
is No Format ( 35, Yes/No) is Yes any non-zero
is yes
99
Custom Format Examples
' User-defined formats. MyStr
Format(4635.784556, .) rounds to 4635.8 MyStr
Format(5459.4, ",0.00") ' Returns
"5,459.40". MyStr Format(334.9, "0.00") '
Returns "334.90". MyStr Format(5, "0.00") '
Returns "500.00". Case conversion built-in to
the Format() function MyStr Format("HELLO",
"lt") ' Returns "hello". MyStr Format("This is
it", "gt") ' Returns "THIS IS IT".
100
Date Format Examples
' Returns current system date in the
system-defined long date format. MyStr
Format(Date, "Long Date") Wednesday,
11/28/01 MyStr Format(Date, dddd dd mmm
yyyy) Wednesday 28 Nov 2001 mm returns the
month number (e.g. 11) mmm returns a 3 letter
abbreviation (e.g Nov) mmmm returns the entire
month name (e.g. November) dd returns the day as
a 2 digit number ddd returns the day as the day
of the week (e.g. Wed) dddd returns the day as
the day of the week (e.g. Wednesday) yy is the
last two digits of the year yyyy is the full 4
digits of the year
101
Time Format Examples
MyStr Format(MyTime, "hms") ' Returns
"17423". MyStr Format(MyTime, "hhmmss
AMPM") ' Returns "050423 PM". S
Format(Time, "hhmmss mm/dd/yy") Surprisingly,
the Time object contains both date and time S is
set to 112035 11/29/01
102
A Round() function
Excels built-in Round() function is not
accessible from VBA Function Round(x As Single,
NumDigits As Integer) Round Int(x (10
NumDigits)) / (10 NumDigits) End Function How
this works Let x 123.4567, and NumDigits
2 102 100 so this expression becomes 123.4567
100) which is 12345.67 int(12345.67) is the
integer part, or 12345 Then that is divided by
100 to convert back to decimal form, 12345 / 100
is 123.45
103
Handling Error Conditions

• Examples
• Incorrect input from the user
• Error detected (or not detected) inside a
  subroutine
• Incorrect (or non-existent) initialization
• Random program performance
• Introduction to types of program errors
• How to handle error conditions in your program

104
Types of program errors
Two Basic Program Errors Syntax error - you
wrote a program statement that VBA cannot process
and VBA gives you a compile error Run-time
error - something goes wrong while the program is
running. You see a dialog box telling you about
the error and offering to End or Debug the
program.
105
Types of Errors

• Runtime error types
• Input/Output faults (incorrect user input)
• Logic faults (error when writing the program)
• Computation faults (wrong algorithm, divide by
  zero)
• Interface faults (incompatible types, e.g.
  intlong)
• Data faults (failure to initialize, off by one
  errors)

106
Handling Input/output errors
Runtime error trapping - catches the error and
lets your program code handle the problem rather
than crashing the program and displaying a rude
dialog box. Dim X As Single X InputBox("Enter
a number")
107
Here is what happens
When you click Okay without entering a number
108
Solutions
1 Dim S As String, X as Single S
InputBox(Enter a number) If Len(S) ltgt 0
Then X S End if But what if S contains
text? 2 Better way On Error Resume Next X
InputBox(Enter a number) MsgBox You entered
X
109
Using the Err variable
Err is a VBA built-in variable that contains a
code number indicating the most recent error
condition. If Err is zero, then no error was
detected. If Err is not zero, then an error
occurred.
Dim X as Single On Error Resume Next X
InputBox("Enter a number") If Err 0 Then
MsgBox "You entered " X Else MsgBox "Data
entry error" End If
110
Writing an Error Handler
Sub DemoError() On Error Goto Handler X
InputBox (Enter a number) Exit Sub Handler
MsgBox (You must enter a numeric value)
Resume back at statement that caused error End
Sub
111
Disabling an Error Handler
On Error Goto Handler remains in effect for rest
of the subroutine To turn off error handling,
use On Error Goto 0 Now, the program will crash
if an error is encountered.
112
Looking Up an Error Code
A generic but not necessarily user friendly
error routine On Error Goto Handler code goes
here exit sub Handler MsgBox "Error "
Err ", " Err.Description Resume End Sub
113
Error handling options in VBA
114
Logic faults
Mistyping a variable name use option
explicit Practice_Variable Practice_Varable
1 Leaving out a Case in Select Case (e.g. A,
B, C, E, F omitting case D) Common
mistakes Using lt instead of lt Using lt when
you really meant lt, etc.
115
Computation faults
Your code implements the solution wrong You used
the wrong method (algorithm) to solve the
problem Omit or use parenthesis
incorrectly 358 when you wanted (35)8 Rou
nd off, data truncation problems Example copying
a single (3.14159) to an Integer Dim X as Single,
I as Integer X 3.14159 I X Becomes 3, and
youve lost the precision of X
116
Data faults
Incorrect initialization VBA is nice in that it
sets all numeric variables to zero, and strings
to . However, sometimes your code may be
expecting a different initial value - and you
forgot to initialize the variable. off by one
- many of you saw this in problem 9 defined as 0
to 99, but tried to reference (100) Or defined 1
to 100, but tried to reference PracticeArray(101)
Divide By Zero
117
Homework 10 (20 points)
See Homework10.doc on the class web site. Will
prompt the user to input a full name and city
name, and then use the string functions to break
apart the input string and write the individual
components in to the spreadsheet so that you end
up with a table that looks similar to