VBA 2-Dimension-Collection in Tabellenblatt einfügen

[gehtnetgibtsnet]

Hallo Excel-Fachleute.

Ich nutze eine Routine, die ich in einem Forum gefunden habe. Diese Routine liefert mir die gewünschten Daten in einer 2-D-Collection und stellt mir als Ergebnis die Collection zu Verfügung.

Jetzt möchte ich gerne den Inhalt der Collection in ein Tabellenblatt einfügen.

Da durchaus die Möglichkeit besteht, dass ich als Antwort mehrere Hundert Ergebnisse geliefert bekomme, möchte ich die Daten nich unbedingt mit Hilfe einer Loop in das Tabellenblatt übertragen.

Frage: gibt es dafür eine elegantere Möglichkeit?

Ich habe eine kleine Testumgebung aufgebaut. Zuerst wird eine 2D-Collection mit den Zahlen 1 bis 26 und im 2. Feld mit den Buchstaben a bis z gefüllt. Danach wird die Collection an den Aufrufer zurückgegeben. Nun sollen die Daten noch in eine (leere) Tabelle eingefügt werden.

Irgendwie habe ich mit der Umwandlung der Collection in einen Array Probleme. Ich kann den Ubound() des Arrays nicht mehr debuggen, Ein Debugging eines anderen 2D-Arrays dagegen funktioniert. Allerdings kann ich einzelne Elemente des Array durchaus debuggen. Und: natürlich funktioniert auch das Laden der Daten in die Tabelle nicht.

Hat jemand eine Idee?

 

[alxa]

2-D-Collection...Möglichkeit besteht, dass ich als Antwort mehrere Hundert Ergebnisse geliefert bekomme, möchte ich die Daten nich unbedingt mit Hilfe einer Loop in das Tabellenblatt übertragen.

Führt kein Weg dran vorbei. Ich glaube, dir graut es nur vom Coding allgemein.

Irgendwie habe ich mit der Umwandlung der Collection in einen Array Probleme. Ich kann den Ubound() des Arrays nicht mehr debuggen,

Schleife drüberlegen und direkt über die Collection iterieren und Elemente auf die Console printen?
(https://stackoverflow.com/questions/2916287/where-does-vba-debug-print-log-to)
Schau mal, dass du das erst hinbekommst, um für Coding-Grundlagen zu sorgen. (dein Nickname verpflichtet dich leider dazu😁)

 

[G-Red]

Es gibt eine möglichkeit die Collection komplett in eine Range einzufügen ohne Schleife, falls es deinem Vorhaben entspricht.

Wie das geht, hier ein StackOverflow

 

[gehtnetgibtsnet]

Hallo alxa,

nein, mir graut es nicht vor dem Coding. In der Zeit, in der ich die Frage im Forum gestellt habe, hätte ich das mit der Loop mehrfach codieren können. Doch: mir liegt nicht an einer möglichen Lösung; ich suche eine sinnvolle Lösung. Muss an meiner Vergangenheit liegen: ich war +40 Jahre in der Datenverarbeitung auf Großsystemen (ohne Windows und Office) tätig. Da hat man auch nicht eine schnelle Lösung gesucht, sondern eine sinnvolle, vorallem dann, wenn der Code nicht nur für einen einmaligen Einsatz erstellt werden soll.

Dir und G-Red herzlichen Dank für die Links. Ich werde mich baldmöglichst damit beschäftigen.

Natürlich ist man froh, wenn man für bestimmte Vorgänge Musterlösungen in den Foren findet, und diese dann so einsetzen kann, dass man diesen Code auch versteht.

Selbstverständlich habe ich, bevor ich im Forum die Frage gestellt habe, auch die Suche-Funktion benutzt. Doch keine Anwort auf meine Frage gefunden.

 

[G-Red]

ich war +40 Jahre in der Datenverarbeitung auf Großsystemen (ohne Windows und Office) tätig.

Warst aber nicht in einem RZ im öffentlichen Dienst tätig?

 

[gehtnetgibtsnet]

G-Red, warum fragst Du?

 

[G-Red]

G-Red, warum fragst Du?

War mal ne Zeitlang für ein RZ im Öffentlichen dienst tätig, da habe ich ähnliches von Kollegen älteren Jahrgangs gehört was das verständniss des Codes betraf. Die jüngeren Kollegen hatten teils bizare Vorstellungen von der Wiederverwendung des fremden Codes . Da hat man mich ua. gebeten ein Stück fremden Code zu implementieren was funktionierte, ohne es vorher zu verstehen, hauptsache schnell schnell .

 

[gehtnetgibtsnet]

Hallo G-Red,

ja, es ist korrekt, dass ich auch in einem solchen RZ tätig war. 99 Prozent meiner Programme habe ich in Assembler erstellt. Die waren (und sind noch immer) teilweise auf IBM-Rechnern und auf BS-2000-Rechnern im Einsatz.

Würde gerne mit Dir außerhalb des Forums Kontakt aufnehmen, die Frage ist nur; wie?

 

[gehtnetgibtsnet]

Hallo alxa,

ich habe mich mit Deinem Link beschäftigt. Danke dafür, dass Du mich über die Verwendung von Debug.Print aufklären möchtest. Meine Antwort: ich bin schon früher aufgeklärt worden.

Das Problem: die von mir per "Set" erzeugte Array sieht zwar wie eine Array aus, und kann auch wie eine Array bearbeitet werden. Jedoch fehlen ihr ein paar Eigenschaften. Deshalb kann man Ubound() und Lbound() nicht auslesen. Das war der Grund, warum ich das nicht debuggen kann.

Man muss wissen, dass eine Collection immer mit 1 beginnt. Und die Anzahl der Einträge kann man mit collectionname.Count ermitteln. Dann kann man eine solche Array trotzdem verarbeiten.

Jetzt werde ich mich noch mit dem Link von G-Red beschäftigen und melde mich dann wieder.

 

[gehtnetgibtsnet]

Hallo G-Red,

inzwischen habe ich mich mit Deinem Link beschäftigt. Es gibt eine klare Aussage: eine Collection kann nicht direkt in ein Tabellenblatt eingefügt werden. Einziger Ausweg: die Collection muss zuvor in einen Array umgewandelt werden. Das geht jedoch nur mit einer Loop. Bei mir kommt erschwerend hinzu. dass die beiden Informationen, welche mit "Add" zu der Collection hinzugefügt werden, als Array definiert werden müssen. Deshalb muss ich zuerst diesen Array wieder auflösen, um dann die beiden Werte in den neu zu füllenden Array einzufügen.

ReDim CollVar(1 To FileColl.Count, 1 To 2)
For VLoop = 1 To FileColl.Count
WorkVar = FileColl(VLoop)
CollVar(VLoop, 1) = WorkVar(0)
CollVar(VLoop, 2) = WorkVar(1)
Next VLoop

Gleiches gilt, wenn ich die Daten aus der Collection direkt in das Tabellenblatt einfügen möchte.

Sollte es eine andere Möglichkeit geben, auf die Array-Daten in jedem Collection-Eintrag zugreifen zu können, und mir dadurch den Umweg über die WorkVar ersparen kann, wäre ich für entsprechende Informationen sehr dankbar.

Nun ein paar Performance-Daten:

Ich habe die Auswertung derart eingestellt, dass ca. 40.000 Add's in die Collection gemacht wurden. Dafür wurden weniger als 0,7 Sekunden benötigt.

Für den Umbau dieser Daten in eine 2-Dim-Array habe ich zusätzlich 5,7 Sekunden benötigt. Und wenn ich die Daten nicht in einen Array umwandle, sondern direkt in das Tabellenblatt (Update-Event: off) einfüge, werden zusätzliche 9,2 Sekunden benötigt.

Meine favoritisierte Lösung ist folgende:

Ich habe jetzt 2 Arrays, welche ich fülle:

CollVar1(VLoop) = WorkVar(0)
CollVar2(VLoop) = WorkVar(1)

Danach fülle ich diese beiden Array in das Tabellenblatt:

Range("A1").Resize(FileColl.Count) = WorksheetFunction.Transpose(CollVar1)
Range("B1").Resize(FileColl.Count) = WorksheetFunction.Transpose(CollVar2)

Für das Füllen des Tabellenblattes werden 0,3 Sekunden benötigt.

Ich habe meine Test-Mappe entsprechend umgestellt und erneut hochgeladen.

Für Verbessungsvorschläge bin ich offen und bedanke mich bei Allen, die mir geantwortet haben.