View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Greg Wilson Greg Wilson is offline
external usenet poster
 
Posts: 747
Default Excel memory and speed when inserting pictures ???

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