Looping Through Ranges Efficiently

[Mike]

In Excel, I can loop through a range more efficiently by utilizing the
following technique:

' The routine checks the value of each cell in the range and
adjusts the Bold property
' of the Font object accordingly.
On Error Resume Next
' Check the cells with constants
For Each cell In Selection.SpecialCells(xlConstants, 23)
If cell.Value < 0 Then cell.Font.Bold = True Else
cell.Font.Bold = False
Next cell
' Check the cells with formulas
For Each cell In Selection.SpecialCells(xlFormulas, 23)
If cell.Value < 0 Then cell.Font.Bold = True Else
cell.Font.Bold = False
Next cell

Is there an equivalent technique in Word? I have to loop through a
rather large table and it is taking too long.

BTW, I got the above technique from John Walkenbach's book.

--Thank you,
--Mike Jr.

 

[macropod]

hi Mike,

Word doesn't have the equivalent of Excel's 'SpecialCells' method. However, depending on what you're doing, you might be able to
work with the fields collection, for example.

Cheers

 

[Mike]

hi Mike,

Word doesn't have the equivalent of Excel's 'SpecialCells' method. However, depending on what you're doing, you might be able to
work with the fields collection, for example.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

In Excel, I can loop through a range more efficiently by utilizing the
following technique:

'    The routine checks the value of each cell in the range and
adjusts the Bold property
'    of the Font object accordingly.
   On Error Resume Next
'   Check the cells with constants
   For Each cell In Selection.SpecialCells(xlConstants, 23)
       If cell.Value < 0 Then cell.Font.Bold = True Else
cell.Font.Bold = False
   Next cell
'   Check the cells with formulas
   For Each cell In Selection.SpecialCells(xlFormulas, 23)
       If cell.Value < 0 Then cell.Font.Bold = True Else
cell.Font.Bold = False
   Next cell

Is there an equivalent technique in Word?  I have to loop through a
rather large table and it is taking too long.

BTW, I got the above technique from John Walkenbach's book.

--Thank you,
--Mike Jr.- Hide quoted text -

- Show quoted text -

Macropod,
My loop looks like this:

Sub Some_Sub(input_file)

Dim txt_doc As Word.Document
Dim row_values(60,000) As Integer
Dim insert_row_num As Integer

Set txt_doc =
ActiveDocument.Application.Documents.Open(input_file, , False)
insert_row_num = 0
max_tab_row = 60,000

With txt_doc.Tables(1)

For k = 2 To max_tab_row
If Len(.Cell(k, 1).Range.Text) > 2 Then ' Cell is empty
row_values(insert_row_num) = k
insert_row_num = insert_row_num + 1
End If
Next k
End With

'ooo

End Sub

This simply takes too long. I was thinking of doing something like the
following.

Dim c1 As cell

With txt_doc.Tables(1)
.Columns(1).Select
For Each c1 In Selection.Cells
If Len(c1.Range.Text) > 2 Then
row_values(insert_row_num) = c1.RowIndex
insert_row_num = insert_row_num + 1
End If
Next c1

But this would give me the entire column, not just the range that has
values. Would it be any faster?

How expensive would it be to load an array with a long column and then
walk down the array? Any quicker than walking the table?

--Thank you,
--Mike Jr.

 

[macropod]

Hi Mike,

There's two basic approaches to looping through a range, as I'm sure you're aware. Here's some code you can use to test the relative
speed of each:
Sub Test1()
Dim oCell As Cell
Dim i As Long, j As Long, k As Long
Dim eTime As Single
eTime = Timer
With ActiveDocument
k = 500
For Each oCell In .Tables(1).Columns(2).Cells
i = oCell.RowIndex
If Len(oCell.Range.Text) = 2 Then
j = j + 1
If i = k Then Exit For
End If
Next
End With
MsgBox k & " " & j & vbCrLf & "Elapsed time: " & ((Timer - eTime + 86400) * 1000 Mod 86400) / 1000 & " seconds."
End Sub

Sub Test2()
Dim i As Long, j As Long, k As Long
Dim eTime As Single
eTime = Timer
With ActiveDocument.Tables(1)
k = 500
For i = 1 To k
If Len(.Cell(i, 2).Range.Text) = 2 Then
j = j + 1
End If
Next
End With
MsgBox k & " " & j & vbCrLf & "Elapsed time: " & ((Timer - eTime + 86400) * 1000 Mod 86400) / 1000 & " seconds."
End Sub

Simply change the k values to suit your needs. Note that both sets of code allow you to exit without having to process the whole
column.

I note that, in the second lot of code you posted, you're using selections. That's sure to slow down the execution speed.

I don't know how much you'd gain anything by copying the data to an array, but it may be worthwhile.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

hi Mike,

Word doesn't have the equivalent of Excel's 'SpecialCells' method. However, depending on what you're doing, you might be able to
work with the fields collection, for example.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

In Excel, I can loop through a range more efficiently by utilizing the
following technique:

' The routine checks the value of each cell in the range and
adjusts the Bold property
' of the Font object accordingly.
On Error Resume Next
' Check the cells with constants
For Each cell In Selection.SpecialCells(xlConstants, 23)
If cell.Value < 0 Then cell.Font.Bold = True Else
cell.Font.Bold = False
Next cell
' Check the cells with formulas
For Each cell In Selection.SpecialCells(xlFormulas, 23)
If cell.Value < 0 Then cell.Font.Bold = True Else
cell.Font.Bold = False
Next cell

Is there an equivalent technique in Word? I have to loop through a
rather large table and it is taking too long.

BTW, I got the above technique from John Walkenbach's book.

--Thank you,
--Mike Jr.- Hide quoted text -

- Show quoted text -

Macropod,
My loop looks like this:

Sub Some_Sub(input_file)

Dim txt_doc As Word.Document
Dim row_values(60,000) As Integer
Dim insert_row_num As Integer

Set txt_doc =
ActiveDocument.Application.Documents.Open(input_file, , False)
insert_row_num = 0
max_tab_row = 60,000

With txt_doc.Tables(1)

For k = 2 To max_tab_row
If Len(.Cell(k, 1).Range.Text) > 2 Then ' Cell is empty
row_values(insert_row_num) = k
insert_row_num = insert_row_num + 1
End If
Next k
End With

'ooo

End Sub

This simply takes too long. I was thinking of doing something like the
following.

Dim c1 As cell

With txt_doc.Tables(1)
.Columns(1).Select
For Each c1 In Selection.Cells
If Len(c1.Range.Text) > 2 Then
row_values(insert_row_num) = c1.RowIndex
insert_row_num = insert_row_num + 1
End If
Next c1

But this would give me the entire column, not just the range that has
values. Would it be any faster?

How expensive would it be to load an array with a long column and then
walk down the array? Any quicker than walking the table?

--Thank you,
--Mike Jr.

 

[Mike]

Macropod,
This is exactly what I was looking for. Thank you. My experience is
with Excel and I needed the Word "cookbook".

Thanks again,
Mike Jr.

Hi Mike,

There's two basic approaches to looping through a range, as I'm sure you're aware. Here's some code you can use to test the relative
speed of each:
Sub Test1()
Dim oCell As Cell
Dim i As Long, j As Long, k As Long
Dim eTime As Single
eTime = Timer
With ActiveDocument
  k = 500
  For Each oCell In .Tables(1).Columns(2).Cells
    i = oCell.RowIndex
    If Len(oCell.Range.Text) = 2 Then
      j = j + 1
      If i = k Then Exit For
    End If
  Next
End With
MsgBox k & " " & j & vbCrLf & "Elapsed time: " & ((Timer - eTime + 86400) * 1000 Mod 86400) / 1000 & " seconds."
End Sub

Sub Test2()
Dim i As Long, j As Long, k As Long
Dim eTime As Single
eTime = Timer
With ActiveDocument.Tables(1)
  k = 500
  For i = 1 To k
    If Len(.Cell(i, 2).Range.Text) = 2 Then
      j = j + 1
    End If
  Next
End With
MsgBox k & " " & j & vbCrLf & "Elapsed time: " & ((Timer - eTime + 86400) * 1000 Mod 86400) / 1000 & " seconds."
End Sub

Simply change the k values to suit your needs. Note that both sets of codeallow you to exit without having to process the whole
column.

I note that, in the second lot of code you posted, you're using selections.. That's sure to slow down the execution speed.

I don't know how much you'd gain anything by copying the data to an array,but it may be worthwhile.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

hi Mike,

Word doesn't have the equivalent of Excel's 'SpecialCells' method. However, depending on what you're doing, you might be able to
work with the fields collection, for example.

Cheers

- Show quoted text -

Macropod,
 My loop looks like this:

Sub Some_Sub(input_file)

    Dim txt_doc As Word.Document
    Dim row_values(60,000) As Integer
    Dim insert_row_num As Integer

    Set txt_doc =
ActiveDocument.Application.Documents.Open(input_file, , False)
    insert_row_num = 0
    max_tab_row = 60,000

    With txt_doc.Tables(1)

        For k = 2 To max_tab_row
            If Len(.Cell(k, 1).Range.Text) > 2 Then ' Cell is empty
                row_values(insert_row_num) = k
                insert_row_num = insert_row_num + 1
            End If
        Next k
    End With

'ooo

End Sub

This simply takes too long. I was thinking of doing something like the
following.

    Dim c1 As cell

    With txt_doc.Tables(1)
    .Columns(1).Select
    For Each c1 In Selection.Cells
        If Len(c1.Range.Text) > 2 Then
            row_values(insert_row_num) = c1.RowIndex
            insert_row_num = insert_row_num + 1
        End If
    Next c1

But this would give me the entire column, not just the range that has
values.  Would it be any faster?

How expensive would it be to load an array with a long column and then
walk down the array?  Any quicker than walking the table?

--Thank you,
--Mike Jr.- Hide quoted text -

- Show quoted text -