[Fixed!] Invalid Forward Reference in VBA

There are several possible causes of an Invalid forward reference error in VBA in Excel, so it can often be tricky to identify the appropriate method to fix the error.

Here are 4 possible solutions that may solve the problem.

Method 1 – Enabling AccessibilitycplAdmin 1.0 Type Admin

Steps:

In VBA Editor, click the Tools tab, then References from the drop-down menu.

Check AccessibilitycplAdmin 1.0 Type Library
Click OK.

Re-run your code to check if the error is solved.

Method 2 – Making a Copy of the Reference Sheet

Sometimes the Invalid forward reference error may occur due to improper selection of a worksheet. If this is the case, the problem can be resolved as follows:

Steps:

Copy the original sheet that you want to select.
Delete the original sheet.
Rename the newly copied sheet with the original sheet name.
Then select this sheet.

Hence, instead of using sheetName.Select, use the following code:

    sheetName = "Sheet1" ‘Change the sheet name according to your need.
    Sheets(sheetName).Copy After:=Sheets(Sheets.Count)
    Application.DisplayAlerts = False
    Sheets(sheetName).Delete
    Application.DisplayAlerts = True
    ActiveSheet.Name = sheetName
    ActiveSheet.Select

How Does the Code Work?

sheetName = "Sheet1"

The name of the sheet that we want to select is stored in a string type variable named sheetName.

Sheets(sheetName).Copy After:=Sheets(Sheets.Count)

The worksheet is copied, and its position is set to the end.

DisplayAlerts = False

The notification alert is turned off.

Sheets(sheetName).Delete

The old sheet is deleted.

DisplayAlerts = True

The notification alert is turned on again.

Name = sheetName

The copied sheet is renamed with the name of the original sheet (sheetName).

Select

The copied sheet (currently active) is selected.

Run the code to check if the error is solved.

Method 3 – Create a Worksheet Object Rather Than Referencing a Sheet

For example, instead of typing:

Workbooks("Sample.xlsm").Worksheets(Sheet2).Range("B2").PasteSpecial

Type:

Dim xl_WB As Excel.Workbook
Dim xl_WS As Excel.Worksheet
Dim xl_Range As Excel.Range
Set xl_WB = Workbooks("Sample.xlsm")
Set xl_WS = xl_WB.Worksheets(Sheet2)
Set xl_Range = xl_WS.Range("B2")
xl_Range.PasteSpecial Paste:=xlPasteValues

How Does the Code Work?

We explicitly declare the workbook, worksheet, and range type variables. Then we assign corresponding values to those variables. Finally, we apply the Paste Special method.

Run the code to check if the error is solved.

Method 4 – Saving a Corrupted Excel File as .xlsm Instead of .xlsx

Sometimes the main Excel file can become corrupted, which can cause an invalid forward reference error. In this case, the following process may resolve the error:

Steps:

Save the file as an .xlsx.
Close the file.
Re-open it.
Add the code.
Save the file as an .xlsm.
Re-run the code to see if the error is solved.

Things to Remember

It is difficult to predict which strategy will be suitable for each specific case, so try all of the provided solutions one by one until the error is resolved.

Download Practice Workbook

Invalid Forward Reference.xlsm