Excel VBA：运行时错误（对象“范围”的方法“值”失败），但仅限于连续运行

我目前正在处理的 VBA 项目遇到问题，特别是一段代码的运行时错误，该代码查找表格底部的下一个空单元格并将存储的字符串写入该范围

现在 - 该项目的快速解释。我在 Excel 工作表中有一个表格，记录了我工作的公司可能即将出现的每项预期工作。为此，我有一个前端，可以控制创建/查看新的“工作”或“机会”，并且此处运行的代码对信息进行一些意义检查，在网络驱动器上为联系人创建标准化的文件夹结构和合同信息，并为该工作生成一个唯一的 ID，然后用于我们的 CRM 和通信

我的代码似乎第一次运行没有问题（添加新工作）->（创建），但第二次运行时崩溃它会抛出运行时错误“-2147417848（80010108）”：对象“范围”的方法“值”失败。在该行：

r.Value = pFix

Excel（Windows 10 上的 2016）将崩溃并重新启动

Edit:我相信这也许是因为r在第二次运行时未正确存储 - 或者在第一次运行后可能未正确从内存中清除。不过我已经尝试过Set r = Nothing以及我读过的内容here https://stackoverflow.com/questions/19038350/when-should-an-excel-vba-variable-be-killed-or-set-to-nothing表明这无论如何都不是问题

这段代码取自按钮_单击用户窗体上的事件来自新工作（屏幕截图中显示的报名表）

Private Sub CommandButton1_Click()
    Dim pFix As String
    Dim sNew As Long
    Dim jNumber As String
    Dim jName As String
    Dim jIndex As String
    Dim jClient As String
    Dim jSite As String
    Dim jComments As String
    Dim cName As String
    Dim createdDate As Date
    Dim r As Range
    Dim hLink As String
    Dim hLink2 As String
    Dim wDir As String
    Dim msg As String
    Dim ans As String

    Set r = Nothing


    wDir = ActiveWorkbook.Path

    If TextBox1.Value = "" Then
        Call MsgBox("Please enter a valid Requester Name", vbCritical)
        Exit Sub
    Else

        If TextBox2.Value = "" Then
            Call MsgBox("Please enter a valid Client Name", vbCritical)
            Exit Sub
        Else

            If TextBox3.Value = "" Then
                Call MsgBox("Please enter a valid Site Description", vbCritical)
                Exit Sub
            Else

            End If
        End If
    End If

    pFix = "GSM"
    sNew = WorksheetFunction.Max(Columns(2)) + 1
    jNumber = pFix & sNew
    jClient = TextBox2.Value
    jIndex = Left(jClient, 1)
    jSite = TextBox3.Value
    jName = jClient & " - " & jSite
    jComments = TextBox4.Value
    cName = TextBox1.Value
    createdDate = Now


    Set r = Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1)

    r.Value = pFix
    r.Offset(0, 1) = sNew
    r.Offset(0, 2) = jNumber
    r.Offset(0, 3) = jName
    r.Offset(0, 4) = jComments
    r.Offset(0, 5) = createdDate
    r.Offset(0, 6) = cName

    Call MsgBox("New Job Number is: " & jNumber, vbOKOnly)

    On Error Resume Next
    hLink = wDir & "\" & jIndex
    MkDir hLink

    hLink = hLink & "\" & jNumber & " - " & jName
    MkDir hLink

    hLink2 = hLink & "\" & "1. Tender Documents"
    MkDir hLink2

    hLink2 = hLink & "\" & "2. Clarifications and Addendums"
    MkDir hLink2

    hLink2 = hLink & "\" & "3. Client Correspondence and MoU's"
    MkDir hLink2

    hLink2 = hLink & "\" & "4. Technical Queries"
    MkDir hLink2

    hLink2 = hLink & "\" & "5. Document Register"
    MkDir hLink2

    hLink2 = hLink & "\" & "6. Subcontractor and Material Pricing"
    MkDir hLink2

    hLink2 = hLink & "\" & "7. Estimate"
    MkDir hLink2

    hLink2 = hLink & "\" & "8. Photos"
    MkDir hLink2

    hLink2 = hLink & "\" & "9. Tender Submission"
    MkDir hLink2

    hLink2 = hLink & "\" & "10. Drawings"
    MkDir hLink2

    hLink2 = hLink & "\" & "11. Post Tender Correspondence"
    MkDir hLink2



    Unload Me

    'Call filterByJobNumber
    Call copyTable

    msg = "Would you like to open the newly created directory?"
    ans = MsgBox(msg, vbYesNo, "Open Directory?")

    If ans = vbYes Then
        Shell "explorer """ & hLink & "", vbNormalFocus
    Else
    End If


End Sub

事实上，它使 Excel 崩溃，而不仅仅是破坏并让我进行调试，这才是让我困惑的事实——事实上，它肯定会第一次运行，但第二次就会崩溃

Edit:我已经把范围缩小到了r.Value = pFix这是存储的 pFix 字符串写入新范围的点r。弹出一个msgbox(pFix)在这一行显示正确之前pFix存储的是字符串，所以错误一定是在范围内

也许一些新的眼光会发现我忽略的错误 - 并且了解原因将防止以后重蹈覆辙

Edit 2:

我已经做了一些进一步的测试，当将值写入范围时，问题肯定发生在运行的代码的第二个实例上r。我创建了一个小测试来强制崩溃，在下面的代码中，Excel 将锁定并在第二个循环中退出 - 它似乎仅在地址为r每次连续运行之间的变化（行数增加），并且创建的新行位于表格的底部（Excel 会自动将此新行添加到表格范围）。有人可以运行代码并确认他们是否有同样的问题吗？

Sub testMacro()

    Dim r As Range
    Dim str As String

    str = "TEST"
    i = 1

    Do Until i = 5
        Set r = Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1)
        Call MsgBox(r.Address, vbOKOnly, "Range address for 'r' is")
        r = str
        i = i + 1
    Loop


End Sub

我尝试卸载/重新安装并修复 Office 2016 安装作为附加措施，但没有帮助。如果它在其他地方无法重复，也许是 Windows 10 的怪癖？

作为“社区维基”回答，从未回答的问题列表中删除，并且我已在不更改代码的情况下解决了问题。

Office 2016/Excel 2016 安装似乎存在一些根本问题。在进一步的测试中，我确实设法生成了 VBA“内存不足”错误，但在这种特殊情况下，对代码的更改似乎没有产生任何积极影响。

再次删除、重新启动和重新安装 Office（第二次）似乎已解决该问题，而无需对工作簿进行任何其他更改，并且原始 Sub 运行时不会产生错误