Databases
StackOverflow2013
Postsdbo
POcomparison Excel and delphi calculation speed
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POcomparison Excel and delphi calculation speed
    text
    copied!<p>I have created a macro in excel which takes a lot of time to calculate all the possibilities to organize stacking of books within a certain height range. All the books have their own dimensions. As the number of books gets higher, the problem becomes more and more complex and has more solutions therefore the calculation time increases. Til a point where it actually freezes of runs out of space in excel rows.</p> <p>Now my question is, If I do this with Delphi Borland, will I run into the same problem or can a delphi program do this much faster? Or are there ways to do this faster and more effectively? I am not so familiar with this so if the question takes some explaining I would very much appreciate it!</p> <pre><code>Public Text, Alpha, Result, Temp_Result, Wgt, Hgt, Combo_No As Integer, Allwd_Combo As Integer Public Max_Wgt As Double, Max_Hgt As Double, Crt_Wgt, Crt_Hgt Sub ListPermutationsOrCombinations() Dim Str_Len As Integer, Len_Text As Integer, TotalComb As Integer Len_Text = Worksheets("Listed Books").Range("A65536").End(xlUp).Row - 1 Worksheets("Buildups").Range("A2:C65536").Clear Next_Row = 1 Max_Wgt = Worksheets("constraints").Range("B1") Max_Hgt = Worksheets("constraints").Range("B2") ReDim Alpha(1 To Len_Text) For j = 1 To Len_Text Alpha(j) = Worksheets("Listed Books").Cells(j + 1, 1) Next j For i = 1 To Len_Text Str_Len = i TotalComb = Application.WorksheetFunction.Combin(Len_Text, Str_Len) Allwd_Combo = TotalComb ReDim Result(1 To TotalComb) ReDim Wgt(1 To TotalComb) ReDim Hgt(1 To TotalComb) ReDim Temp_Result(1 To Str_Len) Combo_No = 1 AddCombination Len_Text, Str_Len Worksheets("Buildups").Cells(Next_Row + 1, 1).Resize(TotalComb) = Application.Transpose(Result) Worksheets("Buildups").Cells(Next_Row + 1, 2).Resize(TotalComb) = Application.Transpose(Wgt) Worksheets("Buildups").Cells(Next_Row + 1, 3).Resize(TotalComb) = Application.Transpose(Hgt) Next_Row = Next_Row + Allwd_Combo Next i End Sub Private Sub AddCombination(Optional PopSize As Integer = 0, _ Optional SetSize As Integer = 0, _ Optional NextMember As Integer = 0, _ Optional NextItem As Integer = 0) Static iPopSize As Integer Static iSetSize As Integer Static SetMembers() As Integer Dim i As Integer If PopSize &lt;&gt; 0 Then iPopSize = PopSize iSetSize = SetSize ReDim SetMembers(1 To iSetSize) As Integer ReDim Crt_Wgt(1 To iSetSize) As Double ReDim Crt_Hgt(1 To iSetSize) As Double NextMember = 1 NextItem = 1 End If For i = NextItem To iPopSize SetMembers(NextMember) = i Crt_Wgt(NextMember) = Worksheets("Listed Books").Cells(i + 1, 2) Crt_Hgt(NextMember) = Worksheets("Listed Books").Cells(i + 1, 3) If NextMember &lt;&gt; iSetSize Then AddCombination , , NextMember + 1, i + 1 Else If (Application.WorksheetFunction.Sum(Crt_Wgt) &gt; Max_Wgt) Or _ (Application.WorksheetFunction.Sum(Crt_Hgt) &gt; Max_Hgt) Then Allwd_Combo = Allwd_Combo - 1 Else SavePermutation SetMembers(), iSetSize, Combo_No Combo_No = Combo_No + 1 End If End If Next i End Sub 'AddCombination Sub SavePermutation(Set_Member, Str_Len As Integer, Combo_No As Integer) For i = 1 To Str_Len Temp_Result(i) = Alpha(Set_Member(i)) Next i Result(Combo_No) = Join(Temp_Result) Wgt(Combo_No) = Application.WorksheetFunction.Sum(Crt_Wgt) Hgt(Combo_No) = Application.WorksheetFunction.Sum(Crt_Hgt) End Sub </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload