Microsoft Excel

Herbers Excel/VBA-Archiv

Performance Optimieren, Array verwenden?


Betrifft: Performance Optimieren, Array verwenden? von: Alex
Geschrieben am: 29.06.2017 09:46:54

Servus zusammen,

für eine bessere Übersicht möchte ich verschiedene Teilprojektpläne in einem Zusammenführen.
Je nach Projekt gibt es zwischen 5-15 Teilprojekte und der maximale Bereich der importiert werden soll aus diesen Exceldateien ist "C10:CA228".
Ich bin bereits auf eine Funktion gestoßen, die es mir erlaubt aus Exceldateien zu importieren, ohne das ich diese jedes mal öffnen muss.

Problem ist: Das ganze dauert extrem lang, da die Formel jeden Wert einzeln überträgt.

Idee:
- das ganze in einem Array einlesen und dann komplett einmal schreiben?
--> Um das ganze mit einem Array zu machen, brauche ich wahrscheinlich nen halben Tag, da ich mich vor der Verwendung von Arrays immer gedrückt habe :D

wäre super, wenn ihr mir hier auf die Sprünge helfen könnte, um die Laufzeit zu verringern. Ob mit Arrays oder was anderes...

Anhang:
- Quelldatei (kleiner Ausschneit eines Teilprojektplans)
https://www.herber.de/bbs/user/114566.xlsx
- Zieldatei mit Code
https://www.herber.de/bbs/user/114565.xlsm


Sub Bereich_auslesen()
Dim pfad As String, datei As String, blatt As String, bereich As Range, zelle As Object

'** Angaben zur auszulesenden Zelle
pfad = "C:\Users\Meier\Desktop"
datei = "Projektplan_KW26.xlsm"
blatt = "1_Projektplan und -monitoring"
Set bereich = Range("C10:BB14")

'** Bereich auslesen
For Each zelle In bereich

'** Zellen umwandeln
zelle = zelle.Address(False, False)

'** Eintragen in Bereich
ActiveSheet.Cells(zelle.Row, zelle.Column).Value = GetValue(pfad, datei, blatt, zelle)

Next zelle

End Sub



Private Function GetValue(pfad, datei, blatt, zelle)

'** Daten aus geschlossener Arbeitsmappe auslesen
'*** Dimensionierung der Variablen
Dim arg As String
'Sicherstellen, dass das datei vorhanden ist
If Right(pfad, 1) <> "\" Then pfad = pfad & "\"
If Dir(pfad & datei) = "" Then
GetValue = "datei Not Found"
Exit Function
End If

'** Das Argument erstellen
arg = "'" & pfad & "[" & datei & "]" & blatt & "'!" & Range(zelle).Range("A1").Address(, ,  _
xlR1C1)
'** Auslesen über Excel4Macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Besten Dank und viele Grüße,
Alex

  

Betrifft: AW: Performance Optimieren, Array verwenden? von: Daniel
Geschrieben am: 29.06.2017 09:59:20

Hi
warum der komplzierte Umweg über das GetValue?
wenn du nur den Wert aus einer Zelle der anderen Datei lesen willst, kannst du doch auch einfach die Formel mit dem entsprechenden externen Zellbezug in die Zelle schreiben.

ansonsten läuft so was einfach schneller, wenn die Datei, aus der man die Daten lesen will, geöffnet ist.

Gruß Daniel


  

Betrifft: AW: Performance Optimieren, Array verwenden? von: Alex
Geschrieben am: 29.06.2017 10:06:11

Servus Daniel,

die Teilprojektdateien ändern sich jede Woche (..KW26, ..._KW27) daher funktioniert das nicht mit dem Bezug in den Zellen.
Das soll eine allg. gültige Vorlage sein.

P.S.
Wenn ich die Datei offen habe ist das Gegenteil der Fall und der Code ist extrem langsamer.

Beste Grüße,
Alex


  

Betrifft: AW: Performance Optimieren, Array verwenden? von: Daniel
Geschrieben am: 29.06.2017 10:20:14

Hi
du kannst ja ein Makro schreiben, welches die entsprechende Formel in die Zellen schreibt und dann ggf die Formeln durch Werte ersetzt.
hierbei kannst du die Formel in alle Zellen gleichzeitig schreiben, was schneller sein sollte als eine Schleife über die Einzelzellen.
Gruß Daniel


  

Betrifft: AW: Performance Optimieren, Array verwenden? von: Alex
Geschrieben am: 29.06.2017 14:48:06

Servus Daniel,

komme erst jetzt wieder dazu...
das hört sich gut an.

Frage:
Wie kann ich in alle Zellen auf einmal die korrekte Formeln reinschreiben?

ich würde das über eine Schleife machen, da jede Zelle im Zielworksheet auf eine andere Zelle im Quellworksheets bezieht...

z.b. nur für verschiedene Spalten

For i = 3 To 10

Worksheets("Tabelle2").Cells(11, i).FormulaLocal = "='C:\Users\meier\Desktop\[Projektplan_Forum] _
Quelldatei" & "'!" & Cells(11, i).adress


Next i
Besten Dank für die Hilfe,
Alex


  

Betrifft: AW: Performance Optimieren, Array verwenden? von: Hajo_Zi
Geschrieben am: 29.06.2017 15:06:55

Hallo Alex,

Worksheets("Tabelle2").Range("C11:J11").Formula = "='C:\Users\meier\Desktop\[Projektplan_Forum] _
Quelldatei" & "'!C11"


GrußformelHomepage


  

Betrifft: AW: Performance Optimieren, Array verwenden? von: Daniel
Geschrieben am: 29.06.2017 15:28:52

Hi

als Relativer Zellbezug ist es immer der selber Zellbezug (gleiche Zeile, gleiche Zeile, dh Versatz 0)
daher kannst du die Formel in alle Zellen gleichzeitig schreiben.

Gruß Daniel