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.