Repost: Speed of inserting pictures and Excel memory

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

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 ?

--
Regards,

Juan Pablo González
Excel MVP

"Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
news:49F8C31B-EAA7-4480-9149-929A0D292885@microsoft.com...
> 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,

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



"Juan Pablo González" wrote:

> 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 ?
>
> --
> Regards,
>
> Juan Pablo González
> Excel MVP

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" <GregWilson@discussions.microsoft.com> wrote in message
news:49F8C31B-EAA7-4480-9149-929A0D292885@microsoft.com...
> 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
>

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

"Peter T" wrote:

> 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,

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.

--
Regards,

Juan Pablo González
Excel MVP

"Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
news:C45F52C3-9198-4D0C-BC35-54FAA1F81385@microsoft.com...
> 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
>
>
>
> "Juan Pablo González" wrote:
>
>> 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 ?
>>
>> --
>> Regards,
>>
>> Juan Pablo González
>> Excel MVP
>

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

"Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
news:B51D0EF8-AD71-40D4-AA79-DC5BC39192D4@microsoft.com...
> 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
>
> "Peter T" wrote:
>
> > 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
>

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

"Juan Pablo González" <JuanPablo@MrExcel.com> wrote in message
news:u3ZnW6auFHA.460@TK2MSFTNGP15.phx.gbl...
> 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.
>
> --
> Regards,
>
> Juan Pablo González
> Excel MVP
>
> "Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
> news:C45F52C3-9198-4D0C-BC35-54FAA1F81385@microsoft.com...
> > 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
> >
> >
> >
> > "Juan Pablo González" wrote:
> >
> >> 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 ?
> >>
> >> --
> >> Regards,
> >>
> >> Juan Pablo González
> >> Excel MVP
> >
>
>

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

"Peter T" wrote:

> 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
>
> "Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
> news:B51D0EF8-AD71-40D4-AA79-DC5BC39192D4@microsoft.com...
> > 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
> >
> > "Peter T" wrote:
> >
> > > 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
> >
>
>
>

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

"Peter T" <peter_t@discussions> wrote in message
news:e$K4ifhuFHA.2540@TK2MSFTNGP09.phx.gbl...
> 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
>
> "Juan Pablo González" <JuanPablo@MrExcel.com> wrote in message
> news:u3ZnW6auFHA.460@TK2MSFTNGP15.phx.gbl...
>> 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.
>>
>> --
>> Regards,
>>
>> Juan Pablo González
>> Excel MVP
>>
>> "Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
>> news:C45F52C3-9198-4D0C-BC35-54FAA1F81385@microsoft.com...
>> > 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
>> >
>> >
>> >
>> > "Juan Pablo González" wrote:
>> >
>> >> 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 ?
>> >>
>> >> --
>> >> Regards,
>> >>
>> >> Juan Pablo González
>> >> Excel MVP
>> >
>>
>>
>
>

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


"Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
news:AA7B5B39-AAA7-4CFE-B05E-C2873D7E4E85@microsoft.com...
> 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
>
> "Peter T" wrote:
>
> > 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
> >
> > "Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
> > news:B51D0EF8-AD71-40D4-AA79-DC5BC39192D4@microsoft.com...
> > > 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
> > >
> > > "Peter T" wrote:
> > >
> > > > 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
> > >
> >
> >
> >

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

"Juan Pablo González" <JuanPablo@MrExcel.com> wrote in message
news:OXwBkfnuFHA.1256@TK2MSFTNGP09.phx.gbl...
> 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
>
> "Peter T" <peter_t@discussions> wrote in message
> news:e$K4ifhuFHA.2540@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> > "Juan Pablo González" <JuanPablo@MrExcel.com> wrote in message
> > news:u3ZnW6auFHA.460@TK2MSFTNGP15.phx.gbl...
> >> 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.
> >>
> >> --
> >> Regards,
> >>
> >> Juan Pablo González
> >> Excel MVP
<snip>