• Hour 14
  • Built-in Functions Save Time
  • • Lots of Functions
    • Numeric Functions
      • Figure 14.1.
    • String Functions
    • A Mid Statement?
    • Date and Time Functions
    • Listing 14.1. Using Timer to
    • time the users math skills.
    • Data-Testing Functions
    • Data Conversion Functions
    • Format Function
    • Youll Rarely Need Format Codes
    • Listing 14.2. Formatting
    • numeric and logical values.
    • Summary
    • Q&A
    • Workshop
      • Quiz
      • Exercises

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

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

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:

Sqr(argument)

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.

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.

---

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.

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.

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.

---

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.

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.

Dim lngBefore As Long

Dim lngAfter As Long

Dim lngTimeDiff As Long

Dim strAns As String



lngBefore = Timer   ` Save seconds since midnight

Do

   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.

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.

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.

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.

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.

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.

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.

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

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

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

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!