Speed up Excel action
Reader Byron Bennett sends us three speed tips for use when performing heavy manipulations in Excel from a VB app.
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:
Use myXL.ScreenUpdating = False to stop the screen from repainting each change you make. Also useful in your Excel VBA code.
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