Hour 14


During this hour you will learn

Using the Supplied Functions

This lesson explores the many built-in functions that Visual Basic provides so that you have less to code. You'll learn all about the numeric, string, date, time, and formatting functions that make up Visual Basic's library.

Topic 1: Numeric Functions

Visual Basic includes many built-in numeric functions that convert and calculate with your data. Although some of the built-in functions are highly technical, many of them are used daily. Most of the built-in functions reduce your programming time. Rather than have to "reinvent the wheel" every time you need Visual Basic to perform a numeric or string operation, you can use one of the many built-in functions to do the job for you.

Overview

As with built-in functions and functions that you write, all the functions in this topic return a value of some kind, and most of them require arguments. Not all functions require arguments, such as the Now function, and those that don't shouldn't have the parentheses after their name when you call them.

The Integer Conversion Functions

The Int() and Fix() functions return whole numbers. Here are their syntaxes:

Int(numericValue)

Fix(numericValue)

Int() returns the integer value of the number you pass in the parentheses; Fix() returns the truncated whole number value of the argument. Truncation means that the fractional part of the argument is taken off the number.

Int() and Fix()return the numeric data type as their argument or Null if you pass them a Null argument.

If you pass to Int() or Fix() a non-numeric argument (such as strings that can't be converted to numbers by the functions), you get a Type mismatch error

The primary difference between Int() and Fix() appears when you pass them negative arguments.

Both of the following statements store 8 (the function's return value) in intAns:

intAns = Fix(8.93)

intAns = Int(8.93)

The following line of code stores -8 in intAns:

intAns = Int(-7.6)

Int() returns the highest integer that's less than or equal to the argument in parentheses.

For negative numbers, Fix() and Int() return very different return values. Fix() simply drops the fractional part of the number-whether it's positive or negative-from the argument.

intAns1 = Fix(-8.93)

intAns2 = Fix(-8.02)

Int() doesn't truncate but returns the closest integer less than or equal to the argument.

The Data Type Conversion Functions

Table 14.1 describes the data type conversion functions, denoted by their initial letter C (for convert). Each function converts its argument from one data type to another.

Table 14.1 The Data Type Conversion Functions

Function Name

Description

CBool()

Converts its argument to the Boolean data type

CByte()

Converts its argument to the Byte data type

CCur()

Converts its argument to the Currency data type

CDate()

Converts its argument to the Date data type

CDbl()

Converts its argument to the Double data type

CDec()

Converts its argument to the Decimal data type

CInt()

Converts its argument to the Integer data type

CLng()

Converts its argument to the Long data type

CSng()

Converts its argument to the Single data type

CStr()

Converts its argument to the String data type

CVar()

Converts its argument to the Variant data type

You must be able to convert the argument to the target data type. You can't convert the number 123456789 to Integer with CInt(), for example, because an Integer data type can't hold an integer that large

Unlike Int() and Fix(), CInt() returns the closest rounded integer to the argument. Look at the remarks to the right of each of the following statements to see what's stored in each variable:

cA1 = CInt(8.5)       ' Stores an 8 in cA2

cA2 = CInt(8.5001)    ' Stores a 9 in cA3



For negative numbers, CInt() also rounds to the closest whole integer.

The following code declares a variable of four different data types and then converts each argument to those data types. Remember that you also can pass these functions expressions that produce numeric results so that you can control the data types of your calculated results before storing them in a field or variable.

vVar1 = CCur(123)      ' Converts 123 to currency data type

vVar2 = CDbl(123)      ' Converts 123 to double-precision data type

vVar3 = CSng(123)      ' Converts 123 to single-precision data type

vVar4 = CVar(123)      ' Converts 123 to the variant data type

The Absolute Value Function

Abs() often works with the sign of a number instead of the value itself. Abs() returns the absolute value of its argument.

Absolute value is the positive value of the argument. The absolute value of 10 is 10, and the absolute value of -10 is also 10. You may find some good uses for absolute value, such as distance and age/weight differences (the distance between two cities is always positive).

Suppose that you want to know how many years' difference in two employees' ages. The following statement stores the difference in ages between intEmp1Age and intEmp2Age:

intAgeDiff = Abs(intEmp1Age - intEmp2Age)

The Math Functions

You don't have to be an expert in math to use many of the mathematical functions that come with Visual Basic. Often, even in business applications, the following functions come in handy:

Sqr()'s argument can be any positive numeric data type. (Square root isn't defined for negative numbers.) The code

intVar1 = Sqr(4)

intVar2 = Sqr(64)

intVar3 = Sqr(4096)

stores 2, 8, and 64 in the three respective variables.

The Randomizing Tools

Two randomizing tools are available in Visual Basic. You never use the Randomize statement by itself. If a module includes Randomize, you find the Rnd() function later in the procedure. Here is the format of the Randomize statement:

Randomize [number]

The format of the Rnd() function is as follows:

Rnd[(number)]

Notice that neither the Randomize statement nor the Rnd() function requires values after them (as the square brackets indicate). The purpose of Rnd() is to generate a random number between 0 (inclusive) and 1 (1 is never generated by Rnd()). You can use the random number function for games, such as simulating dice rolls or card draws.

If you run a procedure with four Rnd() numbers printed, each number is different (random) if you include no number argument. If, however, you specify 0 as the argument to Rnd(), such as x = Rnd(0), the four numbers are the same number. If you specify a number greater than 0 as the argument to Rnd(), a different random number is produced (the same as leaving off the argument altogether). An argument less than zero, such as x = Rnd(-3), always produces the same random number if you specify the same negative argument, and a different random number if the negative argument is always different.

If you want a different random number generated with Rnd() every time it executes, don't specify an argument. If you want the same set of random numbers produced from a series of Rnd() function calls (such as a scientific simulation that you want to repeat several times), specify the same negative argument.

Rnd() always produces the same set of values between procedure runs unless you provide a Randomize statement before the first Rnd(). In other words, if you run a procedure 20 times, the same set of random numbers results no matter what argument (or lack of argument) you use with the Rnd() function calls. The Randomize statement reseeds the random-number generator, a fancy term meaning that Randomize ensures that Rnd() produces a new set of random values each time you run a program.

If you don't specify a number after Randomize, the value of Timer is used. (Timer returns the number of seconds since midnight.) Because the value of Timer changes every second, the Randomize statement ensures that a new seed value is used for the next occurrence of Rnd().

If you want truly random values every time Rnd() appears-even between program runs-put the Randomize statement (with no arguments) at the top of the first procedure in the module and leave off all arguments to Rnd() in subsequent Rnd() calls

The following procedure always produces the same three random numbers every time you run it (assuming that no Randomize statement has executed yet within the module):

Private Sub randIt()

   Dim b As Integer     ' For button clicked

   b = MsgBox(Rnd)      ' A random number

   b = MsgBox(Rnd(0))   ' The same random number

   b = MsgBox(Rnd(0))   ' The same random number

End Sub

If you put a Randomize statement anywhere in the module so that it executes before the three Rnd() statements shown in the preceding example, you still get the same three random numbers due to Rnd()'s zero argument.

If you want three different random numbers always generated (as you usually do), don't specify an argument after Rnd().

You rarely want a random number from 0 to 1, as produced by Rnd(). By using the following simple formula, however, you can generate a random number from low to high (assuming those two variables have been initialized):

Int((high - low + 1) * Rnd + low)

Suppose that you want to offer a special discount to a different customer each month. If your customer numbers fall between 1000 and 6456 (meaning that you have a total of 5,457 customers), you can do something like the following:

intLow = 1000     ' Lowest customer number

intHigh = 6456    ' Highest customer number

intSpecialCustNum = Int((intHigh - intLow + 1) * Rnd + intLow)

Example

The following procedure uses the Abs() function to compute and display the difference between two ages. The ages come from a calling procedure.

Private Sub (intAge1 As Integer, intAge2 As Integer)

  ' Computes and displays the age difference

  lblAdeDiff = Abs(intAge1 - intAge2)

End Sub

Without Abs(), the procedure would print a negative age difference if the first age passed happened to be smaller than the second.

Next Step

You can even write games with Visual Basic! With the procedure in Listing 14.1, you actually can use Visual Basic to write a number-guessing game. The computer generates a random number from 1 to 100, and then uses control statements to offer users hints until they correctly guess the number.

Listing 14.1 Guess1.bas: Guessing Game

Private Sub Guessing ()

' Number-guessing game

  Dim intRNum As Integer

  Dim intUserNum As Integer

  Dim strTitle As String

  Randomize    ' Ensure that the number is always different

  intRNum = Int(100 * Rnd + 1)  ' Generates a number from 1 to 100

  intUserNum = InputBox("Welcome to a game! Guess a number...", strTitle)

  ' Keep looping until the user guesses correctly

  Do While (intUserNum <> intRNum)

    If (intUserNum < intRNum) Then

      intUserNum = InputBox("Too low, try again...", strTitle)

    Else

      intUserNum = InputBox("Too high, try again...", strTitle)

    End If

  Loop

  Beep

  intRNum = MsgBox("Congratulations! You guessed my number!", vbInformation)

End Sub

Topic 2: String Functions

In the preceding topic section, you learned about the numeric functions. In this topic section, you learn about the built-in functions that work with strings. Some functions described in the following sections convert between strings and numbers.

Overview

Visual Basic provides better string-handling capabilities than most programming languages. Your data is often composed of string data. You may need to test various string values and change string values, and you'll find those tasks easy after you learn the string functions.

Some of the items taught in this topic are statements, not functions. However, these statements work hand in hand with string functions, and this topic section is the most logical place to group this material together.

The Len() Length Function

Len() is one of the few functions that can take either a numeric variable or a string for its argument. Len() returns the number of memory bytes needed to hold its argument. Here's the syntax of Len():

Len(Expression)

Len() accepts any string value (variable, literal, or expression). However, only numeric variables, not numeric literals or expressions, work as Len() arguments

If you're getting ready to store 200 single-precision variables and want to see how much memory the data takes, you can code the following:

Dim sngTestIt As Single

sngTestIt = 0     ' A sample single-precision variable

intStorage = (Len(sngTestIt) * 200.0)

This code stores the amount of memory needed to hold 200 single-precision values.

Len() also returns the length (number of characters) of the string variable, string constant, or string expression inside its parentheses. The following MsgBox() function displays a 6 as its output:

b = MsgBox(Len("abcdef"))

If the string contains Null, Len() returns a value of 0. Testing for a null string lets you test to see whether a user entered data in response to an InputBox() function or a control value.


The String Conversion Functions

Several conversion functions work with string data. Table 14.2 describes each string-conversion function used in the following examples.

Table 14.2 The String-Conversion Functions

Function Name

Description

CStr()

Changes its argument to a string

Str()

Converts its numeric argument to a string (actually, to a Variant data type that you can use as a string)

Val()

Converts its string argument to a number, assuming that you pass Val() a string-like number

Although they're easier to use, neither CStr() nor Str() are as powerful as Format(), which you learn about in the next topic section

CStr() and Str() convert their arguments to string values. The only difference is that CStr() doesn't add a leading blank before positive numbers converted to strings; Str() does. The following code displays the difference between CStr() and Str():

Private Sub convStr ()

  Dim s1 As String, s2 As String

  Dim b As Integer   ' For button clicked

  s1 = CStr(12345)

  s2 = Str(12345)

  b = MsgBox("***" & s1 & "***")

  b = MsgBox("***" & s2 & "***")

End Sub

Figure 14.1 shows the first message box displayed; Figure 14.2 shows the second. Notice that no blank appears before the first string because the CStr() function was used, not Str().

FIG. 14.1

This string was created with CStr().

FIG. 14.2

A blank appears before the number.

The ASCII Functions

Appendix B, "The ASCII Table," lists the ASCII characters used with the ASCII functions. You use the Chr()and Asc() functions to convert strings to and from their numeric ASCII values. The ASCII table lists every possible character available on the PC and assigns a sequential number (an ASCII code) to each character.

By putting a number inside the Chr() parentheses, you can produce the character that corresponds to that number in the ASCII table. By using Chr(), you can generate characters for variables and controls that don't appear on your computer's keyboard but that do appear in the ASCII table.

The Asc() function is a mirror image of Chr(). Whereas Chr() takes a numeric argument and returns a string character, Asc() requires a string argument and converts that argument to its corresponding ASCII table number.

Therefore, an A is stored in aVar in the following assignment statement because the ASCII value of A is 65:

aVar = Chr(65)    ' Stores an A in aVar

Of course, it makes more sense to store an A directly in the aVar variable in the preceding example's statement. What, however, if you want to ask a Spanish question inside a message box? Spanish questions always begin with an upside-down question mark, and no upside-down question mark appears on your keyboard. Therefore, you can resort to using Chr() as follows:

' Chr(241) produces an n with a tilde over it

strMyQuest = Chr(191) & "Se" & Chr(241) & "or, como esta?"

b = MsgBox(strMyQuest)

Figure 14.3 shows the message box displayed from this code.

FIG. 14.3

Use ASCII characters to display characters not on the keyboard.

Asc() returns the ASCII number of the character argument you give it. The argument must be a string of one or more characters. If you pass Asc() a string of more than one character, it returns the ASCII number of the first character in the string. For example, the statement

b = MsgBox(Asc("A") & " " & Asc("B") & " " & Asc("C"))

produces the following output in a message box:

65 66 67

Look at the ASCII table in Appendix B to see that these three numbers are the ASCII values for A, B, and C.

Now look at the following example:

strAns = InputBox("Do you want to see the name")

If ((Asc(strAns) = 89) Or (Asc(strAns) = 121)) Then

   b = MsgBox("The name is " + aName)

End If

The user can answer the prompt with y, Y, Yes, or YES. The If...Then test works for any of those input values because 89 is the ASCII value for Y, and 121 is the ASCII value of y. Asc() returns the ASCII value of its string argument's first letter.

The Substring Functions

The substring functions return parts of strings. Right() returns characters from the right side of a string. Right()'s cousin, Left(), returns characters from the left side of a string. Mid() takes up where Right() and Left() fail-Mid() lets you pick characters from the middle of a string.

Here are the syntaxes of the substring functions:

Left(stringValue, numericValue)

Right(stringValue, numericValue)

Mid(stringValue, startPosition[, length])

The following section of code demonstrates Left():

strA = "abcdefg"

partSt1 = Left(strA, 1)   ' Stores a

partSt2 = Left(strA, 3)   ' Stores abc

partSt3 = Left(strA, 20)  ' Stores abcdefg

If you try to return more characters from the left of the string than exist, Left() returns the entire string

Right() works in the same manner as Left(), except that it returns the rightmost characters from a string:

strA = "abcdefg"

partSt1 = Right(strA, 1)   ' Stores g

partSt2 = Right(strA, 3)   ' Stores efg

partSt3 = Right(strA, 20)  ' Stores abcdefg

Mid() accomplishes what Left() and Right() can't-it returns characters from the middle of a string. Mid() uses three arguments: a string followed by two integers. The first integer determines where Mid() begins stripping characters from the string (the position, starting at 1), and the second integer determines how many characters from that position to return. If you don't specify two integers, Mid() uses 1 as the starting position.

Mid() can pull any number of characters from anywhere in the string. The following example shows how the Mid() function works:

strA = "Visual Basic FORTRAN COBOL C Pascal"

lang1 = Mid(strA, 1, 12)  ' Stores Visual Basic

lang2 = Mid(strA, 14, 7)  ' Stores FORTRAN

lang3 = Mid(strA, 22, 5)  ' Stores COBOL

lang4 = Mid(strA, 28, 1)  ' Stores C

lang5 = Mid(strA, 30, 6)  ' Stores Pascal

If you don't specify the length argument, VB returns all the characters to the right of the starting position. If the length is longer than the rest of the string, VB ignores the length argument.

The Mid() Statement


Mid() is a command and a function. It works as a command when it appears on the left side of an assignment statement's equal sign; it's a function when it appears anywhere else. Following is its syntax:

Mid(string, start[, length])

When you use the Mid() statement, Mid() changes the contents of the string used inside the statement's parentheses. The following code initializes a string with three words and then changes the middle word with Mid():

strSentence = "Rain in Spain"

' Change the middle word

Mid(strSentence, 6, 2) = "on"

' After the change

b = MsgBox("After change: " & sentence)  ' Prints Rain on Spain

Converting to Uppercase and Lowercase

The UCase() function returns its string argument in all uppercase letters. LCase() returns its string argument in all lowercase letters. The following MsgBox() function displays VISUAL BASIC:

b = MsgBox(UCase("Visual Basic"))

Justifying and Trimming Strings

LTrim() and RTrim() trim spaces from the beginning or end of a string. LTrim() returns the argument's string without any leading spaces. RTrim() returns the argument's string without any trailing spaces. The Trim() function trims leading and trailing spaces.

Here are the syntaxes of the string-trimming functions:

LTrim(stringExpression)

RTrim(stringExpression)

Trim(stringExpression)

Example

The following ReverseIt() function includes several of the string functions described in this topic. This function, shown in Listing 14.2, reverses a certain number of characters within a string.

Listing 14.2 Reverse.bas: A Function That Reverses a String

Public Function ReverseIt (s As String, ByVal n As Integer) As String

' Accepts: a string and an integer indicating the number of

'          characters to reverse

' Purpose: reverses the specified number of characters in the

'          specified string

' Returns: the modified string

' Reverses the first n characters in s.

    Dim strTemp As String, intI As Integer

    If n > Len(s) Then n = Len(s)

    For intI = n To 1 Step -1

        strTemp = strTemp + Mid(s, intI, 1)

    Next intI

    ReverseIt = strTemp + Right(s, Len(s) - n)

End Function

Suppose that the ReverseIt() function were called with the following statement:

newStr = ReverseIt ("Visual Basic", 6)

If all goes well, the string named newStr will hold the characters lausiV Basic (the first 6 characters are reversed).

Here is how the function works. The first statement, Dim, declares two local variables, the first of which, a string variable named strTemp, holds the reversed string as it's being built. The second variable, intI, is used in the For loop.

The If statement ensures that the integer passed to ReverseIt() isn't larger than the length of the string passed. It's impossible to reverse more characters than exist in the string. If more characters are passed, the If statement ensures that the entire string is reversed by changing the length to reverse to the exact length of the string via the Len() function.

The For loop then counts down, from the position to reverse (stored in n) to 1. By using the Mid() function, Visual Basic concatenates one character from the string, at position n, to the new string being built. As n reaches 1, the reversed characters are sent to the new string. After all the characters that need to be reversed are reversed, the rightmost portion of the passed string is concatenated as is to the reversed characters.

Next Step

The following statements trim spaces from the beginning, end, or both sides of strings:

st1 = LTrim("     Hello")  ' Stores Hello

st2 = RTrim("Hello     ")  ' Stores Hello

st3 = Trim("   Hello   ")  ' Stores Hello

Without the trimming functions, the spaces are copied into the target variables as well as the word Hello.

Earlier, you learned how to use Str() to convert a number to a string. Because Str() always converts positive numbers to strings with a leading blank (where the imaginary plus sign appears), you can combine LTrim() with Str() to eliminate the leading blank. The first of the following two statements stores the leading blank in st1. The second uses LTrim() to get rid of the blank before storing the string into st2.

st1 = Str(234)           ' Stores " 234"

st2 = LTrim(Str (234))   ' Stores "234"

Topic 3: Special Functions

The time and date functions are critical when processing data, especially in a database environment. It might be important to record exactly when a field was edited for security or verification purposes. Also, all printed reports should have the date and time (often called date- and time-stamping) printed on the report, showing exactly when the report was produced. In a stack of like reports, the date and time stamps show when the latest report was printed.

In addition to the time and date functions, Visual Basic supports a special data-formatting function that you can use to display formatted strings.

Overview

The date and time functions help you add such values to your code. The Format() function isn't just for dates or times, but after you learn the date and time functions, you'll know about all the data types available with Format().

Retrieving the Date and Time

Inside most computers are a clock and calendar that Visual Basic programs can read. You may have used the Date and Time functions in your forms. These functions don't require arguments and don't use parentheses, so they don't always look like functions.

Your Windows settings determine the format of the Date and Time return values. For example, on many systems Date will return the system date in the Variant data type in the following format:

mm-dd-yyyy

where mm is a month number (from 01 to 12), dd is a day number (from 01 to 31), and yyyy is a year number (from 1980 to 2099).

Time returns the system time in the Variant data type in the following format:

hh:mm:ss

where hh is the hour (from 00 to 23), mm is the minute (from 00 to 59), and ss is the second (from 00 to 59).

Date uses a 24-hour clock. Therefore, all hours before 1:00:00 in the afternoon equate to a.m. time values, and all times from 1:00:00 until midnight have 12 added to them so 14:30 is 2:30 in the afternoon.

Now combines the Date and Time functions. Now returns a Variant data type in the following syntax (if you were to print the Variant return value of Now in a message box, you'd see this syntax):

mm/dd/yy hh:mm:ss AM|PM

where the placeholder letters correspond to those of the Date and Time functions, with the exception that a 12-hour clock is used and either AM or PM appears next to the time. The vertical line in the syntax indicates that AM or PM appears-not both.

The most important thing to remember about all three date and time retrieval functions is that they return date and time values that are stored internally as double-precision values (with enough precision to ensure that the date and time values are stored accurately). The best way to format date and time values is to use Format(), which you learn about later in this topic discussion.

Assuming that it's exactly 9:45 in the morning, the statement

currentTime = Time

stores 9:45:00 in the variable currentTime. If the date is 2/23/99, the statement

currentDate = Date

stores 2/23/99 in the variable currentDate. The statement

currentDateTime = Now

stores 2/23/99 9:45:00 AM in the variable currentDateTime.

Setting the Date and Time

By using the Date and Time statements, you can set the current date and time from within Visual Basic. After you set your computer's date and time, the date and time remain in effect until you change them again. The placement of Date and Time in an expression indicates whether you want to use the functions or statements.

Here are the formats of the Date and Time statements:

Date = dateExpression

Time = timeExpression

The Date Expression Format

You must enter the dateExpression with pound signs surrounding the date, as follows:

Date = #11/21/1993#

Because there are several date formats, just about any way you're used to specifying the date is recognized by Visual Basic.

Date can recognize the following formats:

mm-dd-yy

mm-dd-yyyy

mm/dd/yy

mm/dd/yyyy

monthName dd, yyyy

mmm dd, yyyy (where mmm is an abbreviated month name, as in Dec)


dd monthName yy

dd-mmm-yy (where mmm is an abbreviated month name, as in Dec)

The following code tells users the currently set date and lets users enter a new date. If users press Enter without entering a date, the previous date is kept.

Dim newDate As Variant

b = MsgBox("The current date is " & Date)    ' Calls function

newDate = InputBox("What do you want to set the date to?")

If IsDate(newDate) Then

   Date = newDate

End If   ' Don't do anything if a good date isn't entered

b = MsgBox("The date is now " & Date)

The Time Expression Format

Here are some of the ways you can express the time:

hh

hh:mm

hh:mm:ss

You must use a 24-hour clock with Time.

Computing the Time Between Events

The Timer function returns the number of seconds since your computer's internal clock struck midnight. The syntax of Timer is simple:

Timer

As you can see, Timer is another function that uses no parentheses.

Timer is perfect for timing an event. For example, you can ask users a question and determine how long it took them to answer. First, save the value of Timer before you ask users; then subtract that value from the value of Timer after they answer. The difference of the two Timer values is the number of seconds users took to answer. Listing 14.3 shows a procedure that does just that.

Listing 14.3 Comptime.bas: Time the Users' Response

Public Sub CompTime ()

' Procedure that times the user's response

  Dim b As Integer   ' MsgBox() return

  Dim Before, After, timeDiff As Variant

  Dim mathAns As Integer

  Before = Timer    ' Save the time before asking

  mathAns = Inputbox("What is 150 + 235?")

  After = Timer     ' Save the time after answering

  ' The difference between the time values

  ' is how many seconds the user took to answer

  timeDiff = After - Before

  b = MsgBox("That took you only" + Str(timeDiff) & " seconds!")

End Sub

More Date Arithmetic

Timer finds the number of seconds between time values, but only for those time values that fall on the same day. DateAdd(), DateDiff(), and DatePart() take up where Timer leaves off. Table 14.3 lists these three date arithmetic functions and their descriptions.

Table 14.3 The Date Arithmetic Functions

Function Name

Description

DateAdd()

Returns a new date after you add a value to a date

DateDiff()

Returns the difference between two dates

DatePart()

Returns part (an element) from a given date

All three date arithmetic functions can work with the parts of dates listed in Table 14.4 Table 14.4 contains the parts of dates these functions work with, as well as their interval values that label each part. You use the interval values inside the date arithmetic functions to get to a piece of a date or time.

Table 14.4 The Time Period Interval Values

Interval Value

Time Period

yyyy

Year

q

Quarter

m

Month

y

Day of year

d

Day

w

Weekday (Sunday is 1, Monday is 2, and so on for Day(), Month(), Year(), and DateDiff())

ww

Week

h

Hour

n

Minute (careful-not m)

s

Second

The DateAdd() Function

Despite its name, DateAdd() works with both dates and times (as do all the date functions) because the date passed to DateAdd() must appear in a Date data type format. Here's the syntax of DateAdd():

DateAdd(interval, number, oldDate)

The interval must be a value (in string form) from Table 14.4. The interval you specify determines what time period is added or subtracted (a second value, minute value, or whatever). The number value specifies how many of the interval values you want to add. Make interval positive if you want to add to a date; make interval negative if you want to subtract from a date. The oldDate is the date or time from which you want to work (the date or time you're adding to or subtracting from). The oldDate doesn't change. The DateAdd() function then returns the new date.

Suppose that you buy something today with a credit card that has a 25-day grace period. The following statement adds 25 days to today's date and stores the result in intStarts:

intStarts = DateAdd("y", 25, Now)

The date stored in intStarts is the date 25 days from today.

You can use either "y", "d", or "w" for the interval if you're adding days to a date

Suppose that you work for a company that requires 10 years before you're vested in the retirement program. The following statement adds 10 years to your start date and stores the vested date in vested:

vested = DateAdd("yyyy", 10, hired)

Notice that the interval string value determines what's added to the date.

For any of the date arithmetic functions, if you don't specify a year, the current year (the year set on the system's clock) is returned

The DateDiff() Function

The DateDiff() function returns the difference between two dates. Embed DateDiff() inside Abs() if you want to ensure a positive value. The difference is expressed in the interval that you specify. Here's the syntax of DateDiff():

DateDiff(interval, date1, date2)

The following statement determines how many years an employee has worked for a company:

beenWith = Abs(DateDiff("yyyy", hireDate, Now))

The DatePart() Function

DatePart() returns a part of a date (the part specified by the interval). With DatePart(), you can find what day, month, week, or hour (or whatever other interval you specify) that a date falls on. Here's the syntax of DatePart():

DatePart(interval, date)

The following statement stores the day number that an employee started working:

DatePart("w", hireDate)

Working with Serial Date and Time Values

Although you may not know about serial values, the date and time functions you've been reading about work with serial values. A serial value is the internal representation of a date or time, stored in a VarType 7 (the Date data type) or a Variant data type. These values actually are stored as double-precision values to ensure the full storage of date and time and that accurate date arithmetic can be performed.

Here's the syntax of the DateSerial() function:

DateSerial(year, month, day)

year is an integer year number (either 00 to 99 for 1900 to 1999, or a four-digit year number) or expression; month is an integer month number (1 to 12) or expression; and day is an integer day number (1 to 31) or expression. If you include an expression for any of the integer arguments, you specify the number of years, months, or days from or since a value. To clarify the serial argument expressions, you use the following two DateSerial() function calls, which return the same value:

d = DateSerial(1998, 10, 6)

and

d = DateSerial(1988+10, 12-2, 1+5)

The DateSerial() functions ensure that your date arguments don't go out of bounds. For example, 1996 was a leap year, so February 1996 had 29 days. However, the following DateSerial() function call appears to produce an invalid date because February, even in leap years, can't have 30 days:

d = DateSerial(1996, 2, 29+1)

Nothing is wrong with this function call because DateSerial() adjusts the date evaluated so that d holds March 1, 1996, one day following the last day of February.

The DateValue() function is similar to DateSerial(), except that DateValue() accepts a string argument, as the following syntax shows:

DateValue(stringDateExpression)

stringDateExpression must be a string that VB recognizes as a date (such as those for the Date statement described earlier in this topic section). If you ask the user to enter a date a value at a time (asking for the year, then the month, and then the day), you can use DateValue() to convert those values to an internal serial date. If you ask the user to enter a full date (that you capture into a string variable) such as October 19, 1999, DateValue() converts that string to the serial format needed for dates.

The TimeSerial() and TimeValue() functions work the same as their date counterparts. If you have three individual values for a time of day, TimeSerial() converts those values to an internal time format (the Variant or VarType 7). Here's the syntax of TimeSerial():

TimeSerial(hour, minute, second)

TimeSerial() accepts expressions for any of its arguments and adjusts those expressions as needed, just as DateSerial() does.

If you have a string with a time value (maybe the user entered the time), TimeValue() converts that string to a time value with this syntax:

TimeValue(stringTimeExpression)

Day(), Month(), and Year() each convert their date arguments (of Variant or VarType 7 data type) to a day number, month number, or year number. These three functions are simple:

Day(dateArgument)

Month(dateArgument)

Year(dateArgument)

Also, Weekday() returns the number of the day of the week (refer to Table 14.4) for the date argument passed to it.

Pass today's date (found with Now) to Day(), Month(), and Year() as shown here:

d = Day(Now)

m = Month(Now)

y = Year(Now)

The current date's day of week number (refer to Table 14.4), month number, and year are stored in the three variables.

The Format() Function

One of the most powerful and complex functions, Format(), returns its argument in a different format from how the argument was passed. Here's the syntax of Format():

Format(expression, format)

Format() returns a Variant data type that you'll almost always use as a string. The expression can be any numeric or string expression. You can format all kinds of data-numbers, strings, dates, and times-to look differently. For example, you might want to print check amounts with commas and a dollar sign.

The format is a string variable or expression that contains one or more of the display-format characters shown in Tables 14.5 through 14.7. The table that you use depends on the kind of data (string, numeric, or date) that you want to format. The tables are long, but after looking at a few examples, you'll learn how to use the display-format characters.

Table 14.5 The String Display-Format Characters

Symbol

Description

@

A character appears in the output at the @ position. If there's no character at the @'s position in the string, a blank appears. The @ fills (if there are more than one) from right to left.

&

This character is just like @, except that nothing appears if no character at the &'s position appears in the string being printed.

!

The exclamation point forces all placeholder characters (the @ and &) to fill from left to right.

<

Less-than forces all characters to lowercase.

>

Greater-than forces all characters to uppercase.

Table 14.6 The Numeric Display-Format Characters

Symbol

Description

Null string, ""

This string displays the number without formatting.

0

A digit appears in the output at the 0 position if a digit appears in the number being formatted. If no digit is at the 0's position, a 0 appears. If not as many zeros in the number are being formatted as there are zeros in the format field, leading or trailing zeros print. If the number contains more numeric positions, the 0 forces all digits to the right of the decimal point to round to the display-format's pattern and all digits to the left print as is. You mostly use this display-format character to print leading or trailing zeros when you want them.

#

The pound-sign character works like 0, except that nothing appears if the number being formatted doesn't have as many digits as the display-format has #s.

.

The period specifies how many digits (by its placement within 0 or #s) are to appear to the left and right of a decimal point.

%

The number being formatted is multiplied by 100, and the percent sign (%) is printed at its position inside the display-format string.

,

If a comma appears among 0s or #s, the thousands are easier to read because the comma groups every three places in the number (unless the number is below 1,000). If you put two commas together, you request that the number be divided by 1,000 (to scale down the number).

E-, E+, e-, e+

The number is formatted into scientific notation if the format also contains at least one 0 or #.

:

The colon causes colons to appear between a time's hour, minute, and second values.

/

The slash ensures that slashes are printed between a date's day, month, and year values.

-, +, $, space

All these characters appear as is in their position within the formatted string.

\

Whatever character follows the backslash appears at its position in the formatted string.

Table 14.7 The Date Display-Format Characters

Symbol

Description

c

Displays either the date (just like the ddddd symbol if only a date appears), the time (just like ttttt if only a time appears), or both if both values are present.

d

Displays the day number from 1 to 31.

dd

Displays the day number with a leading zero from 01 to 31.

ddd

Displays an abbreviated three-character day from Sun to Sat.

dddd

Displays the full day name from Sunday to Saturday.

ddddd

Displays the date (month, day, year) according to your settings in the International section of your Control Panel's Short Date format (usually m/d/yy).

dddddd

Displays the date (month, day, year) according to your settings in the International section of your Control Panel's Long Date format (usually mmmm dd, yyyy).

w, ww

See Table 14.4.

m

Displays the month number from 1 to 12. The m also means minute if it follows an h or hh.

mm

Displays the month number with a leading zero from 01 to 12. The mm also means minute if it follows an h or hh.

mmm

Displays the abbreviated month name from Jan to Dec.

mmmm

Displays the full month name from January to December.

q

Displays the quarter of the year.

y

Displays the year's day number from 1 to 366.

yy

Displays the two-digit year from 00 to 99 (when the year 2000 hits, yy still returns only the 2-digit year).

yyyy

Displays the full year number from 1000 to 9999.

h, n, s

See Table 14.4.

ttttt

Displays the time according to your settings in the International section of your Control Panel's Time format (usually h:nn:ss).

AMPM

Uses the 12-hour clock time and displays AM or PM.

ampm

Uses the 12-hour clock time and displays am or pm.

AP

Uses the 12-hour clock time and displays A or P.

ap

Uses the 12-hour clock time and displays a or p.

The following statements demonstrate the string display-format characters. The remarks to the right of each statement explain that the target variable (the variable on the left of the equal sign) is receiving formatted data.

strS = Format("AbcDef", ">")   ' ABCDEF is assigned

strS = Format("AbcDef", "<")   ' abcdef is assigned

strS = Format("2325551212", "(@@@) @@@-@@@@")    ' (232) 555-1212

As the last statement shows, you can put string data into the format you prefer. If the data to be formatted, such as the phone number in the last line, is a string variable from a table's text field, the Format() statement works just the same.

Suppose that it's possible to leave out the area code of the phone number that you want to print. Format() fills from right to left, so the statement

strS = Format("5551212", "(@@@) @@@-@@@@")

stores the following in strS:

(   ) 555-1212

If you had included the area code, it would have printed inside the parentheses.

Only use the ! when you want the fill to take place from the other direction (when data at the end of the string being formatted might be missing). The statement

strS = Format("5551212", "!(@@@) @@@-@@@@")

incorrectly stores the following in strS:

(555) 121-2

Example

The function in Listing 14.4 contains an interesting use of the DateSerial() function:

Listing 14.4 Duedate.bas: Calculate the Next Weekday Value After a Specified Date

Function DueDate (anyDate)

' Accepts: a Date value

' Purpose: Calculates the first non-weekend day of the month

'           following the specified date

' Returns: the calculated date

    Dim Result

    If Not IsNull(anyDate) Then

        Result = DateSerial(Year(anyDate), Month(anyDate) + 1, 1)

        If Weekday(Result) = 1 Then      ' Sunday, so add one day.

            DueDate = Result + 1

        ElseIf Weekday(Result) = 7 Then  ' Saturday, so add two days.

            DueDate = Result + 2

        Else

            DueDate = Result

        End If

    Else

        Result = Null

    End If

End Function

When this function is called, it's passed a date value stored in the Variant or VarType 7 Date data type. As the remarks tell, the function computes the number of the first weekday (2 for Monday through 6 for Friday) of the next month (the first business day of the month following the argument).

Next Step

The following statements demonstrate how numeric formatting works. The remark to the right of each statement describes how the data is formatted.

strS  = Format(9146, "|######|")  ' |9146| is stored

strS = Format(2652.2, "00000.00") ' 02652.20 is stored

strS = Format(2652.2, "#####.##") ' 2652.2 is stored

strS = Format(2652.216, "#####.##") ' 2652.22 is stored

strS = Format(45, "+###")  ' Stores a +45

strS = Format(45, "-###")  ' Stores a -45

strS = Format(45, "###-")  ' Stores a 45-

strS = Format(2445, "$####.##")   ' Stores a $2445.

strS = Format(2445, "$####.00")  ' Stores a $2445.00

strS = Format(2445, "00Hi00")  ' Stores 24Hi45

The following statements demonstrate how date and time formatting works. The remark to the right of each statement describes how the data is formatted.

Dim d As Variant

d = Now     ' Assume the date and time is

            ' May 21, 1999 2:30 PM

strND = Format(d, "c") ' Stores 5/21/99 2:30:02 PM

strND = Format(d, "w") ' Stores 6 (for Friday)

strND = Format(d, "ww")' Stores 22

strND = Format(d, "dddd") ' Stores Friday

strND = Format(d, "q") ' Stores 2

strND = Format(d, "hh") ' Stores 14

strND = Format(d, "h AM/PM") ' Stores 2 PM

strND = Format(d, "hh AM/PM") ' Stores 02 PM

strND = Format(d, "d-mmmm h:nn:ss")  'Stores  21-May 14:30:02

Summary

As you've seen this hour, Visual Basic supplies many built-in functions. These functions save you work; you don't have to take the time to write the code yourself, but instead can call on these functions to do the work for you. You may not use all these functions, but some prove useful as you program in Visual Basic.

Most of the built-in functions require parentheses after their names. The parentheses are where you pass to the function the data (the arguments) to work on.

Several numeric functions work with integers. These functions primarily round non-integer data to integer results. Other numeric functions exist to convert data to different data types for you. The random statement and function generate random numbers for you.

The Len() function serves as a bridge between the numeric and string functions. It's the only function that works with string and numeric arguments by returning the length (the number of characters of storage) of the argument. Many string functions return portions (such as the leftmost or rightmost characters) of strings. These functions are sometimes called substring functions.

The functions and statements described this hour almost all work with an internal Date data type. Visual Basic recognizes most date formats, and whether you spell out a month name when entering a date is up to you because Visual Basic can interpret the date. The last part of this lesson explained how to format your data so that it appears on-screen the way you want. Formatting doesn't change the data itself, but the Format() function changes the way your data looks to the user.

Hour 15 begins a new part of the book, "Intermediate VB Programming." Now that you have the language details out of the way, it's time to have fun working with advanced controls on forms.

Hour 14 Quiz

  1. What happens if an argument is too big to be converted with a data type conversion function?
  2. What value does the following function call return?
CVar(Null)

  1. Why do you usually need the Randomize statement when you use Rnd()?
  2. True or false: If the following statement appeared in the first procedure executed within a module, the next Rnd() function call would produce a different result virtually every time the module is run.
Randomize Timer

  1. True or false: Both UCase() and LCase() change their arguments.
  2. How can you tell whether the use of Mid() is a function or a statement?
  3. Without looking at the ASCII table in Appendix B, what's stored in n after the following statement finishes?
n = Asc(Chr(192))

  1. What's the difference between the Now function and the Time function?
  2. What's stored in the variable named strS in each of the following statements?
    1. strS = Format("74135", "&&&&&-&&&&") ' Too short ZIPcode?
    2. strS = Format(d, "h am/pm")
    3. strS = Format(12345.67, "######.###")

Hour 14 Homework Exercises

  1. Create a new project with a blank form named frmNums. Write a For...Next loop that passes the following series of numbers to Int(), Fix(), and CInt():

  1. Use a Step value of 0.5 and print each result directly on the form in the immediate execution window with a Print statement like this:
frmNums.Print Int(val), Fix(val), CInt(val)

  1. Study the results to learn how each function varies from each other.
  2. This is the first time you've sent output directly to a form. The Print command is called a method; by applying the Print method to a form, separating the components with a period, you can write directly to a form. Such output is usually too messy for an application, but this exercise is only printing a list of values so printing to the form was much easier than trying to display the results in a list of labels. Hour 16, "Using Form Types," explains more about printing data directly onto forms.
  3. Change the number-guessing procedure to add a counter that begins at 1 and increments every time the user guesses incorrectly. In the ending message box, print the number of times the user guessed. Concatenate the number of guesses to the message.
  4. Write a function that encrypts whatever string is passed to it and returns the encrypted string. Use Asc() and Chr() to add 1 to the ASCII value of each character before concatenating the encrypted character to the return string.

© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.