VBA function examples

In this section, I present a few examples of using VBA functions in code. In many of these examples, I use the MsgBox function to display a value in a message box. Yes, MsgBox is a VBA function — a rather unusual one, but a function nonetheless. This useful function displays a message in a pop-up dialog box. For more details about the MsgBox function, see Chapter 15.

A workbook that contains all the examples is available at this book's Web site.

Displaying the system date

The first example uses VBA's Date function to display the current system date in a message box:

Sub ShowDate()

MsgBox Date End Sub

Notice that the Date function doesn't use an argument. Unlike worksheet functions, a VBA function with no argument doesn't require an empty set of parentheses. In fact, if you provide an empty set of parentheses, the VBE will remove them.

To get the system date and time, use the Now function rather than the Date function. Or to get only the time, use the Time function.

Finding a string length

The following procedure uses the VBA Len function, which returns the length of a string. The Len function takes one argument: the string. When you execute


Sub GetLength()

Dim MyString As String Dim StringLength As Integer MyString = "Hello World" StringLength = Len(MyString) MsgBox StringLength End Sub

Excel also has a Len function, which you can use in your worksheet formulas. The Excel version and the VBA function work the same.

Displaying the integer part of a number

The following procedure uses the Fix function, which returns the integer portion of a value — the value without any decimal digits:



Dim MyValue As Double

Dim IntValue As Integer

MyValue = 123.456

IntValue = Fix(MyValue)

MsgBox IntValue



Determining a file size

The following Sub procedure displays the size, in bytes, of the Excel executable file. It finds this value by using the FileLen function.

Sub GetFileSize()

Dim TheFile As String

TheFile = "c:\MSOFFICE\EXCEL\EXCEL.EXE" MsgBox FileLen(TheFile) End Sub

Notice that this routine hard codes the filename (that is, it explicitly states the path). Generally, this isn't a good idea. The file might not be on the C drive, or this procedure, the message box displays 11 because the argument has 11 characters.

In this case, the message box displays 123.

VBA has a similar function called Int. The difference between Int and Fix is how each deals with negative numbers.

^ Int returns the first negative integer that's less than or equal to the argument (-123.456 turns into -124).

^ Fix returns the first negative integer that's greater than or equal to the argument (-123.456 turns into -123).

the Excel folder may have a different name. The following statement shows a better approach:

TheFile = Application.Path & "\EXCEL.EXE"

Path is a property of the Application object. It simply returns the name of the folder in which the application (that is, Excel) is installed (without a trailing backslash).

Identifying the type of a selected object

The following procedure uses the TypeName function, which returns the type of the selected object (as a string):

Sub ShowSelectionType()

Dim SelType As String SelType = TypeName(Selection) MsgBox SelType End Sub

This could be a Range, a Picture, a Rectangle, a ChartArea, or any other type of object that can be selected.

The TypeName function is very versatile. You can also use this function to determine the data type of a variable.

0 0

Post a comment