Subscript out of range - Most computers work fine (mine included)

Question

I am a new user of vba.

There is recently a vba problem that has left me rather clueless and helpless - subscript out of range - on a particular user's computer when every other user seems to have no issue with using the macro (myself included) hence I can't simply trial and error to troubleshoot.

Hence really need expert help from all of you! Really really appreciated!!

I have used a series of vba, which will run one after another and have pasted them in chronological order as follows.

VBA 1

Sub VBA_1()
Dim ws As Worksheet

        For Each ws In ActiveWorkbook.Worksheets

        ws.Outline.ShowLevels 1, 1

        Next ws
    End Sub

VBA 2

Sub VBA_2()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Protect ("Password")

Next ws

End Sub

VBA 3

Sub VBA_3()
Dim iRet As Integer
Dim strPrompt As String



'Prompt
strPrompt = "This will take about 2 minutes. Click 'OK' to proceed."

'Show msgbox
iRet = MsgBox(strPrompt, vbOKCancel)

'When user clicked 'OK'..
If iRet = vbOK Then

'SaveAs Function
Dim fName As String

fName = Application.GetSaveAsFilename(, "Excel Binary Workbook (*.xlsb), *.xlsb")
If fName = "False" Then
MsgBox "File not saved.", vbOKOnly
Cancel = True
End If

Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=fName, FileFormat:=xlExcel12
Application.EnableEvents = True

' Calculate
   Application.Calculate
   Application.ScreenUpdating = True


' Outlet
    Worksheets("Total Outlets").Activate
'Copy and Paste this portion to each worksheet
        For Each cell In Range("A1")
            If cell.Value = "Not Applicable" Then
            ActiveSheet.Visible = xlSheetHidden
        Else
            Call HypMenuVRefresh
        End If
    Next
'End Outlet & Copy and Paste

    Worksheets("D11101").Activate
        For Each cell In Range("A1")
            If cell.Value = "Not Applicable" Then
            ActiveSheet.Visible = xlSheetHidden
        Else
            Call HypMenuVRefresh
        End If
    Next

    Worksheets("D11102").Activate
        For Each cell In Range("A1")
            If cell.Value = "Not Applicable" Then
            ActiveSheet.Visible = xlSheetHidden
        Else
            Call HypMenuVRefresh
        End If
    Next

'Hide sheets accordingly
Worksheets("Restaurant List").Visible = xlSheetVeryHidden
Worksheets("Hotel List").Visible = xlSheetVeryHidden

'Recalculate
Application.Calculate
Application.ScreenUpdating = True


'Renaming of tabs
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Range("A2").Value = 1 Then
        If ws.Visible = xlSheetVisible Then
            On Error Resume Next
                ws.Name = ws.Range("A10").Value
        End If
    End If
Next ws


'Save Workbook
ActiveWorkbook.Save

'Enable finishing screen to be 'Input'
Sheets("Input").Select

'Show msgbox
MsgBox ("Retrieval Completed!")


Else

MsgBox ("Retrieval of Data Cancelled")

End If

End Sub

replace the `ActiveWorkbook` with `ThisWorkbook` and `ActiveSheet` with `ThisWorkbook.Worksheets("sheet name")` and if still issues post the code of `HypMenuVRefresh` too http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/9292/avoid-using-select-or-activate — Slai, Feb 16 '17 at 10:30
Its hard to say but it is most likely code like this throwing you off.....Worksheets(), it will throw this error if the sheet does not exist and maybe if its un-accessible (not sure about the later) — Paul S, Feb 16 '17 at 10:44
I propose to close this question because the OP does not tell us where the error occurs. — Paul Ogilvie, Feb 16 '17 at 11:07
@PaulOgilvie because the OP does not know, as is explained at the start of the question. — Tim Wilkinson, Feb 16 '17 at 11:09
@TimWilkinson, the OP explained he/she doesn't know _why_, but he/she may very well get to know _where_. But it's always so much easier and faster to shoot in the dark and hope to hit the goal... — user3598756, Feb 16 '17 at 11:38
@fangfy Be sure to qualify all of the ranges and worksheets you are working with. While the code *should* work when Activating a ws and then referring to its range, there is always the chance of error. You can change these to "ws.Range()" and this should eliminate your ranges being the issue. Likewise, be sure to qualify all worksheet references as well, and it is always best to set the workbook to a variable versus using the activeworkbook. You should be able to use ThisWorkbook, or use the "Workbooks()" collection and use the name to activate the correct one. — Brandon Barney, Feb 16 '17 at 12:55
What is `for each cell in range("A1")` it is not a loop if there is just one cell. I agree with the other comments, you could be trying to select a sheet that is not located in the activeworkbook. Hence the "Subscript out of range" error. If you set the variables right, you wouldn't have to activate any workbooks or worksheets. — Davesexcel, Feb 16 '17 at 20:00
@PaulOgilvie, thank you for answering even though it could be ridiculous to you that I don't actually know what's the problem! The frustrating problem is that it works for me and many other users. So far there is a user in China and another in Thailand that seem to be facing this subscript problem hence you may be right that it may be a language setting issue. I will try to get remote access to their computers and find out where exactly this subscript error is found in. Update everyone then! — fangfy, Feb 17 '17 at 07:55

Paul Ogilvie · Answer 1 · 2017-02-16T13:04:36.837

I can think of the following possible causes but do not say any of them is the actual cause:

"...on a particular user's computer..."

Then:

the version of Excel/VBA is different;
somehwere a global Option Base is set (but I believe this cannot be set global, i.e. applicable to all workbooks loaded);
somewhere a separator is "hard coded" that does not conform to the Windows global setings (Control Panel --> Region and Language --> Formats --> Additional Settings);
the language differs with a reflection in VBA (e.g. a keyword/function name in the native language or identifier names with non-US ASCII 7 bit characters).

To find in where the program encounters the error (and stops), make a function that writes a status message to a file after every major step. Make sure to close the file after every message so the message is actually written.

Subscript out of range - Most computers work fine (mine included)

1 Answers1