Speed up Excel action

Speed up Excel action
Byron Bennett

Reader Byron Bennett sends us three speed tips for use when performing heavy manipulations in Excel from a VB app.

1. For long processes that make repeated calls to objects within the Excel App, use code from within Excel VBA instead of your VB App. A quick and easy way to get the code into a fresh Workbook is to import a text file containing your code. The code Example below shows how to load a Sub from a .txt file that puts an array onto a Worksheet. This is many, many times faster than doing it from the VB app.

   Tips 2 & 3 assume that myXL has been set to a new Excel.Application object:

2. Use myXL.ScreenUpdating = False to stop the screen from repainting each change you make. Also useful in your Excel VBA code.

3. Use myXL.DisplayAlerts = False and myXL.Visible = False to hide any trace of what's going on behind the scenes.

Code: ''''''''''''''''''''
'Begining of file: ' 
'SUBSaveData.txt   '
'Save in your VB   '
'App.Path for this '
'example
''''''''''''''''''''
Sub SaveData(aData() as variant)
dim i as integer
With sheets("Sheet1")
'''Your For...Next can be changed to fit
'''your array coming in.
For i = 1 To UBound(aData, 1) + 1
      .Cells(i, 1).Value = aData(i-1)
Next i
End With
End Sub
''''''''''''''''''
'End of Text File'
''''''''''''''''''

'   **** This Code is in your VB App ***
' Don't forget to set a reference to the
' Microsoft Excel #.0 Object Library
Private Sub SaveDataToXL()
Dim myXL as Excel.Application
Dim aData() as Variant

Set myXL = New Excel.Application
myXL.Visible = True   ' So we can see 
                      ' Excel
myXL.ScreenUpdating = False 'Speed it up
myXL.Workbooks.Add
With myXL.ActiveWorkbook
   .Sheets.Add , , , xlModule
   .Sheets("Module1").InsertFile _
     App.Path & "SUBSaveData.txt", True
End With

aData = Array(1,3,2,45,2,45,3,4,"other stuff") 'Adjust your VBA sub
     'to suit your array

myXL.Run "SaveData", aData()
myXL.ScreenUpdating = True
End Sub

Thanks, Byron. For your interest in SearchVB we'll be sending you a free SearchVB.com denim shirt.

Byron develops VB apps for the Graybar Electric accounting department using Oracle and Access on the back-end. He also enjoys helping the company's accountants make friends with those pesky computers that have invaded their lives.