Use Global or Module Level Constants

Store all values that you might need to change in global or module level constants. Then use the constant instead of the value in your code. If you need to change the value later, you can change it in one place and it will take effect throughout your program. Examples of this include:

• Paths used to access files.
• Messages displayed to your user.
• The names of other workbooks used in your add-in.
• Worksheet names used in your add-in.
• Add-in version number.
• Add-in title for message boxes.
• Starting range addresses for building report sheets.

Store all message strings as global constants in one location. This makes them very easy to modify and translate for international versions of your add-in. Error messages displayed to users should always be in global constants, as the wording of these messages often changes with feedback from users.

Don't assume that an object exists unless you've just created it yourself. When accessing objects that could have been potentially removed by the user, always check to see if they exist before attempting to use them. For example, the following subroutine checks to see if DataBook.xls is open before trying to use it:

Sub UseDataBook()
    Dim oBook As Workbook
    On Error Resume Next
        Set oBook = Workbooks("DataBook.xls")
    On Error GoTo ErrorHandler
    ''' The procedure ends here if no data book is found.
    If oBook Is Nothing Then Err.Raise _
        Number:= 999, Description:="Data book not found."
    ''' Continue processing using the oBook variable here.
    Exit Sub

ErrorHandler:
    MsgBox Err.Description, vbCritical, "My Add-in"
    ''' Do any necessary cleanup here...
End Sub