Suchergebnisse (34 Treffer, 1 Begriff) 
Kategorie: Tabelle ▸ Zellen
Wie kann ich die Zelladresse bzw. die Zeilen-/Spaltennummer der aktiven Zelle über Makro feststellen lassen?
Hier sind ein paar Beispiele zur Arbeit mit den Zellangaben der gerade aktiven Zelle:
Sub Zelladresse() With ActiveCell MsgBox .Address MsgBox .Address(False) MsgBox .Address(, False) MsgBox .Address(False, False) MsgBox .Row MsgBox .Column MsgBox "Zeile: " & .Row & " - Spalte:" & .Column End With End Sub
Kategorie: Steuerelemente ▸ Userform
Wie kann man Werte von einer Userform-Textbox an eine Zelle übergeben?
Hier ist eine kleine Anleitung zum Probieren.
Nimm Dir am besten eine neue Mappe und vollziehe die folgenden Schritte nach.
Private Sub TextBox1_Change() range("A2") = TextBox1.Text End Sub
Private Sub CommandButton1_Click() Range("A3") = TextBox2.Text End Sub
Der zweiten Textbox ist diesmal keine Prozedur zugeordnet, also passiert beim Eintragen erstmal nichts. Der Inhalt dieser Textbox wird erst beim Klick auf die Schaltfläche in A3 übernommen.
Private Sub CommandButton2_Click() Unload Me End Sub
Sub Aufruf() UserForm1.Show End Sub
In Deiner Mappe hast Du jetzt:
Und nochmal der Tip: Im Editor kann man ganz einfach mit Strg + F6 zwischen den einzelnen Komponenten wechseln. Mit Strg + Tab ist es zwar auch möglich, bereitet aber Probleme, wenn Multipage-Elemente enthalten sind.
Der Code zum Kopieren:
Private Sub TextBox1_Change() Range("A2") = TextBox1.Text End Sub Private Sub CommandButton1_Click() Range("A3") = TextBox2.Text End Sub Private Sub CommandButton2_Click() Unload Me End Sub Sub Aufruf() UserForm1.Show End Sub
Kategorie: Arrays ▸ Arrayformeln
Was sind die Arrayformeln, die ab Excel 365 eingeführt wurden?
=SUMMEWENN(B2:B13;"Juni";C2:C13)
Auch hier haben wir in einer Zelle ein Ergebnis, nämlich die Summe. Der Unterschied bis hierher liegt nur in den fehlenden geschweiften Klammern.
Der grö�te Unterschied wird jedoch deutlich, wenn in eine Zelle folgende einfache Formel eingegeben wird:
=A2:C13
Geschieht dies bei einer Excelinstallation zum ersten Mal, erscheint eine Meldung:
Formel übergelaufen: Ihre Formel hat mehrere Werte zurückgegeben, weshalb wir sie in die benachbarten leeren Zellen haben überlaufen lassen.
Das Ergebnis wird dann auch deutlich: Die Formel liefert alle Werte, die sich im Bereich befinden, der in der Formel angegeben ist - von der 1 in A2 bis zur 600 in C13. Der erste Wert steht dabei in der Zelle mit der Formel, die anderen Werte rechts neben und unter dieser Zelle - die Formel ist übergelaufen. Das Verhalten ist vergleichbar mit der CSS-Eigenschaft float: left;. Allerdings steht die Formel tatsächlich nur in der einen Zelle; sie kann auch nur dort bearbeitet werden. Sichtbar wird das, wenn im Ergebnis der Inhalt einer anderen Zelle gelöscht werden soll - es geht nicht.
Dieses Verhalten, dass mehrere Ergebnisse ausgegeben werden sollen, kann vielfältig genutzt werden. Beispiel für eine weitere einfache Formel:
=WENN(C2:C13>400;B2:B13;"")
In Zeile 2 neben der Basistabelle liefert sie in den entsprechenden Zeilen die Monate, bei denen die Beträge grö�er als 400 sind. Das Gleiche passiert natürlich auch, wenn die Formel in eine andere Zeile eingetragen wird - nur hat man dann den Offset, weil die Ergebnisse ab der Zelle mit der Formel angezeigt werden.
Diese Formel würde die Zahlen in den entsprechenden Zeilen anzeigen, die zum "Juni" gehören:
=WENN(B2:B13="Juni";C2:C13;"")
Natürlich kann die als Arrayfunktion verwendete Wenn-Funktion auch in anderen Funktionen eingesetzt werden. Hier werden alle Zahlen aus C addiert, wenn sie grö�er als 400 sind:
=SUMME(WENN(C2:C13>400;C2:C13))
In dem Fall haben wir natürlich wieder nur ein Ergebnis, nicht eine Matrix aus mehreren Werten
Dieser Fehler erscheint, wenn Excel eine Arrayformel nicht berechnen oder deren Ergebnisse nicht darstellen kann.
Meist wird dies der Fall sein, wenn schlicht zu wenig Platz für die Ausgabe ist. Dann erscheint in der Zelle mit der Formel die Meldung und es wird mit einem Rahmen dargestellt, wie viel Platz benötigt würde. Im Weg können dabei Zellinhalte sein, aber auch das Ende der Tabelle, verbundene Zellen usw.
Auch wenn in einer Arrayfunktion Zufallszahlen oder andere Ergebnisse verwendet werden, kann der Fehler erscheinen. Dann ist die innere Funktion (zum Beispiel eine für Zufallszahlen) noch nicht fertig, während die äu�ere aber schon rechnen möchte. Da aber die Ergebnisse der inneren Funktion fehlen bzw. unvollständig sind, kommt diese Meldung.
Möglicherweise haben Sie schon die Meldung bekommen:
Warum ist der @-Operator hier? Wir haben ein Upgrade der Formelsprache von Excel durchgeführt. Hieraus resultiert, dass Ihnen vielleicht in manchen Formeln der @-Operator auffallen wird. Ihre Formeln verhalten sich auf dieselbe Weise wie immer.
Gleichzeitig kann es sein, dass sich in Ihren Formeln auf einmal @-Zeichen nach den Gleichheitszeichen befinden, die Sie gar nicht eingetragen haben.
Dieses Verhalten hängt unmittelbar mit den Arrayformeln bzw. Arrayfunktionen zusammen. Nehmen wir obige Formel:
=WENN(C2:C13>400;C2:C13;"")
Sie liefert, wie wir gesehen haben, alle Werte, die grö�er als 400 sind, in und unter der Zelle mit der Formel. Was nun aber, wenn wir nur ein Ergebnis benötigen, das erste? Dieser Fall kann auftreten, wenn Formeln über mehrere Zellen gezogen oder wenn Funktionen verschachtelt werden sollen. In diesem Fall setzen wir direkt hinter das Gleichheitszeichen das @-Zeichen:
=@WENN(C2:C13>400;C2:C13;"")
Nun haben wir nur noch ein Ergebnis; in diesem Fall das erste, die 500.
Um bei diesem Beispiel zu bleiben: Setzen wir in der Formel die letzte Zeile absolut (durch das $-Zeichen), kann die Formel nach unten gezogen werden und wir haben als Ergebnis immer den Wert aus den Zellen ab der Zeile mit der Formel:
=@WENN(C2:C$13>400;C2:C$13;"")
Kategorie: Arrays ▸ Arrayformeln
Können Ergebnisse von Arrayformeln in VBA verwendet werden?
Integrierte Möglichkeiten von Excel (Funktionen, Methoden) können um ein Vielfaches schneller sein, als wenn man diese selbst per VBA erstellt bzw. nachgestaltet. So war es schon immer ein gro�er Unterschied, ob man die Find-Methode von Excel nutzte oder eine Schleife über viele Zellen laufen lie�, um einen bestimmten Wert zu finden. Insofern kann auch in Betracht gezogen werden, mit VBA Arrayformeln (bzw. integrierte Arrayfunktionen) zu nutzen und deren Ergebnisse zu verarbeiten.
Vor den Erläuterungen zu den Arrayfunktionen in VBA eine Vorbemerkung zu EVALUATE(), einer Methode, die recht unbekannt, hier aber oft nützlich ist.
Von anderen Sprachen kennt man eval() is evil - in Excel/VBA macht die Methode genau das, was hinter dem Spruch steckt, ist aber bei richtiger Verwendung ungefährlich. Eine Beschreibung ist bei Microsoft zu finden; wir nutzen diese Methode hier, um Formeln/Funktionen interpretieren zu lassen und zu Ergebnissen zu führen.
Dieser Hinweis war wichtig, weil hier in der Folge eine Formel ohne Funktion (also auch ohne Application.WorksheetFunction) verwendet wird, um Arrayformeln in VBA möglichst einfach zu demonstrieren:
Basis zum Verwenden von Arrayformeln in VBA ist, dass sie Arrays zurückgeben. Schlie�lich ist dies auch beim Einsatz auf dem Tabellenblatt der Fall, nur dass wir dort die Ergebnisse in Zellen sehen. Entsprechend ist auch wichtig zu wissen, dass Bereiche in Arrayergebnissen zwei Dimensionen haben können, strukturiert in Zeilen und Spalten sind.
Die Bereiche in den Arrays aus Arrayformeln sind grundsätzlich in Zeilen gegliedert, sie sind die erste Dimension. Wenn wir nun also in C2:C7 Einträge haben und mit arr = Evaluate("=C2:C7") (statt Evaluate wäre auch Range("C2:C7") möglich) darauf zugreifen, wird als Ubound folgerichtig die 6 ausgegeben. Entsprechend kann mit einer Schleife For intI = 1 To UBound(arr) über die einzelnen Elemente des Arrays gegangen werden.
Allerdings muss jetzt beachtet werden, dass die einzelnen Werte in der ersten Spalte des Arrays stehen - das erste (und in diesem Beispiel einzige) Element in der zweiten Dimension. Ein Zugriff auf arr(intI) würde einen Fehler erzeugen, da hier die Angabe der Spalte fehlt. Erst mit dieser Angabe wird der gewünschte Eintrag geliefert: arr(intI, 1).
Der Aufbau des Codes wäre also:
Sub Test() Dim arr, intI As Integer, strTemp As String arr = Evaluate("=C2:C7") MsgBox "Einträge: " & UBound(arr) For intI = 1 To UBound(arr) strTemp = arr(intI, 1) ' Einträge in Zeilen! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub
Im Beispiel standen die Werte untereinander in den Zellen. Was nun aber, wenn die Werte nebeneinander stehen, zum Beispiel in D1:I1?
In diesem Fall besteht der Array nur aus einer Zeile, der Ubound wird somit 1 sein.
Um an die einzelnen Elemente zu gelangen, gibt es nun die erste Möglichkeit, per Schleife die Elemente dieser einen Zeile zu liefern, wobei allerdings bekannt sein muss, wie viele Elemente die Zeile hat:
For intI = 1 To 6 strTemp = arr(1, intI) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next
Eine Alternative dazu ist, den Array zu transponieren. Dies geht mit der Funktion MTRANS() in der englischen Schreibweise bereits beim Ã?bergeben an die Variable:
arr = Application.WorksheetFunction.Transpose(Evaluate("=D1:I1"))
Damit sind die ehemaligen Spalten nun die Zeilen und der Array kann wie vorher mit Schleife bis zum Ubound (der jetzt im Beispiel wieder 6 ist) durchlaufen werden.
Der Code zum Testen:
Sub Test1() Dim arr, intI As Integer, strTemp As String arr = Evaluate("=D1:I1") MsgBox "Einträge: " & UBound(arr) ' 1, weil nur 1 Zeile For intI = 1 To 6 strTemp = arr(1, intI) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub Sub Test2() Dim arr, intI As Integer, strTemp As String arr = Application.WorksheetFunction.Transpose(Evaluate("=D1:I1")) MsgBox "Einträge: " & UBound(arr) For intI = 1 To 6 strTemp = arr(intI, 1) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub
