VBA循环在40或60次循环后崩溃 [英] VBA loop crashes after 40 or 60 loops

问题描述

我一直在搜寻我的眼睛，我非常新手.

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

我正在使用宏浏览客户ID列表，使用ID过滤数据透视表，将工作表导出为pdf，然后重复循环.

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.

几天后，我终于设法使其运行60次，但是大约之后它重新启动了excel.我刚刚将页面文件从2gb增加到了16gb.

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.

我正在使用do while循环，并且我试图设置对象=清除资源并即时保存文件，而且我也正在使用DoEvents，将screenupdate设置为false，将Microsoft xps设置为默认打印机，将comms打印为false，所有范围都存储在变量中而不是被选择，工作表也存储在变量中而不是被激活.我试过逐步使用断点等...并看到它运行了60次而没有错误..

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

我的猜测是，由于崩溃前它已从40增加到60，所以它可能与资源随着时间的流逝而膨胀.

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.

删除代码，因为我发现崩溃与VBA代码无关，但链接了图像.

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

推荐答案

在导出的工作表中删除链接的图像解决了我的问题.

我不确定我是否完全理解找到的解决方案，但粗略地说:显然，导出功能在链接图像的内核中留下了一个位或一个字节，并且会在宏的每次遍历中累积，最终将其分解.

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.

我要感谢所有评论者的宝贵时间，并在那时为我提供帮助，现在一切正常，它现在可以生成900-1000个单独的PDF，并附上一封电子邮件并发送出去.

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.

因此，在结论中:如果您要使用VBA宏将工作表导出为pdf，其中包括链接的图像，则它们可能会导致不稳定并最终使您的宏崩溃.尝试不链接图像.

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.

命名每个图像以匹配excel中的值.

Naming each image to match a value in excel.

在vba中创建一个变量，即:imgfile = path&值&
" .png".

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

然后我设置IMG = ws.pictures.insert(imgfile)然后插入作为img对象.

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

在使用此功能之前，我将其称为deletepicture子对象，请注意仅删除需要更改的图像.

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.

这篇关于VBA循环在40或60次循环后崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！