Problem solve Get help with specific problems with your technologies, process and projects.

Speed up Excel action

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

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 '
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
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 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.

Dig Deeper on Win Development Resources

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.