tribulusterrestris
New Member
- Joined
- Mar 5, 2015
- Messages
- 7
Hi, I recently solved an undercovered issue.
If your workbook has some picture created with Camera Tool the VBA performance slowdown enormously.
Bottom line is: as long as your picture is dynamic, vba will suffer when changing cells, because it scans all the workbook for changes that can affect the linked picture.
I found 2 approaches to solve:
1) Setup a named range to enable or disable the update (I failed making this work on my brazilian portuguese version of excel, with localized syntax)
Link: Daily Dose of Excel » Blog Archive » Performance of linked pictures
2) Disable the update when needed
Link: Camera tool is slowing macro [SOLVED]
And then I came up with a third solution, inspired by #2
3) Force a refresh only when needed.
Formula is stored on AlternativeText attribute
Procedure:
Use:
What your thoughts?
If your workbook has some picture created with Camera Tool the VBA performance slowdown enormously.
Bottom line is: as long as your picture is dynamic, vba will suffer when changing cells, because it scans all the workbook for changes that can affect the linked picture.
I found 2 approaches to solve:
1) Setup a named range to enable or disable the update (I failed making this work on my brazilian portuguese version of excel, with localized syntax)
Link: Daily Dose of Excel » Blog Archive » Performance of linked pictures
2) Disable the update when needed
Link: Camera tool is slowing macro [SOLVED]
Code:
Sub Test()
Dim S As String
S = Sheets(2).Pictures(1).Formula
Sheets(2).Pictures(1).Formula = ""
'your code here
Sheets(2).Pictures(1).Formula = S
End Sub
And then I came up with a third solution, inspired by #2
3) Force a refresh only when needed.
Formula is stored on AlternativeText attribute
Procedure:
Code:
Sub initCamPicShape(ByRef oCamPic As Object) 'stores formula into alternativetext
oCamPic.ShapeRange.AlternativeText = oCamPic.Formula 'need to be done just when create a new picture
oCamPic.Formula = ""
End Sub
Sub refreshCamPic(ByRef oCamPic)
If (oCamPic.ShapeRange.AlternativeText = "") Then
initCamPicShape (oCamPic)
Else
oCamPic.Formula = Trim(oCamPic.ShapeRange.AlternativeText)
DoEvents
oCamPic.Formula = ""
End If
End Sub
Use:
Code:
refreshCamPic (Sheets("sheetName").Pictures("Picture 62"))
What your thoughts?