Inserting images by VBA is very slow

[Robin121]

Windows10, Office 2013

When inserting .gif's using the VBA-code below, it takes literally minutes to load 200+ images.
I would like to speed that up; any ideas? Thanks.

Dim c As Integer
c = 8
Do Until Cells(c, 9).Value = ""
With ActiveSheet.Pictures.Insert( _
ThisWorkbook.Path & "\Logo's\Landen\allelanden.com\" & Cells(c, 9).Value)
.Top = Cells(c, 10).Top
.Left = Cells(c, 10).Left
.Width = (.Width / .Height) * Cells(c, 10).Height
.Height = Cells(c, 10).Height
End With
c = c + 1
Loop

 

[Robin121]

Windows10, Office 2013

When inserting .gif's using the VBA-code below, it takes literally minutes to load 200+ images.
I would like to speed that up; any ideas? Thanks.

Dim c As Integer
c = 8
Do Until Cells(c, 9).Value = ""
With ActiveSheet.Pictures.Insert( _
ThisWorkbook.Path & "\Logo's\Landen\allelanden.com\" & Cells(c, 9).Value)
.Top = Cells(c, 10).Top
.Left = Cells(c, 10).Left
.Width = (.Width / .Height) * Cells(c, 10).Height
.Height = Cells(c, 10).Height
End With
c = c + 1
Loop

Does it help, for instance, to get the pictures out of the subfolder and store them directly next to the .xlsm-file, in the same folder?

 

[Dan_W]

I don't imagine the location of the image files will make much of a difference, but you may want to consider turning the ScreenUpdating property off at the outset and then on again at the end. You can do this with

Application.ScreenUpdating = False

At the beginning and then

Application.ScreenUpdating = True

At the end. Let us know how it goes.

 

[Robin121]

Thanks for your swift answer! No solution yet, though...takes just as long.

 

[Robin121]

Maybe I spoke a little bit too early...it does save time! Thanks so much.

 

[Dan_W]

Great. That's a relief (LOL).
Out of curiosity, are you able to give a sense of how much time it saves? It would be a useful data point in case anyone else needs to use it in future.

 

[Dan_W]

I should probably have added that there is a tendency for people to forget (or not know in the first place) that screen updating has been turned off - which can become problematic when you encounter an error at some point in the code, and forget that it hasn't been turned back on. In those situations, it can mistakenly appear like Excel has stalled etc. In which case, it is best to include some error handling routines. So, as a basic example, at the outset:

On Error Goto ErrHandler
Application.ScreenUpdating = False

then at the end:

ErrHandler:
Application.ScreenUpdating = True
If Err.Number <> 0 Then MsgBox "Error " & Err.Number & vbNewLine & Err.Description

That way, if there if the VBA code does encounter an error, it will at least make sure to turn the ScreenUpdating property back on and will notify you/the user of the error. Just a thought.

 

[Robin121]

Thanks Dan, I like that idea and it makes the code more complete; I will add it.
Although I still feel the loading of images could go a lot faster, I will gladly post the encountered time difference in my next post.

 

[Robin121]

Come to think of it: what I'd really like toen, is to add a timer function that shows me the elapsed time between start and stop of my function...

 

[Dan_W]

I've posted something like that on this forum before. Bear with me.