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]

Thank you the update!

 

[vzczc]

This issue is very hard to understand. Now the problem is back, without any new windows updates or updates to Excel. The same happens on other computers. It seems that something triggers the computer to get in and out of this state. I have raised the issue with MS support 3 weeks ago, but they have yet to get back to me.

 

[davesyd1970]

Hi all,
I have exactly the same issue and am using it for exactly the same reason (linked pasted images are very slow. To update the image, you point the image formula to a range and then clear it to prevent speed issues).

I am on Windows 10 and 2310 when this issue was observed. I have just done a full windows update and i have also updated Excel so i am just of now on 2311. None of this has solved the issue - the error still pertains (same as the error in the article).

Just wondering if it has been solved and if anyone has any workarounds (prefer to not delete and then repast the image every time).

Thanks

 

[davesyd1970]

Addendum to the above:
I am using a slightly different VBA (and it has been working for years on various machines and servers):

Set shp = FRI.Shapes("IMG_Title")
shp.OLEFormat.Object.Formula = "=TTL_Title"
DoEvents
shp.OLEFormat.Object.Formula = ""

The error i get is as follow:
"Error #1004 - Unable to set the formula property of the picture class"

It is not the VBA. As noted previously, I can copy a range and then past special as a linked image. But I can now no longer delete the formula of the image directly in Excel, whereas before I could.

 

[vzczc]

I have not found a way around this issue, other than deleting all pictures with formulas before doing refresh, then recreate using copy/paste link after. I have code that detects if excel/windows is in this state and do either the old way (which has worked for 10 years) or the delete/recreate. Another problem I have notices is that when using copy/paste, sometimes the paste fails so I have had to implement redo logic around that. Very messy, but as things stand now, there does not seem to be another way.

 

[Dan_W]

The pasting fails because the clipboard runs at a different speed to other programs. VBA often runs too fast for Excel and the clipboard to keep up, and so these errors occurs.

Its extremely difficult to speak in the abstract about these things - do you have a demo workbook with code with a demo routine in it that 'should' work but doesn't?

 

[vzczc]

Sure, I have not found a way to upload a file here, but placed a copy here

Microsoft OneDrive

1drv.ms

 

[Dan_W]

Sure, I have not found a way to upload a file here, but placed a copy here

Microsoft OneDrive

1drv.ms

Thank you for this vzczc, this is very helpful. I see what you mean.

As regards the issues you're having with copying the range, I encountered an issue if the linked picture was already deleted - it threw an error on:

Sheets("Sheet1").Pictures("ThePicture").Delete

which is to be expected. One easy solution to that is to just use On Error Resume Next for the limited purpose of deleting the picture if it exists with:

Sub deletePicture()
    On Error Resume Next
    Sheets("Sheet1").Pictures("ThePicture").Delete
End Sub

and then just calling the deletePicture sub instead of that earlier line above.

If you encounter issues with the copying/pasting of the image, then as explained in an earlier reply, that's likely owing to the different speeds that VBA, Excel and the Clipboard all run at. The easiest solution in those circumstances is to get VBA to pause between the copying and the pasting of the contents; so, after the line with the Copy command, you can call the following Pause function (for example) with Pause 2 (for 2 seconds):

Sub Pause(Optional ByVal Period As Single = 1)
    Period = Period + Timer
    Do
        DoEvents
    Loop While Period > Timer
End Sub

One interesting point I'd note here is that when I was stepping through the code setting the formula to various ranges, something I found was that after setting the formula of the Picture obejct to a given range, you can observe in the Locals window that the formula property now has the set range BUT ALSO a space at the end of it - which is odd. I don't know if it's particularly material, but it is odd, and supports the theory that there is some bug at play somewhere...

 

[vzczc]

What I uploaded was not meant to be functional, just to illustrate the point.

The code that actually runs is something like this (in case it is useful to anyone)

Sub TestPictureLinkUpdate()    
    Dim top as long     'Top position
    Dim left as long    'Left position 
    Dim sht as string
    Dim sourceRangeName as string
    Dim formula as string 
    Dim pictureLinkScaleFactor as double
    dim name as string
    'Normally this runs as a part of a larger solutuion, screenupdating, enableevents are ff and calculation is set to manual and all sheets are unprotected
    'Code has already run to ensure picturelinks have been removed if Excel/computer is in the state where setting formula to "" does not work

    sourceRangeName="source" 'name of the range to use as a basis for picturelink
    formula="=source"
    sht="Sheet1"
    top=10
    left=10
    name="PictureOfSource"
    pictureLinkScaleFactor=1.0 'This is used to scale the image up or down if required

    UpdatePictureFormulaAndPlacement sht, name, formula, sourceRange, pictureLinkScaleFactor, top, left


End Sub

Sub UpdatePictureFormulaAndPlacement(sht As String, name As String, formula As String, sourceRangeName As String, scaleValue As Double, top As Long, left As Long)
    Dim retCode As Long
    If DoesPictureLinkExist(sht, name) = False Then
        AddPictureLinkAndFormat sht, name, sourceRangeName, formula, scaleValue, top, left
    Else
        retCode = PlacePictureLinkFormula(sht, name, formula, scaleValue, top, left)
        If retCode <> RC_OK Then
            MsgBox "Could not place picturelink"
            retCode = RC_OK
        End If
    End If
End Sub

Function PlacePictureLinkFormula(sht As String, name As String, formula As String, scaleValue As Double, top As Long, left As Long) As Long
    Dim retCode As Long
    On Error GoTo errorHandler

    With ActiveWorkbook.Sheets(sht).Pictures(name)
        .top = top
        .left = left
        .Placement = xlFreeFloating 
        .ShapeRange.ScaleWidth scaleValue, msoTrue
        .ShapeRange.ScaleHeight scaleValue, msoTrue
        .formula = formula
    End With
    PlacePictureLinkFormula = 0
    Exit Function

errorHandler:
    On Error GoTo 0
    PlacePictureLinkFormula = -1
End Function

Sub AddPictureLinkAndFormat(sht As String, name As String, sourceRangeName As String, formula As String, scaleValue As Double, top As Long, left As Long)
    Dim sourceSheetName As String
    Dim retryCount As Long
    Dim pic As Picture
    Dim pasteOk As Boolean
    Application.CutCopyMode = False
    Range(sourceRangeName).Copy
    
    retryCount = 0
    Do
        pasteOk = PastePictureLinkSafe(Worksheets(sht), pic)
        If pasteOk Then Exit Do
        retryCount = retryCount + 1
        Sleep (200)
        DoEvents
    Loop Until retryCount > 50

    If retryCount > 0 Then
        Debug.Print "AddPictureLinkAndFormatNew, retryCount=" & retryCount
    End If

    If pic Is Nothing Then
        Debug.Print "No pic"
    Else
        With pic
            .name = name
            .ShapeRange.ScaleWidth scaleValue, msoFalse
            .ShapeRange.ScaleHeight scaleValue, msoFalse
            .top = top
            .Placement = xlFreeFloating
            .left = left
            .formula = formula
        End With
    End If

    Application.CutCopyMode = False
End Sub

Function DoesPictureLinkExist(sht As String, name As String) As Boolean
    Dim plName As String
    On Error GoTo errHandle
    plName = ActiveWorkbook.Sheets(sht).Pictures(name).name
    On Error GoTo 0
    DoesPictureLinkExist = True
    Exit Function
errHandle:
    DoesPictureLinkExist = False
End Function


Function PastePictureLinkSafe(sht As Worksheet, pic As Picture) As Boolean
    On Error Resume Next
    Set pic = sht.Pictures.Paste(Link:=True)

    If Err.Number = 0 Then
        PastePictureLinkSafe = True
    Else
        PastePictureLinkSafe = False
    End If

    On Error GoTo -1
End Function

 

[Dan_W]

The workbook you uploaded was functional for me, though.