Zum WE: Ranges u.Arrays in Xl u.VBA
29.08.2020 22:34:08
Luc:?
Ein Xl-Blatt basiert bekanntlich auf einem festen Raster aus Zeilen und Spalten. Jede dieser Zellen und jeder Teilbereich aus mehreren Zellen ist folglich mit einem Index aus Zeilen- und Spaltennr identifizierbar (Adresse). Im Ergebnis einer Berechnung kann eine Zelle oder auch mehrere auf einmal mit einem Wert (oder eben mehreren Werten auf einmal) gefüllt wdn. Ein Einzelwert ist dabei idR ein dimensionsloser Skalar, mehrere zusammenhängende (bzw in neuesten Xl-Versionen per dynamischem Array berechnete) Werte bilden eine Matrix, sind also stets 2dimensional. Das muss auch bei der Übernahme von Xl-Bereichen in VBA-Programmvariablen beachtet wdn! In Xl-Berechnungsergebnissen können allerdings auch 0-dimensionale Werte vorkommen, sog skalare Tensoren(0,0)¹, wie sie bspw von den Xl-Funktionen ZEILE und SPALTE geliefert wdn². Durch die Ausgabe auf ein Xl-Blatt wdn sie zwar wieder zu einfachen dimensionslosen Skalaren, können aber in Formeln stören, vor allem, wenn diese eigene Funktionen (sog UDFs) enthalten, die diese Xl-Funktionen in ihren Argumenten verwenden und das in ihrem Korpus nicht beachten (ohne IsArray-Einsatz).
Im Gegensatz zur direkten Übernahme eines Xl-Bereichs (ByRef), der seine 2Dimensionalität behält, wird ein (berechnetes) Datenfeld (ByVal, das nur aus einer Zeile besteht, als 1dimensionaler echter Kovektor in VBA abgebildet. Das ist dann mit einem Tensor(0,1) identisch. Ansonsten folgt VBA der Grundstruktur von Xl und verwendet stets Matrizen. Demzufolge wird ein aus einer Spalte bestehender Vektor auch per VBA 2dimensional abgebildet, was auch erforderlich ist, um ihn von einem Kovektor zu unterscheiden. In der Mathematik ist zwar der Vektor primär und der Kovektor sekundär, aber Xl ist nun mal wie eine Datenbank datensatz-orientiert und diese entsprechen Tabellenzeilen also Kovektoren (Listenaufbau, deshalb kann man auch nur nach den Ausprägungen einer Datensatz-Eigenschaft im Kopf der Tabelle, also Zeilenwerten einer Spalte filtern).
Aus dieser Xl-Grundstruktur und ihrer Adaption in VBA hat man in VBA einen eigenen n-dimensionalen Array-Typ entwickelt, der deshalb in anderen Sprachen mitunter als VBA-Array bezeichnet wird (zB in JavaScript). Denn dieser Typ wird üblicherweise nicht verwendet, weil er zu unhandlich ist. Aber auch VBA kann einen üblicheren Typ verwenden, indem ein quasi-n-dimensionales Array auf der Basis 1dimensionaler Arrays aufgebaut wird. Klassisch entspräche das einer Schichtung von (1dimensionalen) Tensoren³, wobei die Elemente eines Vektors aus Kovektoren bestehen, was VBA bei entsprechender Zuweisung automatisch so einstellt. D.h., wenn man einem 1dimensionalen Array (Kovektor) lauter ebenfalls 1dimensionale Arrays als Elemente zuweist, entsteht aus einem Tensor(0,1) ein Tensor(1,1), der zwar einer Matrix entspricht, aber nur in einer Zellformel mittels des Formeltext-Interpreters automatisch in eine abbildbare 2dimensionale Matrix gewandelt wird. In einer Subprozedur entfällt das natürlich und man muss es selber programmieren oder zeilenweise ausgeben bzw ggf eine dazu fähige Xl-Funktion (zB INDEX) benutzen. Die automatische Umwandlung in Xl-Formeln ist selbstverständlich auch nur dann möglich, wenn sich dadurch eine reguläre Matrix ergibt, also alle Kovektoren gleichviel Elemente enthalten. Darüber hinausgehende Tensor-Kombinationen oder die in VBA ebenfalls möglichen „Zwitter“ aus VBA-Array und Tensoren bzw nach diesem Prinzip geschichteten VBA-Arrays wie sie zB auch mit einem ParamArray als Eingabe-Parameter bzw -Argument möglich sind, lassen sich nicht ohne Verdichtung der Matrixformat überschreitenden Werte (bzw Expansion in größere Matrizen)⁴ auf ein Xl-Blatt abbilden, was ja auch bei n-dimensionalen (n>2) VBA-Arrays der Fall ist.
Ein Vektor lässt sich übrigens auch mit Tensoren darstellen. Dabei wdn den Elementen eines 1dimensionalen Arrays skalare Tensoren⁵ zugeordnet. So ergibt sich dann ein Tensor(1,0). Das Ganze setzt natürlich in allen höheren (prioritären) Stufen voraus, dass deren Tensoren bzw VBA-Arrays als Variant deklariert wurden, denn nur ein solcher kann auch Arrays aufnehmen. Das Endstufen-Array kann dagegen mit beliebigem passenden Datentyp deklariert wdn.
Ein Variant muss nicht dimensioniert deklariert wdn, um ein Array aufnehmen zu können. Soll eine Variable redimensioniert wdn, muss sie zuvor auch nicht deklariert wdn. Es ist aber trotzdem angebracht, weil sie sonst der automatischen Übernahme ihrer Schreibweise (GB/kb) im gesamten Programm verlustig geht (hier auch die VBE-Hilfe beachten!). Im Gegensatz zu n-dimensionalen Arrays kann bei einem aus Tensoren zusammengesetzten Array jeder Tensor einzeln redimensioniert wdn. Dabei ist es auch möglich, den Indexbereich zu verschieben ohne auf ein (weiteres) Preserve verzichten zu müssen. Der Vektor eines 2stufigen Tensors wird dabei wie ein 1dimensionales Array behandelt, während seine Elemente, Kovektoren einer Quasi-Matrix, per Durchlauf einzeln redimensioniert wdn müssen, wobei sie wegen der nicht ReDim-kompatiblen Indizierungsmethode, (Zeilenindex)(Spaltenindex), temporär in eine Variant-Hilfsvariable übernommen wdn müssen. Bei Array-„Zwittern“ aus VBA-Arrays (ggf in Kombination mit Tensoren), auch als Arrays in Arrays bekannt, sind dabei natürlich auch deren Einschränkungen zu beachten. Analog ist ebenfalls bei höherstufigen Tensoren bzw -dimensionalen VBA-Arrays zu verfahren.
Natürlich kann ein mehrstufiger Tensor (bzw ein Array-„Zwitter“) auch leer zusammengesetzt wdn, um dann in einem Durchlauf oder mehreren verschachtelten Durchläufen befüllt zu wdn. Ein 4stufiger echter Tensor(2,2) hätte dabei die Einzelelement-Indizierungsform av4Tensor(i)(j)(k)(l) und entspräche somit insgesamt einem Datentessarakt (4dimensionalen Datenkubus), dessen Einzelelemente in VBA-Array-Form so indiziert würden → ar4Tessar(i,j,k,l).
Im Folgenden wird eine derartige Tensor(2,2)-Bildungsmöglichkeit gezeigt, wobei ein zusammenhängender Zellbereich in Tensoren(0,1) zerlegt wird, die dann zu einem Tensor(2,2) zusammengefügt wdn. Mit dem 4./5.Argument der Funktion kann man dann eine der ineinandergeschachtelten Teil-/Submatrizen (bzw Daten-/Subebenen) auswählen. Es wäre natürlich auch möglich, einen bestimmten Kovektor zurückzugeben, indem man nur die ersten 3 Indizes angibt. Bei den Vektoren (Spalten) ist das komplizierter. Hier müssten beim Anlegen des Tensors von vornherein Spalten und Zeilen vertauscht wdn, damit das einfach möglich ist. Allerdings könnte ggf auch INDEX (wie üblich) auf eine isolierte Matrix angewendet wdn.
Rem Konstruktion 4stufiger Tensor(2,2) aus Datenfeld bzw kontinuierl Bereich
' Arg1: DQuelle (ZellBezüge in Werte umgewandelt); Argg2/3: einheitl Größe
' (Zeilen/Spalten) zu bildender u.lt Argg4/5 so anzuzeigender SubMatrizen;
' Argg4/5: flfd Index auszugebender SubMatrix f.HptZeile u.-Spalte aus Di-
' vision Arg1-Größe durch Argg2/3 (b.nicht restloser Aufteilbark d.Gesamt-
' Matrix lt Arg1 bleiben Elemente in d.jeweils letzten Zeilen-/SpaltenSub-
' Matrizen leer, Darstell als 0 oder LeerText richtet sich nach d.Kontext)
' --> soviele Zeilen/Spalten wdn gezeigt, wenn beide Argg4/5 fehlen, keine
' Zahl bzw 0 sind, wobei b.Aufruf in 1er SubProz in d.1.bd Fällen der kom-
' plette Tensor zwecks Weiterverarbeit übgeben wird. Anderenfalls wdn alle
' 3 Fälle gleich behandelt u.die SubMatrizen pro Zelle als Matrixkonstantt
' in TextForm ausgegeben, wobei eine Umwandlg in Matrizen als Elemente 1er
' Matrix erfolgt (nur 1 fehlendes Arg4|5 wird dagg stets durch 1 ersetzt).
' Diese Argg vertragen d.Verwendg v.ZEILE u.SPALTE z.IndexErmittl, Argg2/3
' aber nicht, Arg1 auch ein Datenfeld (u.U. aus einem Ausdruck berechnet).
' Hinweis: Diese UDF ist zur Demonstration 1er möglichen Vorgehensweise b.
' Konstruktion eines 4stufigen Tensors (Qubix: Variant m.Arrays in Arrays)
' gedacht, kann aber auch weitergehend verwendet wdn, wobei das dann v.Fml
' bzw verwendendem Pgm abhängt. Eine Möglichk wäre d.Erzeug v.TabbStapeln,
' die m.d.UDF TensEx wdr in SubMatrizen bzw -Ebenen aufgelöst wdn könnten.
' Vs1.2 -LSr:CyWorXxl -cd:20200824 -1pub:20200829h -lupd:20200828t
Function TensQubix(Bezug, ByVal ErgMxZln As Long, ByVal ErgMxSpn As Long, _
Optional ByVal SubMxZl = "", Optional ByVal SubMxSp = "")
Dim isTensOut As Boolean, cc(1) As Long, cx(1) As Long, px As Long, _
rc(1) As Long, rd As Long, rx(1) As Long, sMx(1) As Long, _
elst$, mxLRKl$, mxSTrz$, mxZTrz As String, wf As WorksheetFunction, _
el, erg, pKv, pVk, sKv, sVk, xBez, zBez As Variant
On Error GoTo fx: Set wf = WorksheetFunction
With Application
mxLRKl = .International(xlLeftBrace) & " " & .International(xlRightBrace)
mxSTrz = .International(xlColumnSeparator)
mxZTrz = .International(xlRowSeparator)
On Error Resume Next: isTensOut = IsError(.Caller): On Error GoTo fx
End With
If IsArray(SubMxZl) Then SubMxZl = wf.Index(SubMxZl, 1)
If wf.IsNumber(SubMxZl) Then sMx(0) = SubMxZl: isTensOut = False
If IsArray(SubMxSp) Then SubMxSp = wf.Index(SubMxSp, 1)
If wf.IsNumber(SubMxSp) Then sMx(1) = SubMxSp: isTensOut = False
If TypeOf Bezug Is Range Then Bezug = Bezug.Value2
cc(1) = ErgMxSpn: rc(1) = ErgMxZln
cc(0) = Abs(Int(UBound(Bezug, 2) / -cc(1)))
rc(0) = Abs(Int(UBound(Bezug, 1) / -rc(1)))
rd = rc(0) * rc(1) - UBound(Bezug, 1)
ReDim pVk(rc(0) - 1), pKv(cc(0) - 1), sVk(rc(1) - 1), sKv(cc(1) - 1)
For px = 0 To rc(1) - 1: sVk(px) = sKv: Next px
For px = 0 To cc(0) - 1: pKv(px) = sVk: Next px
For px = 0 To rc(0) - 1: pVk(px) = pKv: Next px
For Each xBez In Bezug
If Not IsEmpty(xBez) Then pVk(rx(0))(cx(0))(rx(1))(cx(1)) = xBez
rx(1) = (rx(1) + 1) Mod rc(1): rx(0) = (rx(0) - CInt(rx(1) = 0)) Mod rc(0)
If CBool(rd) And (rx(0) + 1) = rc(0) Then
If rx(1) + rd = rc(1) Then rx(0) = 0: rx(1) = 0
End If
cx(1) = (cx(1) - CInt(rx(0) + rx(1) = 0)) Mod cc(1)
cx(0) = (cx(0) - CInt(rx(0) + rx(1) + cx(1) = 0)) Mod cc(0)
Next xBez
If isTensOut Then
TensQubix = pVk
ElseIf sMx(0) + sMx(1) = 0 Then
cx(0) = 0: cx(1) = 0: ReDim erg(rc(0) - 1, cc(0) - 1)
For Each zBez In erg
For Each xBez In pVk(rx(0))(cx(0))
For Each el In xBez
If Not (wf.IsNumber(el) Or wf.IsError(el)) Then
elst = elst & " """ & el & """"
Else: elst = elst & " " & CStr(el)
End If
Next el
sVk(rx(1)) = Join(Split(LTrim(elst)), mxSTrz): elst = ""
rx(1) = (rx(1) + 1) Mod rc(1)
Next xBez
erg(rx(0), cx(0)) = Replace(mxLRKl, " ", Join(sVk, mxZTrz))
rx(0) = (rx(0) + 1) Mod rc(0)
cx(0) = (cx(0) - CInt(rx(0) = 0)) Mod cc(0)
Next zBez
TensQubix = erg
Else: sMx(0) = wf.Max(1, sMx(0)): sMx(1) = wf.Max(1, sMx(1))
TensQubix = pVk(sMx(0) - 1)(sMx(1) - 1)
End If
fx: If CBool(Err.Number) And IsEmpty(TensQubix) Then _
TensQubix = "Error " & Err.Number
Set wf = Nothing
End Function
Rem RufProz f.SubProz TableStack - Var EinzelWert
Sub RufTbStEw(): Call TableStack(True): End Sub
Rem RufProz f.SubProz TableStack - Var TabbStapel
Sub RufTbStAw(): Call TableStack: End Sub
Rem BspProz f.Anwendg UDF TensQubix zur Anlage eines TabbStapels oder Rückgabe
' 1es Einzelwerts; Zielzelle (1.Zelle zu übschreibd Zielbereichs) auswählen!
' Vs1.0 -LSr:CyWorXxl -cd:20200828 -1pub:20200829h -lupd:20200828t
Sub TableStack(Optional ByVal eWert As Boolean)
Const adQBer$ = "Tabelle3!A157:F168" 'Die UDF TensQubix kann sowohl als Bestandteil von Zellformeln als auch in Subprozeduren eingesetzt wdn, wobei hierbei fehlende Argumente 4 und 5 unterschiedlich behandelt wdn (vgl UDF-Kommentar). Fehlen diese im Zellformeleinsatz, wdn sie als 0 gewertet, was zur komprimierten Ausgabe aller Werte in Form von lokalen Matrixkonstanten als Text führt. Dabei wdn sowohl die Primärtensoren (die Hauptebene) als auch die sekundären (mit den Werten) in normale 2dimensionale Xl-Matrizen umgewandelt. Diese sekundären Matrixkonstanten wdn nur im 0-Fall beider Argumente 4/5 in der Demo-Subprozedur TableStack als lokale Formeln angelegt, so dass sie letztendlich die Haupt- und Subebenen des so erzeugten Tabellenstapels (Qubix) bilden. Nur die Werte der Hauptebene wdn so im Tabellenblatt gezeigt, die Werte der Subebenen sind aber ebenfalls vorhanden. Wie schon früher gezeigt, kann man so etwas auch zur Tabellen-Obfuskation verwenden, ggf auch mit zusätzlicher vorheriger Verschlüsselung nach einem Verfahren eigener Wahl. Die realen Daten können zuvor über alle zu bildenden Ebenen (bzw auch nachträglich über die gebildeten) verteilt wdn, wobei sich der Datenschlüssel aus den Positionsindizes der Echtdaten im Qubix ergibt.
Die Demo-Subprozedur⁶ enthält auch einen Zweig, der auf die Übergabe des kompletten Tensors(2,2) wie er ist reagiert und einen Einzelwert in die ausgewählte Zelle einträgt. In beiden Varianten wird dabei eine Submatrix-Größe von 3 Zeilen × 2 Spalten vorausgesetzt; für die Einzelwert-Variante außerdem noch eine sich aus der Aufteilung des Gesamtbereichs ergebende Hauptebenen-Größe von mindestens ebenfalls dieser Größe. Anderenfalls müssten die Indizes entsprechend angepasst wdn. In der Qubix-Variante muss natürlich beachtet wdn, dass der benötigte Platz auf dem Blatt frei ist, sonst wdn dort ggf vorhandene Einzeldaten überschrieben, während das Überschreiben von Daten aus Matrixformeln zu einem Fehler führt.
__________________________________
¹ Ein Tensor ist eine math Funktion der Form Tensor(r,s), wobei r und s nichts über die Anzahl seiner Elemente oder die Dimensionalität aussagen; r+s ergibt stets die Stufigkeit (auch Rang genannt) eines Tensors (vgl bspw Wikipedia).
² Im zu Unrecht oft vernachlässigten Funktionsassistenten erkennbar an den geschweiften Klammern um ihr Ergebnis.
³ In anderen Sprachen sind auch reine Datenfelder (Arrays) oft eigene Objekte, in VBA aber beide Typen (inkl ParamArray) nicht. Objekte sind hier nur Datensammlungen (Collection) und Listen von Objekten, sowie das einem assoziativen Array vglbare Dictionary-Objekt, das aber nicht Gegenstand dieser Ausführungen ist.
⁴ Xl erzeugt bei bestimmten Berechnungen selbst solche eher „exotischen“ quasi-n-dimensionalen Strukturen, kann aber wenigstens ihre 1.Ebene abbilden (auch bei dynamischen Arrays), was mit VBA nicht in gleicher Weise erreichbar ist, denn die nicht abgebildeten Werte sind in Xl durchaus vorhanden, können aber nicht oW indiziert wdn. Mit zusätzlichen Funktionen ist aber ihre Auswertung möglich.
⁵ Skalarer Tensor(0,0) → ein Array, das nur ein Element enthält (vgl Xl-Fktt ZEILE und SPALTE).
⁶ Zum schnellen Aufruf der jeweils gewünschten TableStack-Variante sind die beiden Rufprozeduren beigefügt.
Schönen Sonntag! Luc :-?
Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder. Stapps ironisches Paradoxon