How to use worksheet names as variables in Excel VBA

Question

I am trying to write a macro that will prompt the user to open 2 workbooks and then loop through the worksheets in the 2 books comparing their contents and highlighting any differences in yellow. Each piece seems to be working on its own, but I cannot figure out how to set the workbook names as global variables to be used between the functions in my sub. Any help would be appreciated! :)

Public strFile1 As String
Public strFile2 As String

Public wbSource1 As Workbook
Public wbSource2 As Workbook

Public I As Integer

Sub DifferenceCheckBetweenBooks()
    Call openIt
    Call WorksheetLoop
End Sub

Function openIt()
    strFile1 = Application.GetOpenFilename
    Workbooks.Open strFile1
    Set wbSource1 = Workbooks.Open(strFile1)

    strFile2 = Application.GetOpenFilename
    Workbooks.Open strFile2
     Set wbSource2 = Workbooks.Open(strFile2)
End Function

Function WorksheetLoop()
    Dim WS_Count As Integer    
    WS_Count = Workbooks(wbSource1).Worksheets.Count

    ' Begin the loop.
    For I = 1 To WS_Count
    Call compareBooks
    Next I
End Function

Function compareBooks()
    Dim mycell As Range
    'For each cell in worksheet that is not the same as compared worksheet, color it yellow
    For Each mycell In Workbooks(wbSource1).Worksheets(I).UsedRange
    If Not mycell.Value = Workbooks(wbSource2).Worksheets(I).Cells(mycell.Row, mycell.Column).Value Then
    mycell.Interior.Color = vbYellow
    Workbooks(wbSource2).Worksheets(I).Cells(mycell.Row, mycell.Column).Interior.Color = vbYellow
    End If
    Next
    Workbooks(wbSource2).Worksheets(I).Select
End Function

I am getting the classic "subscript out of range error" which points to my wbSource1 variable as empty.

Workbooks(wbSource1) should be wbSource1 as you have already defined it as a workbook. — SJR

Tim Williams Tim Williams · Accepted Answer · 2019-08-01T16:41:52

Don't do this

Workbooks.Open strFile1
Set wbSource1 = Workbooks.Open(strFile1)

you only need

Set wbSource1 = Workbooks.Open(strFile1)

And as SJR points out:

WS_Count = wbSource1.Worksheets.Count  'plus all other instances of this

You should really refactor your code to remove the globals and use parameters in your methods instead - that's a much safer approach.

Refactored to remove globals:

Sub DifferenceCheckBetweenBooks()
    Dim wb1 As Workbook, wb2 As Workbook

    Set wb1 = OpenIt("Choose first file")
    If wb1 Is Nothing Then Exit Sub

    Set wb2 = OpenIt("Choose second file")
    If wb2 Is Nothing Then Exit Sub

    CompareWorkbooks wb1, wb2

End Sub

Sub CompareWorkbooks(wb1 As Workbook, wb2 As Workbook)
    Dim i As Long, sht1 As Worksheet, sht2 As Worksheet, c As Range, c2 As Range

    For i = 1 To wb1.Worksheets.Count
        Set sht1 = wb1.Worksheets(i)
        Set sht2 = wb2.Worksheets(i)

        For Each c In sht1.UsedRange.Cells
            Set c2 = sht2.Range(c.Address)
            If c.Value <> c2.Value Then
                c.Interior.Color = vbYellow
                c2.Interior.Color = vbYellow
            End If
        Next c
     Next i
End Sub

Function OpenIt(msg As String) As Workbook
    Dim strFile
    strFile = Application.GetOpenFilename(Title:=msg)
    If Len(strFile) > 0 Then Set OpenIt = Workbooks.Open(strFile)
End Function

How to use worksheet names as variables in Excel VBA

1 Answers