Runtime error 6 overflow when looping through worksheets

Question

I am using the code below to remove paths in Excel referencing add-ins for files shared by users. It works with some files, but using it with a new file and receiving a Runtime error 6 overflow error upon opening. It is stopping on the Cell Replace line.

Private Sub Workbook_Open()
    Dim i As Long
    Dim strWorksheetName As String

    strWorksheetName = ActiveSheet.Name

    Application.DisplayAlerts = False
    For i = 1 To Sheets.Count
        Sheets(i).Activate
        If Sheets(i).Type = xlWorksheet Then
            Cells.Replace What:="'c:\*xla*'!", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        End If
    Next i
    Application.DisplayAlerts = True

    Sheets(strWorksheetName).Activate

End Sub

score 2 · Answer 1 · answered Aug 27 '16 at 04:16

This may be being caused because the Workbook_Open event is firing too soon, before everything is loaded.

You might have better luck if you don't try to Activate the worksheets. (Activating them would cause Excel to have to display them, which it might not be able to do because it is still doing other stuff. But making the changes without forcing the sheets to be displayed may let it continue doing what it wants to do. I'm guessing, but until someone comes up with another solution, a guess is as good as anything!)

Try changing your code to:

Private Sub Workbook_Open()
    Dim ws As Worksheet

    Application.DisplayAlerts = False
    For Each ws in Worksheets
        ws.Cells.Replace What:="'c:\*xla*'!", _
                         Replacement:="", _
                         LookAt:=xlPart, _
                         SearchOrder:=xlByRows, _
                         MatchCase:=False, _
                         SearchFormat:=False, _
                         ReplaceFormat:=False
    Next
    Application.DisplayAlerts = True

End Sub

If it works, I'll leave the answer here. If it doesn't, I'll delete the answer and someone else can make a suggestion.

score 1 · Answer 2 · edited May 23 '17 at 12:14

1

It is possible that one or more 'sheets' is/are not .Visible. You cannot .Activate a 'sheet' if it the .Visible property is False or xlVeryHidden.

Option Explicit

Private Sub Workbook_Open()
    Dim i As Long

    Application.DisplayAlerts = False

    For i = 1 To Worksheets.Count
        With Worksheets(i)
            .Cells.Replace What:="'c:\*xla*'!", _
                           Replacement:=vbNullString, _
                           LookAt:=xlPart, _
                           SearchOrder:=xlByRows, _
                           MatchCase:=False, _
                           SearchFormat:=False, _
                           ReplaceFormat:=False
        End With
    Next i

End Sub

Application.DisplayAlerts returns to the default of True after exiting the procedure that changed it to False.
You don't need to .Activate¹ a Worksheet in order to work on it and you can work on a hidden worksheet. Use a With ... End With statement to provide a wide parent worksheet hierarchy.
If you don't activate another worksheet, you don't have to store and reactivate the original.
If you work with the Worksheets collection rather than the Sheets collection then you don't have to check to see if the 'sheet' is a worksheet.

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

edited May 23 '17 at 12:14

Community

1
1

answered Aug 27 '16 at 05:18

I was about to delete my answer because I thought yours was better, but decided to do a test first. I created a workbook with three sheets, set different values in A1 of each sheet, hid one sheet, then ran the following: `Dim ws As Worksheet` `For Each ws In Worksheets` `Debug.Print ws.Name` `ws.Activate` `Debug.Print Cells(1, 1).Value` `Debug.Print ActiveSheet.Cells(1, 1).Value` `ActiveSheet.Cells(1, 1).Select` `Debug.Print Selection.Value` `Next`. I expected errors but didn't get them. Is my version of Excel weird, or is everything I ever thought about hidden sheets wrong?!?! – YowE3K Aug 27 '16 at 18:31
Odd indeed. I remembered activating a hidden worksheet as an error; in fact I believe I've even responded to questions about this (and I proof my responses). I just ran this on xl2016 with VB7 and while the hidden worksheets did not activate, they also did not throw an error. I will have to investigate more on this subject with different versions but that will be tomorrow or the next. This may be a worthwhile question all by itself. – Aug 28 '16 at 04:43
I tried both solutions but still have the same problem. There is some type of interaction with the custom functions as the macro is stripping out the path. The only solution I have found is to run the original macro and to temporarily move the add-ins containing the custom functions out of the normal location. Then it will strip out the all the paths and when I add the add-ins back to the normal location then the file works. If there was some type of command to disable the call to the add-in macros that might due the trick. I have tried setting to manual calcs but that does not work. – john Aug 31 '16 at 16:50

Runtime error 6 overflow when looping through worksheets

2 Answers2