Make code faster

[Gringoire]

Hello guys,
I'm using VBA to fill a Word document (OD below) from an Excel sheet but the code is extremely slow.
Translating about 900 Excel rows lasts 200 seconds
for each string(s) to translate from Excel there are two cases:

• the Excel row is translated in simple word text
• the Excel row is translated in a row of a word table

the first case needs about 0.038 sec each loop:

    Select Case Range_DATI(i%, PrintFRM)       
        Case "TESTO"                            'Case data to simple text
            With OD.ActiveWindow.Selection
                .InsertAfter (Range_DATI(i%, 2) & vbCr)
                .Collapse wdCollapseEnd
            End With

The second case (the most frequent) needs about 0.2 sec each loop.

 Case "DATA"                             'CASE data to tab
        dummy = TimeACode("s")
            NumTab = OD.Tables.Count
            If Range_DATI((i% - 1), PrintFRM) <> "DATA" Then    'Creating table if not existing
                Crea_tabella_AIDA
                NumTab = NumTab + 1
            Else
                OD.Tables.Item(NumTab).Rows.Add        'Add row if table already exists
            End If
            With OD.Tables.Item(NumTab)                'Filling the cells
                RigaAttiva = OD.Tables.Item(NumTab).Rows.Count
                .Cell(RigaAttiva, 1).Range.Text = Range_DATI(i%, 2)
                .Cell(RigaAttiva, 2).Range.Text = Range_DATI(i%, 3)
                .Cell(RigaAttiva, 3).Range.Text = Range_DATI(i%, 4)
                OD.ActiveWindow.Selection.EndOf Unit:=wdTable
                OD.ActiveWindow.Selection.MoveDown
            End With
        dummy = TimeACode("stop")      'Fino a qui
        Case else
...

The User can't wait more than three minutes for each Sheet conversion.
Is there some way to make the code above faster?
Sorry for the maybe silly question but I'm not good at all with Word VBA.

thanks

 

[offthelip]

You haven't posted all of your code, however the reason VBA is slow is often because there are lots of interaction between the vba code and the worksheet, THIS IS VERY SLOW. So the easiest way of speeding up VBa is to avoid accessing the worksheet in the loop. In your case you appear to be copying from one range to another range three time every loop and also moving the selection point,. this is going to be slow. The way round this is to copy the entire worksheet used in RANGE_dati inot a variant array, then go though the loop copying the values to another output variant array and then write the whole output array to the worksheet in one go. This is likely to be at least 1000 times faster than your current code

 

[offthelip]

I have just been looking at adding rows to table in Word, this is also likely to be slow, so I think you need to process all the data into a variant array and the create the word table the correct size for the data, this will avoid adding a row at a time. then copy the variant array into the table

 

[Trixterz]

Try disabling the ScreenUpdating and Calculation, I find it it make things a lot faster.

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'Yours code here...

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True