Bereich.Verschieben analog ActiveCell.Offset

Bild

Betrifft: Bereich.Verschieben analog ActiveCell.Offset
von: Gerd
Geschrieben am: 19.07.2015 17:13:28

Hallo,
ich suche ein Möglichkeit den VBA Befehl ActiveCell.Offset(x,y) analog als Excel Formel umzusetzen.
In VBA gibt mir der Befehl den Wert einer anderen Zelle, mit entsprechendem Versatz zur aktiven Zelle zurück z.B. ActiveCell.Offset(0,2) den Wert zwei Zellen rechts von der aktiven Zeilen.
Ich möchte das gleiche mit einer normalen Excel Formel erreichen. Ich kenne den Befehl Bereich.Verschieben, der hilft mir aber nur teilweise. Er erwartet als Parameter Bezug, Zeilen, Spalten. Den Bezug möchte ich aber nicht angeben.
Es funkioniert also wenn ich in die Zelle C2 schreibe Bereich.Verschieben("C2";0;2), dann erhalten ich den Wert in Zelle E2 zurück. Ich möchte aber in Zelle C2 schreiben
(Pseudoformel) "=Bereich.Verschieben(ActiveCell;0;2)", das schaffe ich aber nicht.
Kann ich die aktuelle Zelle irgendwie indirekt über Me, This.... referenzieren ? Oder gibt es andere Vorschläge einen Wert relativ zur aktuellen Zelle zu lesen ?
Viele Grüße,
Gerd

Bild

Betrifft: mit INDEX() und ZEILE() und SPALTE() ...
von: der neopa C
Geschrieben am: 19.07.2015 17:29:43
Hallo Gerd,
... mit z.B. =INDEX(1:1048576;ZEILE()-4;SPALTE()+3) liest Du den Wert 3 Spalten rechts und 4 Zeilen oberhalb der "aktuellen" Zelle (jedoch statischen! Zelle, weil nur da "aktuell" ist, wo die Formel drin steht).
Gruß Werner
.. , - ...

Bild

Betrifft: AW: mit INDEX() und ZEILE() und SPALTE() ...
von: Gerd
Geschrieben am: 19.07.2015 17:42:10
Hallo Werner,
vielen Dank für die schnelle Antwort, das hilft mir sehr :-)
Gerd

Bild

Betrifft: Ich sehe keine reguläre Möglichkeit, dass ganz ...
von: Luc:-?
Geschrieben am: 19.07.2015 17:50:48
…ohne VBA hinzubekommen, Gerd;
eine noch relativ einfache Möglichkeit, dass weitgehend mit Fmln zu erledigen, wäre aber die Folgende:
1. einen Namen (hier für das Blatt!) definieren, zB aktZelle (Bezug ist egal, kann so, wie von Xl vorgeschlagen, stehen bleiben, da er ohnehin ständig überschrieben wird);
2. eine EreignisProzedur für das relevante Blatt in dessen Dokument­Klassen­modul anlegen:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const naAktZ$ = "aktZelle"
    Me.Names(naAktZ).Value = "=" & Target.Address
End Sub
3. Deine BEREICH.VERSCHIEBEN-Fml eintragen und dabei auf den definierten Namen beziehen, also zB so: =BEREICH.VERSCHIEBEN(aktZelle;0;2)
Nach Eintrag bezieht sich die Fml zuerst einmal auf ein Offset ihrer StandortZelle, was eigentlich keinen Zirkel­Bezugs­Fehler ergeben sollte (falls doch, Iteration aktivieren!). Sobald die AuswahlZelle gewechselt wird, wird der Offset der neuen Auswahl angezeigt.
Gruß, Luc :-?

Bild

Betrifft: AW: Ich sehe keine reguläre Möglichkeit, dass ganz ...
von: Gerd
Geschrieben am: 19.07.2015 18:05:12
Hallo Luc,
danke für den Vorschlag, ich bin mit _SelectionChange sehr vorsichtig, da das immmer ausgelöst wird und die Seite ca. 10.000 aktive Zellen hat. Dann mal testweise mit VBA. Dort kann ich die Funktion definieren:

Function Offset(Zeile As Integer, Spalte As Integer)
    Offset = ActiveCell.Offset(Zeile, Spalte)
    
End Function
Sie funktioniert auch im Blatt beim eintrage einwandfrei. Ich schaffe es aber nicht eine Neuberechnung dieser eigenen Funktion auszulösen. Auch bei "Arbeitsblatt neu berechnen" bleibt der alte Wert stehen, nur wenn ich die Zelle wieder betrete und per Enter bestätige wird neu berechnet. Wie könnte man die Neuberechnung erreichen ?
Viele Grüße,
Gerd

Bild

Betrifft: Deine UDF hat ja auch das gleiche Problem ...
von: Luc:-?
Geschrieben am: 19.07.2015 18:32:11
…wie die hier von mir erwähnte, Gerd,
weil sich ihre Argumente ja nicht ändern. Auch, wenn du sie volatil machst, ändert das nichts, sie würde nur reagieren, wenn Xl eine Notwendigkeit zur Neuberechnung vorliegt. Das ist bei einfachem Wechsel der Auswahl aber nicht der Fall. Durch Anklicken (Edit-DoppelKlick) löst du eine solche Notwendigkeit aus.
Deshalb habe ich auch den Anstoß durch SelectionChange vorgeschlagen. Dadurch ändert sich der Bezug von BEREICH.VERSCHIEBEN, das ohnehin volatil ist, und es wird sofort neu berechnet.
Aus diesem Grund musste ich in der verlinkten UDF-Beschreibung auch diese „Klimmzüge“ vorsehen, die du bei deiner UDF ggf auch machen müsstest, wenn du dieses Ereignis, was das vereinfacht, nicht nutzen willst.
Luc :-?

Bild

Betrifft: AW: Deine UDF hat ja auch das gleiche Problem ...
von: Luschi
Geschrieben am: 19.07.2015 18:56:13
Hallo Gerd,
so sollte es doch klappen:
=BEREICH.VERSCHIEBEN(INDIREKT(ADRESSE(ZEILE();SPALTE()));0;2)
Gruß von Luschi
aus klein-Paris

Bild

Betrifft: AW: Deine UDF hat ja auch das gleiche Problem ...
von: Gerd
Geschrieben am: 19.07.2015 19:56:01
Luschi,
das ist gut. Adresse(Zeile();Spalte()) entspricht quasi dem "Me", das ich gesucht habe.
Viele Grüße,
Gerd

Bild

Betrifft: Das bezweifle ich, ...
von: Luc:-?
Geschrieben am: 19.07.2015 21:29:03
…Gerd,
denn, was Luschi hier zeigt, bezieht sich immer auf die StandortZelle dieser Fml (die ja nicht die jeweils ausgewählte sein muss!), genauso, wie es auch neopa schon geschrieben hatte. Damit ist eine allgemeinere Lösung, so, wie sie dir vorzuschweben schien, nur mit neopas Fml oder auf meine Weise (bzw Ergänzung deiner UDF auf die von mir unter dem Link beschriebene Art) möglich. Anderenfalls haben neopa und ich dich gründlich missverstanden (obwohl ActiveCell ja eindeutig war!). Dann ginge es aber auch ganz ohne INDIREKT!
Luc :-?

Bild

Betrifft: AW: Adresse(Zeile();Spalte()) geht einfacher
von: Daniel
Geschrieben am: 19.07.2015 22:48:53
Hi
das geht auch etwas einfacher, wenn die Ausgangszelle für das Bereich.Verschieben immer die Zelle sein soll, in der die Formel steht:

=Bereich.Verschieben(Indirekt("ZS";0);0;2)
oder noch einfacher, wenn man sowieso schon mit Indirekt arbeitet:´
=Indirekt("ZS(2)";0)
aber vielleicht reicht es ja für dich auch aus, wenn du in den Optionen in die Z1S1-Bezugsart wechselst.
dann kannst du das "gib mir den Wert zwei Zellen rechts von mir aus", direkt als Zellbezug mit =ZS(2) hinschreiben, ohne dass du dich darum kümmern musst, in welcher Zelle du gerade bist.
Gruß Daniel
Gruß Daniel

Bild

Betrifft: Warum dann so umständlich, ...
von: Luc:-?
Geschrieben am: 20.07.2015 01:03:10
…Daniel? ;-]
In deinem 1. Fall würde ja schon bspw A1:=Bereich.Verschieben(A1;0;2) und im 2. die direkte Angabe der ZielZellAdresse C1 reichen.
Vielleicht solltest du doch mal die Ausgangsfrage lesen. Da geht's um die ActiveCell, was man weder mit Luschis Vorschlag noch deinen „Vereinfachungen“ erreichen kann (auch, wenn Gerd das glauben will). Anderenfalls hat er die Frage falsch formuliert und benötigt gar kein Offset!
Gruß, Luc :-?

Bild

Betrifft: AW: darum so umständlich aus deiner sicht...
von: Daniel
Geschrieben am: 20.07.2015 01:32:04
weil du die Fragestellung immer noch nicht verstanden hast.
Gerd erwähnt zwar die ActiveCell am Anfang seiner Frage, aber nur als Beispiel in Verbinung mit der Offset-Funktion.
wenn man dann den Rest seiner Frage aufmersam liest (ohne dabei das wichtige zu vergessen ;-)) dann erkennt man schnell dass es Gerd eigentlich um die Zelle geht, in welche die Formel steht und nicht um die Zelle, welcher aktuell der Cursor markiert.
In der folge spricht er ja auch nicht von der "aktiven" Zelle, sondern von der "aktullen", ein kleiner, aber gewichtiger Unterschied, so wie sich ActiveCell von Application.Caller unterscheidet.
und er möchte halt in seiner Formel den Application.Caller verwenden, ohne diesen direkt addressieren zu müssen.
Dass unsere Interpretation seiner Fragestellung richtig ist und deine falsch, hat er ja hiermit
https://www.herber.de/forum/messages/1437269.html
bestätigt.
sorry Luc, aber nur weil Gerd einmal die ActiveCell in seinem Beispiel erwähnt hat, meint er diese hier nicht.
Gruß Daniel

Bild

Betrifft: Habe den BT mehrfach gelesen, aber eure ...
von: Luc:-?
Geschrieben am: 20.07.2015 02:21:53
…Interpretation kann ich daraus nicht ableiten, auch nicht, dass ActiveCell nur ein Bsp sein soll. (Werner, der sich aus VBA heraushält, offensichtlich auch nicht, sonst hätte er das auch einfacher fassen können.) Gerd hat eindeutig von Referenzieren gesprochen und das er den Bezug nicht angeben will, was für mich heißt, dass er wechseln kann und nicht auf die Fml­Standort­Zelle fixiert sein soll, was er aber bei allen anderen Lösungen ist, weil es dafür ja auch keine XlStandard­Fml­Lösung gibt. Ansonsten ist für mich schon Gerds 1.Satz ausschlaggebend.
Und selbst, falls du mit deiner, an die Luschis anschließender Interpretation recht hättest, ist deine „Vereinfachung“ nicht wirklich eine…! ;->
ActiveCell ist weder Application.Caller noch Application.ThisCell, was in deiner Auflistung ja noch fehlt. Aber viell kennst du ja den Unterschied zwischen den beiden Letzt­genannten nicht… ;-]
Luc :-?

Bild

Betrifft: AW: Habe den BT mehrfach gelesen, aber eure ...
von: Daniel
Geschrieben am: 20.07.2015 08:22:17
nunja, Gerd hat ja bestätigt, dass Luschis Interpreation richtig ist, damit erübrigt sich alle Spekulation, wie er es gemeint haben könnte.
und wenn du jetzt der Meinung bist, dass der Textstring "ZS" nicht einfacher ist als die Kombination der drei Funktionen Adresse(Zeile(), Spalten()), dann würde ich mal interessieren, wie deine Definition von "einfach" in bezug auf Formeln in Excel aussieht.
Gruß Daniel

Bild

Betrifft: AW: Habe den BT mehrfach gelesen, aber eure ...
von: Gerd
Geschrieben am: 20.07.2015 09:35:23
Für mich erfüllen die beiden Lösungen
=INDEX(1:1048576;ZEILE()-4;SPALTE()+3)
=BEREICH.VERSCHIEBEN(INDIREKT(ADRESSE(ZEILE();SPALTE()));0;2)
den angestrebten Zweck. Beide geben den Wert einer Zelle zurück, die sich relativ zu der Zelle befindet, in welcher die Formel steht.
Lediglich die Schreibweise ist recht komplex, sobald ich damit mehrere Zellen addiere entsteht schon eine recht große Formel. Insofern wäre eine UDF "=Offset(0;2)" die beste Lösung die ich mir derzeit vorstellen könnnte. Von dieser hatten wir aber ja schon festgestellt, dass sie zwar einmalig funktioniert, aber dann leider keine Neuberechnung angestoßen werden kann, weil die Eingangsparameter konstant sind.
Viele Grüße,
Gerd

Bild

Betrifft: AW: Habe den BT mehrfach gelesen, aber eure ...
von: Daniel
Geschrieben am: 20.07.2015 09:59:55
Hi
wie gesagt:
=Indirekt("ZS(2)",0)
ist funktionsgleich mit
=BEREICH.VERSCHIEBEN(INDIREKT(ADRESSE(ZEILE();SPALTE()));0;2)
=Indirekt("Z(-4)S(3)",0)
ist funktionsgleich mit
=INDEX(1:1048576;ZEILE()-4;SPALTE()+3)
wenn du eine UDF hast, in welcher die verwendeten Zellbezüge nicht in den Parametern stehen, aber diese UDF trotzdem bei Ändreungen neu berechnet werden soll, dann musst du in den Funktionscode einfach die Zeile:

Application.Volatile

hinzufügen.
Dann wird eine Formel mit dieser Funktion bei jeder Änderung eines Zellwertes mit neu berechnet.
(was natürlich nicht besonders gut für die Performance ist, wenn du diese Funktion in grosser Anzahl einsetzt oder mit rechenzeitintensiven Formeln wie ZählenWenn, SVerweis, Summenprodukt usw kombinierst).

Public Function xOffset(Zeile As Long, Spalte As Long)
Application.Volatile
xOffset = Application.ThisCell.Offset(Zeile, Spalte)
End Function


Bild

Betrifft: AW: Habe den BT mehrfach gelesen, aber eure ...
von: Gerd
Geschrieben am: 20.07.2015 14:13:40
Vielen Dank, mit Application.Volatile rechnet die Formel wirklich doch sofort. (den Performanceeffekt hieraus teste ich noch.)

Bild

Betrifft: Dann hast du dich aber recht verschwurbelt ...
von: Luc:-?
Geschrieben am: 20.07.2015 14:14:27
…ausgedrückt, Gerd,
was darauf schließen lässt, dass du Xl-Probleme, anders als die meisten Nutzer, eher mit Pgmm als mit Fmln zu lösen versuchst…
Einem Objekt wie ActiveCell kann man per nachgesetztem Pkt Eigenschaften oder Methoden zuordnen. Bestimmte Eigenschaften begrün­den dabei wieder neue Objekte, während eine Methode irgend­etwas mit dem Objekt macht, was uU ebenfalls ein neues Objekt ergibt. Letzteres ist bei ActiveCell.Offset(z, s) der Fall. Will man das mit einer eigenen Fkt (UDF) nachgestalten, kann man in dem Fall, dass vom Standort der Fml, in der die UDF steht, ausge­gangen wdn soll, zwar auf die Angabe des Standorts als Argument verzichten, trotzdem muss aber mit diesem gearbeitet wdn. Man sieht diese Angabe also als optionales Argument vor, damit die UDF auch anders verwendet wdn kann, ersetzt aber im Falle des Fehlens dieser Angabe das Argument durch Application.Caller bzw Application.ThisCell. Der Unterschied zwischen beiden besteht hptsächlich darin, dass Ersteres mehrere Zellen umfassen kann (bei Einsatz in mehrzelligen MatrixFmln), Letzteres immer nur eine. Im Falle des Fehlens dieses Arguments kann sich die Fml aber nicht selbst aktualisieren, falls sie weder volatil ist noch eine volatile StandardFkt in ihr vorkommt. Diese könnte man aber auch an eines der anderen Argumente koppeln, dann müsste sie nicht mal selbst volatil sein, wenn man das extern, nicht im UDF-Pgm, macht.
Die meisten XlStandard-Fktt benötigen aber stets ein HptArgument, das sich auf einen Bereich (oder ein Datenfeld als Ergebnis eines Ausdrucks) bezieht. Das wäre im Falle von BEREICH.VERSCHIEBEN das 1.Argument, das in deinem Fall die StandortZelle dieser Fml referenzieren müsste. Also würde bei StandortZelle A1 auch schon =BEREICH.VERSCHIEBEN(A1;0;2) ausreichen, was auch zu keinem ZirkelBezugsFehler führt. Man kann aber auch ganz auf diese volatile XlFkt verzichten und stattdessen INDEX verwenden, was hier =INDEX(A1:C1;3) ergäbe.
Meine ursprüngliche Lösung ging dagg tatsächlich von der jeweils aktiven Zelle, die mit der aktuellen Auswahl identisch ist, aus. Die Fml hätte so immer einen Wert 2 Zellen rechts von derselben ergeben. Darüber, inwieweit so etwas sinnvoll sein könnte, habe ich nicht weiter nachgedacht, da man hier schon die auf den 1.Blick merwürdigsten Anforderungen lesen konnte.
Gruß, Luc :-?

Bild

Betrifft: AW: Dann hast du dich aber recht verschwurbelt ...
von: Gerd
Geschrieben am: 20.07.2015 14:22:32
Hallo Luc,
ja, kann sein ich das ich es nicht so gut ausgedrückt habe. Ich stecke in der VBA Programmierung nicht ganz so tief drin, dass ich UDFs schon ganz einschätzen könnte. Dein "volldynamischer" Ansatz immer den Wert versetzt zu einer beliebigen aktiven Zelle zurückzugeben ist auch interessant, aber so weit gingen meine Überlegungen gar nicht, ich habe aber auf jeden Fall was gelernt dadurch, vielen Dank.
viele Grüße,
Gerd

Bild

Betrifft: Bitte sehr! owT
von: Luc:-?
Geschrieben am: 20.07.2015 14:32:40
:-?

Bild

Betrifft: Dann hast du meinen, von dir kommentierten ...
von: Luc:-?
Geschrieben am: 20.07.2015 13:32:59
…BT nicht richtig gelesen, denn in dem steht schon eine wirklich einfachere Variante bei dieser Interpretation!
Luc :-?

Bild

Betrifft: AW: Dann hast du meinen, von dir kommentierten ...
von: Daniel
Geschrieben am: 20.07.2015 14:02:34
Ja, Luc, hab ich gelesen.
Allerdings wollte Gerd ja die Eingabe der konkreten Zelladresse vermeiden, so dass er unabhängig davon, in welche Zelle er die Formel schreibt, immer den selben Formeltext verwenden kann.
du schlägst hier genau das als Lösung vor, was Gerd vermeiden will.
von daher ein extrem unnützer Beitrag deinerseits.
Gruß Daniel

Bild

Betrifft: So ein Quatsch! Jetzt klebst du aber an Gerds ...
von: Luc:-?
Geschrieben am: 20.07.2015 14:21:05
…Ausdrucksweise, die detailliertere Kenntnis der Wirkungsweise von Fmln vermissen lässt. Wenn die angegebene Adresse relativ ist, wie hier, passt sie sich an jeden FmlStandort an. Will er sie per VBA-Pgm eintragen, muss er das dann am Besten in der relativen R1C1-Form tun.
Was soll also das Rumgeschwurbel (oder fehlen dir etwa gewisse Kenntnisse?)!
Luc :-?

Bild

Betrifft: AW: So ein Quatsch! Jetzt klebst du aber an Gerds ...
von: Daniel
Geschrieben am: 20.07.2015 14:33:41
Hi
falls du meinen Betrag gelesen hast, dann solltst du wissen, dass ich ihm die verwendung von Relativen Zellbezügen in Z1S1-Schreibweise bereits vorgeschlagen habe.
Gruß Daniel

Bild

Betrifft: Eben, aber per INDIREKT, nicht im Zuge ...
von: Luc:-?
Geschrieben am: 20.07.2015 15:41:07
…eines Eintrags der Fml per VBA-Pgm! Damit hättest du dann unnötigerweise im 1.Fall gleich 2 volatile Fktt eingesetzt. Also red' dich nicht raus!
Außerdem sollte dir ja wohl klar sein, dass ich das gelesen hatte, denn darauf bezog sich ja schließlich mein Kommentar.
Luc :-?

Bild

Betrifft: AW: Eben, aber per INDIREKT, nicht im Zuge ...
von: Daniel
Geschrieben am: 20.07.2015 16:45:36
sorry, es stand nirgendwo geschrieben, dass Gerd die Formel per VBA in die Zelle eintragen will.
Sondern die Frage war so formuliert, als wollte er die Formel von Hand in die Zelle schreiben, ohne bei relativen Zellbezügen die Absolute Adresse eintragen zu müssen.
Wenn man per VBA eine Formel mit relativen Zellbezug in eine Zelle schreiben will, nimmtn man natürlich die R1C1-Bezugsart, das ist ja logisch.
Aber wie gesagt, es war nie die Rede davon, dass die Formel per Makro geschrieben werden muss.
Gruß Daniel

Bild

Betrifft: Eben, deshalb war's hier ja auch nicht nötig! ;-]
von: Luc:-?
Geschrieben am: 21.07.2015 04:17:26
:-?

Bild

Betrifft: AW: Deine UDF hat ja auch das gleiche Problem ...
von: Gerd
Geschrieben am: 19.07.2015 19:54:27
Ok, habe verstanden, warum die UDF sich nicht neu berechnen will :-) Vielen Dank

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Inhalt im Textfeld markieren"