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.
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.