Repost: Speed of inserting pictures and Excel memory

[Greg Wilson]

If I don't get a successful response I'll assume it is hopeless and move on.
Follwoing is a repost from yesterday.

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 sizing and positioning the new.
Sounds clunky but works quite well with a minor flaw: After selecting the
photo set or after closing and reopening Excel, 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
scroll past it (delete it) and later scroll back to it (reinsert it), the
process is much faster. It seems as though Excel remembers the photo.
Actually, it is apparently the combination of photo and specified size that
is relevant.

Insertion Specifics:
The photos are selected using GetOpenFileName and the file names are pasted
to a hidden worksheet. The configuration (number of lines of photos and
photos per
line) is specified by the user and 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:
Attempts to "initialize" the photos by quickly inserting and then deleting
them (i.e. cycling through the entire series) that failed were as follows:
1) Deleting each photo immediately after inserting before inserting the next
instead of inserting all of them followed by deleting all at once.
2) Conducting the process outside of the visible range in order to hide it.
3) Conducting the process on a separate sheet in order to hide it.
4) Making them invisible.
5) Not applying the correct size (slightly smaller or bigger failed).

Successful Code:
The appended code works extremely well. After "initializing" the photos
using this code there is no delay whatsoever in the scrolling process.
However, as implied by the above failed efforts, it is apparently mandatory
that the process be conducted 'in your face' which is a nuisance. Hopefully
someone can give me an insight so I can create a more elegant solution.

Much appreciative of any help.

Regards,
Greg

Working Code:
Sub InitializePhotos()
Dim i As Long, ii As Long
Dim W As Single, H As Single, Size As Single
Dim P As Picture
Dim ShpCnt As Long
Dim Arr() As Variant
'Pocedure only called if public PhotosInitialized is False
Size = Range("Gen4") 'Named range that stores picture size
ShpCnt = ws.Shapes.Count
Application.ScreenUpdating = False
For i = 1 To UBound(PhotoList)
Set P = ws.Pictures.Insert(PhotoList(i))
If W = 0 Then
W = P.Width: H = P.Height
End If
P.Left = 0: P.Top = 0
P.Width = Size
P.Height = H / W * Size 'Preserve picture proportionality
ReDim Preserve Arr(i)
Arr(i) = i + ShpCnt
Next
DoEvents
Application.ScreenUpdating = True
ws.Unprotect
ws.Shapes.Range(Arr).Delete
ws.Protect
PhotosInitialized = True
End Sub

 

[Juan Pablo González]

I didn't look deep in the code, but I've seen in the past some projects that
have similar issues (being slow the first time). So, could it be possible
to do a "fake" scroll to do that first load, and then the process would
appear normal to the user ?

 

[Greg Wilson]

Juan,

Thanks a million for responding. The InitializePhotos macro that I appended
is simpler and more efficient than forcing a complete scroll becasue it only
adds each selected photo once and then deletes them all at once.

The simulated scrolling process makes it look like the photos at one end
shift out of view while the majority shift over and new ones come into view
at the opposite end. What actually happens is that I delete all of them and
then reinsert most of the same photos back but shift them over relative to
their former positions. I refrain from reinserting those at the end that
appears to shift out of view and insert new ones at the opposite end where it
appears that new photos scroll into view. So, if the object is just to
"introduce" photos to Excel and size them (I call this Initializing), then
this is a very inefficient process.

I failed to mention that, after adding all the photos with the
InitializePhotos macro with screen updating set to False, I have to set it to
True BEFORE deleting them or the macro doesn't work. In other words, it's as
if Excel needs to "see" all of them at the same time with the correct size
before I delete them. However, even with screen updating set to False, you
can still see the photos get added. So it's a nuisance. And I can't hide it -
It must be in view !!!

As would be expected, if I run the macro a second time, since Excel has
already seen the photos, the macro is extremely fast.

I havn't experimented with the new Shapes.AddPicture method. I use the old
Pictures.Insert method instead; the reason being that for the latter, width
and height dimensions are required arguments. I suspect I'm missing something
here because, depending on the camera, pictures have different proportions
(don't assume I have any expertise). So if you specify the dimensions you
thereby specify the proportionality which may be wrong for the particular
camera; and thus the photos will be distorted. Do I have this wrong ?

Any insights much appreciated.

Kind regards,
Greg

 

[Peter T]

Hi Greg,

I have some routines that add large numbers of pictures from file, resized
and placed to suit. Like you, I find the first time in a session of Excel I
add a picture is significantly slower than subsequently. Strangely, normally
(but not always) it's only slow for the first inserted picture assuming all
in the same folder, Unlike you I don't find resizing & placing is relevant
as regards time.

I don't find any reason to use an InitializePhotos routine such as yours.
The "extra" time is same if inserted & deleted to a dummy sheet or as & when
required. Instead I add direct to the active sheet. I don't disable
screenupdating but I ensure I scroll well away both from the active cell
(where the picture will first be inserted) and less importantly away from
where any will be moved after resizing. Then I scroll or "Goto" back.

If I know all the original picture H/W proportions are identical, instead of
insert pictures, sizing & placing, I use the AddPicture method and do all in
one go.

FWIW, to delete ALL pictures on a sheet simply:
..Pictures.Delete

Regards,
Peter T

 

[Greg Wilson]

Thanks Peter. I was using a For Each loop to delete the photos for the
scrolling feature. I like your batch delete method better.

I did a brief experiment with the Shapes.AddPicture method and found it did
not behave the same. Initializing the photos had no affect, but this was only
a very brief experiment.

If I go the route of using Shapes.AddPicture then (apparently) I'll need to
add functionality that allows the user to specify the height/width ratio. In
my case, all the photos for a given survey will be the same size but the size
may change between surveys (i.e. cameras or camera settings may change). I
keep wondering if I'm missing something because it seems everyone should be
bitching about this problem: If you don't know the correct proportionality
for the photos, then they will be distorted if you specify incorrect values
for the width and height arguments. As you may have noticed in my code, I
currently insert the photos using Pictures.Insert and test for the height and
width and obtain the proportionality this way. I then change the size to suit.

Regards,
Greg

 

[Juan Pablo González]

Greg,

You are correct about the Shapes.AddPicture, it is a pain. However, it is
simply "fixable". If you record a macro that selects a shape, and reset it
to its default state, you'll have the required lines to resize the shape to
its original size without distorting it.

I had to switch from the Pictures.Insert because it was failing for no
apparent reason that I could see, and so far I haven't had any other issues
with Shapes.AddPicture, besides having to resize the picture after I insert
it.

 

[Peter T]

Hi Greg,

I don't think AddPicture vs Insert.Picture will make any difference as
regards the main issue that the first time to add/insert is slower. Also as
you say, the first time the picture is ever drawn it can flicker, perhaps
only noticeable with very large image files. Anyway it's two separate delays
that one way or another are not going to be avoided, at least I don't think
so.

If interested I can send a stripped down version of a few things from my xls
image browser.
Insert Next or Previous image in current folder
optionally change size, fade in rate
or
Insert all images from current folder
optionally change size, gap width

change current folder with GetOpenFileName

It does not disable screenupdating nor preload images yet I think reasonably
smooth. What might be of interest to you is all insert & resizing is done
outside the visible range before moving into it.

Regards,
Peter T
email: pmbthornton gmail com

 

[Peter T]

Hi Juan Pablo,

I never thought to use the Reset button in Format Picture / Size, for the
purpose you describe. Good idea.

The same dialog indicates original Height & Width. Seems strange that VBA
does not expose these properties directly, or does it ?

Regards,
Peter T

 

[Greg Wilson]

Thanks for the offer Peter.

Today I revamped a copy of my project to use Shapes.AddPicture instead of
Pictures.Insert. I found that I was mistaken in my comment that it behaved
differently. It in fact behaves exactly the same as far as I can tell.

My project requires me to be able to handle potentially several hundred
photos and to be able to scroll through them with no difficulties. That's why
I use the technique of systematic deletion and reimportation for my scrolling
feature. This way, the file size stays small. However, as I mentioned, the
file names get copied to a hidden worksheet and the layout of the names
models the actual picture layout: X names (photos) long by Y names (photos)
wide. I use nested loops to retrieve the names during the scrolling process
which slows things down explaining why I'm more affected than you. There is
also a lot of code for other features in the project.

The delay isn't that bad but I want the scrolling feature to be really
slick. Imagine scrolling through photos that encompass a complete bridge. You
need to scroll from one end to the other before Excel has seen all the photos
and the problem goes away.

You might want to reconsider the IntializePhotos approach. It only takes a
few seconds to cycle through a couple hundred photos, after which there is
virtually no delay with my scroll feature. It appears to be the case that
unless the photos are within the visible range they don't get drawn which
explains why it doesn't work otherwise. Of course, don't do this unless there
is a need.

I very much appreciate your offer but the functionalities you mention that I
would need have already been created and the project is quite complex. It
would require a great deal of work to revamp. I've adapted your suggestion of
ws.Pictures.Delete instead of using a loop. I also assign macros to the
photos for another situation and use ws.Pictures.OnAction = "XYZ" which batch
assigns the macros.

Best regards,
Greg

 

[Juan Pablo González]

Not directly that I know of, but I think you can get the image properties by
loading it into a IPicture object, if I remember correctly (I think I did
something like that a while ago, can't remember for sure now)

 

[Peter T]

Hi, Greg,

Appreciate the detailed explanation of what you're doing, sounds like an
interesting project.

I use nested loops to retrieve the names during the scrolling process
which slows things down explaining why I'm more affected than you.

I'm surprised looping a string array of 500 would slow things relative to
anything to do with manipulating pictures. Perhaps maintaining an index
system might avoid any looping at all.

But that's in passing, sounds like you've got things pretty well optimized.

Regards,
Peter T

 

[Peter T]

Hi Juan Pablo,

Yes I'm sure that would be a possibility, lots of code though!

I've been meaning to look more into the IPicture object for other reasons,
eg to make low res' / memory thumbnails.

Currently I store the original dimensions just after Insert.Picture, before
resizing.

Regards,
Peter T

Not directly that I know of, but I think you can get the image properties by
loading it into a IPicture object, if I remember correctly (I think I did
something like that a while ago, can't remember for sure now)

--
Regards,

Juan Pablo González
Excel MVP

<snip>