Office: Definierte Tabelle als Range verwenden

Hallo zusammen,

bei meinen ersten Versuchen mit VBA in Excel 2007 stehe ich gerade vor dem Problem eine bestimmte Spalte einer Tabelle Zeile für Zeile zu durchwandern. Ich verwende Tabellen da ich mit diesen den Datenbereich komfortabel definieren kann und nicht den Code bzw. Steuerdaten verändern muss. Allerdings weiß ich nicht wie ich auf eben jene Tabelle in Form einer Range zugreifen kann.
Unten dargestellter Code soll in Spalte 10 die einmaligen Werte identifizieren (Code-Nummern der Arbeitspakete) und diese in einem weiteren Sheet in die benamte Ragen "pspElements" schreiben. Letztere wird dann von einem Drop-Down Feld im User-Interface genutzt um ein bestimmtes PSP Element für weitere Berechnungen auszuwählen.
Leider kommt es in der rot markierten Zeile zu einem Type Mismatch. Wenn ich die Range manuell angebe (z.B. J2:J100) passiert der Fehler nicht.

Hat jemand eine Idee wie ich das lösen könnte? Ich würde nur ungern auf die Tabelle verzichten...


Vielen Dank,
Klayman

Code:

:)

 

Hallo Klayman,

probier mal:
Code:

Grüße
EarlFred

 

tatsache - funktioniert :-) Vielen Dank dafür!!
Aber zum generellen Problem: Wie kann ich aus einer Table eine Range machen? Das nächste Problem das ich haben werde ist es einen Filter auf eben jene Table zu legen. Es sollen dann nur die Einträge mit einem bestimmten PSP-Code (Spalte 10) gefiltert werden. Anschließend wird über die gefilterten(!) Reihen berechnet (for each) und zum Schluss der Filter wieder gelöscht. Ich erhoffe mir daraus eine schnellere Abarbeitung als wenn ich jede Zeile der Table auf das PSP-Kriterium prüfe und sie nur wenn true in die Berechnung einbeziehe. Irgendwelche Ideen?

Viele Grüße,
Klayman

Edit: Handelt es sich bei einer Tabelle um eine abgeleitete Range die eben über noch mehr Attribute und Funktionen verfügt? Wäre die Methode Cells() dann eine Refernz zur eigentlichen Range??

 

Hallo Klayman,

das tust Du doch schon!
Mit Deinem Code greifst Du auf den Datenbereich (als Range!) der Liste zu. Ich verstehe Deine Frage also nicht, was Du anderes erreichen willst.

Thema schneller / langsamer:
Das "Abklappern" in der Tabelle nach Filtern unter Verwendung von SpecialCells(xlCellTypeVisible) ist schneller als alles zu prüfen. Wenn Du Performance über alles wünscht, ist das dennoch nicht die beste Wahl - mitunter ist sie kaum schneller, als alles zu prüfen (je nach Datenlage, Filterung etc).

Einen zusammenhängenden Bereich einer Tabelle kannst Du (in "einem Rutsch") in ein Datenfeld (Array) einlesen und dieses anstelle des eigentlichen Tabelleninhalts zum Arbeiten nehmen. Das ist Welten schneller als alle "Abklappercodes", die auf das Tabellenblatt selbst zugreifen - bei großen Datenmengen merkst Du das gewaltig! Ich rede hier davon, dass Codes anstelle vieler Sekunden (teilweise sogar Minuten) zum Abarbeiten plötzlich nur noch Bruchteile von Sekunden brauchen - nur um die Größenordnung zu verdeutlichen.

Daneben gibt es aber auch weitere Ansätze, gegen die selbst Arrays uralt aussehen (können): Datenbankabfragen oder Dictionarys (nur als Beispiel)
Datenbankabfragen: Universell einsetzbar - wer SQL beherrscht, sollte hier einsteigen, da er wenig neues Lernen muss. Gute Lektüre: Online - Excel Programmierung VBA Makro: ADO und Excel (1) - Kleines Vorwort und Einstieg
Dictionarys: Sparen die ADODB-Verbindung und damit etwas Zeit - sind aber nicht für alle Fälle geeignet obwohl sehr felxibel, aber man muss es "verstehen" und warten können - das kann komplizierter werden als die Anpassung eines SQL-Statements... Je nach Anzahl der Abfragen holt ADO dann den "Zeitverlust" wieder rein - abzüglich Malus für die komplexere Wartung.... Entscheide selbst.
Noch etwas Schub: Early Binding anstelle Late Binding, womit Du Dir aber Nachteile wiederum in der Flexibilität einkaufst...

Also: Genug Ideen?

Viel Spaß bei der Umsetzung!

Grüße
EarlFred

 

Das Tabellenblatt ist ein Objekt (Klasse), in welchem die Zellen also gleichwertige Objekte identischer Eigenschaften zusammengefasst bzw. enthalten sind. Somit ist das Tabellenblatt der Container, der die Zellen enthält und folglich in der Objekthierarchie das übergeordnete Objekt, welches logischerweise mehr Eigenschaften als das enthaltene Objekt Zelle hat. Auf die Eigenschaften eines übergeordneten Objektes kann man über die Parent-Eigenschaft des Objektes (Zelle/Zellen) zugreifen.

Die Schreibweise Cells() ist ein Bezug zur Zelle selbst, nicht zum Tabellenblatt. Cells() verweist immer nur auf 1 Zelle, wogegen Range entweder auf eine Zelle oder auf einen Zellbereich verweisen kann.

 

Hallo EarlFred,

danke für die Tipps. Meine ursprüngliche Frage war wie ich das Table-Objekt als Range übergeben/verwenden kann. Anscheinend schafft die Methode Cells Abhilfe, allerdings verstehe ich nicht warum. Wenn ich mir vom ListObject (i.e. die Tabelle) die Adresse ausgeben lasse, erhalte ich exakt den gleichen Wert wie wenn ich eine "echte" Range manuell erstelle und mit einem String zuweise. Ich kann aber das ListObject Objekt nicht an Funktionen übergeben die eine Range erwarten (eigentlich klar, aber ich hatte gehofft dass die irgendwie vererbt sind).

Was die Performance angeht so werde ich zunächst mal Deinen Vorschlag mit den Arrays ausprobieren. Über SQL Queries und ADO auf andere Exceldateien zuzugreifen klingt aber auch spannend. Die Frage ist ob es so viel performanter ist... On va voir ;-)

Viele Grüße,
Klayman

 

Hallo Klayman,

nochmal: Du liest doch die Range der Liste aus!
Code:

Diese kannst Du weiter verarbeiten, übergeben, zerhackstücken und und und wie jede andere Range auch - denn es ist eine Range! *wink.gif*

Du bekommst lediglich Deine Schwierigkeiten, weil Du mit Columns(10) auf eine Spalte dieser Range verweist - hier knistert es halt bei der For-Each-Schleife, nicht aber bei der später verwendeten Funktion CountIf!

Bei dieser Zeile
Code:

ist Trim(Cel.Value) das Problem, nicht aber ResultRng!

Nimm mal Deinen Code und füge VOR dieser Zeile jene ein:
Code:

Fällt der Groschen? Du kannst als 2. Parameter bei CountIf nicht mehr als einen Wert übergeben, tust es aber trotzdem (böse, böse! *wink.gif*) - das mag Excel nunmal nicht.

Um nun also wirklich jede einzelne Zelle in der For-Each-Schleife abzuarbeiten:
For Each Cel In SearchRng.Cells

Das hat - ich wiederhole mich lieber - nix mit dem Auslesen der Range der Liste an sich zu tun.


Grüße
EarlFred

 

Hallo nochmal,

vielleicht noch als Abhilfe folgende Codeänderung:
Code:

Damit sollte es auch in Deinem Code laufen.

Grüße
EarlFred

 

Vielen Dank für Eure Hilfe, ich hab es hin bekommen und werde "nur" die Performance noch ein wenig zu verbessern versuchen.

Viele Grüße,
Klayman

 

Hallo nochmal :-)

bin noch ein wenig am Grübeln wie ich eine Range möglichst optimal in einen array bekomme um besagte Performancevorteile zu erreichen. Bislang laufe ich jede Zeile einer auf einem Worksheet definierten Tabelle durch und stelle bestimmte Berechnungen an.

Code:

Wenn ich es richtig verstanden habe, dann kostet jede Interaktion mit einem Worksheet Zeit, d.h. man liest eine Range möglichst zu Beginn als Array in den Speicher (wie groß darf der sein??) und rechnet dann damit.
Man könnte natürlich jetzt Zeile für Zeile die Range durchgehen und diese in einen Array einlesen. Aber würde das nicht genausolange dauern? In den beiden Funktionen werden aus der übergebenen Reihe vier oder fünf Werte ausgelesen und daraus der Rückgabewert berechnet.

Viele Grüße,
Klayman

 

Hallo Klayman,

zu Limitationen von Arrays:
http://www.xlam.ch/xlimits/arrays.ht...y-Limitationen

Daten in ein Array einzulesen geht nach folgendem Prinzip:
Code:

Also: Der Array-Variablen vom Typ Variant werden die Werte eines Blattbereichs zugewiesen. Das ist alles.

Bei Listen:
Code:

Grüße
EarlFred

 

Super, das funktioniert schonmal. Kann ich denn einen Array per For Each Schleife durchlaufen und dann eine gesamte Zeile an eine Funktion übergeben? Meine bisherigen Versuche mit 'arrayRow as Variant' waren leider nicht von Erfolg gekrönt.
Alternativ müsste man wohl eine Zeile als eindimensionalen Array einlesen und diesen übergeben...

Viele Grüße,
Klayman

 

Hallo Klayman,

um eine Zeile aus einem Array zu fischen, kannst Du die Arbeitsblattfunktion INDEX nehmen:
Beispiel Zeile 2:
Code:

Für die 2. Spalte, falls von Interesse:
Code:

Beachte: arrTeil ist bei diesen Beispielen eindimensional, wenn Du eine Zeile ausliest, zweidimensional, wenn Du eine Spalte ausliest.

Grüße
EarlFred

 

Hallo EarlFred,

danke für Deine Tipps. Ich habe inzwischen festgestellt, dass Arrays immer byRef übergeben werden. Der Funktion übergebe ich daher jetzt nur die Referenz auf den Array sowie einen Index auf die erste Dimension, i.e. die Zeile.
Was soll ich sagen? Es ist wahnsinnig schnell geworden, von ca. 7 Sekunden auf unter eine!! Von daher: Thumbs up!!

Viele Grüße,
Klayman

 

Hallo Klayman,

ByRef ist Standardwert in VBA, nicht nur bei Arrays. Arrays könnten aber auch ByVal übergeben werden, wenn sinnvoll.

Grüße
EarlFred