Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
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 "Cool Sport" skrev i melding ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
Harald Staff wrote:
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 "Cool Sport" skrev i melding ... 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. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
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? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
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, *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
It's a bit difficult to follow your routine, but in a quick look you say the
problem is he === 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 "Cool Sport" wrote in message ... 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, *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
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? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
S2pid me, please ignore my last post
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed up performance for very big array
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 he === 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speed of array formula | Excel Worksheet Functions | |||
charting performance speed | Charts and Charting in Excel | |||
Excel Speed Performance help | Excel Discussion (Misc queries) | |||
Worksheet speed with array formula | Excel Discussion (Misc queries) | |||
VBA - speed up macro, working with array? | Excel Programming |