什么导致Excel VBA中的错误70？ [英] What causes Error 70 in Excel VBA?

问题描述

我有一些代码不断导致

Error 70: Permission Denied

在我的VBA代码。我不明白为什么，因为我知道工作表是不受保护的，我可以修改它。相关代码是

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

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?

推荐答案

一般来说，这是由于尝试使用相同的名称两次引起的。尝试这样做：

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

这篇关于什么导致Excel VBA中的错误70？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！