Excel macro not working after Windows update - Office 365

[leverage]

After a recent windows update, a previous Excel vba script that was working, no longer functions correctly.

The macro operation, when working, opens a csv file that is defined in the active workbook as String aString. The csv file contains a list of variables and corresponding values for those variables. The macro returns the original active workbook and reads the defined named cells in the active workbook and updates those named cells with the values defined in the csv file.

The issue appears to be that despite returning to the original active workbook the command to generate the For loop to cycle through the named cells no longer returns a value for the variable name or which worksheet the variable lives in.

The macro works perfectly in Excel build Version 1902 (Build 11328.20318)  but does not work in Version 2002 (Build 12527.21416).

The original code was originally written in Excel 2016 and has been functioning perfectly up until the latest company approved version of window 10 was updated.

The code is:

-----------------------------

Public Sub findNamedRange()

 strWorkBook = ActiveWorkbook.Name

Workbooks(strWorkBook).Activate

Dim Nm As Name
Dim varName As String
Dim varSheet As String
Dim varCell As String

For Each Nm In Workbooks(strWorkBook).Names
With Nm
varName = Nm.Name
MsgBox "Named range """ & varName
varSheet = .RefersToRange.Parent.Name
varCell = Nm.RefersToRange.Address(0, 0)
End With
MsgBox "Named range """ & varName & """ refers to range" & vbCr & _
varCell & " on sheet """ & varSheet & """."

Next Nm

End Sub

---------------------------------

If I create a new workbook in Excel Version 2002 (Build... and copy this code into a new module it works perfectly, in terms of finding the named cells, their address and the sheet they are located on. However, If I try to run this module in the Excel workbook created pre- Build Version 2002, it fails with a warning message "Named range  [ _xlfn.ANCHORARRAY ], which I presume means it cannot find any named ranges in the workbook.

[#2 · Accepted solution]

Issue resolved after lots of searching on stackoverflow.

It seems that between Excel V1902 and V2002 a hidden variable _xlfn.SINGLE exists in the workbook. When the macro loops through, it sees the named range, cannot resolve its address or sheet location and stops. The hidden variable was only possible to see after running an additional macro to display hidden named ranges.

The fix was to include a loop to skip the named variable _xlfn* if found. Full detail and solution available on Stackoverflow 

[#3]

Ya encontramos la solución, por si a alguien le interesa, levanté un ticket el área de Soporte Técnico de Microsoft me ayudaron muy pacientemente, aunque es una solución temporal les puede funcionar:

Fuimos a Office Admin Center, en la sección de Configuración de la Organización, Actualizaciones de Office. Ahí cambiamos del canal actual al canal semi anual.

Hicimos la prueba, desintalando Office (versión 2205) y reinstalamos Office (ahora quedó como la versión 2108) . Hicimos las pruebas y ahora se pudo habilitar las macros como se había hecho hasta ahora.

Aquí les comparto información adicional sobre el tema que me brindaron los Ingenieros de Microsoft: https://docs.microsoft.com/es-es/DeployOffice/security/internet-macros-blocked

El problema se presenta debido a la última actualización de Office (versión 2205), al pasar a las actualizaciones en cana semi anual, bajamos una versión y esto nos permitirá continuar utilizando macros sin problema, sólo hay que estar pendientes de que esto se corrija con Microsoft, para quienes utilizan la actualización en canal actual es probable que se presente este problema en automático, por lo que se sugiere cambiarla en tanto se corrige.

Espero les sea de utilidad, ya lo probé y si me funcionó, aunque debemos seguir monitoreando.

Saludos.

[#4]

We just had a Windows Office 365 update yesterday 7/5/2022, and since then a number of my macros corrupt Excel,, where the sheet tabs disappear, or don't work, and no cell or row or column appears to be selectable, etc, only a close and reopen of the workbook corrects it.

I played around with a simple macro, and found the "Application.Screenupdating = False" is the cause, just commenting it out makes the macro work OK.

[#5]

Thanks worked for me..