Tip

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.


This was first published in January 2001

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top