# Excel sheet code is taking too much time

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, _
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, _
SortMethod:=XlSortMethod.xlPinYin)

End With

• What's i20's value? – Mathieu Guindon Apr 27 '14 at 2:41
• 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. – the_lotus Apr 28 '14 at 15:48

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

• 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. – user2788201 Apr 27 '14 at 5:40
• SheetRows would be a slightly more descriptive name ;) – Mathieu Guindon Apr 27 '14 at 5:42

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