Camera tool is slowing macro

[Yokie Cahyadi]

Is this a known issue or bug? I have a file that contains simple macros to
automate repetitive tasks. The macro works fine and very fast until this
month. I added one tab to show few charts which were created using the camera
tool. Strange but true, the same macro now runs very slow and takes a
considerable time to complete. I'm very sure that the problem is not the way
I wrote the macros (if I deleted the new tab, my macro would run very fast as
usual). Note that my macros do not refer to the charts and vice versa. I
believe if you have a worksheet/workbook with charts created using the camera
tool, they will slow your macros.

Please let me know how I can use the camera tool and at the same time, I can
run my macro smoothly and fast.

Thanks in advance.

 

[NickHK]

I would imagine it is because you now have linked pictures that have to
continually update when the data is changed.
The updating is a background operation, but obviously takes time/CPU cycle
to execute, giving less to your macros.

NickHK

 

[Yokie Cahyadi]

Yes, I have linked pictures using the camera tool. I made a testing to prove
that the camera tool is slowing down the macro (or CPU as you said). I
compared the processing time before and after the linked pictures existed as
follows.
- Open a workbook that contains a macro (any macro will do). Run the macro
and observe the processing time;
- Open a second new workbook and use the camera tool to create couple or
more linked pictures;
- With the 2nd workbook still opens, run the same macro above and notice
that it takes longer time to run it now. The difference will be more
noticeable as you increase the numbers of linked pictures.

Is there a workaround to this issue?

Thanks again.
============

 

[Harald Staff]

Hi

Temporarily removing the link (or better, inserting the link only when you
need it and removing it again afterwards) usually helps:

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

HTH. Best wishes Harald

 

[Yokie Cahyadi]

Hi Harald,

Thanks for the codes. It worked well as a workaround solution. I hope
Microsoft will address this issue on the next Excel version. I still can't
believe it that one linked picture in any opened workbook can slow down
noticeably any macro run, even in the different workbook and what really
amazes me the macro has nothing to do with the linked pictures. It seems
there is a memory leak somewhere if you open a workbook that has linked
pictures.

Once again thanks for the workaround solution.

 

[NickHK]

Well, as the picture is linked, Excel has to check if it needs updating on
every .Calculate
If the picture sized, some kind of BitBlt will also be required.
Whilst it seems it could be implemented better, certainly expect some
overhead.

NickHK

 

[Harald Staff]

Glad it helped, thank you for the feedback.
I don't think many people use this feature, so don't get too optimistic.Most
people doesn't even have formulae in their worksheets. And I can understand
why pictures are awfully complicated to handle from a programmer's point of
view. So let us not complain, rather take pride in being among the chosen
few who know how to tame this ancient beast

Best wishes Harald