Excel Vba, Picturelink, Changes in Excel Version 2310

[vzczc]

I just noticed a breaking change after updating to Version 2310 today (Version 2310 (Build 16924.20124 Click-to-Run))

If you have a picturelink in Sheet1 with the name pictureOne where the formula property is set to "=A1:A10"

In previous versions, you could do the following in vba
Sheets("Sheet1").Pictures("pictureOne").formula=""

You can no longer do that in version 2310 and get an error message when trying to do that.

You can set the formula property to something else but not to "" or vbNullString.

I can not seem to find any documentation to suggest there are any breaking changes to the Excel Object model in this version.

Does anyone have an idea what is going on here?

As for the question, why I would want to do this. The reason is that when running complex vba code that takes a long time to run, Excel is very slow if you have any active picture links in your workbook. In previous versions, you could simply store all picture formula properties, set them all to "", do your refresh and calculations and then simply put the formula back

 

[Dan_W]

What error message do you get?

 

[vzczc]

The error message is "'Unable to set the Formula property of the Picture class"
This worked fine in version 2309, but fails in 2310

Context of the code

Sub ResetPicturesLinksForSheet(sht As String)
Dim pic As Picture
Dim isProtected As Boolean
Dim pCount As Integer
Dim i As Integer

pCount = Sheets(sht).Pictures.count
If pCount > 0 Then
isProtected = Sheets(sht).ProtectContents
If isProtected = True Then
doUnProtect Sheets(sht).name ', "ResetPicturesLinksForSheet"
End If
For i = 1 To pCount
Sheets(sht).Pictures(i).formula = vbNullString 'Fails here with Run-time error '1004': Unable to set the Formula property of the Picture class
Next i
If isProtected = True Then
doProtect Sheets(sht).name ', "ResetPicturesLinksForSheet"
End If
End If
End Sub

 

[Dan_W]

I see what you mean. I'm also using version 2310, and I've just been testing it out (I assume that you're referring to Linked Pictures, say, to a specific range on a worksheet or something?): it seems that running code that sets the Formula property of a linked picture to vbnullstring (or "") will delete the target range you would ordinarily find in the formula property, but does not delete the image as such. Trying it out manually, this is the expected result, and when I run code, this 'works'. If I try and run the same code again, though, it throws the error you describe. If I try and change the formula to a different range after having deleted the formula by replacing it with vbNullString, it again throws the error, whereas changing the range in the formula box manually will in fact work. From what I can see, one the picture's link (via the Formula property) has been removed programmatically, it cannot then be reset to a new range programmatically.

You could always delete and remake the linked picture objects programmatically, and having just tried it out, that works as expected, but I'm not sure if that will work for your purposes.

To be honest, I've never had much of an occasion to used linked pictures (at least, programmatically, that is), so I don't recall how it used to function or what workarounds might ordinarily have been employed, though others might?

Sorry I can't be more helpful.

 

[vzczc]

I have a very simple testfile that illustrates the problem, but there seems no way to upload it here.

If i set the formula to a range that contains no data, it works, it is only when i set the formula property to "" or vbNullString that causes the problem. As I said earlier, I have a quite large workbook, where the picturelinks are used as a sort of dashboard to mulitple ranges in various sheets. It fetches a lot of data and do a lot of calculations in vba. It grinds to an absolute halt if the workbook contains any picturelinks with formula property set to anything. That is why I have been using this solution for many years with no problems. The problem with deleting and recreating the picturelinks in vba, you need to use copy and paste and that is not reliable. It fails randomly, and I have not found a way to do this without copy/paste.

I will try to raise this with Microsoft support, but I have my doubts if they will be able to help me.

 

[Dan_W]

Thanks for the update. Two things come to mind: (1) what do you mean when you say that copy/paste is not reliable? It should be; (2) Do they need to be linked pictures? Those can be computationally very taxing for Excel to maintain - it doesn't surprise me that Excel grinds to a halt in those circumstances.

 

[vzczc]

Another thing I noticed. If the file is stored on OneDrive and AutoSave is on, the picture link seems to be converted from a picturelink to a picture when the document is autosaved. After that has happens, there is no way to reactivate the picturelink, you can set the formula property and it is updated, but it is no longer a picture link.

 

[vzczc]

We worked around the issues related to picture links by removing all picturelinks in other tabs than the one that is currently active (we have code that runs on activation/deactivation of tabs that does this). Changes to data in the current sheet may cause changes to some of the linked ranges, so it is important to keep it active. It is only during refresh that any active picturelinks cause problems. This is probably a result of MIcrosoft doing some changes under the hood in Excel and we would have to revisit the method we used in the past (deleting and creating them using vba copy, paste)

 

[vzczc]

After further investigation, it seems that this issue may be related to windows. My colleague had the problem yesterday (in Windows 10), but was silently updated this morning with a windows update that seems to relate to the Windows Health tool. After that, the formula property could be set to "". I also found, that you do not really need to use VBA to illustrate the issue. Simply copy a range, pasted it as a linked picture. After that, when you click on the object, you can see that the formula points to the range you copied. When windows/Excel is in this state, it is not possible to remove the formula in the formula bar.

 

[vzczc]

Just an update here. It started working again after Windows 11 update KB5032288. So again, probably not related to Office version at all, but to some underlying DLL in windows.