VBA is crashing after about 350 pages

[lars-daniel]

Hi there,

I'm having a huge mailmerge document with 6,000 pages and many tables and columns. Each cell with "-1" inside it should get a grey background. Since I was not able to do this using IF function, I'm trying it with VBA (see below).

Description of the workflow: I'm running a very simple code to get through all the columns of all tables in my word document, make its background to "grey" and remove the content, if its numeric content is "-1".

On a 250 page document (mailmerge page 1-250), anything works fine. At 350 pages or more, the whole thing crashes at

c.Range.Text = ""

My Office 2010 is 32-bit, but it's far from memory limited. I also tried this code on Office 2016, but the code never completed... seems like VBA integration got worse there.

Anyone with an idea on how to make it better or fix it? "Conditionally Shade Table Cells" trick doesn't work here, since the margins of some cells can't be set to zero.

Sub grey()
  Application.ScreenUpdating = False
  Dim tbl As Table
  Dim c As Word.Cell
  For Each tbl In ActiveDocument.Tables
    For Each c In tbl.Range.Cells
      If Val(c.Range.Text) = -1 Then
        c.Shading.BackgroundPatternColor = wdColorGray10
        c.Range.Text = ""
      End If
    Next
    Set c = Nothing
  Next
  Application.ScreenUpdating = True
End Sub

Best,
Lars-Daniel

[macropod]

You might try giving Word some breathing space for its own housekeeping:

Sub Grey()
Application.ScreenUpdating = False
Dim Tbl As Table, Cll As Cell, i As Long
For Each Tbl In ActiveDocument.Tables
  For Each Cll In Tbl.Range.Cells
    i = i + 1: If i Mod 500 = 0 Then DoEvents
    With Cll
      If Split(.Range.Text, vbCr)(0) = "-1" Then
        .Shading.BackgroundPatternColor = wdColorGray10
        .Range.Text = ""
      End If
    End With
  Next
Next
Set Cll = Nothing: Set Tbl = Nothing
Application.ScreenUpdating = True
End Sub

[lars-daniel]

Thanks a lot ! It's still running - can't tell you about the result. But since it's still running, you seem to have solved the problem. Latest (and biggest) Intel i7 with latest Office still uses 1 core only, three cores (+ hyperthreading) are idling. I should better split it into single files and run in parallel

Do you suggest to set use .Range.Text to remove the value or shall I do it the "replace"-way?

With ActiveDocument.Content.Find
    .Execute FindText:="-1", ReplaceWith:="", Format:=True, Replace:=wdReplaceAll, MatchWholeWord:=False
End With

Shall I also add this to each loop?

ActiveDocument.UndoClear

EDIT:
Oops, it doesn't work every time. I've got very much cells, which haven't get touched at all.

[macropod]

Originally Posted by lars-daniel

Latest (and biggest) Intel i7 with latest Office still uses 1 core only, three cores (+ hyperthreading) are idling. I should better split it into single files and run in parallel

Splitting the document into multiple files won't help unless you also start multiple independent Word sessions.

Originally Posted by lars-daniel

Oops, it doesn't work every time. I've got very much cells, which haven't get touched at all.

In that case, there's more than just -1 in the cells (e.g. some spaces). You might try changing:
Split(.Range.Text, vbCr)(0)
to:
Trim(Split(.Range.Text, vbCr)(0))

[lars-daniel]

Originally Posted by macropod

Splitting the document into multiple files won't help unless you also start multiple independent Word sessions.

Sure. I don't like the rise of multi-core processors in recent years. Only a fraction of the end-user applications support this, but fortunately Excel is one of them. For Server it's clear, there's something in it.

Originally Posted by macropod

In that case, there's more than just -1 in the cells (e.g. some spaces). You might try changing:
Split(.Range.Text, vbCr)(0)
to:
Trim(Split(.Range.Text, vbCr)(0))

Thanks, let me try. Maybe that's why I used Val() I think. What do you think about replace and removing my undo buffer?

Edit:
Didn't work. I'm still having many "-1". I've changed it back to "Val(.Range.Text) = -1". This works safe in my case.

[macropod]

Originally Posted by lars-daniel

What do you think about replace and removing my undo buffer?

That would be of little or no benefit; the time taken to clear the buffer may actually slow down the process.

[lars-daniel]

Originally Posted by macropod

That would be of little or no benefit; the time taken to clear the buffer may actually slow down the process.

Ah okay, same as unset($var) in PHP. Thanks a lot.