This lesson gives you a reference for many of the most common built-in functions.
You will learn about the numeric, string, time, date, and formatting functions that
Visual Basic supplies. By using the built-in functions Visual Basic gives you, you
won't have to spend a lot of time writing your own code for common routines. For
example, you never need to write code that extracts a square root because Visual
Basic supplies a built-in square root function for you.
As with all functions, the built-in functions return values and, optionally, accept
arguments that you pass to the functions when you call the functions. Some built-in
functions don't accept arguments; although the style is inconsistent, you don't specify
the parentheses after the built-in function names that do not accept arguments. Without
the parentheses, it is easy to confuse the function with a regular Visual Basic statement.
Fortunately, only a few don't take arguments.
The highlights of this hour include
- Which numeric functions exist
- How to use string functions to manipulate strings
- Which date and time functions let you modify date and time values
- How the data-testing functions inspect data
- When to convert data from one type to another with the data-conversion functions
- How to format your data to look the way you want it to look
Lots of Functions
Many built-in mathematical functions exist, including data conversion functions,
common math functions, trigonometric and logarithmic functions, and formatting functions.
The next few sections explain how to use many of the more common kinds of numeric
functions you might need to use in your own applications.
Visual Basic includes several numeric functions you can use to help calculate
expressions. To start with, you might as well learn the square root function described
in the previous section. Here is the format of Visual Basic's built-in square root
Remember that a function accepts one or more arguments and returns a value based
on the argument list. Figure 14.1 illustrates the Sqr() function. The function
accepts a single argument and returns the square root of that argument.
14.1. The Sqr() function returns the square of the argument
you pass to it.
Suppose you wanted to store the square root of a builder's measurement in a control
named txtSqrMeas. You could do so like this:
txtSqrMeas.Text = Sqr(sngMeas)
Table 14.1 lists several additional mathematic functions you can use.
Table 14.1. Common built-in numeric functions.
||Returns the argument's absolute value. The absolute value is the positive equivalent
of the argument, so the absolute value of both -87 and 87 is 87.
Use absolute values for distance calculations and weight differences because such
values must always be positive.
||Returns the argument's arc tangent, expressed in radians. To compute the arc tangent
in degrees (or any other trigonometric function), multiply the argument by pi (approximately
3.14159) and then divide by 180.
||Returns the argument's cosine value, expressed in radians.
||Returns the argument's natural logarithm base.
||Returns the number of memory characters required to hold the argument.
||Returns the argument's natural logarithm.
||Returns the argument's sine value, expressed in radians.
||Returns the argument's tangent value, expressed in radians.
New Term: Pi is a mathematical value
that approximates 3.14159 and is used in many area calculations.
NOTE: Don't need scientific
logarithmic or trigonometric functions? That's fine, but many financial calculations
use such functions, so these routines are not just for highly scientific calculations.
Visual Basic will keep them ready in case you do need them.
TIP: Search Visual
Basic's online help for Derived Math Functions for an exhaustive list of
built-in numeric functions Visual Basic offers. If you write a lot of scientific
and engineering applications, you'll be surprised at how well the language supports
advanced functions. From its early roots, the BASIC language and its predecessors
have offered surprisingly advanced functions for such a simplified language.
Unlike the numeric functions, Visual Basic's string functions return a string
and often work with one or more string arguments. Table 14.2 lists several of the
more common string functions you'll work with. The string functions accept controls
as well as variables and literals and expressions so you can manipulate controls
with the string functions.
NOTE: Table 14.2 includes
the argument format because some string functions require multiple arguments.
Table 14.2. Common built-in string functions.
||Returns the ASCII character that matches the numeric argument.
||Returns the argument in all lowercase letters. If any character in the argument is
already lowercase, no change takes place for that character.
||Returns the leftmost int characters from the string argument.
||Returns the number of characters in the string. (Notice that Len() works
on numeric arguments as well.) Also, Len() does not return a string even
though Len() works with string arguments.
||Returns the string argument, with any leading spaces trimmed off.
|Mid(str, intStart [, intLen])
||Returns a substring of the string argument, starting with the character at intStart
and continuing until the entire rest of the string is extracted or until the optional
intLen characters have been extracted. Mid() is called the midstring function
because it can return the middle portion of a string.
||Returns the rightmost int characters from the string argument.
||Returns the string argument, with any trailing spaces trimmed off.
||Converts its numeric argument to a string with the numeric digits in the string.
||Returns the argument in all uppercase letters. If any character in the argument is
already uppercase, no change takes place for that character.
New Term: A substring is part of a
Suppose you want to determine whether a string variable's value will fit inside
a text box before you attempt to assign the string to the Text Box control (assume
that the text box does not have AutoSize set to True). If the text
box is large enough to hold 20 characters, the following If statement fragment
will be True if the string fits in the text box:
If (Len(strVar) <= 20) Then ` String fits
Suppose you need to compare two password string values. Given that the user may
have entered the password in all uppercase or a case mixture, the following code
tests the stored password against one entered in a string variable, and the code
uses UCase() to ensure that they compare with the same case matches:
If UCase(strUser) = UCase(strPassword) Then
` This If leg is true if the passwords match
The LTrim() function is often useful for trimming the leading blank from
strings you make from numbers. For example, Str(123) returns the string
literal " 123" (notice the leading blank). Sometimes, when writing
certain kinds of files, you need to write strings of data instead of numbers and
Str() comes in handy. If, however, you need to strip off the leading blank,
you can embed Str() within LTrim() to return the string digits
without the leading blank, like this: LTrim(Str(123)).
Left() returns the left part of a string or control value that Visual
Basic converts to a string. Therefore, the following stores only the first five characters
from the string argument into strAns:
strAns = Left(txtUser.Text, 5)
Whereas Left() returns the left part, Right() returns the right
part of a string. Mid() can return the middle part of a string. Therefore,
the following expression becomes "der" when Mid() returns
the middle three letters: Mid("Federal", 3, 3). Due to the optional
third argument, Mid() works like the Right() function if you omit
the third argument because Mid() returns all characters from the starting
position to the end of the string if you don't put the third argument inside Mid()'s
A Mid Statement?
Visual Basic includes both a Mid() function and a Mid statement.
The difference is subtle, so you should understand how the Mid()s compare.
Mid is a statement if Mid appears on the left side of an assignment.
The Mid() statement replaces part of a string with another value. If the
string variable named strSentence holds "I flew home",
you can replace the verb flew with rode, like this: Mid(strSentence,
3, 4) = "rode". If you omit the third argument from this Mid
statement, Visual Basic will use as many characters as possible to fill the string.
In this example, the third argument does nothing but clarify the programmer's intent
because the replacement string is four characters long. If you were to specify a
third argument value less than 4, Mid() replaces fewer characters.
Date and Time Functions
Applications today need to be able to access and work with date and time values.
Many applications are written for business and scientific purposes, where recording
the date and time of the program run is vital to the success of the project. Visual
Basic includes the date and time functions described in Table 14.3.
WARNING: The date-
and time-returning functions Date, Now, and Time do not
require arguments, so they do not use parentheses.
Table 14.3. Date and time functions.
||Returns the current date.
|DateSerial(intYr, intMo, intDay)
||Returns an internal date value for the three arguments.
|DateAdd(strIntrvl, intN, dteDate)
||Adds the intN value to the date specified by dtrDate for the given strIntrvl.
|DateDiff(strIntrvl, dte1, dte2)
||Returns the number of time intervals (specified by strIntrvl) between the two dates.
||Returns the strIntrvl portion of the dtrDate.
||Returns the current date and time in the date format.
||Returns the current time.
||Returns the number of seconds since midnight.
|TimeSerial(Chapter, min, sec)
||Returns the current date and time in the internal date format for the time specified.
You may think that Table 14.3 is ambiguous in places, but Visual Basic gives you
many ways to manipulate and test date and time values. You'll probably use a limited
set, depending on your data needs. Most of the time your application simply needs
to know the current date or time to display the date or time on a form or report.
Assign Date, Time, or Now (for both) to return the current
date, time, or both.
NOTE: The returned
value is the internal Date data type format Visual Basic uses for variables
declared as Date data type variables. You can assign and work with dates
returned from Date, Time, and Now by assigning them to
and from variables declared as Date data type variables. When you print
the value, Visual Basic respects your computer's International Windows settings and
prints the date or time in your country's format. You can use the Format()
function described in this lesson's final section to format the date into a form
The serial date and time functions let you convert a three-part date into a date
that matches the internal Date data type so you can work with variables
that hold dates you specify. For example, if you want to store the value July 18,
1998, in a Date data type variable named dteDue, you can do so
dteDue = DateSerial(1998, 7, 18)
If the year falls within the 20th century, you can omit the 19 before
the year. If you ever try to store a value that does not correspond to a proper date
or time value, the IsDate() function (described in the next section) will
return False to let you know that a bad date or time appears in the Date
data type variable.
In a similar manner, TimeSerial() returns an internal Date data
type when you specify the three time parts, like this:
dteTimePaid = TimeSerial(14, 32, 25) ` Stores 2:32:25 P.M.
The time works on a 24-hour clock, so 14 represents 2:00 in the afternoon.
The Date data type holds dates, times, and date and time values, so your
Date variable will hold whatever date or time combination you send.
TIP: The DateSerial()
and TimeSerial() functions let you specify expressions inside their argument
lists to manipulate specific date and time portion values. For example, the expression
DateSerial(1998, 7, 18-31) returns the date 31 days before July 18, 1998.
Therefore, you don't have to worry about the number of days in a month or anything
else. Such calculations are useful for aging accounts receivable balances. Use an
expression inside TimeSerial() to eliminate worry with going past midnight,
as in this example: TimeSerial(14-20, 30, 16). Such an expression represents
20 hours before 2:30:16 p.m.
DateAdd(), DateDiff(), and DatePart() require a special
string interval value that comes from Table 14.4. The interval tells these date functions
how to change the date argument.
Table 14.4. Date and time interval string values.
||Day of year
Suppose the user entered a date value into a control or variable and you needed
to work with a date 30 days after that date to remind the user after 30 days that
a project is due. You can add 30 days to a date value without worrying about days
in each month or year changes (as would happen if the date fell in late December)
by specifying the following expression that adds 20 days to the date to return another
date 20 days in the future: DateAdd("d", 20, dteUserDate). You
can subtract 20 days using a negative interval. Suppose you want the date one year
from the user's date? Code this expression anywhere you need the future date in a
year and you don't have to worry about leap year: DateAdd("yyyy", 1,
The DateDiff() function uses Table 14.4's interval string value to return
the number of intervals between two dates. For example, the following expression
returns the number of weeks between two date values: dateDiff("ww",
Use Table 14.4's interval value and the DatePart() function to obtain
the integer number that represents the specified value. You can determine the day
of the week (assuming that the week starts with Sunday being 1) that you
were born by coding this expression: DatePart("d", dteUserBDay).
Visual Basic includes three additional functions that strip off the day, month,
and year values from a Date data type variable: Day(), Month(),
and Year(). If you want to work with the current year, you can strip off
the year from the current date like this:
intYear = Year(Date) ` Get this year
The Timer function is useful for determining the amount of time that
has passed between two time values. Timer requires no arguments or parentheses.
To use Timer, save the value of Timer in a variable and when you
are ready to know how much time has elapsed since the first time, you can compare
or save the current value of Timer again. Listing 14.1 demonstrates the
Timer function that tests your arithmetic speed.
Listing 14.1. Using Timer to time
the users math skills.
Dim lngBefore As Long
Dim lngAfter As Long
Dim lngTimeDiff As Long
Dim strAns As String
lngBefore = Timer ` Save seconds since midnight
strAns = InputBox("What is 150 + 235?", "Hurry")
Loop Until Val(strAns) = 385
lngAfter = Timer ` Save seconds since midnight now
` The difference between the stored time values
` is how many seconds the user took to answer
lngTimeDiff = lngAfter - lngBefore
MsgBox ("The took you only " & Str(lngTimeDiff) & _" seconds!")
The code uses the two saved Timer values to determine how long the user
took to answer. If the user does not answer correctly, the time keeps ticking. If
the user happens to run this right before midnight, the results will not be accurate
because of the day change.
The Is...() functions are called the data inspection functions. When
you store a value in a variable declared as a Variant data type variable,
the data inspection functions can test that variable to see what kind of data type
the variable can be. The data inspection functions are especially useful for working
with user entries in controls and variables.
New Term: Data inspection functions
are functions that inspect data and return information about the data type.
Table 14.5 describes the data inspection functions.
Table 14.5. Data inspection functions for testing data types.
||True if the argument can convert to a Date data type.
||True if the argument has even been initialized with any value since the
argument's declaration. IsEmpty() works with variable arguments only, not
||True if the argument holds Null (such as an empty string) and works
for controls as well as variables.
||True if the argument can convert to a Numeric data type.
Notice that Visual Basic supports no IsString() function. If you want
to test for a String value, you must use a different kind of function. If
you need more specific information about a data type, you can use the VarType()
function, which returns a value that indicates the exact data type an argument can
be. If you expect the user to enter an integer, for example, you can test with VarType()
to see if the argument is a valid integer. Use Table 14.6 to determine if the return
type is your expected data type.
Table 14.6. The VarType() return values.
||Empty and not initialized argument.
||Invalid data or a null string argument.
||Data Access Object (DAO) argument. A Data Access Object is an advanced database value
such as a field or record.
||Array argument of the type specified by the int addition to 8192.
If VarType(dataVal) returns a number greater than 8192, subtract
8192 from the return value to arrive at the data type (such as 12
for a Variant data type). A return value of 8194, therefore, represents
an integer array.
Data Conversion Functions
Once you determine what kind of value a Variant variable or a control
holds, you can convert that argument to its associated data type. The conversion
functions shown in Table 14.7 describe the conversions you can perform.
Table 14.7. The data conversion functions.
||Converts its string argument to the ASCII number that matches the first (or only)
character in the string.
||Converts the argument to an equivalent Currency data type.
||Converts the argument to an equivalent Double data type.
||Rounds its fractional argument to the next highest integer.
||Converts the argument to an equivalent Long data type.
||Converts the argument to an equivalent Single data type.
||Converts the argument to an equivalent String data type.
||Converts the argument to an equivalent Variant data type.
||Truncates the fractional portion.
||Rounds the number down to the integer less than or equal to its arguments.
||Converts its numeric argument to a hexadecimal (base-16) value.
||Converts its numeric argument to an octal (base-8) value.
New Term: Hexadecimal is the base-16
New Term: Octal is the base-8 number
Normally, the following assignment stores .1428571 in a label named lblValue:
lblValue.Caption = (1 / 7)
The following, however, adds precision to the label for a more accurate calculation
to assign .142857142857143 to the label:
lblValue.Caption = CDbl(1 / 7)
Use these conversion functions when you need the exact data type for more precision
in calculations or controls.
Visual Basic cannot read your mind, so it doesn't know how you want numbers displayed
in your applications. Although Visual Basic sometimes displays none, one, or two
decimal places for currency values, you'll almost always want those currency values
displayed to two decimal places with a dollar sign and commas when appropriate.
As with the date and time functions, if you've set your computer's international
settings to a country other than the United States, your formatted currency values
may differ from those shown here. (This guide uses U.S. settings.) Some countries
use commas to indicate decimal places, whereas the United States uses the decimal
Format() returns a Variant (convertible to a String)
data type formatted to look the way you need. Format() does not change a
value, but Format() changes the way a value looks. Here is the format of
Often, you'll assign the result of Format() to other variables and controls.
Generally, you'll perform all needed calculations on numeric values before formatting
those values. After you've performed the final calculations, you'll then format the
values to String (or Variant) data types and display the resulting
answers as needed.
Expression can be a variable, an expression, or a constant. strFormat must be
a value from Table 14.8. Visual Basic contains many format strings in addition to
the ones shown in Table 14.8. You can even develop your own programmer-defined format
strings, although this guide doesn't go into those.
Table 14.8. The strFormat values.
||Ensures that a dollar sign ($) appears before the formatted value, followed
by a thousands separator (a decimal point or comma for values over 999; your country
setting determines whether the thousands separator is a comma or a decimal). Two
decimal places will always show. Visual Basic displays negative values in parentheses.
||Displays at least one digit before and two digits following the decimal point, with
no thousands separator.
||Displays the number with no thousands separator.
||Displays the time in 12-hour format and the a.m. or p.m. indicator.
||Displays On if the value contains a nonzero or True value and displays
Off if the value contains zero or a False value.
||Displays the number, multiplied by 100, and adds the percent sign to the right of
||Displays numbers in scientific notation.
||Displays the time in 24-hour format.
||Displays True if the value contains a nonzero or True value, and
displays False if the value contains zero or a False value.
||Displays Yes if the value contains a nonzero or True value and
displays No if the value contains zero or a False value.
You'll Rarely Need Format Codes
If the predefined formats from Table 14.8 don't match the format you need, you
can define your own using special formatting codes. This lesson would be twice as
long as it is if all the programmer-defined formats were taught here. The good news
is that, when you do define your own formats, you'll almost always use just a combination
of the pound sign and zeros to format the values you need. Each pound sign in the
format indicates where a digit goes, and the zero indicates that you want either
leading or trailing zeros. The following assignment displays the value of Weight
to three decimal places: lblMeas.Caption = Format(Weight, "######.000")
You could also request that no decimal point should appear by formatting a fractional
value such as Weight, and Visual Basic will round the number as needed to
fit the target format. The following assignment displays Weight with no
decimal places shown on the screen: lblMeas.Caption = Format(Weight, "######")
Listing 14.2 contains a series of formatting function calls that convert numeric
and logical values to formatted Variant data types that you can display.
Listing 14.2. Formatting numeric
and logical values.
Dim FormValue (8) As String
` Change 12345.678 to $12,345.68
FormValue(1) = Format(12345.678, "Currency")
` Change 12345678 to 12345.68
FormValue(2) = Format(12345.678, "Fixed")
` Change .52 to 52.00%
FormValue(3) = Format(.52, "Percent")
` Change 1 to Yes
FormValue(4) = Format(1, "Yes/No")
` Change 0 to No
FormValue(5) = Format(0, "Yes/No")
` Change 1 to True
FormValue(6) = Format(1, "True/False")
` Change 0 to False
FormValue(7)= Format(0, "True/False")
TIP: If you use VB's
Professional or Enterprise Editions, you can add the Masked Edit ActiveX control,
which lets you specify an edit mask that formats data in a manner similar to Format().
New Term: An edit mask is a format string,
such as "#,###.##", that specifies how you want numeric and string
data to appear.
You now have many new tools for your programming utility belts because you now
have a good understanding ofVisual Basic's built-in functions. The functions calculate,
manipulate strings, work with time and date values, convert data, and format output
data. You don't have to add special controls to the toolbox to use the built-in functions
because Visual Basic's programming language supports all these functions automatically.
Now that you've mastered the functions, you can learn how to access large amounts
of data to work with. Hour 15, "Visual Basic Database Basics," describes
how to use the Data control so that your application can write and read data to and
from external database files.
- Q Why would I want to perform date arithmetic?
A Date arithmetic is useful for determining the exact date (or time) after another
period of time goes by. For example, suppose you need to know the exact day that
three months from today's date falls on. You cannot just add 3 or 90 (3 times 30
days) to a date value; not only can you not add to a Date data type, but
even if you could, such math does not take into account leap years, days in the months,
and year changes. By using DateAdd() and DatePart(), you can perform
such calculations with date values and be assured that the result will fall on a
Q If Timer returns the number of seconds since midnight, how can I use Timer to determine
how much time has passed for a given task?
A The key to using Timer is to save the value of Timer before
the task begins and then save the value of Timer after the task begins.
You then can subtract the values to determine how many seconds elapsed between the
two tasks. A single Timer reading would not be very beneficial by itself,
but the two before and after values can be very helpful indeed.
The quiz questions and exercises are provided for your further understanding.
See Appendix C, "Answers," for answers.
- 1. What preparation must you do before the built-in functions are available
2. What is the value stored in each of the following assignment statements?
a. strA = Left("abcdefg", 3)
b. strB = Right("abcdefg", 3)
c. strC = Mid("abcdefg", 2, 3)
d. strD = Mid("abcdefg", 2)
- 3. Is the following Mid() a function or a statement?
Mid(strTest, 2, 4) = "abcd"
- 4. Is the following Mid() a function or a statement?
strAns = Mid(strTest, 2, 4)
- 5. What is the value stored in each of the following assignment statements?
a. intA = Int(20.34)
b. intB = CInt(20.34)
c. intC = Fix(-2.8)
d. intD = Int(-2.8)
6. What value appears in varAns after the following assignment?
varAns = Val(LTrim(Str("10")))
- 7. What is the 24-hour time for 12:56 p.m.?
8. What is a thousands separator?
9. True or false: You must use Format() to properly format date and
time values because the built-in date and time functions cannot interpret your International
10. True or false: Now returns information for both the current date
and the current time.
- 1. Write a program that stores the 256 ASCII characters (from ASCII 0
to ASCII 255) in a string array that's defined to hold 256 characters.
2. Write a subroutine procedure that asks the user for the time that he clocked
into work and then for the time he clocked out. Display, in three labels, the total
number of seconds worked, the total number of minutes worked, and the total number
of hours worked.
3. Write a subroutine procedure that asks the user for his birthday. If the user
entered a valid date (check to make sure and keep asking if the user did not enter
a date), display a message box telling the user how many years until retirement age
of 65. If the user is older than 65, congratulate him on a long life!