VBA Crashes on first line Set Var = Workbooks("name").Worksheets("name")

Question

Below is a simple code to check for changes between 2 workbooks. 1 is a master workbook the other is a list of changes sent to me. I literally have both opened and I have ensured they are opened within the same instance. Code is stored within my personal macro workbook so I can check multiple files.

My code literally crashes on the first line. Set cSheet Workbooks("CNO_CostGroups_v2.xlsx").Worksheets("CostCenters") No errors, no messages, nothing. Excel simply goes into not responding, crashes, and reopens everything in an auto recovered version. I have stepped through this code line by line using F8 on my keyboard. Excel crashes each time and I can't get past here.

This code was working when written a few weeks ago and was used a couple times after that. My only guess is that perhaps my Excel updated, but no real indication that it happened. I don't know what version it may have been when code is written, but what is on my machine now is 64 bit, Version 2002, Build 12527.21416. I know I didn't change from 32 bit to 64 bit.

My question is what can I do? I've even tried running this code in Immediate Window and it correctly returns the value of A1 ?Workbooks("CNO_CostGroups_v2.xlsx").Worksheets("CostCenters").Range("A1").Value. What is causing it to crash? It seems like a simple enough line to me, and as you can see all variables have been declared appropriately.

Sub CheckForChanges()
Dim nSheet As Worksheet, cSheet As Worksheet, cIndexRng As Range, nHeadRng As Range, nIndexRng As Range
Dim C As Range, col1 As Long, col2 As Long, row1 As Long, row2 As Long

'Must have master version of cost center groups open
'Set intitial values
Set cSheet = Workbooks("CNO_CostGroups_v2.xlsx").Worksheets("CostCenters")
Set cIndexRng = cSheet.Range("P1", cSheet.Range("P1").End(xlDown))
Set nSheet = ActiveSheet
Set nHeadRng = nSheet.Range("A1", nSheet.Range("A1").End(xlToRight))
Set nIndexRng = nSheet.Range("P2", nSheet.Range("P2").End(xlDown)) 'Ensure This part is referencing the correct index column (currently in column P)
col1 = 1
col2 = nHeadRng.Count

'Check the file structures are the same
For Each C In nSheet.Range("A1", nSheet.Range("A1").End(xlToRight))
  If C.Value <> cSheet.Cells(1, col1).Value Then
    MsgBox ("Make sure you have open a current Cost Center file and that the column headers match")
    Exit Sub
  End If
  col1 = col1 + 1
Next C

'Begin checking file for changes. Needs updates in Yellow, new lines all together in Green
For Each C In nIndexRng
  row1 = C.Row
  If IsFound(C.Value, cIndexRng, row2) Then
    If row1 = row2 Then nSheet.Cells(row1, 16).Interior.Color = RGB(146, 208, 80)
    For col1 = 1 To col2
      If nSheet.Cells(row1, col1).Value <> cSheet.Cells(row2, col1).Value Then
        nSheet.Cells(row1, col1).Interior.Color = RGB(255, 255, 0)
      End If
    Next col1
  Else
    nSheet.Range(nSheet.Cells(row1, 1), nSheet.Cells(row1, col2)).Interior.Color = RGB(146, 208, 80)
  End If
Next C

End Sub

UPDATES: The issue is not bad names.

Answer 1

Try to dim the workbook:

Dim wb As Workbook
Dim cSheet As Worksheet, ' ...

Set wb = Workbooks("CNO_CostGroups_v2.xlsx")
Set cSheet = wb.Worksheets("CostCenters")

' snip