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.

How do you know that the mentioned line is the 'guilty one'? Did you run the code line by line and Excel freezes when try running of that code line? — FaneDuru, Apr 07 '21 at 14:32
Yes, I stepped through the code using F8 and that is the line causing excel to crash. I'll update the question to include this. — MartyMcfly0033, Apr 07 '21 at 14:33
The used workbook name may be wrong, even if it looks OK. Please, try to activate the workbook in discussion and create a simple test `Sub` containing a single code line: `Debug.Print ActiveWorkbook.name = "CNO_CostGroups_v2.xlsx"`. Does it return `True` in Immediate Window? If yes, activate the sheet in discussion and test it using `Debug.Print ActiveSheet.Name = "CostCenters"`. Does it return `True`? — FaneDuru, Apr 07 '21 at 14:56
The names are correct. I have updated the question with a screenshot — MartyMcfly0033, Apr 08 '21 at 13:46

score 2 · Accepted Answer · answered Apr 07 '21 at 14:41

2

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

answered Apr 07 '21 at 14:41

Gustav

53,498
7
29
55

This got me past that section thank you. Any ideas on why I needed to set them separately and why I couldn't just skip the workbook step? – MartyMcfly0033 Apr 08 '21 at 13:56
I guess it is because the workbook object in your code line gets out of scope as soon as it has been executed, leaving the worksheet orphaned. Excel VBA is very sensitive to careful housekeeping of its objects. – Gustav Apr 08 '21 at 14:52

score 0 · Answer 2 · answered Apr 07 '21 at 14:57

I am not sure what could be causing this, but try optimising code by turning off few features and also try to declare variables to see if that works for you or not.


'initialising the optimisation
Sub InitOptimisation()
    'optimising code by turning off few features
    Application.DisplayAlerts = False
    Application.CutCopyMode = False 'to disable the ants crawling
    Application.ScreenUpdating = False
    Application.Interactive = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
End Sub

'turn on the optimisation again
Sub DestroyOptimisation()
    Application.DisplayAlerts = True
    Application.CutCopyMode = True 'to disable the ants crawling
    Application.ScreenUpdating = True
    Application.Interactive = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

Sub calling()
Call InitOptimisation

Dim costGroup_wb As Workbook
Dim costCenter As Worksheet

Set costCenter = Workbooks("CNO_CostGroups_v2.xlsx").Worksheets("CostCenters")
'do your logic
Call DestroyOptimisation
End Sub

Thanks, but the optimization piece isn't the problem. Your code still fails on `Set costCenter = Workbooks("CNO_CostGroups_v2.xlsx").Worksheets("CostCenters")` when I try to run it — MartyMcfly0033, Apr 08 '21 at 14:29

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

2 Answers2