首页 文章

在Excel 2007中运行正常的vba代码将无法在Excel 2013中运行

提问于
浏览 次
0

我有一些vba代码，添加用户名和密码来设置用户编辑范围 .
代码在Excel 2007中运行得很漂亮，但是当我尝试在Excel 2013中运行它时，我不断收到运行时错误1004应用程序定义或对象定义错误 . 代码在行集erTimeInputs处停止 . 我相信这是因为变量erTimeInputs的值为nothing，但我不能让它接受一个值 . 我试图将其定义为各种数据类型，但似乎没有任何工作 .

这是我的代码：

Sub AddUserEditRanges()

'Update username and password before running this macro

Dim UserNameArr() As Variant
Dim UserRangeArr()  As Variant
Dim UserPsswdArr() As Variant
Dim v As Variant
Dim LastUserRow As Integer
Dim i As Integer
Dim ws As Worksheet, rng As Range, UserRange As Range, aer As AllowEditRange
Dim UserName As String
Dim Psswd As String
Dim erTimeInputs as Variable

'Find number of users
LastUserRow = Range("A170").End(xlDown).Row

'set arrays
UserNameArr = Range("B168:B" & LastUserRow).Value
UserPsswdArr = Range("C168:C" & LastUserRow).Value
UserRangeArr = Range("D168:D" & LastUserRow).Value

    For v = LBound(UserNameArr) To UBound(UserNameArr)

    UserName = UserNameArr(v, 1)
    UserRange = UserRangeArr(v, 1)
    Psswd = UserPsswdArr(v, 1)

        'Add edit ranges to the worksheets for all users.
       Set erTimeInputs = _
              Worksheets("Week1").Protection.AllowEditRanges.Add(Title:= _
              UserName, Range:=Worksheets("Week1") _
              .Range(UserRange), Password:=Psswd)

        Set erTimeInputs = _
              Worksheets("Week2").Protection.AllowEditRanges.Add(Title:= _
              UserName, Range:=Worksheets("Week2") _
              .Range(UserRange), Password:=Psswd)
    Next


End Sub
excel vba

1 回答

  • 1

    如您所评论，您可以显式声明您的变量为 AllowEditRange 变量类型 .
    现在，只要您分配给它的 Title 不存在就应该没有问题，否则它将抛出运行时错误 .
    我不知道你为什么需要设置或将它分配给变量对象，但如果你想要的只是添加用户名和密码，你可以在不将其分配给变量的情况下进行 .

    For v = LBound(UserNameArr) To UBound(UserNameArr)
    UserName = UserNameArr(v, 1)
    UserRange = UserRangeArr(v, 1)
    Psswd = UserPsswdArr(v, 1)
    'Check if worksheet is unprotected and ready for editing
    If Worksheet("Week1").ProtecContents = True Then Msgbox "Unprotect Sheet first", _
        vbCritical: Exit Sub

    'Add edit ranges to the worksheets for all users.
    If AllowEditExist(UserName, Worksheets("Week1")) Then
        'Delete Existing
        Worksheets("Week1").Protection.AllowEditRanges(UserName).Delete
    End If
        'Add New 
    Worksheets("Week1").Protection.AllowEditRanges.Add Ttle:= _
        UserName, Range:=Worksheets("Week1") _
        .Range(UserRange), Password:=Psswd
Next

    Edit1: 以下例程将检查AllowEditRange是否已存在 .

    Function AllowEditExist(mytitle As String, sh As Worksheet) As Boolean
    Dim a As AllowEditRange: AllowEditExist = False
    For Each a In sh.Protection.AllowEditRanges
        If a.Title = mytitle Then
            AllowEditExist = True
            Exit For
        End If
    Next
End Function

    Important: 您应该在添加之前取消保护工作表，删除AllowEditRanges，否则会生成错误 .

    回复于

相关问题