Zum Bau eines Produktkonfigurators mit Excel benötigt man meistens eine Matrix, in der man definiert, welche Konfigurationsparameter mit welchen anderen Parametern zusammen möglich sind und welche sich in der Kombination auschließen, z.B. die Farbe weiß ist erhältlich für das iPhone 6, aber nicht für die 16GB Variante.
Diese Regeln sind ser anfällig für Fehler, deshalb benötigt man zum Testing eine Ausgabe alle möglichen Varianten des Konfigurators.
Das größte Problem bei größeren Konfiguratoren sind die vielen möglichen Kombinationen, für die es schon mal Wochen dauern kann alle zu berechnen, wenn man versucht einfach alle möglichen Kombinationen durchzugehen, denn die Laufzeit verdoppelt ich mit jeder weiteren möglichen Konfigurationsmöglichkeit.
Der Algorithmus hier ist etwa doppelt so schnell, je nach Matrix, indem er prüft, ob die ersten beiden Parameter passen und wenn ja, dann die nächsten 3 usw. Er überspringt für den Fall, dass z.B. die ersten 3 Parameter nicht passen, alle Kombinationen mit diesen 3 Parametern und spart dadurch Zeit.
Gern kann ich das Skript an Ihre Bedürfnisse anpassen.
Sub generateAllArticles()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim headingRow, firstRow, firstColumn, lastColumn As Integer
Dim rowLong As Long
headingRow = ActiveSheet.Range("first_productid_column").row
firstRow = ActiveSheet.Range("first_productid_column").row + 1
firstColumn = ActiveSheet.Range("first_productid_column").column
lastColumn = ActiveSheet.Range("productIdColumn").column - 2
endFound = False
values1 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn).value)
values2 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn + 1).value)
values3 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn + 2).value)
values4 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn + 3).value)
values5 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn + 4).value)
values6 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn + 5).value)
values7 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn + 6).value)
values8 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn + 7).value)
values9 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn + 8).value)
values10 = getAllCombinationsOfDropdownInCell(Cells(headingRow, firstColumn + 9).value)
rowLong = firstRow
Do While Not endFound
For index1 = 0 To UBound(values1) Step 1
Cells(rowLong, firstColumn).value = values1(index1)
valueProductNumber1 = getProductData(values1(index1), Cells(headingRow, firstColumn).value, sheet)
If checkIfValidCombination(rowLong, firstColumn, valueProductNumber1(0), 3, sheet) = False Then
GoTo nextIndex1
End If
For index2 = 0 To UBound(values2) Step 1
Cells(rowLong, firstColumn + 1).value = values2(index2)
valueProductNumber2 = getProductData(values2(index2), Cells(headingRow, firstColumn + 1).value, sheet)
If checkIfValidCombination(rowLong, firstColumn + 1, valueProductNumber2(0), 6, sheet) = False Then
GoTo nextIndex2
End If
For index3 = 0 To UBound(values3) Step 1
Cells(rowLong, firstColumn + 2).value = values3(index3)
valueProductNumber3 = getProductData(values3(index3), Cells(headingRow, firstColumn + 2).value, sheet)
If checkIfValidCombination(rowLong, firstColumn + 2, valueProductNumber3(0), 7, sheet) = False Then
GoTo nextIndex3
End If
For index4 = 0 To UBound(values4) Step 1
Cells(rowLong, firstColumn + 3).value = values4(index4)
valueProductNumber4 = getProductData(values4(index4), Cells(headingRow, firstColumn + 3).value, sheet)
If checkIfValidCombination(rowLong, firstColumn + 3, valueProductNumber4(0), 8, sheet) = False Then
GoTo nextIndex4
End If
For index5 = 0 To UBound(values5) Step 1
Cells(rowLong, firstColumn + 4).value = values5(index5)
valueProductNumber5 = getProductData(values5(index5), Cells(headingRow, firstColumn + 4).value, sheet)
If checkIfValidCombination(rowLong, firstColumn + 4, valueProductNumber5(0), 9, sheet) = False Then
GoTo nextIndex5
End If
For index6 = 0 To UBound(values6) Step 1
Cells(rowLong, firstColumn + 5).value = values6(index6)
valueProductNumber6 = getProductData(values6(index6), Cells(headingRow, firstColumn + 5).value, sheet)
If checkIfValidCombination(rowLong, firstColumn + 5, valueProductNumber6(0), 10, sheet) = False Then
GoTo nextIndex6
End If
For index7 = 0 To UBound(values7) Step 1
Cells(rowLong, firstColumn + 6).value = values7(index7)
valueProductNumber7 = getProductData(values7(index7), Cells(headingRow, firstColumn + 6).value, sheet)
If checkIfValidCombination(rowLong, firstColumn + 6, valueProductNumber7(0), 11, sheet) = False Then
GoTo nextIndex7
End If
For index8 = 0 To UBound(values8) Step 1
Cells(rowLong, firstColumn + 7).value = values8(index8)
valueProductNumber8 = getProductData(values8(index8), Cells(headingRow, firstColumn + 7).value, sheet)
If checkIfValidCombination(rowLong, firstColumn + 7, valueProductNumber8(0), 12, sheet) = False Then
GoTo nextIndex8
End If
For index9 = 0 To UBound(values9) Step 1
Cells(rowLong, firstColumn + 8).value = values9(index9)
valueProductNumber9 = getProductData(values9(index9), Cells(headingRow, firstColumn + 8).value, sheet)
If checkIfValidCombination(rowLong, firstColumn + 8, valueProductNumber9(0), 13, sheet) = False Then
GoTo nextIndex9
End If
For index10 = 0 To UBound(values10) Step 1
Cells(rowLong, firstColumn + 9).value = values10(index10)
correct = calculatePrice(Cells(rowLong, firstColumn))
'we have a valid combination -> insert new row
If correct = True Then
rowLong = rowLong + 1
Call copyRow
Cells(rowLong, firstColumn).value = values1(index1)
Cells(rowLong, firstColumn + 1).value = values2(index2)
Cells(rowLong, firstColumn + 2).value = values3(index3)
Cells(rowLong, firstColumn + 3).value = values4(index4)
Cells(rowLong, firstColumn + 4).value = values5(index5)
Cells(rowLong, firstColumn + 5).value = values6(index6)
Cells(rowLong, firstColumn + 6).value = values7(index7)
Cells(rowLong, firstColumn + 7).value = values8(index8)
Cells(rowLong, firstColumn + 8).value = values9(index9)
Cells(rowLong, firstColumn + 9).value = values10(index10)
End If
Next index10
nextIndex9:
Next index9
nextIndex8:
Next index8
nextIndex7:
Next index7
nextIndex6:
Next index6
nextIndex5:
Next index5
nextIndex4:
Next index4
nextIndex3:
Next index3
nextIndex2:
Next index2
nextIndex1:
Next index1
endFound = True
Loop
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "done!"
End Sub
Function getAllCombinationsOfDropdownInCell(columProductName)
'your own function
End Function
Function checkIfValidCombination()
'your own function
End Function
Function calculatePrice()
'your own function
End Function