title: How to use worksheet names as variables in Excel VBA
body: <p>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! :)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am getting the classic "subscript out of range error" which points to my wbSource1 variable as empty.</p>