Search code examples
excelvbanamed-ranges

Previously working Excel Named Range gives a "Reference isn't valid" and returns error 1004 in a simple macro

I have a couple of files where previously good named ranges stopped working. Running Office 365 for business, 64 bit, Win 11.

The named ranges appear in name manager with the correct cell reference and value. However, "Go To" returns a "Reference isn't valid" and a simple macro to display the named range value returns a 1004 error.

Illustration of Name Manager, Go To and Code error

Sub Test_Range()
   MsgBox(ThisWorkbook.Worksheets("Dashboard").Range("Input_QB").Value)
End Sub

The problem started after 15 Jul. Another user noted a similar problem on microsoft.com and thought it was a bug with a version release (version 2406 build 16.0.17726.20078). A moderator redirected the user to a MS feedback site (stopped the troubleshooting cold). https://answers.microsoft.com/en-us/msoffice/forum/all/i-have-found-a-run-time-bug-error-1004-in/ab6f100a-8c89-4489-9c43-b08a5416f5c9

Things I have tried with no success:

  • Deleting the named range and using the same name to setup a new named range resulted in the same errors
  • Saving, closing, and reopening resulted in the same errors
  • "Sheets("Dashboard").Range("Input_QB").Value" resulted in the same error
  • "Range("Input_QB").Value" resulted in the same error
  • Rebuilding the Sheet and all the named ranges resulted in the same error

Strange Result:

  • Renaming the named range as a different name works ("Input_QBr"), however, restoring the old name ("Input_QB") results in the same 1004 error.

Feedback to Microsoft: Reported as a problem via Help inside of Excel.

Solution

  • The most efficient way (workaround) to handle this is to rename all the named ranges in the Workbook and then update the references in the vba code.

    • Frank Ball's xml answer points to an issue inside Excel (the xml changes are beyond my abilities)
    • rotabor's answer is another workaround to Microsoft's issue (lots of work to employ workaround). MsgBox Names("Input_QB").Value

    If MS provides a response to my feedback, I will update this.