Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1228to1232
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 beschleunigen

Makro beschleunigen
Wolfgang
Hallo an alle,
habe folgendes Problem und hoffe, ihr könnt mir ein paar Tips geben..
In einer Excel Datei sind auf 15 Arbeitsblättern jeweils 10 kleine Rechentabellen untergebracht.
Zu diesen soll, für den Fall dass sie ausgefüllt worden sind, ein Diagramm angezeigt werden, dessen Daten aus einer anderen Excel Datei stammen.
Das Problem ist nur, dass ein Makrodurchlauf, wenn für alle Tabellen Daten geholt werden müssen, ca. 1:15 Minuten dauert.
Der Code, der für eine der 150 Tabellen die Daten holt sieht folgenermaßen aus:
----------
If Worksheets("WSG30").Cells(4, 1).Value = 0 Then
Worksheets("WSG30").ChartObjects("Diagramm 3").Visible = False
Else
Worksheets("WSG30").ChartObjects("Diagramm 3").Visible = True
Dateiname = Worksheets("WSG30").Cells(4, 1).Value & ".xlsx"
objexcel.Workbooks.Open (ThisWorkbook.Path & "\" & Dateiname)
objexcel.Visible = False
Set objsheet = objexcel.Sheets("2012")
izl = 30
Do Until inh = "Bedarf"
inh = objsheet.Cells(izl, 1).Value
izl = izl + 1
Loop
For Spalte = 4 To 15 Step 1
Worksheets("Tabelle5").Cells(58, Spalte).Value = objsheet.Cells(izl - 1, Spalte)
Next
Do Until inh = "Normal-Kapazität"
inh = objsheet.Cells(izl, 1).Value
izl = izl + 1
Loop
For Spalte = 4 To 15 Step 1
Worksheets("Tabelle5").Cells(61, Spalte).Value = objsheet.Cells(izl - 1, Spalte)
Next
Do Until inh = "Maximal-Kapazität"
inh = objsheet.Cells(izl, 1).Value
izl = izl + 1
Loop
For Spalte = 4 To 15 Step 1
Worksheets("Tabelle5").Cells(68, Spalte).Value = objsheet.Cells(izl - 1, Spalte)
Next
objexcel.Workbooks.Close
End If
-----
Dieser Code ist aktuell ganze 150 mal vorhanden, weil ich es nicht geschafft habe, die Worksheet/ Diagrammwahl über Variablen zu steuern.
Nun habe ich persönlich 3 Ansätze, wie es eventuell schneller geht,
Zum einen habe ich versucht, mit Arrays den Kopiervorgang zu erledigen..
Da ich mich mit Arrays nicht auskenne bin ich da schonmal gegen eine Wand gelaufen..
Zum anderen steht die Frage im Raum, ob man die Daten kopiert bekommt, ohne die Excel Dateien alle einzeln zu öffnen.. bzw. ob man die Suche innerhalb der Exceldatei durchführen kann, ohne diese zu öffnen.
Und ja..die dritte Möglichkeit ist vielleicht den Code per Variabler Worksheet/ Diagrammanwahl zu verkürzen, wobei ich wie schon erwähnt daran gescheitert bin.
Ich hoffe ihr könnt mir hier weiterhelfen.
Lieben Gruß

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Makro beschleunigen
16.09.2011 08:48:44
Daniel
Hi
mal ein paar Ideen zur Optimierung:
die Schleife

For Spalte = 4 To 15 Step 1
Worksheets("Tabelle5").Cells(58, Spalte).Value = objsheet.Cells(izl - 1, Spalte)
Next

lässt sich auch einfach als Formel abbilden und wird damit schneller ausgeführt:

with Worksheets("Tabelle5").Cells(58, 4).Resize(1, 12)
.formulaR1C1 = "="'" & objsheet.name & "'!R" & izl-1 & "C"
.formula = .Value
end with

und das hier

izl = 30
Do Until inh = "Bedarf"
inh = objsheet.Cells(izl, 1).Value
izl = izl + 1
Loop

geht einfacher mit der .Find-Funktion:

izl = 30
izl = objsheet.Range("A" & izl & ":A10000").Find(what:="Bedarf", lookat:=xlwhole).Row

oder mit Worksheetfunction.Match (entspricht der Excelfunktion "Vergleich")

izl = 30
izl = izl -1 + Worksheetfunction.Match("Bedarf", objSheet.Range("A" & izl & ":A10000"), 0)
Gruß, Daniel
Anzeige
AW: Makro beschleunigen
16.09.2011 09:47:19
Wolfgang
Hallo,
Danke schonmal für die Ideen, bin gerade am umbauen des Codes.
Jetzt aber auch direkt noch eine Frage:
Die Zeile izl= 30 habe ich nur eingefügt, weil vor Zeile 30 das Wort Bedarf nicht vorkommen KANN,
für die FIND Funktion dürfte das aber doch egal sein, oder?
(Jede Zeile weniger ist ein Genuss :-) )
Gruß
AW: Makro beschleunigen
16.09.2011 10:31:49
Daniel
HI
wenn es vorher nicht vorkommen kann, kannst du es auch weglassen.
zwar dürfte auch die Find-Funktion schneller werden, wenn sie weniger Zellen durchsuchen muss, aber bei 30 Zellen ist das so minimal, daß es wahrscheinlich noch nicht mal messbar ist.
Gruß, Daniel
Anzeige
AW: Makro beschleunigen
16.09.2011 13:03:27
Wolfgang
Hallo noch einmal,
habe den Code jetzt nach Daniels Schema umgebaut,
Neue Zeit: 1:05 Minuten :D
Ist nur eigentlich immer noch zu lange...
Hab jetzt mal ne kleine Schleife getippt, die nur einfach 150 mal ein workbook öffnet und schließt...
Ergebnis: Das alleine kostet schon mehr als 45 Sekunden ...-.-
Bleiben gerade mal 20 Sekunden für Suche der Daten und kopieren/ Verweise erstellen.
Kann man an dem Öffnen/Schließen Block irgendetwas ändern?
Soweit auf jeden Fall schon Danke für die Tips!
Beim zweiten Vorschlag habe ich das Problem, dass er mir in der Zeile arrTmp = [...]
"Laufzeitfehler13" zurückgibt.
Mach ich was an der Deklarierung der Variablen arrTmp falsch?
(arrTmp (12) as integer bzw. arrTmp () as integer) Liebe Grüße
P.S.:
Habe am Code noch etwas geändert:
.FormulaR1C1 = "='[" & Dateiname & "]" & objsheet.Name & "'!R" & izl & "C"
=> Ohne Dateiname kein Zugriff :-)
Anzeige
AW: Makro beschleunigen
16.09.2011 13:18:16
Rudi
Hallo,
versuch mal
arrTmp = .Range(.Cells(iZl, 4), .Cells(iZl, 15)).Value
Gruß
Rudi
AW: Makro beschleunigen
16.09.2011 13:44:39
Wolfgang
Muss ich leider nochmal passen,
zuerst bekomme ich in der Zeile izl = Application.Match[..] einen Fehler gemeldet,
wenn ich izl nach Daniels Lösung einfüge, also:
izl = objsheet.Range("A" & izl & ":A10000").Find(what:="Bedarf", lookat:=xlwhole).Row
komme ich zwar über die Hürde, bleibe aber wieder bei arrTmp stehen.
Habe aber für
arrTmp () bzw. arrTmp (12) zwei unterschiedliche Fehler. Sobald in den Klammern etwas steht bekomme ich die Meldung
"Keine Zuweisung an Datenfeld möglich"..
Was bedeuten die beiden Fehler denn?
Anzeige
AW: Makro beschleunigen
16.09.2011 14:38:07
Rudi
Hallo,
in der Zeile izl = Application.Match[..] einen Fehler gemeldet
dann gibt's den Suchbegriff nicht.
arrTmp muss so wie ich geschrieben habe definiert sein. Ohne Klammern.
Evtl. klappt das so:

  Sub yyyy()
Dim iZl As Variant, objWkb As Workbook, objSheet As Worksheet, objExcel As Application
If Worksheets("WSG30").Cells(4, 1).Value = 0 Then
Worksheets("WSG30").ChartObjects("Diagramm 3").Visible = False
Else
Worksheets("WSG30").ChartObjects("Diagramm 3").Visible = True
Dateiname = Worksheets("WSG30").Cells(4, 1).Value & ".xlsx"
Set objExcel = CreateObject("excel.application")
objExcel.Visible = False
Set objWkb = objExcel.Workbooks.Open(ThisWorkbook.Path & "\" & Dateiname)
Set objSheet = objWkb.Sheets("2012")
With objSheet
iZl = Application.Match("Bedarf", .Columns(1), 0)
If Not IsError(iZl) Then
arrTmp = .Range(.Cells(iZl, 4), .Cells(iZl, 15))
Worksheets("Tabelle5").Cells(58, 4).Resize(, 12) = arrTmp
End If
iZl = Application.Match("Normal-Kapazität", .Columns(1), 0)
If Not IsError(iZl) Then
arrTmp = .Range(.Cells(iZl, 4), .Cells(iZl, 15))
Worksheets("Tabelle5").Cells(61, 4).Resize(, 12) = arrTmp
End If
iZl = Application.Match("Maximal-Kapazität", .Columns(1), 0)
If Not IsError(iZl) Then
arrTmp = .Range(.Cells(iZl, 4), .Cells(iZl, 15))
Worksheets("Tabelle5").Cells(68, 4).Resize(, 12) = arrTmp
End If
End With
objWkb.Close False
End If
Set objExcel = Nothing
End Sub

Gruß
Rudi
Anzeige
AW: Makro beschleunigen
19.09.2011 07:43:07
Wolfgang
Hallo Rudi,
funktioniert leider wieder nicht, selbe Fehlermeldung.
Das Wort Bedarf habe ich nochmal nachgeschaut, kommt sicher vor.
Muss das ganze aber so oder so heute den Nutzern zur Verfügung stellen.
Danke auf jeden Fall an euch beide,
für Gedanken und Hilfe :-)
Und sollte ich noch ein Geheimnis zur Beschleunigung entdecken,
werde ich das ganze natürlich posten ;-)
Grüße
AW: Makro beschleunigen
16.09.2011 10:11:07
Rudi
Hallo,
noch ne Möglichkeit:

  If Worksheets("WSG30").Cells(4, 1).Value = 0 Then
Worksheets("WSG30").ChartObjects("Diagramm 3").Visible = False
Else
Worksheets("WSG30").ChartObjects("Diagramm 3").Visible = True
Dateiname = Worksheets("WSG30").Cells(4, 1).Value & ".xlsx"
objExcel.Workbooks.Open (ThisWorkbook.Path & "\" & Dateiname)
objExcel.Visible = False
Set objSheet = objExcel.Sheets("2012")
With objSheet
iZl = Application.Match("Bedarf", .Columns(1), 0)
arrTmp = .Range(.Cells(iZl, 4), .Cells(iZl, 15))
Worksheets("Tabelle5").Cells(58, 4).Resize(, 12) = arrTmp
iZl = Application.Match("Normal-Kapazität", .Columns(1), 0)
arrTmp = .Range(.Cells(iZl, 4), .Cells(iZl, 15))
Worksheets("Tabelle5").Cells(61, 4).Resize(, 12) = arrTmp
iZl = Application.Match("Maximal-Kapazität", .Columns(1), 0)
arrTmp = .Range(.Cells(iZl, 4), .Cells(iZl, 15))
Worksheets("Tabelle5").Cells(68, 4).Resize(, 12) = arrTmp
End With
objExcel.Workbooks.Close
End If

Gruß
Rudi
Anzeige
AW: Makro beschleunigen
16.09.2011 13:22:17
Wolfgang
Hallo ihr beiden nochmal,
habe jetzt den Vorschlag von Daniel komplett umgesetzt( das ganze 150 mal ohne Fehler umzubauen ist recht mühsam :-) )und tata...ist auf jeden fall schneller!
Nur...noch nicht cshnell genug. Stehe gerade bei 1:05 Minuten.
Habe Spaßeshalber mal die Zeit gestoppt, die eine Schleife braucht, um 150 mal ein Workbook zu öffnen und wieder zu schließen...sind immehrin 45 Sekunden.
Kann ich hier noch was schrauben?
Denke, dass ich an den 20 Sekunden nicht mehr allzuviel schrauben kann, die das Makro braucht um die Daten zu lesen/ schreiben?
@Rudi,
hätte deinen Vorschlag gerne ausprobiert, aber scheinbar mache ich bei der Deklaration von arrTmp etwas falsch, zumindest gibt er mir den Laufzeitfehler 13 "Typen unverträglich" zurück.
Habe es mit arrTmp () as Integer bzw arrTmp (12) as Integer versucht...?
Und Danke an euch beide für die Hilfe bisher!
P.S.: Daniel, ich habe an deinem Code auch etwas gebastelt und zwar wie folgt:
.FormulaR1C1 = "='[" & Dateiname & "]" & objsheet.Name & "'!R" & izl & "C"
Denke da ist dir in der Hektik was verloren gegangen :-)
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige