Invalid Forward Reference, or Reference to Uncompiled Type

Question

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."

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.

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.

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.

Accepted Answer

My friend and I had the same issue. I enabled the "AccessibilitycplAdmin 1.0 type admin" under Tools > References (within the VBA editor), that fixed the issue in both computers

Answer 2

I had the same issue with a macro today.

Noticed that the error popped up when selecting a sheet using Sheets(sheet_name_var).Select.

A workaround that I've found is:
- Make a copy of the sheet the macro was having problems with,
- Delete the original sheet,
- Rename the copy to the original name.

Hope this helps.

Answer 3

Must Read

Explanation of Top Answer

The top answer's proposed solution of adding a VBA library reference works because it forces the file to recompile its vba libraries, effectively reestablishing the proper order to run macros against various objects in the application.

Note:

It does not matter what VBA library reference you check -- there's nothing special about the accessibility libraries.
You don't have to keep the library enabled, in fact, it would be best to remove the library after confirming the issue is resolved. Keeping it enabled does nothing while adding complexity to the file's vba library which was ultimately the root cause of this issue in the first place.
I almost always lose my double click event when this happens, which serves as a good indicator (obviously with Application.Events=True)

Invalid Forward Reference, or Reference to Uncompiled Type

12 Answers

Must Read

Explanation of Top Answer

Elena Rostova