Ten Tips for Microsoft Office VBA Library Development

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Paul Cornell
Microsoft Corporation

July 2002

Applies to:
     Microsoft Office Visual Basic® for Applications (VBA) version 6.3

Summary: Discover ten tips for writing code that targets the Office Visual Basic for Applications (VBA) version 6.3 library. (15 pages)

Contents

Use the InputBox Function to Work with User Input
Enhance Message Boxes with additional Buttons and Icons
Use the Err Object's Raise Method to Raise Custom Errors
Use the Collection Collection to Manage Groups of Objects and Collections
Enforce Strict Use of Data Types by Using the VBA Conversion Functions
Format Expressions by Using the VBA Format Functions
Get Quick Access to File Properties by Using the VBA File Functions
Use the GetSetting and SaveSetting Functions to Read and Write Registry Settings
Use the IIf Function for Simple True-False Comparisons
Use UserForms for Complex User Input

Use the InputBox Function to Work with User Input

The InputBox function is ideal when you need to get a single piece of input from the user at run time without constructing a UserForm. The InputBox function takes this format:

InputBox(Prompt, Title, Default, XPos, YPos, HelpFile, Context)

Where:

  • Prompt is the text that the user sees.
  • Title is an optional argument representing the text in the input box's title bar.
  • Default is an optional argument representing any existing text in the input box's text box.
  • XPos and YPos are optional arguments representing the horizontal and vertical position of the input box on the screen.
  • HelpFile and Context are optional arguments representing the help file and help context ID used to hook up context-sensitive (F1) help to the input box.

Here's an example of how to use the InputBox function:

Public Sub TestInputBoxFunction()

    ' Purpose: Demonstrates the use of the VBA InputBox function.
    
    Dim strFirstName As String
    Dim strLastName As String
    
    ' Get the user's first and last name.
    strFirstName = InputBox(Prompt:="Type your first name.", _
        Title:="Full Name Demo")
    strLastName = InputBox(Prompt:="Type your first name.", _
        Title:="Full Name Demo")
        
    ' Display the full name only if both input boxes have text in them.
    If strFirstName = "" Or strLastName = "" Then
        MsgBox Prompt:="You did not enter all required information."
    Else
        MsgBox Prompt:="You entered " & strFirstName & " " & _
            strLastName & ".", Title:="Full Name Demo"
    End If

End Sub

Enhance Message Boxes with additional Buttons and Icons

The MsgBox function contains an optional argument, Buttons, that allows you to place additional buttons and icons on your message boxes by specifying a VbMsgBoxStyle value. For a list of VbMsgBoxStyle values, see the VBA Object Browser.

Here is an example of how to place additional buttons and icons on your message boxes, as well as work with message box results:

Public Sub CustomMessageBoxes()

    ' Purpose: Demonstrates how to work with custom message boxes.

    Dim iResponse As Integer
    
    MsgBox Prompt:="Abort/Retry/Ignore (Ignore Default)", _
        Buttons:=vbAbortRetryIgnore + vbDefaultButton3
    MsgBox Prompt:="Critical", Buttons:=vbCritical
    MsgBox Prompt:="Exclamation", Buttons:=vbExclamation
    MsgBox Prompt:="Information", Buttons:=vbInformation
    MsgBox Prompt:="OK/Cancel", Buttons:=vbOKCancel
    MsgBox Prompt:="Question", Buttons:=vbQuestion
    MsgBox Prompt:="Retry/Cancel", Buttons:=vbRetryCancel
    MsgBox Prompt:="Yes/No", Buttons:=vbYesNo
    MsgBox Prompt:="Yes/No with Information", _
        Buttons:=vbYesNo + vbInformation
    MsgBox Prompt:="Yes/No with Critical and Help", _
        Buttons:=vbYesNo + vbCritical + vbMsgBoxHelpButton

    ' Determine which button the user selects.
    iResponse = MsgBox(Prompt:="Click Yes or No.", _
        Buttons:=vbYesNo + vbCritical)

    Select Case iResponse
        Case vbYes
            MsgBox Prompt:="You clicked Yes."
        Case vbNo
            MsgBox Prompt:="You clicked No."
    End Select

End Sub

Use the Err Object's Raise Method to Raise Custom Errors

Suppose you want to make a custom error occur when some piece of code does something you don't want it to do. For example, for troubleshooting purposes, a user may input incorrect data; you want to stop the program by raising an error, complete with an error code and the source of where the error occurred. To enable this, use the Err object's Raise method. The Raise method takes the following format:

Raise(Number, Source, Description, HelpFile, HelpContext)

Where:

  • Number is the error's number. To assign a custom error number to your custom error, add your custom error number to the vbObjectError constant (-2147221504) to make sure your custom error number doesn't conflict with any Office built-in error numbers. For example, to generate the error number -2147220504, assign vbObjectError + 1000 to the Err.Number property.
  • Source is an optional argument representing the source of the error. One option is to use a String value corresponding to the name of the procedure in which the error occurs.
  • Description is an optional argument representing any additional information about the error.
  • HelpFile and HelpContext are optional arguments representing the help file and help context ID used to hook up help to the error message box.

Here's an example of raising a custom error in code:

Public Sub TestRaiseCustomError()

    ' Purpose: Raises a custom error in code.

    ' Enable error handling.
    On Error GoTo TestRaiseCustomError_Err

    ' Raise a built-in error (division by zero).
    ' Comment the next line of code to raise only a custom error.
    MsgBox 1 / 0

    ' Raise a custom error.
    Err.Raise Number:=vbObjectError + 1000, _
        Source:="TestRaiseCustomError", _
        Description:="My custom error description."

TestRaiseCustomError_End:
    Exit Sub

TestRaiseCustomError_Err:
    MsgBox Prompt:="Error number " & Err.Number & " was raised. " & _
        vbCrLf & "Source: " & Err.Source & vbCrLf & _
        "Description: " & Err.Description
    Resume TestRaiseCustomError_End

End Sub

Use the Collection Collection to Manage Groups of Objects and Collections

The Collection collection provides a convenient way to refer to a group of objects and collections as a single object. The objects and collections in the collection don't have to be of the same data type.

To add items to a collection, use the Add method, which takes the following format:

Add(Item, Key, Before, After)

Where:

  • Item is the object or collection you want to add to the collection.
  • Key is an optional argument that specifies a unique key name for this item.
  • Before and After are optional arguments that specify where you want to add the item in the collection.

The following sample code demonstrates how to use the Collections collection. For more information, see the "Collection Object" topic in VBA Help.

' Class module (clsTest.cls).

Public FirstName As String
Public LastName As String
===================================================================
' Code module (modTest.bas).

Public Sub TestCollections()

    ' Purpose: Demonstrates the use of the Collections collection.

    Dim colNames As New Collection
    Dim iItem As Integer
    Dim vItem As Variant

    On Error GoTo TestCollections_Err

    Do
        ' Create an object to add to the collection.
        Dim clsTest_Item As New clsTest
        ' Set collection properties.
        clsTest_Item.FirstName = InputBox(Prompt:="First name?")
        clsTest_Item.LastName = InputBox(Prompt:="Last name?")

        ' Add the object to the collection...
        If clsTest_Item.FirstName <> "" And clsTest_Item.LastName <> "" Then
            colNames.Add _
                Item:= clsTest_Item, _
                Key:= clsTest_Item.FirstName & clsTest_Item.LastName
        ' ...unless the user doesn't fill out both first and last name.
        Else
            ' List the contents of the collection.
            For Each vItem In colNames
                MsgBox vItem.FirstName & " " & vItem.LastName
            Next vItem
            Exit Do
        End If

        Set clsTest_Item = Nothing

    Loop

TestCollections_End:
    Exit Sub

TestCollections_Err:
    Select Case Err.Number
        Case 457    ' Duplicate key name.
            MsgBox "You can't add duplicate first and last name pairs. " & _
                "Try again."
        End Select

    Resume TestCollections_End

End Sub

Enforce Strict Use of Data Types by Using the VBA Conversion Functions

The String values "1" and "2" do not equal the Integer values 1 and 2. In many cases, VBA can do the conversion for you automatically, but you should never rely on this behavior as your only means of converting data types.

You can use the conversion functions (CBool, CInt, CLng, CSng, CStr, and so on) to convert values from one data type to another. If the value passed to the conversion function is outside the range of the data type being converted to, an error occurs (run-time error '13': Type mismatch).

For example, consider the following procedure:

Public Sub TestCInt()

    ' Purpose: Demonstrates how conversion functions such as
    ' CInt are used.

    On Error GoTo TestCInt_Err

    ' Without CInt, "1" + "2" = "12".
    MsgBox "Without CInt: " & (InputBox("Enter the first integer.") + _
        InputBox("Enter the second integer."))

    ' With conversion functions, "1" + "2" = 3.
    MsgBox "With CInt: " & CInt(InputBox("Enter the first integer.")) + _
        CInt(InputBox("Enter the second integer."))

TestCInt_End:
    Exit Sub

TestCInt_Err:
    Select Case Err.Number
        Case 13     ' Type mismatch.
            MsgBox Prompt:="You must enter both numbers, " & _
                "and you can only enter numbers " & _
                "(no letters or blanks). Try again."
        Case Else
            MsgBox "Error number: " & Err.Number & vbCrLf & _
                "Description: " & Err.Description
    End Select

    Resume TestCInt_End

End Sub

You can also use the Is functions (IsDate, IsNull, IsNumeric, IsObject, and so on) to return True if an expression is a date, is Null, is numeric, is an object, and so on.

Format Expressions by Using the VBA Format Functions

Use the Format functions (Format, FormatCurrency, FormatDateTime, FormatNumber, and FormatPercent) to format general information, currency, dates and times, numbers, and percentages.

The use of most of the Format functions is self-explanatory or can be deduced by referencing VBA Help. The Format function itself is very flexible and allows you to create your own custom formats. See the following VBA Help topics for built-in and custom formats:

  • "Named Date/Time Formats (Format Function)"
  • "Named Numeric Formats (Format Function)"
  • "User-Defined Date/Time Formats (Format Function)"
  • "User-Defined Numeric Formats (Format Function)"
  • "User-Defined String Formats (Format Function)"
  • "Different Formats for Different Numeric Values (Format Function)"
  • "Different Formats for Different String Values (Format Function)"

The following sample code demonstrates the use of built-in and custom formats:

Public Sub TestFormats()

    ' Purpose: Demonstrate the use of built-in and custom formats.

    ' Use named functions.

    ' Displays "1,294.97".
    MsgBox prompt:=FormatNumber(expression:=1294.9675)

    ' Displays "$1,294.97".
    MsgBox prompt:=FormatCurrency(expression:=1294.9675, _
        NumDigitsAfterDecimal:=2)

    ' Displays "5/29/2002 4:14:00 AM".
    MsgBox prompt:=FormatDateTime("May 29, 2002 4:14")

    ' Displays "89.98%".
    MsgBox prompt:=FormatPercent(expression:=0.899756)

    ' Use built-in formats.

    ' Displays "1,294.97".
    MsgBox prompt:=Format(expression:=1294.9675, Format:="Standard")

    ' Displays "$1,294.97".
    MsgBox prompt:=Format(expression:=1294.9675, Format:="Currency")

    ' Displays "5/29/2002 4:14:00 AM".
    MsgBox prompt:=Format(expression:="May 29, 2002 4:14", _
        Format:="General Date")

    ' Displays "89.98%".
    MsgBox prompt:=Format(expression:=0.899756, Format:="Percent")

    ' Use custom formats.

    ' Displays "1,294.97".
    MsgBox prompt:=Format(expression:=1294.9675, Format:="0,0.00")

    ' Displays "$1,294.97".
    MsgBox prompt:=Format(expression:=1294.9675, Format:="$0,0.00")

    ' Displays "5/29/2002 4:14:00 AM".
    MsgBox prompt:=Format(expression:="May 29, 2002 4:14", _
        Format:="m/d/yyyy h:Nn:Ss AM/PM")

    ' Displays "89.98%".
    MsgBox prompt:=Format(expression:=0.899756, Format:="0.00%")

End Sub

Get Quick Access to File Properties by Using the VBA File Functions

You can quickly obtain file properties such as the created date, the last modified date, or the number of bytes in a file by using the VBA FileDateTime and FileLen functions, for example:

Public Sub TestFileProperties()

    ' Purpose: Demonstrates the use of the VBA FileDateTime 
    ' and FileLen functions.
    
    ' Create the following file in your C:\ drive.
    Const FILE_PATH As String = "C:\SampleFile.txt"
    
    On Error GoTo TestFileProperties_Err
    
    ' List the created/modified date and the number of bytes
    ' for the file.
    MsgBox Prompt:="'" & FILE_PATH & "' was created or last " & _
        "modified on " & FileDateTime(pathname:=FILE_PATH) & "."
    MsgBox Prompt:="'" & FILE_PATH & "' contains " & _
        FileLen(pathname:=FILE_PATH) & " bytes."
        
TestFileProperties_End:
    Exit Sub
    
TestFileProperties_Err:
    Select Case Err.Number
        Case 53     ' File not found.
            MsgBox Prompt:="Can't find file '" & FILE_PATH & "'. " & _
                "Check the file path and try again."
        Case Else
            MsgBox "Error number: " & Err.Number & vbCrLf & _
                "Description: " & Err.Description
    End Select
    
    Resume TestFileProperties_End

End Sub

Use the GetSetting and SaveSetting Functions to Read and Write Registry Settings

You can use a computer's registry to store application initialization and configuration settings. VBA allows you to read and write registry settings in the following registry path:

My Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\{Subkey}

To read registry settings in this registry path, use the GetSetting function. To write registry settings to this registry path, use the SaveSetting function.

Caution   Incorrectly modifying a computer's registry can have serious consequences, including needing to reinstall a computer's operating system. Be sure to back up a computer's registry, as well as update the computer's Emergency Repair Disk (ERD), before manipulating a computer's registry.

The GetSetting and SaveSetting functions take the following format:

SaveSetting(AppName, Section, Key, Setting)
GetSetting(AppName, Section, Key, Default)

Where:

  • AppName is a subkey of theVB and VBA Program Settingskey.
  • Section is a subkey of the key specified in the AppName argument.
  • Key is a value of the key specified in the Section argument.
  • Setting is a piece of data stored in the value specified in the Key argument.
  • Default is an optional argument specifying the data returned if no value is set in the Key argument.

Use the DeleteSetting function to delete a subkey or value.

The following sample code creates a subkey with two values, lists their values, deletes the subkey, and tries to list their values again.

Public Sub TestRegistryFunctions()

    ' Purpose: Demonstrates the use of the VBA SaveSetting
    ' and GetSetting functions.

    ' Create a subkey with two values.
    SaveSetting AppName:="MyApp", Section:="MySection", _
        Key:="MyKey", Setting:="MySetting"
    SaveSetting AppName:="MyApp", Section:="MySection", _
        Key:="MyKey2", Setting:="MySetting2"
        
    ' Displays "MySetting" and "MySetting2".
    MsgBox Prompt:=GetSetting(AppName:="MyApp", _
        Section:="MySection", Key:="MyKey")
    MsgBox Prompt:=GetSetting(AppName:="MyApp", _
        Section:="MySection", Key:="MyKey2")
        
    ' Delete the subkey.
    DeleteSetting AppName:="MyApp"
    
    ' Displays empty strings.
    MsgBox Prompt:=GetSetting(AppName:="MyApp", _
        Section:="MySection", Key:="MyKey")
    MsgBox Prompt:=GetSetting(AppName:="MyApp", _
        Section:="MySection", Key:="MyKey2")

End Sub

Use the IIf Function for Simple True-False Comparisons

Instead of using If…Else…End If statements to evaluate single-statement true-false comparisons, you should use the VBA IIf function. The IIf function is easier to debug and shortens the amount of code you need to write.

The IIf function takes the following format:

IIf(Expression, TruePart, FalsePart)

Where:

  • Expression is the true-false comparison that you want to make.
  • TruePart is the value returned if the comparison evaluates to True.
  • FalsePart is the value returned if the comparison evaluates to False.

The following sample code illustrates the use of the IIf function:

Public Sub TestIIf()

    ' Purpose: Demonstrate the use of the VBA IIF function.

    Dim iFirst As Integer
    Dim iSecond As Integer
    Dim sResult As String
    
    iFirst = 1
    iSecond = 2
    
    ' Use If...Else...End If to evaluate.
    If iFirst = 1 Then
        MsgBox Prompt:="i = 1"
    Else
        MsgBox Prompt:="i <> 1"
    End If
    
    If iSecond > 3 Then
        MsgBox Prompt:="i > 3"
    Else
        MsgBox Prompt:="i <= 3"
    End If
    
    ' Use the IIf function to shorten your code.
    MsgBox Prompt:=IIf(Expression:=iFirst = 1, TruePart:="i = 1", _
        FalsePart:="i <> 1")
    MsgBox Prompt:=IIf(Expression:=iSecond > 3, TruePart:="i > 3", _
        FalsePart:="i <= 3")
            
End Sub

Use UserForms for Complex User Input

For complex user input or non-textual user input, UserForms are preferable to input boxes. Input boxes can only accept one piece of user input at a time, and this input can only be provided as text.

To add a UserForm to your VBA application, in the Visual Basic Editor, on the Insert menu, click UserForm. You can then add user input controls through the Toolbox (View menu, Toolbox command) and attach code that executes when users interact with these controls. For more information on working with UserForms or controls on UserForms, click the UserForm or UserForm control and then press F1.