Instead of declaring variables implicitly, declare all your variables explicitly:

Dim strName As String
strName = "Lola Montez"

Use that approach rather than the implicit declaration approach, which skips declaring the variable and merely assigns a value to it (which implicitly creates it):

strName = "Lola Montez"

Explicit declaration allows VBA to allocate only as much memory as that variable type needs. What's more, by specifying the data type of a variable, you relieve VBA of the necessity to waste time figuring out the data type each time the variable appears in your code. Better still, you avoid the risk of unintentionally storing the wrong type of data in the variable. Because the variable is explicitly typed, VBA displays an error message rather than storing the data and changing the variable type.

Table 16.1 shows the details on the amounts of memory that the different types of variables require.

How much memory you save by specifying data types, and how much difference choosing variable types makes to your procedures, depends on the type of work you're doing. For example, if you store a million characters in a Single variable, the 12 bytes you save by specifying that it's a String variable rather than a Variant variable make little difference. But if you use many variables on a computer with limited memory, specifying the appropriate data types for your variables may save enough memory to enable your procedure to run where it otherwise wouldn't have been able to, or at least enable it to run faster. Of course, hardware is continually improving—and memory is hardware. Now that RAM is becoming cheap and plentiful, conserving memory is becoming increasingly less an issue for programmers.

A second reason for declaring your variables explicitly rather than implicitly is to make your code easier to read and to debug. And a third reason is that you can implement some runtime range checking. If you know something will be less than 32,768, and you therefore declare it as being the Integer data type (rather than the Long type), you'll automatically get a helpful error if a Long-size value creeps into it somehow at runtime.

Sub Applying_Arial_Font()
'
' Applying_Arial_Font Macro
' Applies the Arial font to the selected text
'
    With Selection.Font
        .Name = "Arial"
        .Size = 13
        .Bold = False
        .Italic = False
        .Underline = wdUnderlineNone
        .UnderlineColor = wdColorAutomatic
        .StrikeThrough = False
        .DoubleStrikeThrough = False
        .Outline = False
        .Emboss = False
        .Shadow = False
        .Hidden = False
        .SmallCaps = False
        .AllCaps = False
        .Color = wdColorAutomatic
        .Engrave = False
        .Superscript = False
        .Subscript = False
        .Spacing = 0
        .Scaling = 100
        .Position = 0
        .Kerning = 0
        .Animation = wdAnimationNone
    End With
End Sub

Sub Stripped_Down_Procedure_Applying_Arial_Font()
    Selection.Font.Name = "Arial"End Sub

When you're performing multiple actions with an object, you can often use With statements to avoid repeating the object reference for each action. This simplifies your code. It becomes easier to read. And it may make it run marginally faster.

For example, the following statements contain multiple references to the first Paragraph object—Paragraphs(1)—in the ActiveDocument object in Word:

ActiveDocument.Paragraphs(1).Range.Font.Bold = True
ActiveDocument.Paragraphs(1).Range.Font.Name = "Times New Roman"
ActiveDocument.Paragraphs(1).LineSpacingRule = wdLineSpaceSingle
ActiveDocument.Paragraphs(1).Borders(1).LineStyle = wdLineStyleDouble
ActiveDocument.Paragraphs(1).Borders(1).ColorIndex = wdBlue

You can replace this redundancy by employing a With structure that references the Paragraphs(1) object in the ActiveDocument object to simplify the number of references involved:

With ActiveDocument.Paragraphs(1)
    .Range.Font.Bold = True
    .Range.Font.Name = "Times New Roman"
    .LineSpacingRule = wdLineSpaceSingle
    .Borders(1).LineStyle = wdLineStyleDouble
    .Borders(1).ColorIndex = wdBlue
End With

When you need to work with multiple child objects contained within a single parent object, you can either use separate With statements or pick the lowest common denominator of the objects you want to work with and use an outer With statement along with nested With statements for the child objects.

If you wish, you can further reduce the number of object references in the previous code example by using nested With statements for the Font object in the Range object and for the Borders(1) object, like this:

With ActiveDocument.Paragraphs(1)
    With .Range.Font
        .Bold = True
        .Name = "Times New Roman"
    End With
    .LineSpacingRule = wdLineSpaceSingle
    With .Borders(1)
        .LineStyle = wdLineStyleDouble
        .ColorIndex = wdBlue
    End With
End With

With statements are great for reducing repetitive object references and making your code easier to read, but don't use them just because you can. If you have only one statement within a With statement, as in the following example (which again uses Word), you're probably wasting your time typing the extra code to set up the With structure:

With ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary) _
    .Range.Words(1)
    .Bold = True
End With

Likewise, don't nest With statements unless you need to—it gets confusing, like this bizarre example:

With ActiveDocument
    With .Sections(1)
        With .Headers(wdHeaderFooterPrimary)
            With .Range
                With .Words(1)
                    With .Font
                        .Italic = True
                        .Bold = False
                        .Color = wdColorBlack
                    End With
                End With
            End With
        End With
    End With
End With

This code is better when written like this:

With ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range. _
    Words(1).Font
    .Italic = True
    .Bold = False
    .Color = wdColorBlack
End With

When you use a Select Case statement, arrange the Case statements so that the most likely ones appear first. This saves VBA some work and time—VBA goes down through the list of Case statements until it finds a match, so the earlier in the list it scores a match, the quicker the execution of the statement.

If you need to implement a setting (especially a Boolean one) every time a particular procedure runs, there's no point in checking the current value. For example, suppose you wanted to make sure the EnableAutoRecover property (a Boolean property that sets or returns whether the AutoRecover feature is on for the current workbook) of the ActiveWorkbook object in Excel is set to True. You could check the current value of EnableAutoRecover and, if it is False, set it to True like this:

If ActiveWorkbook.EnableAutoRecover = False Then _
    ActiveWorkbook.EnableAutoRecover = True

But that wastes code. Instead, simply go ahead and just set the property to True:

ActiveWorkbook.EnableAutoRecover = True

To improve the efficiency of your code, try to remove all unused elements from it. When creating a complex project with many interrelated procedures, it's easy to end up with some procedures that are almost or entirely useless. You were trying out various approaches and perhaps sketched in a couple of procedures that ended up never being used, for example.

You'll find it easier to remove superfluous procedures if you've commented your code comprehensively while creating it so you can be sure that what you're removing is unused rather than used. If you're in doubt as to which procedure is calling which, display the Call Stack dialog box (see Figure 16.1); choose View

Figure 16.1. The Call Stack dialog box lets you see which procedure has called which.

Alternatively, try one of these techniques:

Before you remove an apparently dead procedure from your code, make sure not only that it's unused in the way the code is currently being run, but also that it's not used in ways in which the procedure might be run were circumstances different. If you think that the procedure might still be used, try moving it to a project from which you can easily restore it rather than deleting it altogether.

Once you've removed any unused procedures, examine the variables in the procedures. Even if you're using the Option Explicit declaration and declaring every variable explicitly, check that you haven't declared variables that end up not being used. For simple projects, you'll be able to catch the unused variables by using the Locals window to see which of them never get assigned a value. For more complex projects, you may want to try some of the available third-party tools that help you remove unneeded elements from your code.

If in doubt, comment out the declaration of the supposedly superfluous variable, make sure you're using Option Explicit, and run the code a few more times, exercising the different paths that it can take—the various tasks that the code performs. If you don't get a "Variable Not Defined" compile error, you can probably eliminate the variable.

Removing unused procedures and variables isn't crucial. They do no real harm; they're just a bit of debris. But they do clutter up your code, potentially making it harder to understand and modify if you come back to it later for maintenance or reuse.

Back Up Your Modules, Forms, and Class Modules

Before removing an entire module, use the File

As you've seen in the examples so far in this book, you can make code much easier to follow by indenting some lines of code with tabs or spaces to show their logical relation to each other or to visually illustrate subordination and structures such as loops.

You can click the Indent and Outdent buttons on the editor's Edit toolbar or press Tab and Shift+Tab to quickly indent or unindent a selected block of code, with the relative indentation of the lines within the block remaining the same.

Use the line-continuation character (a space followed by an underscore) to break long lines of code into two or more shorter lines. Breaking lines makes long statements fit within the Code window on an average-size monitor at a readable point size and enables you to break the code into more logical segments.

You can't use the line-continuation character to break strings, however. If you want to break a long string, you must divide the string into smaller strings and then use the concatenation character (&) to attach the parts together again. You can separate the parts of the divided string (which are merely separated by the line-continuation character). For example, consider a long string such as this:

strMessageText = "The macro has finished running. Please check your presentation
to ensure that all blank slides have been removed."

Instead, you could divide the string into two, and then rejoin it like this:

strMessageText = "The macro has finished running. " & _
    "Please check your presentation to ensure that " & _
    "all blank slides have been removed."

To make your code more readable, use blank lines to separate statements into logical groups. For example, you might segregate all the variable declarations in a procedure as shown in the following example so that they stand out more clearly:

Sub Create_Rejection_Letter

    Dim strApplicantFirst As String, strApplicantInitial As String, _
        strApplicantLast As String, strApplicantTitle As String
    Dim strJobTitle As String
    Dim dteDateApplied As Date, dteDateInterviewed As Date
    Dim blnExperience As Boolean

    strApplicantFirst = "Shirley"
    strApplicantInitial = "P"
    strApplicantLast = "McKorley"
]

You can use variables to simplify and shorten complex syntax. For example, you could display a message box by using an awkwardly long statement such as this one:

If MsgBox("The document contains no text." & vbCr & vbCr _
    & "Click the Yes button to continue formatting the document." & _
    " Click the No button to cancel the procedure.", _
    vbYesNo & vbQuestion, _
    "Error Selecting Document: Cancel Procedure?") Then

Alternatively, you could use one String variable for building the message and another String variable for the title:

Dim strMsg As String
Dim strTBar As String
strMsg = "The document contains no text." & vbCr & vbCr
strMsg = _
  strMsg & "Click the Yes button to continue formatting the document. "
strMsg = strMsg & "Click the No button to cancel the procedure."
strTBar = "Error Selecting Document: Cancel Procedure?"
If MsgBox(strMsg, vbYesNo & vbQuestion, strTBar) Then

At first sight, this code looks more complex than the straightforward message box statement, mostly because of the explicit variable declarations that increase the length of the code segment. But in the long run, this approach is much easier to read and modify.

In the previous example, you could also replace the vbYesNo & vbQuestion part of the MsgBox statement with a variable (preferably a Long rather than a Variant). But doing so makes the code harder to read and is seldom worthwhile.

Often when you call another procedure, you'll need to pass information to it from the calling procedure and, when the procedure has run, pass back other information or a modified version of the same information.

The best way to pass information from one procedure to another is by using arguments. You declare the arguments to pass in the declaration line of the procedure that passes them. The arguments appear in the parentheses after the procedure's name. You can pass either a single argument (as the first of the following statements does) or multiple arguments separated by commas (as the second does):

Sub PassOneArgument(MyArg)
Sub PassTwoArguments(FirstArg, SecondArg)

As with functions (discussed in Chapter 9), you can pass an argument either by reference or by value. When a procedure passes an argument to another procedure by reference, the recipient procedure gets access to the memory location where the original variable is stored and can change the original variable. By contrast, when a procedure passes an argument to another procedure by value, the recipient procedure gets only a copy of the information in the variable and can't change the information in the original variable.

Passing an argument by reference is useful when you want to manipulate the variable in the recipient procedure and then return the variable to the procedure from which it originated. Passing an argument by value is useful when you want to use the information stored in the variable in the recipient procedure and at the same time make sure the original information in the variable doesn't change.

By reference is the default way to pass an argument, but you can also use the ByRef keyword to state explicitly that you want to pass an argument by reference. Both of the following statements pass the argument MyArg by reference:

Sub PassByReference(MyArg)
Sub PassByReference(ByRef MyArg)

To pass an argument by value, you must use the ByVal keyword. The following statement passes the ValArg argument by value:

Sub PassByValue(ByVal ValArg)

In practice, however, you'll rarely, if ever, need to employ ByVal. Arguments are nearly universally passed by reference, the default.

If necessary, you can pass some arguments for a procedure by reference and others by value. The following statement passes the MyArg argument by reference and the ValArg argument by value:

Sub PassBoth(ByRef MyArg, ByVal ValArg)

You can explicitly declare the data type of arguments you pass in order to take up less memory and ensure that your procedures are passing the type of information you intend them to. But when passing an argument by reference, you need to make sure that the data type of the argument you're passing matches the data type expected by the called procedure. For example, if you declare a String in the caller procedure and try to pass it as an argument when the called procedure is expecting a Variant, VBA gives an error.

To declare the data type of an argument, include a data-type declaration in the argument list. The following statement declares MyArg as a String and ValArg as a Variant:

Sub PassBoth(MyArg As String, ValArg As Variant)

You can specify an optional argument by using the Optional keyword. Place the Optional keyword before the ByRef or ByVal keyword if you need to use ByRef or ByVal:

Sub PassBoth(ByRef MyArg As String, ByVal ValArg As Variant, _
    Optional ByVal MyOptArg As Variant)

Listing 16.1 shows a segment of a procedure that uses arguments to pass information from one procedure to another.

1.  Sub GetCustomerInfo()
 2.      Dim strCustName As String, strCustCity As String, _
             strCustPhone As String
 3.      'Get strCustName, strCustCity, strCustPhone from a database
 4.      CreateCustomer strCustName, strCustCity, strCustPhone
 5.  End Sub
 6.

7.  Sub CreateCustomer(ByRef strCName As String, _
         ByRef strCCity As String, ByVal strCPhone As String)
 8.      Dim strCustomer As String
 9.      strCustomer = strCName & vbTab & strCCity _
             & vbTab & strCPhone
10.      'take action with strCustomer string here
11.  End Sub

Listing 16.1 contains two minimalist procedures—GetCustomerInfo and CreateCustomer—that show how to use arguments to pass information between procedures:

Another way to pass information from one procedure to another is to use either private variables or public variables. You can use private variables if the procedures that need to share information are located in the same module. If the procedures are located in different modules, you'll need to use public variables to pass the information.

Listing 16.2 contains an example of passing information by using private variables.

1.  Private strPassMe As String
 2.
 3.  Sub PassingInfo()
 4.      strPassMe = "Hello."
 5.      PassingInfoBack
 6.      MsgBox strPassMe
 7.  End Sub
 8.
 9.  Sub PassingInfoBack()
10.      strPassMe = strPassMe & " How are you?"
11.  End Sub

Listing 16.2 begins by declaring the private String variable strPassMe at the beginning of the code sheet for the module. strPassMe is then available to all the procedures in the module.

The PassingInfo procedure (lines 3 to 7) assigns the text Hello. (with the period) to strPassMe in line 4 and then calls the PassingInfoBack procedure in line 5. Execution then shifts to line 9, which starts the PassingInfoBack procedure. Line 10 adds How are you? with a leading space to the strPassMe String variable. Line 11 ends the PassingInfoBack procedure, at which point execution returns to the PassingInfo procedure at line 6, which displays a message box containing the strPassMe string (now Hello. How are you?). Line 7 ends the procedure.

The first way to create a property is to declare a Public variable in the class module. Doing so creates a read/write property with the name of the variable. For example, the following statement (entered in a class module) creates a read/write Boolean property named HardCover:

Public HardCover As Boolean

Using a Public variable is an easy way to create a property, but it's a bit limited: You can't choose to make the property read-only (or write-only), and you can't execute any other code when you set or return the value of the property.

You can then set and return the property in the normal manner. For example, say we've created the Boolean property HardCover in an instance named MastVBA of the Book class. The following statements set (storing or writing data in) the property and then display a message box returning (reading the value from) it:

MastVBA.HardCover = False
MsgBox MastVBA.HardCover

The second and more formal way to create a property is to use property procedures. There are three types of property procedures—Property Let, Property Get, and Property Set:

You typically use these procedures in pairs, pairing a Property Get procedure with a Property Let procedure or a Property Set procedure with a Property Let procedure. You can also use a Property Let procedure on its own to create a read-only property.

Property Let name ([arglist,] value)
   [statements]
End Property

Property Let Title(NewTitle As String)
    bookTitle = NewTitle
End Property

Property Get name [(arglist)] [As type]
    [statements]
End Property

Property Get Title() As String
    Title = bookTitle
End Property

Property Set name ([arglist,] reference)
    [statements]
End Property

Property Set Where(rngR As Range)
    bookRange = rngR
End Property

Listing 16.3 shows the full listing of properties for the Book class.

1.  Public Property Let Title(strT As String)
 2.      bookTitle = strT
 3.  End Property
 4.
 5.  Public Property Get Title() As String
 6.      Title = bookTitle
 7.  End Property
 8.
 9.  Public Property Let Author(strA As String)
10.      bookAuthor = strA
11.  End Property
12.
13.  Public Property Get Author() As String
14.      Author = bookAuthor
15.  End Property
16.
17.  Public Property Let Pages(intPages As Integer)
18.      bookPages = intPages
19.  End Property
20.
21.  Public Property Get Pages() As Integer
22.      Pages = bookPages
23.  End Property
24.
25.  Public Property Let Price(curP As Currency)
26.      bookPrice = curP
27.  End Property
28.
29.  Public Property Get Price() As Currency
30.      Price = bookPrice
31.  End Property
32.
33.  Public Property Let PublicationDate(dtePD As Date)
34.      bookPublicationDate = dtePD
35.  End Property

36.
37.  Public Property Get PublicationDate() As Date
38.      PublicationDate = bookPublicationDate
39.  End Property
40.
41.  Public Property Get Available() As Boolean
42.      Available = Date >= bookPublicationDate
43.  End Property

In Listing 16.3, each property for the Book class is declared as Public so that it is publicly accessible.

The code illustrates how you organize your paired procedures by putting each Property Let procedure next to the corresponding Property Get procedure: The Property Let Title procedure in lines 1 through 3 is matched by the Property Get Title procedure in lines 5 through 7, and so on for the Author, Pages, Price, and PublicationDate property procedures.

Pairing the procedures makes it easy to read the code to make sure each procedure that should have a counterpart does have one and to make sure the arguments match. But you can separate your Property Let procedures and Property Get procedures if you prefer.

You'll notice that the Property Get Available property procedure in lines 41 through 43 doesn't have a corresponding Property Let procedure. The Available property is read-only, with its value being generated inside the object, never assigned from code outside the object (as a Property Let writing procedure would permit).