excel - VBA loop crashes after 40 or 60 loops

Question

i've been googling my eyes out and i'm very much a novice.

I'm using a macro to run through a list of customer id, filter a pivot with the id, export a sheet as pdf then repeat the loop.

I have finally after several days managed to get it to run for 60 times but about then it restarts excel. I have just increased the pagefile from 2gb to 16gb.

I'm using a do while loop and im trying to set objects = nothing to clean up resources and im saving the file at one point and i also am using DoEvents, set screenupdate to false, set microsoft xps as default printer, print comms to false, all ranges are stores in variables instead of selected, worksheets are also stored in variables instead of being activated. I have tried stepping through, using breakpoints etc... and seeing as it runs 60 times without errors..

My guess is that since it's upped from 40 to 60 before crash that it might have to do with resources being bloated over time.

Removed code as i found out the crashing had nothing to do with the VBA code, but linked images.

Answer 1

Removing linked images in the worksheet that is exported fixed my issue.

I am not sure i fully understood the solution i found, but in rough terms: Apparently the export feature leaves behind a bit or byte in the kernel of the linked image and this accumulates on each pass of the macro, eventually breaking things down.

Unfortunately i only found 1 other similar problem back when i was googling like a mad man, so i cannot link to where i found this information.

I would like to thank all commenters for their time and help back then, i got it all working and it now produces 900-1000 individual PDFs and attached them an email and sends it off.

So in conclusion: If you are exporting a worksheet to pdf with VBA macro, that includes linked images they can cause instability and eventually crash your macro. Try to not link images.

If you need images: I used linked images because i needed them to be dynamic and change according to requirements. I found another way to do this, that inserts appropiate image instead (with a delete function first in case a previous inserted image exists). I did not write that code myself if i remember correctly, but it involves:

• Having my images in a folder.

• Naming each image to match a value in excel.

• Creating a variable in vba that is: imgfile = path & value &
  ".png".

• Then i SET IMG = ws.pictures.insert(imgfile) I then insert this as an img object.

   With img
          .ShapeRange.LockAspectRatio = msoFalse
          .Left = ws.Range("g15").Left
          .Top = ws.Range("g15").Top
          .Width = R.Width
          .Height = R.Height
          .Placement = 1
          .PrintObject = True
           nameofpicture = .Name
   End With
  

before i use this i call a deletepicture sub, be mindful of only deleting the image(s) you need to change.

Sub deletepictures()

Dim pic As Shape
Dim radd As String
radd = Range("g14").Address

For Each pic In Worksheets("Rapport").Shapes        
    If Left(pic.Name, 3) = "Pic" Then pic.Delete    
    Next pic
End Sub

I hope someone else will find this useful, it's a tricky thing to google took me about two full weeks worktime to find useful information about.