Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1532to1536
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Makro wird sehr langsam (mit Code)

Makro wird sehr langsam (mit Code)
11.01.2017 16:22:41
Max
Guten Tag zusammen,
ich habe folgendes Problem: Habe mir für den OpenSolver einen Optimierungsalgorithmus geladen, der ein definiertes Problem zeitlich dynamisch lösen soll. Konkret geht es um ein System aus Stromerzeugung, Bezug und Speicher, das kostenoptimal betrieben werden soll. Dafür liegt eine Strompreiszeitreihe vor.
Für dieses Problem soll für jeden Tag eines Jahres nacheinander für 300 Akteure mit unterschiedlichen Strombedarfen, Erzeugungsvariablen etc. die kostenoptimale Lösung ermittelt werden (365 Tage *300 Akteure) und einige Ergebniszahlen festgehalten werden.
Bei der Ausführung des folgenden Makros wird der Arbeitsspeicher mit der Zeit voll und das _
Makro wird unfassbar langsam. Anfangs benötigt das Makro noch ca. 2 Sekunden pro Tag --> ca. 12 _
Minuten/Akteur; das wäre noch in Ordnung, jedoch nimmt die Bearbeitungszeit schnell zu bis auf _
10 Sekunden pro Tag. Gibt es eine Möglichkeit, den folgenden Code umzuschreiben, sodass alles, _
was die Bearbeitung des Makros verlangsamt, umgangen wird`?

Private Sub CommandButton1_Click()
Set wks = Worksheets
Dim spalte_ka As Long
Dim z As Long
Dim i As Long
Application.Calculation = xlCalculationManual
Application.CutCopyMode = False
spalte_ka = 2
For spalte_ka = 2 To 318 'While wks("Lastgaenge_KA-Liste_DE").Cells(1, spalte_ka)  "" And  _
spalte_ka  ""
'Aufsetzen Zielfunktion und Entscheidungsvariablen
OpenSolver.ResetModel
OpenSolver.SetObjectiveFunctionCell Range("Q" & z + 23)
OpenSolver.SetObjectiveSense (MinimiseObjective)
OpenSolver.SetDecisionVariables Range(Cells(z, 13), Cells(z + 23, 16))
'Nichtnegativitätsbedingungen
OpenSolver.AddConstraint Range("M" & z, "P" & z + 23), RelationGE, , 0
'Leistung BHKW nicht größer als Nennleistung
OpenSolver.AddConstraint Range("N" & z, "N" & z + 23), RelationLE, Range("I6")
'Gasspeicher nicht über Kapazität
OpenSolver.AddConstraint Range("M" & z, "M" & z + 23), RelationLE, Range("I2")
OpenSolver.AddConstraint Range("O" & z, "O" & z + 23), RelationLE, Range("I2")
'Gasmenge, die verstromt wird, nicht größer als die
Application.CutCopyMode = False
t = 1
For t = 1 To 24
'Strombedarf = Netzbezug + BHKW-Leistung
OpenSolver.AddConstraint Range("K" & z), RelationEQ, , Range("N" & z).Address & "+" & Range("P"  _
_
_
_
_
_
_
_
_
_
_
& z).Address
' Speicherfüllstand am Ende
OpenSolver.AddConstraint Range("O" & z), RelationEQ, , Range("M" & z).Address & "+" & Range("L"  _
_
_
_
_
_
_
_
_
_
_
& z).Address & "-" & Range("N" & z).Address
z = z + 1
Next t
Application.CutCopyMode = False
'Speicherfüllstand Übergabe
t = 1
If z = 26 Then
z = z - 23
Else
z = z - 24
End If
For t = 1 To 24
OpenSolver.AddConstraint Range("M" & z), RelationEQ, , Range("O" & z - 1).Address
z = z + 1
Next t
Application.CutCopyMode = False
Cells(9, 9) = (z - 2) / 24
OpenSolver.RunOpenSolver
If z 

Die Submakros des OpenSolver kann ich hier aufgrund der Größe nicht veröffentlichen, sie sind zudem nicht von mir geschrieben. Da die anfängliche Bearbeitung jedoch schnell geht, hoffe ich, dass der Fehler nicht in diesen Funktionen, sondern in der Gestaltung der Rechenabfolge, die die sequenzielle BEarbeitung der einzelnen Tage und Akteure beinhaltet, liegt.
Ich hoffe auf Eure Hilfe, beste Grüße
Max

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro wird sehr langsam (mit Code)
11.01.2017 16:24:51
Max
Keine Ahnung wie die "-" da reingerutscht sind, die sind im Originalcode natürlich nicht enthalten.
AW: Makro wird sehr langsam (mit Code)
11.01.2017 16:26:29
Max
Hier nochmal der Code so, wie er sein sollte:
Private Sub CommandButton1_Click()
Set wks = Worksheets
Dim spalte_ka As Long
Dim z As Long
Dim i As Long
Application.Calculation = xlCalculationManual
Application.CutCopyMode = False
spalte_ka = 2
For spalte_ka = 2 To 318 'While wks("Lastgaenge_KA-Liste_DE").Cells(1, spalte_ka)  "" And  _
spalte_ka  ""
'Aufsetzen Zielfunktion und Entscheidungsvariablen
OpenSolver.ResetModel
OpenSolver.SetObjectiveFunctionCell Range("Q" & z + 23)
OpenSolver.SetObjectiveSense (MinimiseObjective)
OpenSolver.SetDecisionVariables Range(Cells(z, 13), Cells(z + 23, 16))
'Nichtnegativitätsbedingungen
OpenSolver.AddConstraint Range("M" & z, "P" & z + 23), RelationGE, , 0
'Leistung BHKW nicht größer als Nennleistung
OpenSolver.AddConstraint Range("N" & z, "N" & z + 23), RelationLE, Range("I6")
'Gasspeicher nicht über Kapazität
OpenSolver.AddConstraint Range("M" & z, "M" & z + 23), RelationLE, Range("I2")
OpenSolver.AddConstraint Range("O" & z, "O" & z + 23), RelationLE, Range("I2")
'Gasmenge, die verstromt wird, nicht größer als die
Application.CutCopyMode = False
t = 1
For t = 1 To 24
'Strombedarf = Netzbezug + BHKW-Leistung
OpenSolver.AddConstraint Range("K" & z), RelationEQ, , Range("N" & z).Address & "+" & Range("P"  _
& z).Address
' Speicherfüllstand am Ende
OpenSolver.AddConstraint Range("O" & z), RelationEQ, , Range("M" & z).Address & "+" & Range("L"  _
& z).Address & "-" & Range("N" & z).Address
z = z + 1
Next t
Application.CutCopyMode = False
'Speicherfüllstand Übergabe
t = 1
If z = 26 Then
z = z - 23
Else
z = z - 24
End If
For t = 1 To 24
OpenSolver.AddConstraint Range("M" & z), RelationEQ, , Range("O" & z - 1).Address
z = z + 1
Next t
Application.CutCopyMode = False
Cells(9, 9) = (z - 2) / 24
OpenSolver.RunOpenSolver
If z 

Anzeige
Das sind keine - sondern _ :-)
11.01.2017 16:36:42
lupo1
.
Und es wird wieder passiert sein. Keine Sorge! Die Antworter kennen das.
AW: Makro wird sehr langsam (mit Code)
11.01.2017 17:12:31
Cardexperte
Hallo Max,
habe mir immer die Frage gestellt, ob Excel das richtige Programm ist, bin zu folgender Überzeugung gelangt:
bis 1000 Datensätze Excel geeignet, alles was darüber ist, Datenbankprogramm Access, wie ich folgender Zeile entnehme: J2:L8761, werden ca. 8000 Zellen und mehr gelöscht, damit folgt siehe oben.
Eine Möglichkeit ist noch die manuelle Berechnung einzuschalten und nur gelegentlich "richtig" zu berechnen, aber ist nur eine Hilfskrücke.
AW: Makro wird sehr langsam (mit Code)
11.01.2017 17:21:40
Maximilian
Hallo cardexperte,
danke für die Antwort, wenn sie auch eher frustrierend als aufbauend ist ;-). Kannst du mir denn verraten, wo Du das Problem vermutest? Wie beschrieben kann ich mit der Dauer eines Durchlaufs/Jahres rechnen, wenn das gleiche dann für den nächsten Akteur gestartet wird, wird es jedoch deutlich langsamer. Beim nächsten Akteur dann wieder...
Ich vermute, dass dort irgendetwas zwischengespeichert wird, was ich gar nicht benötige (?).
Beste Grüße
Max
Anzeige
Nur 1000 Zeilen in Excel?
11.01.2017 17:23:44
lupo1
... dann schreibst Du aber schlechten Code oder verwendest die falschen Formeln.
Je mehr Zeilen Du verwendest, desto mehr sollte allerdings auch die Verantwortung und das Wissen um das Modell standhalten. Das ist richtig.
paar Vorschläge
11.01.2017 18:00:19
Michael
Hi Max,
es gibt quasi keinen Codeblock, der nicht optimierungswürdig wäre - mangels Open Solver und Daten hier nur ein paar allg. Hinweise:
Alt:

Worksheets("Berechnung_KA_DE").Range("I2:I6").Clear
Worksheets("Berechnung_KA_DE").Range("J2:L8761").Clear ' K zwischen J und L
Worksheets("Berechnung_KA_DE").Range("K2:P8761").Clear ' K zwischen J und L
Worksheets("Berechnung_KA_DE").Range("S2:AC8761").Clear
Worksheets("Berechnung_KA_DE").Range("I2:I6").Clear ' doppeltm s. 1. Zeile
Worksheets("Berechnung_KA_DE").Range("AF2:AF8761").Clear
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False ' hier unnötig

Neu:

Worksheets("Berechnung_KA_DE").Range("I2:I6").Clear
Worksheets("Berechnung_KA_DE").Range("J2:P8761").Clear ' zusammengefaßt
Worksheets("Berechnung_KA_DE").Range("S2:AC8761").Clear
Worksheets("Berechnung_KA_DE").Range("AF2:AF8761").Clear
Application.Calculation = xlCalculationAutomatic ' nach weiter unten verschieben

Alt:

'BHKW-Wirkungsgrad einfügen
wks("Lastgaenge_KA-Liste_DE").Cells(11, spalte_ka).Copy
wks("Berechnung_KA_DE").Cells(3, 9).PasteSpecial xlPasteValues
wks("Berechnung_KA_DE").Cells(3, 9) = wks("Berechnung_KA_DE").Cells(3, 9) / 100

Neu:

'BHKW-Wirkungsgrad einfügen
wks("Berechnung_KA_DE").Cells(3, 9).value = _
wks("Lastgaenge_KA-Liste_DE").Cells(11, spalte_ka) / 100
' die ganzen .copy/paste raus...

Alt:

i = 2 ' wozu, wenn in Schleife erneut gesetzt?
For i = 1 To 8761
Cells(i, 12) = Cells(5, 9) * 0.614 * 10 * Cells(3, 9)
Next i

Ganz am Anfang neue Variable Dim zw as double, die den Wert zwischenspeichert, dann:
zw = Cells(5, 9) * 6.14 * Cells(3, 9) ' 0.614 * 10 = 6.14
Range("L1:L8761").Value = zw

Alt:

For j = 1 To 8761
Cells(j, 19) = (Cells(j, 11) - Cells(j, 12)) * Cells(j, 5) / 1000
Cells(j, 20) = Cells(j, 16) * Cells(j, 5) / 1000
Cells(j, 22) = Application.WorksheetFunction.Sum(Range(Cells(1, 19), Cells(j, 19)))
Cells(j, 23) = Application.WorksheetFunction.Sum(Range(Cells(1, 20), Cells(j, 20)))
Cells(j, 25) = (Cells(j, 11) - Cells(j, 12)) * Cells(j, 6) / 1000
Cells(j, 26) = Cells(j, 16) * Cells(j, 6) / 1000
Cells(j, 28) = Application.WorksheetFunction.Sum(Range(Cells(1, 25), Cells(j, 25)))
Cells(j, 29) = Application.WorksheetFunction.Sum(Range(Cells(1, 26), Cells(j, 26)))
Next j

j ist übrigens nicht deklariert!
neu:

Sub t()
Dim j& ' ist noch nicht gedimt; & = as long
Dim iA ' in wie input + Array
Dim oST, oVW, oYZ, oABAC ' o wie output + Buchstabe
iA = Range("A1:P8761")
ReDim oST(1 To 8761, 1 To 2)
ReDim oVW(1 To 8761, 1 To 2)
ReDim oYZ(1 To 8761, 1 To 2)
ReDim oABAC(1 To 8761, 1 To 2)
For j = 1 To 8761
oST(j, 1) = (iA(j, 11) - iA(j, 12)) * iA(j, 5) / 1000
oST(j, 2) = iA(j, 16) * iA(j, 5) / 1000
oYZ(j, 1) = (iA(j, 11) - iA(j, 12)) * iA(j, 6) / 1000
oYZ(j, 2) = iA(j, 16) * iA(j, 6) / 1000
If j = 1 Then
oVW(j, 1) = oST(j, 1)
oVW(j, 2) = oST(j, 2)
oABAC(j, 1) = oYZ(j, 1)
oABAC(j, 2) = oYZ(j, 2)
Else
oVW(j, 1) = oVW(j - 1, 1) + oST(j, 1)
oVW(j, 2) = oVW(j - 1, 2) + oST(j, 2)
oABAC(j, 1) = oABAC(j - 1, 1) + oYZ(j, 1)
oABAC(j, 2) = oABAC(j - 1, 2) + oYZ(j, 2)
End If
Next j
'For j = 1 To 8761
' Cells(j, 19) = (Cells(j, 11) - Cells(j, 12)) * Cells(j, 5) / 1000
' Cells(j, 20) = Cells(j, 16) * Cells(j, 5) / 1000
' Cells(j, 22) = Application.WorksheetFunction.Sum(Range(Cells(1, 19), Cells(j, 19)))
' Cells(j, 23) = Application.WorksheetFunction.Sum(Range(Cells(1, 20), Cells(j, 20)))
' Cells(j, 25) = (Cells(j, 11) - Cells(j, 12)) * Cells(j, 6) / 1000
' Cells(j, 26) = Cells(j, 16) * Cells(j, 6) / 1000
' Cells(j, 28) = Application.WorksheetFunction.Sum(Range(Cells(1, 25), Cells(j, 25)))
' Cells(j, 29) = Application.WorksheetFunction.Sum(Range(Cells(1, 26), Cells(j, 26)))
' Next j
Range("S1").Resize(8761, 2) = oST
Range("V1").Resize(8761, 2) = oVW
Range("Y1").Resize(8761, 2) = oYZ
Range("AB1").Resize(8761, 2) = oABAC
End Sub

Schöne Grüße,
Michael
Anzeige
AW: Makro wird sehr langsam (mit Code)
12.01.2017 09:01:57
Maximilian
Danke für die Ratschläge! Werde versuchen, sie einzubauen und dann mal zu schauen, ob es besser wird.
Beste Grüße
Max

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige