ChangeLink locks up Excel completely

[HKafeman]

With reference to this old thread: https://www.excelforum.com/excel-pro...ink-error.html

We have a complex Spreadsheet with external links to Data in other spreadsheets, so has 4 file links.

We have been using this call for several years to change the links to different files on the local C: Drive after checking they exist

ThisWorkbook.ChangeLink OldLink, NewLink

Now randomly it seems, Excel sometimes gets completely stuck at this call.

I currently have Microsoft 365 MSO (version 2401 Build 16.0.17231.20084) 64-bit, running on Windows 10.

The symptoms (strangely it does not happen every time performing the same actions with the same linked files!) are that when our code calls ThisWorkbook.ChangeLink:

Excel is completely stuck and cannot even be killed by Task Manager. The only way to continue is to restart Windows!

At this point another Excel window is opened showing the Home screen where you can select a New "Blank workbook", etc. You can close the additional Excel. BUT it is the original Excel window that is completely stuck!

Also "Svchost . exe (DcomLaunch -p)" is stuck taking about 27% of my CPU (which has 14 Physical and 20 Logical Cores), with this detail shown in Task Manager:

Service Host: DCOM Server Process Launcher (5)

System Events Broker
Power
Plug and Play
DCOM Server Process Launcher
Background Tasks and Infrastructure Service

The "Services and Controller App" is taking ~9% and Excel about ~6%!

A Thread "ChangeLink error" on this Forum from a while ago had a solution to the same issue proposed in Post #10 (from 17th Nov 2020 by carlosrgns) saying Excel goes wrong "If the link is not referenced in the active sheet".

But I do not understand what was meant! - Referring to that post and the code included in it:

Does it mean that you need to make sure that at least one cell in each Worksheet references each external Link?

If I understand the code correctly, it opens the new linked file, then finds the old link and sets up a loop through all the Worksheets in the Workbook.

However, as soon as it manages to activate the Worksheet and then activate the Workbook and call ChangeLink and set UpdateXlsLinkSource to True, with Err = 0, it will exit the SHEET loop!

So I do not see how that prevents ChangeLink from hanging Excel and also Err will surely always be 0 from the "UpdateXlsLinkSource = True" line before it is tested????

Any clarification or pointers anyone can give on this will be much appreciated.

FYI
One of my colleagues thought the issue might be being caused by UDFs (recently added) using Named Ranges internally, rather than being passed all values they need to work. However, I think I have eliminated this as a possible cause.

He has said: "Unhandled errors in UDFs are ignored (the do not trigger run-time errors) and may cause Excel to get confused about the chain of calculations it needs to do.".

He has also seen Run time Error 10 "This array is fixed or temporarily locked" which he thinks also might be related to UDFs and/or RedDim statements!?! - However "Debug" then highlights a line which is 2 call stack levels above a call to ChangeLink with no ReDim statements anywhere in that code!?! - All very confusing.

Thanks
Henry