Hour 14

Built-in Functions Save Time

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

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.

Numeric Functions

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


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.

Figure 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.
Function Description
Abs() 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.
Atn() 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.
Cos() Returns the argument's cosine value, expressed in radians.
Exp() Returns the argument's natural logarithm base.
Len() Returns the number of memory characters required to hold the argument.
Log() Returns the argument's natural logarithm.
Sin() Returns the argument's sine value, expressed in radians.
Tan() 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.

String Functions

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.
Function Description
Chr(int) Returns the ASCII character that matches the numeric argument.
LCase(str) Returns the argument in all lowercase letters. If any character in the argument is already lowercase, no change takes place for that character.
Left(str, int) Returns the leftmost int characters from the string argument.
Len(str) 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.
LTrim(str) 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.
Right(str, int) Returns the rightmost int characters from the string argument.
RTrim(str) Returns the string argument, with any trailing spaces trimmed off.
Str() Converts its numeric argument to a string with the numeric digits in the string.
UCase(str) 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 string.

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 argument list.

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.
Function Description
Date 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.
DatePart(strIntrvl, dteDate) Returns the strIntrvl portion of the dtrDate.
Now Returns the current date and time in the date format.
Time Returns the current time.
Timer Returns the number of seconds since midnight.
TimeSerial(hour, 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 you need.

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 like this:

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.
Interval Description
h Hour
d Day
m Month
n Minute
q Quarter
s Second
y Day of year
w Weekday
ww Week
yyyy 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, dteUserDate).

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", dteUser1, dteUser2).

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.

Data-Testing Functions

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.
Function Description
IsDate() True if the argument can convert to a Date data type.
IsEmpty() True if the argument has even been initialized with any value since the argument's declaration. IsEmpty() works with variable arguments only, not controls.
IsNull() True if the argument holds Null (such as an empty string) and works for controls as well as variables.
IsNumeric() 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.
Return Named Literal Describes
0 vbEmpty Empty and not initialized argument.
1 vbNull Invalid data or a null string argument.
2 vbInteger Integer argument.
3 vbLong Long argument.
4 vbSingle Single argument.
5 vbDouble Double argument.
6 vbCurrency Currency argument.
7 vbDate Date argument.
8 vbString String argument.
9 vbObject Object argument.
10 vbError Error argument.
11 vbBoolean Boolean argument.
12 vbVariant Variant argument.
13 vbDataObject Data Access Object (DAO) argument. A Data Access Object is an advanced database value such as a field or record.
14 vbDecimal Decimal argument.
17 vbByte Byte argument.
8192+int vbArray 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.
Function Description
Asc() Converts its string argument to the ASCII number that matches the first (or only) character in the string.
CCur() Converts the argument to an equivalent Currency data type.
CDbl() Converts the argument to an equivalent Double data type.
CInt() Rounds its fractional argument to the next highest integer.
CLng() Converts the argument to an equivalent Long data type.
CSng() Converts the argument to an equivalent Single data type.
CStr() Converts the argument to an equivalent String data type.
CVar() Converts the argument to an equivalent Variant data type.
Fix() Truncates the fractional portion.
Int() Rounds the number down to the integer less than or equal to its arguments.
Hex() Converts its numeric argument to a hexadecimal (base-16) value.
Oct() Converts its numeric argument to an octal (base-8) value.

New Term: Hexadecimal is the base-16 number system.

New Term: Octal is the base-8 number system.

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.

Format Function

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 book uses U.S. settings.) Some countries use commas to indicate decimal places, whereas the United States uses the decimal point.

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 Format():

Format(Expression, strFormat)

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 book doesn't go into those.

Table 14.8. The strFormat values.
strFormat Description
"Currency" 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.
"Fixed" Displays at least one digit before and two digits following the decimal point, with no thousands separator.
"General Number" Displays the number with no thousands separator.
"Medium Time" Displays the time in 12-hour format and the a.m. or p.m. indicator.
"On/Off" Displays On if the value contains a nonzero or True value and displays Off if the value contains zero or a False value.
"Percent" Displays the number, multiplied by 100, and adds the percent sign to the right of the number.
"Scientific" Displays numbers in scientific notation.
"Short Time" Displays the time in 24-hour format.
"True/False" Displays True if the value contains a nonzero or True value, and displays False if the value contains zero or a False value.
"Yes/No" 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?

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 valid date.

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?

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 to you?

What is the value stored in each of the following assignment statements?

strA = Left("abcdefg", 3)

strB = Right("abcdefg", 3)

strC = Mid("abcdefg", 2, 3)

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?

intA = Int(20.34)

intB = CInt(20.34)

intC = Fix(-2.8)

intD = Int(-2.8)

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

What is a thousands separator?

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

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.

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.

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!