Speed up performance for very big array

[Cool Sport]

I have table of raw data (1000 rows and 150 columns) stored in a
worksheet. I'd like to copy them all into a 2-dimension array then do
all the calculation with this array (not the raw data on the sheet).

The calculated result will be in another array (that has the same size
or bigger). This array will be copied back into another sheet (result
sheet).

The problem is that it runs really slow when it calculates values for
every element in result array.

Please anyone can tell me how to speed up or better solution for this
issue.

All the best

 

[Harald Staff]

One of Excel's fantastic features is that she calculates extremely fast and
reliable. You would need a fantastic calculation routine written in a speedy
language like C++ to get close to her performance. So I suggest you take
advantage of Excel's built-in calculation, a VBA equivalent will not do.
Well it will, but it is doomed to be far slower.

HTH. Best wishes Harald

 

[Martin Brown]

One of Excel's fantastic features is that she calculates extremely fast and
reliable. You would need a fantastic calculation routine written in a speedy
language like C++ to get close to her performance. So I suggest you take
advantage of Excel's built-in calculation, a VBA equivalent will not do.
Well it will, but it is doomed to be far slower.

HTH. Best wishes Harald

The key to this is probably to switch off automatic recalculation and
screen updating before starting the array computation. 150x1000 should
not really tax Excel on a half decent machine.

Regards,
Martin Brown

 

[Cool Sport]

Thanks a lot for a very quick response

Your suggestion would be the best but I haven't got any idea of doing it
in C++. A thought came to my mind that I could make a dll in VB.Net then
pass the raw data array to a procedure and let .net do it.

It is possible?

 

[Cool Sport]

I have used all the possible techniques in Excel to improve its
performance including what you have mentioned. My computer was hung when
the first version ran. Now it takes about 10 to 70 seconds depending on
how complex the calculation formulas are.

I am using evaluate method to get the result of each formula. However,
the problem was still the same even if I avoided using this methods.

 

[Peter T]

Hi,

If I folllow what you mean maybe something like the following. With this
meaningless calculation, for me the array method was 25 times faster than
working with cells.

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub test()
Dim vArr, rng As Range
Dim x As Long, i As Long, j As Long, t As Long
Dim rw As Long, cl As Long

'some data
Set rng = [a1:a10000]
For i = 1 To 5
Set rng = rng.Offset(0, 1)
rng.Value = i
Next

'cells method
t = GetTickCount
For i = 1 To 10000
x = 0
For j = 2 To 6
x = x + Cells(i, j)
Next
Cells(i, 8) = x
Next

Debug.Print "Cells calc " & GetTickCount - t

''Array method
t = GetTickCount
Set rng = [b1:f10000]
vArr = rng
ReDim nArr(1 To rng.Rows.Count, 1 To 1) As Long

rw = rng.Rows.Count
cl = rng.Columns.Count
For i = 1 To rw
x = 0
For j = 1 To cl
x = x + vArr(i, j)
Next
nArr(i, 1) = x
Next

[j1:j10000].Value = nArr
Debug.Print "Array calc " & GetTickCount - t

End Sub



Regards,
Peter

 

[Cool Sport]

Thanks Peter for your time,

Mine is somehow similar to the code you wrote. I have found out that the
problem is where the arrow is as below:

//////////////////////////////////////////////////
''Array method
t = GetTickCount
Set rng = [b1:f10000]
vArr = rng
ReDim nArr(1 To rng.Rows.Count, 1 To 1) As Long

rw = rng.Rows.Count
cl = rng.Columns.Count
For i = 1 To rw
x = 0
For j = 1 To cl
==========> x = x + vArr(i, j)
Next
nArr(i, 1) = x
Next

[j1:j10000].Value = nArr
Debug.Print "Array calc " & GetTickCount - t
//////////////////////////////////////////////////


Following is my sub:


//////////////////////////////////////////////////

Public Sub printFormulaTable(Optional ByVal formulaColumns As String =
"#control#")
On Error GoTo errHandle
Dim i As Integer, j As Integer
Dim Temp As Variant
Dim count1 As Integer, count2 As Integer

If IsEmpty(rawData) Then setupArrays

Dim n As Date
n = Time()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If formulaColumns = "#control#" Then
formulaColumns = ""
For i = 1 To UBound(fList, 1)
If fList(i, 3) = True Then
formulaColumns = formulaColumns & "," & fList(i, 1)
End If
Next i
formulaColumns = Mid(formulaColumns, 2)
End If

If Len(formulaColumns) > 0 Then
Temp = Split(formulaColumns, ",")

count1 = UBound(Temp) + 1 ' number of formula columns from 0 ->
count1
count2 = UBound(rawData, 1) ' number of rows except header row
in rawData

ReDim mainData(count2, count1) As Variant

For j = 0 To count1 - 1
mainData(1, j + 1) = Temp(j)
For i = 2 To count2
=== PROBLEM ===> mainData(i, j + 1) = fEvaluate(Temp(j), i)
Next i
Next j

With wsMainData
.Range(.Cells(1, 1), .Cells(count2, count1)).Delete
.Range(.Cells(1, 1), .Cells(count2, count1)).FormulaArray =
mainData
.Range(.Cells(1, 1), .Cells(count2, count1)).name =
"Database2"
End With
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox n & vbCrLf & Time

Exit Sub
errHandle:
writeLog "printFormulaTable", " error " & Err & ": " & Error(Err) &
" i=" & i & " j=" & j
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub 'printFormulaTable

//////////////////////////////////////////////////


User-defined fEvaluate() function takes a formula as parameter than it
will substitute relevant values for each variable in the formula and
finally return calculated result.

It is ok if the array has less than about 300 rows and 150 columns.

I know it is better to let Excel does this job. But my task is that I
have to produced complicated reports that based on violated calculated
values (not the original).

I also know that it is faster and easier to do it in MS Access 'cause I
have done this before. But I am required to do this in Excel.

I have been trying many methods to reach as close as I can. I am really
appreciate any idea or suggestion from you.

Best regards,

 

[Peter T]

It's a bit difficult to follow your routine, but in a quick look you say the
problem is here:

=== PROBLEM ===> mainData(i, j + 1) = fEvaluate(Temp(j), i)

and you say fEvaluate returns an array.
I wouldn't necessarily expect that line to be the problem, but rather this
one:

..Range(.Cells(1, 1), .Cells(count2, count1)).FormulaArray = mainData

because by now mainData is an array of arrays. Maybe this will demonstrate:

Sub test2()
Dim v(1 To 2, 1 To 2)
Dim x(1 To 2, 1 To 2)
k = 0

For a = 1 To 2: For b = 1 To 2
For c = 1 To 2: For d = 1 To 2
k = k + 1
x(c, d) = k
Next: Next
v(a, b) = x
Next: Next

For a = 1 To 2: For b = 1 To 2
For c = 1 To 2: For d = 1 To 2
Debug.Print "a " & a; " b " & b; " c " & c; " d " & d _
, v(a, b)(c, d)
Next: Next
Next: Next

End Sub

note v(a, b)(c, d) in the debug line

I expect you would need to re-dimension your Range multiple times (in a
loop) to that of each array that's being dumped, not the dimensions of
mainData. And, somehow ensure you are not overwriting cells. Rather you than
me!

Regards,
Peter

Thanks Peter for your time,

Mine is somehow similar to the code you wrote. I have found out that the
problem is where the arrow is as below:

//////////////////////////////////////////////////
''Array method
t = GetTickCount
Set rng = [b1:f10000]
vArr = rng
ReDim nArr(1 To rng.Rows.Count, 1 To 1) As Long

rw = rng.Rows.Count
cl = rng.Columns.Count
For i = 1 To rw
x = 0
For j = 1 To cl
==========> x = x + vArr(i, j)
Next
nArr(i, 1) = x
Next

[j1:j10000].Value = nArr
Debug.Print "Array calc " & GetTickCount - t
//////////////////////////////////////////////////


Following is my sub:


//////////////////////////////////////////////////

Public Sub printFormulaTable(Optional ByVal formulaColumns As String =
"#control#")
On Error GoTo errHandle
Dim i As Integer, j As Integer
Dim Temp As Variant
Dim count1 As Integer, count2 As Integer

If IsEmpty(rawData) Then setupArrays

Dim n As Date
n = Time()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If formulaColumns = "#control#" Then
formulaColumns = ""
For i = 1 To UBound(fList, 1)
If fList(i, 3) = True Then
formulaColumns = formulaColumns & "," & fList(i, 1)
End If
Next i
formulaColumns = Mid(formulaColumns, 2)
End If

If Len(formulaColumns) > 0 Then
Temp = Split(formulaColumns, ",")

count1 = UBound(Temp) + 1 ' number of formula columns from 0 ->
count1
count2 = UBound(rawData, 1) ' number of rows except header row
in rawData

ReDim mainData(count2, count1) As Variant

For j = 0 To count1 - 1
mainData(1, j + 1) = Temp(j)
For i = 2 To count2
=== PROBLEM ===> mainData(i, j + 1) = fEvaluate(Temp(j), i)
Next i
Next j

With wsMainData
.Range(.Cells(1, 1), .Cells(count2, count1)).Delete
.Range(.Cells(1, 1), .Cells(count2, count1)).FormulaArray =
mainData
.Range(.Cells(1, 1), .Cells(count2, count1)).name =
"Database2"
End With
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox n & vbCrLf & Time

Exit Sub
errHandle:
writeLog "printFormulaTable", " error " & Err & ": " & Error(Err) &
" i=" & i & " j=" & j
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub 'printFormulaTable

//////////////////////////////////////////////////


User-defined fEvaluate() function takes a formula as parameter than it
will substitute relevant values for each variable in the formula and
finally return calculated result.

It is ok if the array has less than about 300 rows and 150 columns.

I know it is better to let Excel does this job. But my task is that I
have to produced complicated reports that based on violated calculated
values (not the original).

I also know that it is faster and easier to do it in MS Access 'cause I
have done this before. But I am required to do this in Excel.

I have been trying many methods to reach as close as I can. I am really
appreciate any idea or suggestion from you.

Best regards,

 

[Cool Sport]

Uhm, did I say fEvaluate() return an array??? Nope. That function will
return a variant value.

I have found that it takes time to loop through a big array. The
following code takes about 2 second to run even with or without the
lines 9 and 10.

1: Dim n As Date
2: n = Time()
3: count1 = 150
4: count2 = 200

5: For j = 0 To count1 - 1
6: mainData(1, j + 1) = Temp(j)
7: row = 1
8: For i = 2 To count2
9: row = row + 1
10: mainData(row, j + 1) = fEvaluate(Temp(j), i)
11: Next i
12:Next j

13:MsgBox n & vbCrLf & Time


Any suggestion?

 

[Cool Sport]

S2pid me, please ignore my last post

 

[Peter T]

Uhm, did I say fEvaluate() return an array??? Nope. That function will

return a variant value.

No you didn't, S2pid-me-2, apologies and please disregard my previous.

It is ok if the array has less than about 300 rows and 150 columns.

I don't see any obvious reason for a problem here:

=== PROBLEM ===> mainData(i, j + 1) = fEvaluate(Temp(j), i)

if fEvaluate is simply returning a value

Following would fail in xl97 or xl2000 with over 5461 elements:
..FormulaArray = mainData

But that's considerably less than your 45,000 so take it you're using a
later version.

Can you simulate the problem in a stripped down routine that can be posted.

Regards,
Peter