How to use named constants with option explicit?

Question

As I am trying to conform to best practices, I'm putting Option Explicit at the top of my code and declaring all of my variables. But this has caused a conflict with another best practice of using named constants. What should I do?

The first issue was with xlFalse; I'm not having any issues with any of the other xl variables, so this seems like a standalone case. But I'm not really worried about it, because I can just put in False and the code works. (It works, right?)

Sub GetEOP()

    'Executes when the user clicks the "Import Data" button, imports the EOP data

    GlobalDeclarations

    'Open Open dialog, assigns selected file path to wordFileName
    wordFileName = Application.GetOpenFilename("Rich Text Files (*.rtf), *.rtf," & "Word Files (*.doc;*.docx), *.doc;*.docx", , "EOP Table File?")

    'Stop code if the user clicks Cancel (otherwise, wordFileName would be assigned a value of "False")
    If wordFileName = False Then Exit Sub

    'Open the selected file in Word
    Set wordDoc = GetObject(wordFileName)

    'Copy the values of the first table in the Word document to Excel
    With wordDoc.Tables(1)
        For wordRow = 1 To .Rows.Count
            For wordCol = 1 To .Columns.Count
                EOP.Cells(wordRow, wordCol).Value = WorksheetFunction.Clean(.cell(wordRow, wordCol).Range.Text)
            Next wordCol
        Next wordRow
    End With

    'Close Word
    Set wordDoc = Nothing

    'Find the last row and column of the EOP sheet.
    rw2 = EOP.Cells.Find("*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=xlFalse).Row 'ERROR: Variable not defined -- xlFalse
    cl2 = EOP.Cells.Find("*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=xlFalse).Column 'ERROR: Variable not defined -- xlFalse

    'Format EOP worksheet
    EOP.Cells(1, 1).RowHeight = 15
    EOP.Cells(2, 1).RowHeight = 15
    EOP.Cells(1, 1).Resize(50, 12).Interior.Color = clrbg
    EOP.Cells(1, 1).Resize(1, cl2).Interior.Color = clr2
    EOP.Cells(2, 1).Resize(rw2 - 1, cl2).Interior.Color = clr1

End Sub

But the more serious issue is when I'm running Word code from Excel, as in wordDoc.Close savechanges:=wdDoNotSaveChanges. I can just use wdDoNotSaveChanges = 0 instead and not worry about it, but I figured there's probably a better way to handle this scenario.

There's a huge amount of code here; the line throwing the error is all the way at the bottom, in the Closes Word Document chunk of code.

Sub DrillFolder(Folder)

    Dim SubFolder
    For Each SubFolder In Folder.SubFolders

        'Skip Archive and Old folders
        If InStr(UCase(SubFolder.Name), UCase("Archive")) > 0 Or _
           InStr(UCase(SubFolder.Name), UCase("Old")) > 0 Or _
           InStr(UCase(SubFolder.Name), UCase("do not use")) > 0 Then GoTo SkipFolder

        DrillFolder SubFolder

SkipFolder:
    Next

    Dim File
    Dim sortFile As Worksheet

    For Each File In Folder.Files

        'Checks if the file name has any of these time points in it.
        If InStr(UCase(File.Name), UCase("bl")) > 0 Or _
           InStr(UCase(File.Name), UCase("eop")) > 0 Or _
           InStr(UCase(File.Name), UCase("t2")) > 0 Or _
           InStr(UCase(File.Name), UCase("t3")) > 0 Then

            'Only look at RTFs
            If File.Type = "Rich Text Format" Then

                'Sorts the file according to the time point in the file name.
                If InStr(UCase(File.Name), UCase("bl")) > 0 Then Set sortFile = Worksheets("Base")
                If InStr(UCase(File.Name), UCase("eop")) > 0 Then Set sortFile = Worksheets("EOP")
                If InStr(UCase(File.Name), UCase("t2")) > 0 Then Set sortFile = Worksheets("T2")
                If InStr(UCase(File.Name), UCase("t3")) > 0 Then Set sortFile = Worksheets("T3")

                'sortTotalRow is used to make sure that entries in one worksheet don't skew the alignment of other worksheets
                Dim sortTotalRow As Integer
                Select Case sortFile.Name

                    Case "Base"
                        sortTotalRow = baseTotalRow
                    Case "EOP"
                        sortTotalRow = eopTotalRow
                    Case "T2"
                        sortTotalRow = t2TotalRow
                    Case "T3"
                        sortTotalRow = t3TotalRow
                    Case Else
                        sortTotalRow = 0

                End Select

                        'Opens the selected file in Word
                        Set wordDoc = wordApp.Documents.Open(File.Path)

                        'Adds file information to the Import worksheet
                        With Import.Cells(docCount + 2, 1)
                            .Value = wordDoc
                            .Offset(0, 1).Value = wordDoc.Tables(1).Rows.Count
                            .Offset(0, 2).Value = sortFile.Name
                            .Offset(0, 3).Value = wordDoc.FullName
                        End With


                        'Copies values of first table in document to Excel
                        If wordDoc.Tables.Count > 0 Then
Restart:                       With wordDoc.Tables(1)

                                'Combine Birth Month and Birth Year columns, if they're separate
                               Dim i As Integer
                               For i = 1 To .Columns.Count
                                    If InStr(UCase(WorksheetFunction.Clean(.cell(1, i).Range.Text)), UCase("Birth year")) = 1 Then

                                        'Change Birth Month column to Birth Month and Year column
                                        Dim k As Long
                                        For k = 1 To .Rows.Count
                                            .cell(k, i - 1).Range.Text = .cell(k, i - 1).Range.Text & .cell(k, i).Range.Text
                                        Next k

                                        'Then delete the Birth Year column, so it doesn't get copied twice
                                        .Columns(i).Delete
                                        GoTo Restart

                                    End If

                                    'Delete "describe yourself" and "Where are you taking this survey?" columns
                                    If InStr(UCase(WorksheetFunction.Clean(.cell(1, i).Range.Text)), UCase("describe")) > 0 Then
                                        .Columns(i).Delete
                                        GoTo Restart
                                    End If
                                    If InStr(UCase(WorksheetFunction.Clean(.cell(1, i).Range.Text)), UCase("taking this survey")) > 0 Then
                                        .Columns(i).Delete
                                        GoTo Restart
                                    End If
                                Next i

                                'Account for Grandma code by putting in dummy values for non-Gcode rounds
                                Dim gmaFind As Boolean

                                'Check for Gma in the table
                                With wordDoc.Range.Find
                                    .Text = "Grandmother"
                                    .MatchCase = False
                                    gmaFind = .Execute
                                End With

                                'If you don't find it, add a column and fill it with XXX
                                If gmaFind = True Then

                                Else
                                    .Columns.Add
                                    For i = 1 To .Rows.Count
                                        .cell(i, .Columns.Count).Range.Text = "XXX"
                                    Next i
                                End If



                                For wordRow = 1 To .Rows.Count
                                    For wordCol = 1 To .Columns.Count

                                        'Takes off headers for the tables after the first
                                        If sortTotalRow <> 0 And wordRow = 1 Then GoTo Skip

                                        'Skip "How do you describe yourself?" column
                                        If InStr(WorksheetFunction.Clean(.cell(1, wordCol).Range.Text), "describe") > 0 Then GoTo Skip

                                        'Skip "Please tell us the place where you are taking this survey." column
                                        If InStr(WorksheetFunction.Clean(.cell(1, wordCol).Range.Text), "taking this survey") > 0 Then GoTo Skip

                                        'If you're in the Obs column, also add in the name of the round.
                                        If wordCol = 1 Then
                                            sortFile.Cells(wordRow + sortTotalRow, wordCol).Value = Left(wordDoc.Name, 8) & " " & WorksheetFunction.Clean(.cell(wordRow, wordCol).Range.Text)
                                        Else
                                            sortFile.Cells(wordRow + sortTotalRow, wordCol).Value = WorksheetFunction.Clean(.cell(wordRow, wordCol).Range.Text)
                                        End If
'This is a flag
'to tell GoTo
'where to go.
Skip:                               Next wordCol
                                Next wordRow

                                'Adds number of records to totalRow, so the next table appears under the previous.
                                Select Case sortFile.Name

                                    Case "Base"
                                        'The -1 is accounting for the headers of the tables that have been omitted
                                        '(otherwise it would be a blank row)
                                        baseTotalRow = baseTotalRow + .Rows.Count - 1
                                    Case "EOP"
                                        eopTotalRow = eopTotalRow + .Rows.Count - 1
                                    Case "T2"
                                        t2TotalRow = t2TotalRow + .Rows.Count - 1
                                    Case "T3"
                                        t3TotalRow = t3TotalRow + .Rows.Count - 1
                                    Case Else

                                End Select

                            End With
                        End If

                'At the end of importing, docCount is used as "Debug.print "Time taken to import docCount documents""
                docCount = docCount + 1

                'Closes Word Document
                'Const wdDoNotSaveChanges As Integer = 0
                wordDoc.Close savechanges:=wdDoNotSaveChanges 'ERROR: Variable not defined -- wdDoNotSaveChanges

            End If
        End If


    Next


End Sub

Thanks for the help!

Answer 1

If I understand correctly, you're discovering how Option Explicit is saving you from very serious bugs!

If your code refuses to compile because wdDoNotSaveChanges is undefined, it's because the Microsoft Word object library isn't referenced (i.e. you're probably late-binding it).

Without Option Explicit, VBA happily compiles, and the undefined identifiers are, at execution time, defined on-the-fly as implicit Variant local variables holding the default value for any Variant variable: Empty... which, when passed to a function/procedure/method that accepts an integer type, silently gets converted to a 0, regardless of what you named it.

Constants defined in the Word object library are not loaded if you're late-binding the Word object model, so in order to use named constants and avoid magic numbers (the best practice you're referring to), you need to define your own - so you hit MSDN (or temporarily reference the object model and hit F2 to find the values you need) and reproduce the values in your own code:

Private Enum WdSaveOptions
    wdDoNotSaveChanges = 0 ' equivalent to Boolean value False
    wdPromptToSaveChanges = -2
    wdSaveChanges = -1 ' equivalent to Boolean value True
End Enum

And now not only Option Explicit won't interfere, your code will also work correctly!