VBA has a rich and comprehensive function library as well as tools to assist in their use. The subsections that follow introduce you to many of the commonly used functions. You will see examples of how they are used. Be aware that this is just a sampling of the functions available and provides you with just a few ideas of how you can use these functions. The potential use of the VBA function library is limited only by your needs and your imagination.
The following sections describe some of the most commonly used functions and provide examples. On some rainy day, you should go through the online Help to become familiar with the rest of VBA's built-in functions.
You can find the following examples in basBuiltIn in the Chap23Ex.mdb database.
The Format function formats expressions in the style specified. The first parameter is the expression you want to format; the second is the type of format you want to apply. Here's an example of using the Format function:
Sub FormatData() Debug.Print Format$(50, "Currency") 'Prints $50.00 Debug.Print Format$(Now, "Short Date") 'Prints the current date Debug.Print Format$(Now, "DDDD") 'Displays the word for the day Debug.Print Format$(Now, "DDD") 'Displays 3 - CHAR Day Debug.Print Format$(Now, "YYYY") 'Displays 4 - digit Year Debug.Print Format$(Now, "WW") 'Displays the Week Number End Sub
The result of using this function appears in Figure 23.11.
The Instr function returns the position where one string begins within another string:
Sub InstrExample() Debug.Print InStr("Alison Balter", "Balter") 'Returns 8 Debug.Print InStr("Hello", "l") 'Returns 3 Debug.Print InStr("c:\my documents\my file.txt", "\") 'Returns 3 End Sub
InStrRev begins searching at the end of a string and returns the position where the compiler finds one string within another string:
Sub InstrRevExample() Debug.Print InStrRev("c:\my documents\my file.txt", "\") 'Returns 16 End Sub
Notice that the InStr function returns 3 as the starting position for the backslash character within "c:\my documents\my file.txt", whereas the InStrRev function returns 16 as the starting position for the backslash character in the same string. This is because InStr starts searching at the beginning of the string, continuing until it finds a match, whereas InStrRev begins searching at the end of the string, continuing until it finds a match.
Left returns the left-most number of characters in a string:
Sub LeftExample() Debug.Print Left$("Hello World", 7) 'Prints Hello W End Sub
Right returns the right-most number of characters in a string:
Sub RightExample() Debug.Print Right$("Hello World", 7) 'Prints o World End Sub
Mid returns a substring of a specified number of characters in a string. This example starts at the fourth character and returns five characters:
Sub MidExample() Debug.Print Mid$("Hello World", 4, 5) ''Prints lo Wo End Sub
UCase returns a string that is all uppercase:
Sub UCaseExample() Debug.Print UCase$("Hello World") 'Prints HELLO WORLD End Sub
DatePart returns the specified part of a date:
Sub DatePartExample() Debug.Print DatePart("YYYY", Now) 'Prints the Year Debug.Print DatePart("M", Now) 'Prints the Month Number Debug.Print DatePart("Q", Now) 'Prints the Quarter Number Debug.Print DatePart("Y", Now) 'Prints the Day of the Year Debug.Print DatePart("WW", Now) 'Prints the Week of the Year End Sub
DateDiff returns the interval of time between two dates:
Sub DateDiffExample() Debug.Print DateDiff("d", Now, "12/31/99") ''Days until 12/31/99 Debug.Print DateDiff("m", Now, "12/31/99") ''Months until 12/31/99 Debug.Print DateDiff("yyyy", Now, "12/31/99") ''Years until 12/31/99 Debug.Print DateDiff("q", Now, "12/31/99") ''Quarters until 12/31/99 End Sub
DateAdd returns the result of adding or subtracting a specified period of time to or from a date:
Sub DateAddExample() Debug.Print DateAdd("d", 3, Now) 'Today plus 3 days Debug.Print DateAdd("m", 3, Now) 'Today plus 3 months Debug.Print DateAdd("yyyy", 3, Now) 'Today plus 3 years Debug.Print DateAdd("q", 3, Now) 'Today plus 3 quarters Debug.Print DateAdd("ww", 3, Now) 'Today plus 3 weeks End Sub
Replace replaces one string with another:
Sub ReplaceExample() Debug.Print Replace("Say Hello if you want to", "hello", "bye") 'Returns Say Bye if you want to Debug.Print Replace("This gets rid of all of the spaces", " ", "") 'Returns Thisgetsridofallofthespaces End Sub
StrRev reverses the order of text in a string:
Sub StrReverseExample() Debug.Print StrReverse("This string looks very funny when reversed!") 'Returns !desrever nehw ynnuf yrev skool gnirts sihT End Sub
MonthName returns the text string associated with a month number:
Sub MonthNameExample() Debug.Print MonthName(7) 'Returns July Debug.Print MonthName(11) 'Returns November
With the Object Browser, you can view members of an ActiveX component's type library. In plain English, the Object Browser enables you to easily browse through a component's methods, properties, and constants. You can also use it to copy information and add it to your code. The Object Browser even adds a method's parameters for you.
The following steps let you browse among the available methods, copy the method you want, and paste it into your code:
With the VBE active, select View | Object Browser (note that the Object Browser submenu also shows an icon that you can use from the toolbar) or press F2 to open the Object Browser window (see Figure 23.12).
The Object Browser window is divided into an upper part and a lower part. You use the drop-down list at the upper-left of the window to filter the items you want to display in the lower part of the window. Use this drop-down list to select the project or library whose classes and members you want to view in the lower part of the window.
In the lower portion of the window, select the class from the left list box, which lists Class modules, templates for new objects, Standard modules, and modules containing subroutines and functions.
Select a related property, method, event, constant, function, or statement from the Members Of list box. In Figure 23.12, the basUtils module is selected from the list box on the left. Notice that the subroutines and functions included in basUtils appear in the list box on the right.
Click the Copy to Clipboard button to copy the function name and its parameters to the Clipboard so that you can easily paste it into your code.
The example in Figure 23.12 shows choosing a user-defined function selected from a module in a database, but you can select any built-in function. Figure 23.13 shows an example in which the DatePart function is selected from the VBA library.