IanFScott
11-10-2011, 07:21 AM
Does anyone else find that updating a sheet using vba causes error 9 (Subscript of of range) to occur but the code itself is NOT interupted.
With RegSheet
With .Range(.Cells(4, 1), .Cells(NumRecs + 4, 8))
Err.Clear
.Value = arrayReg ' Error 9 thrown here
.Borders.LineStyle = xlContinuous ' But not here
.Borders.Weight = xlThin ' Or here
End With
Err.Clear
.Range("B6").Value = 2 ' Error 9 thrown here
Err.Clear
.Range("B6").Value = "Text" ' And here
Err.Clear
.Range("B6").Formula = "=SUM(C6:F6)" ' And here
Err.Clear
RegSheet.Rows("4:2000").Delete ' and here
End With
Stepping through the above code (Regsheet and arrayReg etc. are set earlier) the error shows up (using the watch window) on each occasion the sheet is changed.
The work around is to use Application.EnableEvents = False even if, as in this case, there is no code using events.
The workbook was originally a 2003 .xls and is now saved as a 2007 .xslm
With RegSheet
With .Range(.Cells(4, 1), .Cells(NumRecs + 4, 8))
Err.Clear
.Value = arrayReg ' Error 9 thrown here
.Borders.LineStyle = xlContinuous ' But not here
.Borders.Weight = xlThin ' Or here
End With
Err.Clear
.Range("B6").Value = 2 ' Error 9 thrown here
Err.Clear
.Range("B6").Value = "Text" ' And here
Err.Clear
.Range("B6").Formula = "=SUM(C6:F6)" ' And here
Err.Clear
RegSheet.Rows("4:2000").Delete ' and here
End With
Stepping through the above code (Regsheet and arrayReg etc. are set earlier) the error shows up (using the watch window) on each occasion the sheet is changed.
The work around is to use Application.EnableEvents = False even if, as in this case, there is no code using events.
The workbook was originally a 2003 .xls and is now saved as a 2007 .xslm