运行代码时挂起 [英] Hangs when running a code

问题描述

这里我正在寻找一种适用的方法...

Here I'm looking for an applicable approach...

如何将正在运行的VBA过程发送到后台以防止工作簿挂起，直到VBA语句停止运行结束。

How can send the running VBA procedures to background for prevent workbook hangs until the VBA statements running are finished.

运行VBA过程时挂起...

Hangs when running a VBA procedure...

指出 Document.PrintOut 内置过程具有 Background 参数，当Microsoft Word打印文档时，该参数具有宏继续。

Point that the Document.PrintOut built-in procedure has Background argument which have the macro continue while Microsoft Word prints the document.

运行时，Excel 挂起。发送以下截图代码的 c 程序

The Excel Hangs while running .Send procedure of below snip code

Dim iMsg As Object
Dim iConf As Object

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

With iMsg
    Set .Configuration = iConf
    .To = strTo
    .ReplyTo = "Email Address"
    .From = """Sender Name (Email Address)"" <EmailAddress>"
    .Subject = strSubject
    .TextBody = strBody
    .AddAttachment ThisWorkbook.Path & "\Temp\" & ThisWorkbook.Name
    .Send
End With

Excel 挂起而用于下一个在下面的代码中循环：

Excel Hangs while For Next loop in below code:

Sub PrintIt()

Dim objWord As Word.Application
Dim objDocTotal As Word.Document
Dim objDoc As Word.Document
Dim i As Integer
Dim strOutfile As String
Dim rg As Word.Range

    ActiveSheet.OLEObjects("SalaryPaycheck").Activate
    Set objWord = GetObject(, "Word.Application")
    objWord.Visible = False
    Set objDoc = objWord.ActiveDocument
    Set objDocTotal = Documents.Add
    objWord.Application.DisplayAlerts = wdAlertsNone
    objWord.Application.ScreenUpdating = False

    For i = worksheetfunction.Min(range("Table1[Column1]") To _
        worksheetfunction.Max(range("Table1[Column1]")

        Range("Key").Value = i

        With objDoc
            .Fields.Update
            .Content.Copy
        End With

        Set rg = objDocTotal.Content
        With rg
            .Collapse Direction:=wdCollapseEnd
            If i > 1 Then .InsertBreak wdPageBreak
            .PasteAndFormat wdFormatOriginalFormatting
        End With
    Next i


    strOutfile = "<Path>\Salary.pdf"

    objDocTotal.ExportAsFixedFormat outputfileName:= _
                                    strOutfile, exportformat:=wdExportFormatPDF, _
                                    openafterexport:=False, optimizefor:=wdExportOptimizeForPrint, Range:= _
                                    wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent



    objDocTotal.Close False
    objWord.Quit
    Set objDoc = Nothing
    Set objWord = Nothing

End Sub

与上述情况相同CPU利用率大约低于40％！

As in above cases the average of CPU Utilization is approximately lower than 40%!

我们从Excel 2007中知道支持多线程（ support.microsoft。）

And we know from Excel 2007 the multi-threaded is supported (support.microsoft.)

为什么在运行某些代码（如上例）时Excel挂起，直到它们运行是终止还是结束？

Why Excel hangs when running some codes (as exampled above) while the codes running, up to they have terminated or end?

如何防止工作簿挂在上方，与背景的论点相同能否在MS-Word中进行 PrintOut 过程的功能？

How can prevent Workbooks hanging in above same as the Background's argument ability for PrintOut procedure in MS-Word which said at top of here?

请注意。

推荐答案

让我总结一下您的问题，并尝试使您尽可能清楚。.

Let me summarize your questions and try to make things as clear as possible to you..

由于Excel启用了多线程处理，为什么不能在多个线程上运行VBA代码？

Since multi-threaded processing is enabled for Excel, why can't I run my VBA code on multiple threads?

因为VBA不支持多线程。所有VBA代码都在一个线程（主线程）上运行。

Because VBA doesn't support multi-threading. All VBA code runs on one thread (the main thread).

好，我明白了。现在，既然Excel本身是多线程的，为什么在运行VBA代码时冻结它？

Okay, I get that. Now since Excel itself is multi-threaded, why does it freeze while the VBA code is running?

好吧，VBA代码运行在主线程。主线程还用于显示Excel GUI 和执行其他操作。现在，当您的VBA代码运行时，它将阻塞主线程，从而冻结。阅读本文：在Excel中进行多线程重新计算以了解有关确切运行的更多信息在多个线程上：

Well, VBA code runs on the main thread. The main thread is also used to display the Excel GUI and do other things. Now, when your VBA code is running, it blocks the main thread, hence the freezing. Read this article: Multithreaded Recalculation in Excel for more about what exactly runs on multiple threads:

同样，只要您具有VBA代码，工作簿就会 始终 挂起需要一些时间来处理运行。您可以通过编写以下简单的代码来进行测试：

So again, The workbook will always hang whenever you have VBA code that's taking some time to process running. You can test that by writing code as simple as:

For i = 1 To 100000
    Debug.Print (i)
Next

..观察Excel冻结直到循环结束。

..and watch Excel freezes until the loop finishes.

那么，如何防止工作簿冻结？

So, how can I prevent the workbook from freezing? Is there no way to achieve this?

嗯，您提出了两种情况：

Well, you proposed two cases:

1. For 循环：

1. The For loop:

您可以通过添加 DoEvents 。 DoEvents 的作用是，它使
产生了执行代码的权限，从而可以处理其他
条消息。因此，先前的代码看起来像是下面的
：

You might use a workaround for this by adding DoEvents inside the loop. What DoEvents does is that it yields the execution of your code to allow processing other messages. So, the previous code would look something like the following:

For i = 1 To 100000
    Debug.Print (i)
    DoEvents
Next

但是，这并不是真的允许用户在代码运行时对工作簿进行更改的一种很好的做法。有时我确实使用了 DoEvents （例如，在显示用户表单时），但是我确保在代码运行时用户没有

However, it's not really a good practice to allow the user to make changes to the workbook while your code is running. Sometimes I do use DoEvents (e.g., when displaying a userform), but I make sure that while my code is running the user doesn't have access to the sheet being used by the code.

.Send 方法， CDO.Message ：

The .Send method of CDO.Message:

我相信这是在后台运行的，如果正确配置所有内容。检查这个问题。

I believe this runs in the background and shouldn't block the thread if configure everything correctly. Check this question.

希望有帮助。

这篇关于运行代码时挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！