Transcript VBA Quick Review

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
Yet to come
•
•
•
•
•
•
•
If-Then statements
Select-Case statements
For-Next loop
Do loops
Local versus global variables
Arrays
For Each loops
Yet to come
•
•
•
•
•
Function subroutines
User Forms
Error handling
Using Solver from within VBA
Comprehensive application example
IF-THEN form
If condition Then statement
Example:
If Income > 50000 Then TaxRate = 0.33
Simplest form of If-Then is followed by a single
program statement
Conditional Expressions
in IF-THEN statements
If Income > 50000 And Deductions>5 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
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 >, <, =, <=, >= , <>
which come before logical operators AND , OR.
•3+5*3 evaluates to 18, not 8*3 or 24.
•3+5*3 > 10 evaluates to TRUE
•3 + 5*3 > 30 evaluates to FALSE
Use of parenthesis may make expressions easier to read
IF-THEN-END IF Form
IF condition THEN
Multiple statements
END IF
Example:
If NumberOrdered > 500 Then
PricePerUnit = 1.20
Reorder = True
Inventory = Inventory - NumberOrdered
End If
IF-THEN within IF-THEN
IF condition THEN
IF condition THEN
… statements
End IF
End If
Example:
If NumberOrdered > 500 Then
If NumberOrdered < Inventory Then
PricePerUnit = 1.20
Reorder = True
Inventory = Inventory - NumberOrdered
End if
End if
IF-THEN ELSE form
IF condition THEN
Statements1
ELSE
Statements 2
END IF
If NumberOrdered <100 Then
Price = 1.50
Else
Price = 2.00
End if
Nested IF-THEN ELSE form
IF condition1 THEN
If condition2 THEN
If condition3 THEN
Statements3
ELSE statements3a
ELSE statements2
ELSE statements1
END IF
IF-THEN-ELSEIF form
IF condition1 THEN
Statements1
ELSEIF condition2 THEN
Statements 2
ELSEIF condition3 THEN
Statements 3
ELSE ‘ optional
Statements 4
END IF
Select Case
When lots of IF statements get complex and confusing…
And you’d 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
Select Case Example
(from text)
Select Case ProductIndex
Case Is <=3
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
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 <13
Case Is >20
Case expression, expression, …
Example:
Case 10, 11, 12, 13
Case 10 to 20, 30 to 40
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
Today
•
•
•
•
For-Next Loop
Use of Range() within loops
Exit For statement
Do-Loops
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.
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
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.
Using For with Range()
Sub demo()
Dim I As Integer
' Highlight the range of cells to make the work area visible
Range("B5:B15").Select
' Now, set each value of the range to a value of 1 to 10.
For I = 1 To 10
Range("B5:B15").Cells(i) = I
MsgBox "I=" & i
Next I
End Sub
Range().Cells(row,column)
Sub demo()
Dim i As Integer
' Highlight the range of cells to make the work area visible
Range("B5:B15").Select
' Now, set each value of the range to a value of 1 to 10.
For i = 1 To 10
Range("B5:C15").Cells(i, 1) = i
Range("B5:C15").Cells(i, 2) = i * i
MsgBox "I=" & i
Next i
End Sub
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
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)
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
Do Loops
•Use For loops when you wish to repeat a group
of program statements a known number of times
For I=1 to 10 … Next I
•Use Do loops to repeat program statements
until or while some condition is true (but you don’t know
how 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
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
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
#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, then…go 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.
Do Loop Examples
See Week10Demo.xls spreadsheet file
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
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
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
Subroutine Parameters
Subroutines without parameters:
GetInputData()
Subroutines with parameters:
ChangeRecords(“A1:B10”)
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(“C10:C20”)
ChangeRecords(“D1:E10”)
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)
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
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
Subroutine Parameters 4a
Nested subroutine calls
Subroutines can call subroutines, which call subroutines …
Sub Main()
Call SubA()
Sub SubA()
Call SubB()
End Sub
Sub SubB()
Call SubC()
End Sub
Sub SubC()
…
End Sub
Main calls SubA()
SubA, in turn, calls SubB()
SubB, in turn, calls SubC()
Returns back to SubA()
SubC() runs and returns back to SubB()
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)
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
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”
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)
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
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
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
It’s preferable to
use parameters than to
pass values in global
variables. This is an
example of how not
to pass values!
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
Private Subroutines
•Normally, all subroutines are public and can be
called by other subroutines in any module.
•Only public subroutines appear in the Macros
dialog box
•You can create private subroutines that can be called
by procedures only within the module where they
are 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!
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
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”
Optional parameters example
Call ComputeTax( 1000)
Call ComputeTax( 1000, .33 )
Call ComputeTax ( 1000, .39, -15)
Call ComputeTax(1000, , -15)
“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
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(A1:A10)
Average(A1:A10)
Max(A1:A10)
Each performs a function, and returns a value
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(A1:A10) + 13*Cubed(C10)
User written functions are added to the function list…
(see Excel demo)
Function Example #1
Function Cubed(X As Single) As Single
Cubed = X * X * X
End Function
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
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
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”
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
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 >= 40000
SalesCommission = TotalSales * 0.14
End Select
End Function
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
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 ***
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
Arrays
An array is a collection of data
Think of a range of cells as an array of data
We’ve 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
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)
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 ….
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)
For Each
For Each is the last loop example
See Week10demo.xls
ForEachDemo2 and 3
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.
Homework #9, problem (a)
(same data as HW #8)
Shipment Weight And Cost Table
Weight
Cost
100
15
200
20
300
25
400
30
500
35
600
40
700
45
800
50
900
55
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.
Homework #9b
Subroutines practice:
Implement problem #1 on page 167
Homework #9c
Arrays and Function subroutines with parameters
Declare two “global” arrays, Weight() and Cost() to hold the values from
the HW#8 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 we’ve 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.
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
UserForms: Overview
The elements or “controls” of a dialog box
UserForm Editor
Dialog Box “ActiveX” Controls
Setting “Properties” of Controls
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)
“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
UserForm Excel Demo
and Lab Practice Exercise
Chapter 12, section 12.3.
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)
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
Product1Option = Product1CheckBox.Value
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
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.
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 <enter>
the extra spaces would be trimmed to convert
“ John “ to just “John”
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
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 InputValue=“YES” Then …
Better approach:
If UCase(InputValue) = “YES” Then ...
String Functions 4
Similarly, you can convert all characters to lower case:
InputValue = LCase(InputValue)
would convert “YES” to “yes”, or “Yes” to “yes”.
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”
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 )
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)
String Functions 8
InStr’s 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:
S=“The 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.
String Functions 9
Example:
S=“The 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.
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 = #17:04:23#
MyDate = #January 27, 1993#
Time
Example:
Dim S as String
S = Time
MsgBox S
displays 2:29:53 PM
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 “01:20:30 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.
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”.
Standard Formats
“General Number”Display 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
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
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", "<")
' Returns "hello".
MyStr = Format("This is it", ">")
' Returns "THIS IS IT".
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
Time Format Examples
MyStr = Format(MyTime, "h:m:s") ' Returns "17:4:23".
MyStr = Format(MyTime, "hh:mm:ss AMPM")
' Returns "05:04:23 PM".
S = Format(Time, "hh:mm:ss mm/dd/yy")
Surprisingly, the Time object contains both date and time
S is set to “11:20:35 11/29/01”
A Round() function
Excel’s 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
10^2 = 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
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
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.
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. int=long)
•Data faults (failure to initialize, off by one errors)
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:")
Here is what happens
When you click Okay without entering a number:
Solutions
#1:
#2:
Dim S As String, X as Single
S = InputBox(“Enter a number:”)
If Len(S) <> 0 Then
X=S
End if ‘ But what if S contains text?
‘ Better way
On Error Resume Next
X = InputBox(“Enter a number:”)
MsgBox “You entered: “ & X
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
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
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.
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
Error handling options in VBA
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 < instead of <=
Using <= when you really meant <, etc.
Computation faults
Your code implements the solution wrong
You used the wrong method (algorithm) to solve the problem
Omit or use parenthesis incorrectly:
3+5*8
when you wanted
(3+5)*8
Round 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 you’ve lost the precision of X
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
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:
First name
Bob
Lori
Tasha
Last name
Smith
Jones
Pavlov
City
SPOKANE
CHENEY
DEER PARK
Time
11:29:01
11:29:55
11:30:25
Date
Wednesday, Nov 14, 2001
Wedneseday, Nov 14, 2001
Wednesday, Nov 14, 2001