Excel memory and speed when inserting pictures ???

[Greg Wilson]

Hope someone can give me an insight on what's happening here so I can come up
with an elegant fix.

Background:
I have a fairly complex Excel project that allows you to select from a large
number of picture files (100 to 500 typically) and to display a portion of
them at a time on a worksheet. You can scroll through the entire selection by
calling a UF and using a scroll bar. This has to do with photo-mapping large
flat surfaces (e.g. bridge decks) and splicing the photos together so that
you can scroll through the entire survey area. For reasons I won't go into,
existing photo-stitching software is not suitable.

Problem:
The picture scrolling process involves systematic deletion of existing
photos followed by importation of new and appropriately sizing and
positioning the new. Sounds clunky but works quite well with a minor flaw:
After selecting the photo set, when you first start scrolling, there is a
slight delay in inserting the new photos. Loop code is used for this.
However, once a picture has initially been inserted, when you delete it by
scrolling past it and later reinsert it (scroll back to it), the speed is
much superior. It seems as though Excel remembers the photo and/or the
appropriate size.

Insertion Specifics:
The photos are selected using GetOpenFileName and, using configuration
information specified by the user (number of lines of photos by photos per
line), the file names are pasted to a hidden worksheet. The layout of the
names on the worksheet models the photo layout. The picture scrolling process
involves deleting the existing pictures and then importing the new pictures
by refering to the file names on in the hidden worksheet. Size and position
information is handled similarly.

Failed Efforts:
I wrote a procedure that "initiallized" the photos after selecting them.
This used loop code that individually inserted (without sizing and
positioning) and immediately deleted each of the selected photos with screen
updating set to False. This, however, didn't work. I tried another method
that ran through the entire photo set replicating the scrolling process
(delete old, insert new, size new, position new). This did work but was far
too clunky even with screen updating set to False.

Example Code:
I prepared example code for this post but couldn't replicate the problem. I
believe this is because of the relative simplicity of the example code. The
actual project is quite complex and it would take a lot of time to create
example code that replicates the problem. I was hoping to avoid doing this.
I'm just looking for general insights, suggestions; something that might get
me thinking in the right direction.

Much appreciative of any help.

Regards,
Greg

 

[Greg Wilson]

Further to my post, the below code works in correcting the photo scroll issue
discussed. It was tested several times successfully by closing and reopening
the project, then selecting the photos (GetOpenFileName method), running the
InitializePhotos routine, and then scrolling the photo set - i.e. no more
delay in scrolling whatsoever.

Oddly, the location of the photos when running the InitializePhotos routine
must be within the visible range. It repeatedly failed to work when the Left
property of the inserted photos (P.Left) was set to a value outside of view.
It also repeatedly failed to work if the photos were made invisible. It is
obviously also related to the photo size because it also doesn't work if the
size is set to a different value than that selected by the user (Size).

The below code works every time in solving the photo scroll speed issue.
Interested in anyone's insight.

Regards,
Greg

Sub InitializePhotos()
Dim i As Long
Dim W As Single, H As Single
Dim Size As Single
Dim P As Picture

Size = Range("Gen4") 'Named range holding photo size selection
Application.ScreenUpdating = False
For i = LBound(PhotoList) To UBound(PhotoList)
Set P = ws.Pictures.Insert(PhotoList(i))
If W = 0 Then
W = P.Width: H = P.Height 'Get original dimensions
End If
P.Left = 0: P.Top = 0
P.Width = Size
P.Height = H / W * Size 'Preserve proportionality
Next
Application.ScreenUpdating = True
End Sub

 

[DM Unseen]

Greg,

I had something similar creating a list of all Facid's on a worksheet.
What worked (partially) is

- to resize cells so the pictures fit nicely in a cell.
- to set each picture to move and size with cells. (set this as shape
default is the fastest way)
- hide columns/rows with pictures you don't need

This prevents Excel from drawing them, and hence you could have some
speed increase

BTW I would suspect a procedure that updates the picture objects with
new picture files to work faster. I have not investigated this, but I
suspect the overhead would be less. You would need to move the
row/column of pictures leaving the screen area to the opposite side and
update their picture elements with the new pictures. The actual picture
objects would always be the same, and not change, just their image.
This would prevent Excel to constantly destroy and recreate objects.

DM Unseen