vba Worksheet 的方法 Visible 失败，但代码运行成功
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28217943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Method Visible of Worksheet fails, but code runs successfully
提问by user1283776
The following line:
以下行：
ThisWorkbook.Sheets(vSheets(i, 1)).Visible = xlSheetVisible
generates the error:
产生错误：
Run-time error '-2147417848 (80010108)':
Method 'Visible' of object '_Worksheet' failed
But if I click play in the debugger, execution finishes without a problem. So the line causes my code to go into the debugger. But it continues if I click play.
但是，如果我在调试器中单击播放，则执行完成没有问题。所以该行导致我的代码进入调试器。但是如果我点击播放它会继续。
Why?
为什么？
EDIT1: Here is a screenshot where you can see that the debugger has stopped (with the error code written above) but you can also see in the immediate window that the Visible property exists.
EDIT1：这是一个屏幕截图，您可以在其中看到调试器已停止（带有上面写的错误代码），但您也可以在即时窗口中看到 Visible 属性存在。
EDIT2: I tried setting wks as Variant instead of object and setting wks.Visible=Trueinstead of wks.visible = xlSheetVisible. Neither of the changes helped. I still get the same error:
EDIT2：我尝试将 wks 设置为 Variant 而不是 object 并设置wks.Visible=True而不是wks.visible = xlSheetVisible. 这些变化都没有帮助。我仍然收到相同的错误：
回答by Getafix
I found a similar issue after the code worked no worries for weeks. Eventually found that somehow I had set workbook protection (and not sheet protection as intended). This prevented simple sheet unhide actions through the normal user interface without giving any hint at the fact that workbook protection was set preventing structure changes!
在代码运行数周后我发现了类似的问题。最终发现我以某种方式设置了工作簿保护（而不是预期的工作表保护）。这可以防止通过普通用户界面进行简单的工作表取消隐藏操作，而没有暗示设置了工作簿保护以防止结构更改！
回答by Gary
Tried a modified version of your code - this seemed to work for me:
尝试了您的代码的修改版本 - 这似乎对我有用：
Sub TestSub()
Dim Sheets(4) As Worksheet
Dim x As Worksheet
Dim i As Integer
For Each x In ThisWorkbook.Sheets
Set Sheets(i) = x
i = i + 1
Next
ToggleAllSheets Sheets(), xlSheetVisible
End Sub
Public Sub ToggleAllSheets(ByRef XlSheets() As Worksheet, xlVis As XlSheetVisibility)
Dim xlSheet As Variant
For Each xlSheet In XlSheets
If xlSheet.Visible <> xlVis Then
xlSheet.Visible = xlVis
End If
Next
End Sub
回答by TmTron
I also got this error:
我也收到了这个错误：
Error 1004: Method 'Visible' of object '_Worksheet' failed
In my case the problem was, that the code tried to hide the currently active sheet. So I just had to set ActiveSheetto another (visible) sheet.
就我而言，问题是代码试图隐藏当前活动的工作表。所以我只需要设置ActiveSheet另一个（可见的）工作表。
