引用其他工作簿对大多数人都是有效的，但是对于两台机器来说，会导致“下标超出范围”的错误

时间：2022-02-15 16:43:50

I have a file which is used by 70 people (identical file, each team has their own copy for their geographical zone). This file works for 68 of the people. When the two that don't work log on a different machine it works. I can't see any difference in settings. "ignore other applications.." is unticked and the other file opens in the same instance of Excel.

我有一个供70个人使用的文件(相同的文件，每个团队都有自己的地理区域副本)。这个文件适用于68个人。当两个不工作的人在另一台机器上登录时，它就工作了。我看不出设置有什么不同。“忽略其他应用程序..”未勾选，另一个文件在同一个Excel实例中打开。

Below is an extract of the code. The file opens ok, but when it gets to the line With Workbooks("Master").... it gets a "subscript out of range" error. I've tried doing it without using a "With" statement but that didn't help.

下面是代码的摘录。打开文件好了,但是当它到达与作业本(“主”)....它会得到一个“超出范围的下标”错误。我试过不使用“With”语句，但这没有用。

I can step through all the rest of the code, it's just the lines that refer to the "Master" work book that error (and they work fine on the other 68 machines).

我可以遍历所有代码的其余部分，这只是指向“Master”工作簿的行出错(它们在其他68台机器上运行良好)。

            If IsAlreadyOpen("Master.xlsm") = False Then

            Workbooks.Open (filepath), ReadOnly:=False, UpdateLinks:=False, Password:="password here"

            Else
            Workbooks("Master.xlsm").Activate
            End If

              With Workbooks("Master").Sheets("This Sheet")
                            .Cells.EntireRow.Hidden = False
                            .Cells.EntireColumn.Hidden = False
                            .AutoFilterMode = False

                End With

Can anybody suggest anything to fix this?

有人能提出什么解决办法吗?

1 个解决方案

#1

1  

This would be an appropriate place to use object variable (to represent a Workbook object) instead of relying on Activate methods. Note this may require modifications elsewhere, if you are relying on ActiveWorkbook or unqualified Sheets, etc.

这是使用对象变量(表示工作簿对象)而不是依赖于激活方法的合适位置。注意，如果您依赖于ActiveWorkbook或不合格的表等，这可能需要在其他地方进行修改。

 Dim wbMaster as Workbook
 If Not IsAlreadyOpen("Master.xlsm") Then
    Set wbMaster = Workbooks.Open(filepath, ReadOnly:=False, UpdateLinks:=False, Password:="password here")
 Else:
    Set wbMaster = Workbooks("Master.xlsm")
 End If
 With wbMaster.Sheets("This Sheet")
     .Cells.EntireRow.Hidden = False
     .Cells.EntireColumn.Hidden = False
     .AutoFilterMode = False
 End With

#1

1  

This would be an appropriate place to use object variable (to represent a Workbook object) instead of relying on Activate methods. Note this may require modifications elsewhere, if you are relying on ActiveWorkbook or unqualified Sheets, etc.

这是使用对象变量(表示工作簿对象)而不是依赖于激活方法的合适位置。注意，如果您依赖于ActiveWorkbook或不合格的表等，这可能需要在其他地方进行修改。

 Dim wbMaster as Workbook
 If Not IsAlreadyOpen("Master.xlsm") Then
    Set wbMaster = Workbooks.Open(filepath, ReadOnly:=False, UpdateLinks:=False, Password:="password here")
 Else:
    Set wbMaster = Workbooks("Master.xlsm")
 End If
 With wbMaster.Sheets("This Sheet")
     .Cells.EntireRow.Hidden = False
     .Cells.EntireColumn.Hidden = False
     .AutoFilterMode = False
 End With
标签：

相关文章