Camera tool is slowing macro

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.

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" <Yokie [email protected]> wrote in message
news:[email protected]...
> 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.

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.
============


"NickHK" wrote:

> 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" <Yokie [email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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" <[email protected]> skrev i melding
news:[email protected]...
> 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.
> ============
>
>
> "NickHK" wrote:
>
>> 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" <Yokie [email protected]> wrote in
>> message
>> news:[email protected]...
>> > 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.
>>
>>
>>

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.

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

"Yokie Cahyadi" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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

"Yokie Cahyadi" <[email protected]> skrev i melding
news:[email protected]...
> 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.
>

Hi Harald (or anyone else )

Could you possibly explain what I need to do to apply this workaround to my macro, I'm new (and getting better) and don't really understand what the workaround is doing.

I have some code on worksheet 1 that won't run due to two pics on worksheet 3 that are linked to graphs (named ranges) on worksheet 11.

I hope someone can help, its taken two days to find this thread, but I'm so happy I nw know what the issue is!

J

Hi Harald (or anyone else )

Could you possibly explain what I need to do to apply this workaround to my macro, I'm new (and getting better) and don't really understand what the workaround is doing.

I have some code on worksheet 1 that won't run due to two pics on worksheet 3 that are linked to graphs (named ranges) on worksheet 11.

I hope someone can help, its taken two days to find this thread, but I'm so happy I nw know what the issue is!

J