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.

Answer 1

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

I was having the same error caused by an issue that was potentially related, and it was helpful to create a worksheet object rather than referencing a sheet within a workbook all in one step. So, instead of:

Workbooks("Upload.xlsm").Worksheets(branchName).Range("C7").PasteSpecial

You could instead try:

Dim xlWB as Excel.Workbook 
Dim xlWS as Excel.Worksheet
Dim xlRange as Excel.Range
Set xlWB = Workbooks("Upload.xlsm")
Set xlWS = xlWB.Worksheets(branchname)
Set xlRange = xlWS.Range("C7")
xlRange.PasteSpecial Paste:=xlPasteValues

Sometimes it's beneficial to break up the steps, even though it is doing the same thing.

Answer 4

The only way I found to solve it was:

• Change the property "Load To" for all query results in the damaged Sheet to "Only Create Connection".
• Make a copy of the Sheet
• Delete the damaged Sheet
• Rename the just created Sheet to the name of the damaged one
• Change all the query results to the new Sheet in the same original location

I hope this helps.

Answer 5

I had a very similar issue, I have several similar excel files, all with the same code. The code ran in all excels but one, with the same error:

"run-time error '-2147319767 (80028029)' Automation error, Invalid forward reference, or reference to uncompiled type."

I was able to solve the problem by saving the corrupt excel as .xlsx instead of .xlsm, closing all excel applications, reopen the .xlsx, add the code and save as .xlsm. Now it is working...

Answer 6

Exactly same thing happened to me. But with a little bit more serious consequences as my dashboard was already in production. It was really shocking as code as running smoothly just 5 mins earlier.

Here's the changes that I made -

1. Conditional formatting
2. Pivot table updates - major formatting updates

I believe some weird combination of conditional formatting over pivot table might have caused this issue.

One peculiar symptom associated with this corrupted workbook/sheet as that I was unable to see any macro in the dialogue while I was trying to assign a macro to the button. The dialogue box was empty ! not listing any macros from any module/form/worksheet.

Tried -

1. Removing pivot table
2. Resetting formatting
3. Removing conditional formatting
4. Restarting excel
5. Restarting system

None of the above worked !

Next solution, as suggested by Horacio, is to duplicate and delete the corrupted sheet. This immediately solved the issue.

Answer 7

Similar problem - same error. The sheet is the results of a query "Load To" table. Followed the idea from https://stackoverflow.com/users/13737858/horacio-cano. Changed "Load to" from table to Connection only; deleted the sheet; changed "Load to" to new table; refreshed query. Macro runs without error.

Answer 8

I solved this by removing the reference to Microsoft Scripting Runtime in Tools>References selecting "Ok" and then re-adding it again.