Transcript Document 7808260

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