Hangs when running a code

Question

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

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

Hangs when running a VBA procedure...

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

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

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

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

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

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?

Regards.

Answer 1

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

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

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

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

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:

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

..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. The For loop:

   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
   

   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.

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

Hope that helps.