MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 11.01.2017, 16:46   #1
maxs1904
Neuer Benutzer
Neuer Benutzer
Standard VBA - VBA häufig durchlaufender Code wird sehr langsam

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 <= 2

Worksheets("Berechnung_KA_DE").Range("I2:I6").Clear
Worksheets("Berechnung_KA_DE").Range("J2:L8761").Clear
Worksheets("Berechnung_KA_DE").Range("K2:P8761").Clear
Worksheets("Berechnung_KA_DE").Range("S2:AC8761").Clear
Worksheets("Berechnung_KA_DE").Range("I2:I6").Clear
Worksheets("Berechnung_KA_DE").Range("AF2:AF8761").Clear
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False

'Range("AE2:AE8761").Copy
'Range("AF2:AF8761").PasteSpecial xlPasteValues

wks("Berechnung_KA_DE").Cells(13, 8) = wks("Lastgaenge_KA-Liste_DE").Cells(1, spalte_ka)
'Lastgang für Größenklasse kopieren und einfügen
wks("Lastgaenge_KA-Liste_DE").Range(wks("Lastgaenge_KA-Liste_DE").Cells(14, spalte_ka), wks(" _
Lastgaenge_KA-Liste_DE").Cells(8773, spalte_ka)).Copy
wks("Berechnung_KA_DE").Cells(2, 11).PasteSpecial xlPasteValues
'Speichergröße kopieren und einfügen
wks("Lastgaenge_KA-Liste_DE").Cells(9, spalte_ka).Copy
wks("Berechnung_KA_DE").Cells(2, 9).PasteSpecial xlPasteValues
'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
'Gasertrag einfügen
wks("Lastgaenge_KA-Liste_DE").Cells(7, spalte_ka).Copy
wks("Berechnung_KA_DE").Cells(4, 9).PasteSpecial xlPasteValues
wks("Berechnung_KA_DE").Cells(5, 9) = wks("Berechnung_KA_DE").Cells(4, 9) / 8760
i = 2

For i = 1 To 8761
Cells(i, 12) = Cells(5, 9) * 0.614 * 10 * Cells(3, 9)
Next i

Application.CutCopyMode = False

'BHKW-Nennleistung kopieren und einfügen einfügen
wks("Lastgaenge_KA-Liste_DE").Cells(8, spalte_ka).Copy
wks("Berechnung_KA_DE").Cells(6, 9).PasteSpecial xlPasteValues

wks("Berechnung_KA_DE").Cells(8, 9) = (spalte_ka - 1) / 317 * 100

z = 2
Application.CutCopyMode = False

While Cells(z, 1) <> ""
'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 < 50 Then
z = z - 1
End If

Wend
Application.CutCopyMode = False

j = 2
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

Application.CutCopyMode = False

wks("Lastgaenge_KA-Liste_DE").Cells(8780, spalte_ka) = wks("Berechnung_KA_DE").Cells(8761, 23) - _
wks("Berechnung_KA_DE").Cells(8761, 22)
wks("Lastgaenge_KA-Liste_DE").Cells(8781, spalte_ka) = wks("Berechnung_KA_DE").Cells(8761, 29) - _
wks("Berechnung_KA_DE").Cells(8761, 28)
spalte_ka = spalte_ka + 1
Application.CutCopyMode = False

Next spalte_ka 'Wend
End Sub



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
maxs1904 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 17:14   #2
Klaus-Dieter
MOF Profi
MOF Profi
Standard

Hallo Max,

habe das jetzt nicht alles untersucht. Zwei Dinge sind mir aufgefallen:

1. Das ist überflüssig: spalte_ka = 2, da über eine Schleife der Startwert der Variable ohnehin definiert wird, brauchst du diese Zuweisung nicht.

2. Hiermit: Cells(i, 12) = Cells(5, 9) * 0.614 * 10 * Cells(3, 9) bekommen 8761 Zellen den gleichen Wert zugewiesen. Da ich deine Liste nicht kenne, kann ich nur den Vorschlag machen, das nicht über eine Schleife laufen zu lassen. Mach das einfach so:


Code:

wert = Cells(5, 9) * 0.614 * 10 * Cells(3, 9)
Range("M1:M8761") = wert
das erspart 8760 Zellzugriffe.

__________________


Viele Grüße Klaus-Dieter

Klaus-Dieter's Excel und VBA Seite
Klaus-Dieter ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 17:24   #3
maxs1904
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo Klaus-Dieter,

danke für die Antwort, werde ich mal ausprobieren. Ist sonst generell etwas daneben, was die Verlangsamung erklärt, wie bspw., dass etwas zwischengespeichert wird, das gar nicht benötigt wird in weiteren Durchläufen nach dem ersten?

Danke und Gruß
Max
maxs1904 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 17:35   #4
Klaus-Dieter
MOF Profi
MOF Profi
Standard

Hallo Max,

wie ich schon geschrieben hatte, habe ich das nicht alles untersucht, nur anhand des Quelltextes kann man dazu nicht so viel sagen. Ungünstig ist es immer wenn viele Zellen über eine Schleife direkt angesprochen werden. Da lohnt es sich oft, die Berechnungen, oder was auch immer in Array-Variablen auszulagern, um diese dann direkt in die Liste zu schreiben. (Minimierung der Anzahl an Zellzugriffen).

__________________


Viele Grüße Klaus-Dieter

Klaus-Dieter's Excel und VBA Seite
Klaus-Dieter ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.01.2017, 20:04   #5
Hans Hofmann
MOF Profi
MOF Profi
Standard

Hallo,

grundsätzlich sind String-Operationen eine Performance-Bremse, weil der Text-Speicher zu gemüllt wird, was zu einer aufwändigen Garbage-Collection zum Aufräumen führt.
Also z.B. Textoperationen
Range("M" & z, "M" & z + 23) mit Cells(13,z) ausführen
usw.
Auch die Aktionen über die Zwischenablage wenn eh blos xlPasteValues bewegt werden sind zeitaufwändig verwende eine einfache Zuweisung

wks("Berechnung_KA_DE").Cells(4, 9).Value = wks("Lastgaenge_KA-Liste_DE").Cells(7, spalte_ka).Value

Es könnte helfen statt der Namen-Referenzen einen Objekt-Pointer zu setzen und damit zu arbeiten:
Set KA_DE = wks("Berechnung_KA_DE")
KA_DE.Cells(4, 9).Value

__________________

Gruß HW

WebSite: Veröffentlichungen zu PP & VBA


Geändert von Hans Hofmann (11.01.2017 um 20:38 Uhr). Grund: Objekt-Pointer ergänzt
Hans Hofmann ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.01.2017, 09:01   #6
maxs1904
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Vielen Dank für die Ratschläge! Werde diese umsetzen und mich dann wieder melden!
maxs1904 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.01.2017, 10:26   #7
Beverly
MOF Guru
MOF Guru
Standard

Hi Maximilan,

beachte bitte künftig, dass Crossposting (das gleichzeige Posten von Fragen in mehreren Foren) unerwünscht ist.


GrußformelBeverly's Excel - Inn

__________________

Möchtest du dich für die Hilfe bei der Lösung deines Problems bedanken? Das kannst du ganz einfach durch die Bewertung eines Beitrags (Schalter unten links).
Beverly ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.01.2017, 13:19   #8
maxs1904
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Sorry!
maxs1904 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 22:23 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.