VBA codes using Named Ranges is not working since migration to O365

Question

We recently migrated to O365 and we have several spreadsheets that are legacy items that transpose information from one sheet to another (info from input sheet to a table that is on another sheet for upload and is reconfigured to a different format) .

Up until last week all of them were working for everyone perfectly but now named ranges are causing this error message for some users but not all which seems odd. To our knowledge nothing was updated last week other than we did a server restart.

We have reviewed Macro security codes to align the users having trouble with the ones that are not having trouble. This has not made a difference unless there are other macro alignments not directly in excel that need to be done. The error message and code that it is getting stuck on is below:

Sub SummaryFillOut()

Range("DocType").value = DocType

Range("ShipTo").value = ShipTo

Range("CurrentStatus").value = CurrentStatus

Range("DocDate").value = DocDate

Range("InvcContact").value = InvcContact

Range("InvcDesc").value = InvcDesc

If Doc = 0 Then

    Range("Doc").value = "New"

Else

    Range("Doc").value = Doc

End If

**Range("Rev").value = Rev**

Range("PartnerCode").value = PartnerCode

If DueDate = 0 Then

    Range("DueDate").value = ""

Else

    Range("DueDate").value = DueDate

End If

Range("TermCode").value = TermCode

If Range("TermCode").value <> Range("TermsAccpacCode").value Then

    Range("TermCode").value = Range("TermsAccpacCode").value

End If



Range("InvcOther").value = InvcOther

Range("InvcSpecInst").value = InvcSpecInst



Call SummaryFillOutFormulas
End Sub

The other ranges are not causing an issue just the one bolded above relating to named code "Rev" in the same workbook which throws the error message.  The value of this range is "0" in most cases but it can have a value if a revision occurs on an existing document.  

Compared to the expertise online, I understand VBA and have problem solved successfully before but this does not seem to be consistent other than some ranges don't work for some users on some spreadsheets so any help with the debugging of this would be appreciated!

Thanks in advance for your review of this item.  JF

Answer 1

I have noticed a number of named range references in VBA suddenly causing errors in the last few weeks as well. The named range appears normal in the name editor but cannot be referenced in VBA. Even deleting the named range and recreating it with the same name still fails. Deleting it and recreating it with a different name does work though. However deleting a named range is not a great solution if it's also referenced in formulas in the workbook, as that is going to break them all.

In addition we also seem to have found an issue where named range references inside of an Excel formulas itself that was being used as a conditional check is functionally silently failing. Evaluation of the formula shows the error, but it's otherwise not obvious because the formulate still "functions" while always defaults to the fallback state of the conditional check.

Clearly something has gone very wrong with named ranges in a recent Excel update, which seems like a kind of huge deal to break.

Going to look into the ability to rollback to an earlier version here as well...