是什么导致 Excel VBA 中出现错误 70？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/948950/
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
What causes Error 70 in Excel VBA?
提问by a_m0d
I have some code which keeps causing an
我有一些代码不断导致
Error 70: Permission Denied
in my VBA code. I can't work out why, because I know that the worksheet is unprotected and that I can make changes to it. The code in question is
在我的 VBA 代码中。我不知道为什么，因为我知道工作表不受保护，我可以对其进行更改。有问题的代码是
sh.Name = "square"
It attempts to rename a shape that has been copied from another sheet and pasted into the sheet - there are no other shapes in the sheet with that name, because prior to these code I have already deleted all shapes with that name.
它尝试重命名从另一个工作表复制并粘贴到工作表中的形状 - 工作表中没有具有该名称的其他形状，因为在这些代码之前，我已经删除了具有该名称的所有形状。
Any suggestion as to what might cause this permissions error?
关于可能导致此权限错误的任何建议？
回答by Oorang
Generally that one is caused by trying to use the same name twice. Try doing this instead:
通常，这是由于尝试使用相同的名称两次造成的。尝试这样做：
Sub Example()
Dim lngIndx As Long
Dim ws As Excel.Worksheet
Dim shp As Excel.Shape
Set ws = Excel.ActiveSheet
Set shp = ws.Shapes.AddShape(msoShapeOval, 174#, 94.5, 207#, 191.25)
If NameUsed(ws, "Foo") Then
lngIndx = 2
Do While NameUsed(ws, "Foo" & CStr(lngIndx))
lngIndx = lngIndx + 1
Loop
shp.name = "Foo" & CStr(lngIndx)
Else
shp.name = "Foo"
End If
End Sub
Private Function NameUsed(ByVal parent As Excel.Worksheet, ByVal name As String) As Boolean
Dim shp As Excel.Shape
Dim blnRtnVal As Boolean
name = LCase$(name)
For Each shp In parent.Shapes
If LCase$(shp.name) = name Then
blnRtnVal = True
Exit For
End If
Next
NameUsed = blnRtnVal
End Function
回答by Harryd
Clean as you go. Set objects to nothing, strings to nullstring after using them and don't use the same names between functions and subroutines.
边走边打扫。使用后将对象设置为空，字符串设置为空字符串，并且不要在函数和子程序之间使用相同的名称。
回答by David Salzer
"Permission Denied" is not for a protected worksheet but for wrong access to a property or variable.
“权限被拒绝”不是针对受保护的工作表，而是针对对属性或变量的错误访问。
I believe that "sh" is null at the point you are trying to access it and set its "Name" property. try to see if you initialized it correctly before setting its properties.
我相信“sh”在您尝试访问它并设置其“Name”属性时为空。在设置其属性之前尝试查看您是否正确初始化了它。
回答by KingsInnerSoul
There are several answers here on StackOverflow about this VB Error. Each answer or situation is unique in reality - although each existing answer states a different potential root cause (file permissions, folder permissions, name reuse, ranges, etc).
StackOverflow 上有几个关于这个 VB 错误的答案。每个答案或情况在现实中都是独一无二的 - 尽管每个现有答案都说明了不同的潜在根本原因（文件权限、文件夹权限、名称重用、范围等）。
I would recommend narrowing down the root-cause by double clicking on the side of the stating function/code in order to mark a breakpoinnt (looks like a red dot) (Alternatively, you can right click on the line of the code - Select the Toggleand then Breakpoint).
我建议通过双击声明函数/代码的一侧来缩小根本原因以标记断点（看起来像一个红点）（或者，您可以右键单击代码行 - 选择Toggle然后Breakpoint）。
Next, run your code, and it will stop in your breakpoint. You can then Step-Into/Over/Out your code and essentially find the line of code that is responsible for throwing your error code. (Step Into is F8, Step over is Shift+F8((Go To the Debugtop menu to see more options)))
接下来，运行您的代码，它将在您的断点处停止。然后，您可以单步执行/跳过/退出您的代码，并基本上找到负责抛出错误代码的代码行。(Step Into is F8, Step over is Shift+F8((转到Debug顶部菜单查看更多选项)))
Once you identified the responsible line of code - you can start looking further.
一旦确定了负责的代码行 - 您就可以开始进一步查找。
In my case scenario, I was using a protected variable name "Date" (look into variable names). Once I renamed it to something else, the problem was fixed.
在我的情况下，我使用了受保护的变量名称“Date”（查看变量名称）。一旦我将其重命名为其他内容，问题就解决了。
