无效的前向引用或对未编译类型的引用 [英] Invalid forward reference, or reference to uncompiled type

问题描述

我目前正在使用代码来更新文件，并循环运行多个工作表.到现在为止，代码运行顺利.在今天的运行中，我遇到运行时错误'-2147319767(80028029)'自动化错误，无效的前向引用或对未编译类型的引用."

I'm currently using a code to update a file with a loop running through multiple sheets. Up until now, the code ran smoothly. On today's run, I encountered "run-time error '-2147319767 (80028029)' Automation error, Invalid forward reference, or reference to uncompiled type."

错误发生在行 Workbooks("Upload.xlsm").Worksheets(branchName).Range("C7").PasteSpecial Paste:= xlPasteValues 上，并在第6次迭代中出现循环.

The error occurs on the line Workbooks("Upload.xlsm").Worksheets(branchName).Range("C7").PasteSpecial Paste:=xlPasteValues and presents itself on the 6th iteration of the loop.

我使用 On Error Resume Next 作为完成该运行的临时措施，因为在那时必须完成该运行.

I used On Error Resume Next as a temporary measure to complete the run as it was imperative to have it done at that time.

完成后，有3个迭代失败(第六，第七和第十次).这三个没有相互关联(即不同的副本源，值等)，但是在其他迭代中，它们具有完全相同的副本源/值，已成功完成.

Upon completion, 3 of the iterations had failed (sixth, seventh and tenth). The three had no correlation to one another (i.e. different copy sources, values, etc) but had other iterations with the exact same copy source/values which completed successfully.

稍后在这些表上运行另一个复制命令会导致相同的错误.我最终不得不删除并重新创建工作表才能解决该错误.

Running another copy command at a later time onto these sheets resulted in the same error. I eventually had to delete and recreate the sheet to resolve the error.

' Uploads file update

fpath = Workbooks("TEG Rates.xlsm").Worksheets("Link List").Range("E3").Value

Workbooks.Open fpath & "Upload.xlsm"

For branchNo = 21 To 37

    branchName = Workbooks("TEG Rates.xlsm").Worksheets("Link List").Range("A" & branchNo).Value
    branchGroup = Workbooks("TEG Rates.xlsm").Worksheets("Link List").Range("B" & branchNo).Value

    ' Copy/Paste Buy & Sell

    Workbooks("TEG Rates.xlsm").Worksheets(branchGroup).Range("D7:G111").Copy
    Workbooks("Upload.xlsm").Worksheets(branchName).Range("C7").PasteSpecial Paste:=xlPasteValues

    For no = 7 To 10

        Workbooks("Upload.xlsm").Worksheets(branchName).Range("D" & no).Value = "=ROUND(100/C" & no & ",6)"

    Next no

    Workbooks("Upload.xlsm").Worksheets(branchName).Range("D14").Value = "=ROUND(100/C14,6)"
    Workbooks("Upload.xlsm").Worksheets(branchName).Range("D15").Value = "=ROUND(10000/C15,4)"

    Workbooks("Upload.xlsm").Worksheets(branchName).Range("D16").Value = "=ROUND(100/C16,6)"
    Workbooks("Upload.xlsm").Worksheets(branchName).Range("D19").Value = "=ROUND(100/C19,6)"

Next branchNo

Workbooks("Upload.xlsm").Close SaveChanges:=True
Application.CutCopyMode = False

虽然我目前可以使用此代码，但我担心的是我的团队在我不在时会遇到此代码.是什么原因引起的，我该怎么做才能防止这种情况的发生?如果需要，我愿意提供这些文件.

While currently I am able to operate this code, my concern is that my team will encounter this whilst I'm away. What could have caused this/what can I do to prevent this from occurring? I'd be willing to provide the files if required.

推荐答案

我的朋友和我遇到了同样的问题.我试图启用"AccessibilitycplAdmin 1.0 type admin"，在工具>下引用，两台计算机均已解决该问题

My friend and I had the same issue. I tried to enable the "AccessibilitycplAdmin 1.0 type admin" under Tools > References, that fixed issue in both computers

这篇关于无效的前向引用或对未编译类型的引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！