4
\$\begingroup\$

I have an Excel sheet with lot of rows with data. I am separating it into 2 sheets with some condition satisfies.

This is taking too much time. How can I improve the speed? Please suggest any changes to my code.

Dim bmver As Object(,) = New Object(i20, 36) {}
Dim bmhor As Object(,) = New Object(i20, 36) {}

Dim ik As Integer = 0
Dim ij As Integer = 0
With oWs

    For i As Integer = 2 To i20 - 1
        If (.range("E" & i).value = .range("L" & i).value) Then

            For j As Integer = 0 To 36
                bmhor(ij, j) = .cells(i, j + 1).value
            Next
            ij = ij + 1
        End If

    Next

    For k As Integer = 2 To i20 - 1
        If (.range("C" & k).value = .range("J" & k).value) Then

            For j1 As Integer = 0 To 36
                bmver(ik, j1) = .cells(k, j1 + 1).value
            Next
            ik = ik + 1
        End If

    Next


End With
oWs = oBook.Worksheets("beamhor")
oWs.activate()
Dim r6 As Range = oWs.Range("A2").Resize(bmhor.GetLength(0), 37)
r6.Value2 = bmhor

With oWs

    .Range("A2:AE" & i20).Sort(Key1:=.range("E2"), Order1:=Excel.XlSortOrder.xlAscending, _
         Key2:=.range("L2"), Order2:=Excel.XlSortOrder.xlAscending, _
          Key2:=.range("C2"), Order2:=Excel.XlSortOrder.xlAscending, _
         Key2:=.range("J2"), Order2:=Excel.XlSortOrder.xlAscending, _
           Orientation:=XlSortOrientation.xlSortColumns, _
             Header:=Excel.XlYesNoGuess.xlNo, _
           SortMethod:=XlSortMethod.xlPinYin)

End With
oWs = oBook.Worksheets("beamver")
oWs.activate()
Dim r7 As Range = oWs.Range("A2").Resize(bmver.GetLength(0), 37)
r7.Value2 = bmver
With oWs

    .Range("A2:AE" & i20).Sort(Key1:=.range("E2"), Order1:=Excel.XlSortOrder.xlAscending, _
                  Key2:=.range("L2"), Order2:=Excel.XlSortOrder.xlAscending, _
                 Key2:=.range("C2"), Order2:=Excel.XlSortOrder.xlAscending, _
                   Key2:=.range("J2"), Order2:=Excel.XlSortOrder.xlAscending, _
                   Orientation:=XlSortOrientation.xlSortColumns, _
                  Header:=Excel.XlYesNoGuess.xlNo, _
                 SortMethod:=XlSortMethod.xlPinYin)

End With
\$\endgroup\$
  • \$\begingroup\$ What's i20's value? \$\endgroup\$ – Mathieu Guindon Apr 27 '14 at 2:41
  • \$\begingroup\$ If I remember, it's possible to pull a range directly in an array instead of reading each cells at a time using sheet.get_Range and range.Cells.Value. This might be faster since it'll do less call to the interopt. \$\endgroup\$ – the_lotus Apr 28 '14 at 15:48
3
\$\begingroup\$

Excel interoperability is inherently slow - you have the .NET runtime talking to a COM object model through COM interop, there's a performance penalty just for doing that.

Hence, you should strive to reduce the number of times you're accessing the worksheet cells, be it to read or to write.

Before going into the algorithm, I'd like to point out the naming issues. Bad names make the code harder to read and longer to parse. Typing code is 20% of the job - the other 80% is spent reading code, so when the first 20% makes a good job at being readable, the 80% can be more productive.

Avoid single-letter identifiers (for-loop variable is ok though) and disemvoweling. Use comments - don't write code that assumes the reader is also looking at the worksheet in question: name things so that we know what columns C, E, J and L are, or comment to that effect.

Now. We have two loops, both iterating from 2 To i20 - 1. Round one would be to merge them:

For i As Integer = 2 To i20 - 1

    If (.range("E" & i).value = .range("L" & i).value) Then

        For j As Integer = 0 To 36
            bmhor(ij, j) = .cells(i, j + 1).value
        Next

        ij = ij + 1

    End If

    If (.range("C" & i).value = .range("J" & i).value) Then

        For j As Integer = 0 To 36 'I'd believe identifier j can be reused here
            bmver(ik, j) = .cells(i, j + 1).value
        Next

        ik = ik + 1

    End If

Next

Just doing that has reduced the number of iterations by half. Now these two blocks look awfully similar. Let's see if we can extract a method here:

Sub NameMe(Value1 As String, Value2 As String, Row As Long, TheWorksheet As Worksheet, TheArray As Object(,))

    Dim Counter As Integer
    If (Value1 = Value2) Then

        For i As Integer = 0 To 36
            TheArray(Counter, i) = TheWorksheet.Cells(Row, i + 1).Value
        Next

        Counter += 1

    End If

End Sub

Then the loop can look like this:

Dim ValueForColumnE As String
Dim ValueForColumnL As String
Dim ValueForColumnC As String
Dim ValueForColumnJ As String

For i As Integer = 2 To i20 - 1

    ValueForColumnE = .range("E" & i).Value.ToString()
    ValueForColumnL = .range("L" & i).Value.ToString()
    ValueForColumnC = .range("C" & i).Value.ToString()
    ValueForColumnJ = .range("J" & i).Value.ToString()

    NameMe ValueForColumnE, ValueForColumnL, i, ows, bmhor
    NameMe ValueForColumnC, ValueForColumnJ, i, ows, bmver

Next
\$\endgroup\$
  • \$\begingroup\$ i20 is no. or rows in that sheet. thanks for your words. i am improving my programing skills day by day. i tested with the code you suggested. But its also taking same time as much as mine. \$\endgroup\$ – user2788201 Apr 27 '14 at 5:40
  • \$\begingroup\$ SheetRows would be a slightly more descriptive name ;) \$\endgroup\$ – Mathieu Guindon Apr 27 '14 at 5:42
0
\$\begingroup\$

Here are a few tips that I have used often to speed up macros and code that runs against excel sheets:

  1. Turn off screen updating in excel.
  2. Set the calculation mode to manual if you have other formulas and you are changing range values.
  3. If possible load all the data into an array and only write it to the sheet once.

I have had significant speedups when doing those from inside Excel using VBA and outside over COM using python. Here are a few snippets on how it is done, I used the Excel VBA syntax although you would want to adjust them for your code.

' Turn off updates in excel
Application.ScreenUpdating = False
Application.Calculation = xlCalculateManual

The other part is a little trickier but in my experience is extremely fast when doing it inside Excel. Perhaps this concept could be applied to your code.

' Setup and load a range into an array
Dim ColumnAValues As Variant
Dim EndNum as Long

' Find the end of the used data range and copy the data into an array.
EndNum = ActiveSheet.UsedRange.Rows.Count
ColumnAValues = ActiveSheet.Range(Cells(1, 1), Cells(EndNum, 1)).Value

' Do all the calculations on the data using the array.

' Save your array back into the range.
ActiveSheet.Range(Cells(1, 1), Cells(EndNum, 1)) = ColumnAValues

When I am done I turn both the screen and calculation back on.

' Turn back on updates
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

For speed comparison, I did an example sheet with ~ 16000 rows, I read two cells, added them together and wrote them into another... it took about 4 seconds (Excel VBA). When I convert using the mentioned array method it completed in far less than a second...

To show it using com, here is an example using python, the set each 'cell' method takes about 27 seconds on my machine, the set a 'range' method takes about 5.3 seconds, ignoring the 5 second wait so you can see the sheet, the 'range' method is many times faster:

import time
import win32com.client as win32


def excel(method):
    """ Open Excel using COM """
    starttime = time.time()
    # http://msdn.microsoft.com/en-us/library/ee861528.aspx
    xlCalculationManual = -4135
    xlCalculationAutomatic = -4105

    xl = win32.gencache.EnsureDispatch('Excel.Application')
    wb = xl.Workbooks.Add()
    sh = wb.ActiveSheet

    xl.Visible = True
    time.sleep(5)  # So that you can see the sheet
    xl.Calculation = xlCalculationManual
    xl.ScreenUpdating = False

    if method == 'cell':
        for i in range(1, 15000):
            sh.Cells(i, 1).Value = 'ABC'

    if method == 'range':
        cells = []
        for i in range(1, 15000):
            cells.append(('ABC',))
        sh.Range(xl.Cells(1, 1), xl.Cells(len(cells), 1)).Value = cells

    xl.ScreenUpdating = True
    xl.Calculation = xlCalculationAutomatic
    print("Runtime " + "{:.2f}".format(round(time.time() - starttime, 1)))

if __name__ == "__main__":
    excel('cell')
    excel('range')
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.