Microsoft Excel

Herbers Excel/VBA-Archiv

Zelladressen von FunktionsParametern ermitteln


Betrifft: Zelladressen von FunktionsParametern ermitteln von: Jürgen
Geschrieben am: 14.09.2019 14:40:47

Liebe Forumsmitglieder,

dies ist mein erster Forumseintrag auf "herber.de/forum", weswegen es möglich sein kann, dass ich ggf. bei der Suche nicht erfolgreich war und bereits eine Lösung existiert und/oder, dass ich irgendwelche "schrägen" Formatierungen (mangels Herber-Forums-Erfahrung) innerhalb dieses Threads verbreche. In beiden Fällen und ggf. noch weiteren denkbaren Pannenfällen bitte ich vorsorglich um Entschuldigung und gelobe für evtl. Folgebeiträge Besserung.

Nun jedoch zur eigentlichen Frage (bzw. dem Versuch einer hoffentlich verständlichen Frage) und der Hoffnung auf tolle Lösungsvorschläge... dürfen durchaus auch trivial sein.

Frage/Aufgabenstellung:
---------------------------
Ich würde gerne eine Funktion "basteln", bei der ich möglichst wenig Parameter übergeben will bzw. wo ich mir wünsche, dass ich in der Funktion Werte aus Zellen auslese, deren Zelladresse abhängig von den übergebenen Parameterwerten ist.

Klingt ziemlich verwirrend und ich würde ohne ein Beispiel selbst nicht verstehen, was ich da soeben formuliert habe. Daher nun ein Beispiel, um hoffentlich ein wenig Klarheit bzgl. der Problemstellungsformulierung zu schaffen.

Bei dem Beispiel geht es darum, dass innerhalb der Funktion primär die Werte aus Zeile 2 interessant sind, jedoch hinsichtlich der Spalte A auch noch Inhalte der Vorgänger- und der Nachfolgezeile benötigt werden. Wird die Funktion um eine Zeile nach unten verschoben, so ist es dann die Zeile 3 und deren Vorgänger- bzw. Nachfolgezeile, usw....
Selbstverständlich soll die Funktion nicht nur bzgl. Spalte A, sondern auch für sämtliche anderen Spalten funktionieren, was bei der nun folgenden Variante 1 natürlich kein Problem ist.

Variante 1 (hier werden der Funktion alle erforderliche Zelladressen übergeben):

            	A	B	C
            1	Name	Wert	Ergebnis
            2	Max	2	=xFunktionEinfach(A2;A1;A3;B2)
            3	Max	3	
            4	Hugo	7	
            5	Ina	6	
            6	Ina	34	
            7	Ina	53	
            8	Doro	4	
            9	Bertram	6
            

Variante 2 soll genau das gleiche, wie Variante 1, machen. Allerdings wird A1 (Vorgängerzeile) und A3 (Nachfolgezeile) nicht übergeben, da die Funktion sich dies idealerweise selbst herleitet.

Variante 2 (hier werden der Funktion zwei Adressen übergeben, die anderen Adressen und deren Inhalte sollen innerhalb der Funktion ermittelt werden):
            	A	B	C
            1	Name	Wert	Ergebnis
            2	Max	2	=xFunktionBesser(A2;B2)
            3	Max	3	
            4	Hugo	7	
            5	Ina	6	
            6	Ina	34	
            7	Ina	53	
            8	Doro	4	
            9	Bertram	6
            


Nachfolgend nun die zugehörigen CodeFragmente.

Zunächst Variante 1, bei der pille_palle_einfach die jeweiligen Werte in entsprechenden Variablen beim Funktionsaufruf direkt übergeben werden.
            Public Function xFunktionEinfach(Name_Aktuell As String _
                                           , Name_Vorläufer As String _
                                           , Name_Nachfolger As String _
                                           , Wert As String _
                                            ) As String
                                                
              ' hier passiert irgendwas Sinnvolles
              xFunktionEinfach = ErgebnisDerSinnvollenVerarbeitung
                                                 
            End Function
            

Die Variante 2 ist grundsätzlich verarbeitungstechnisch identisch mit der Variante 1. Allerdings werden hier nur die aktuellen Zeilenwerte übergeben. Die Vorgängerzeile und die Nachfolgezeile soll diese Funktion selbst ermitteln und dann die entsprechenden Zellinhalte auslesen.
            Public Function xFunktionBesser(Name_Aktuell As String _
                                           , Wert As String _
                                           ) As String
                                           
              Dim Name_Vorläufer As String
              Dim Name_Nachfolger As String
              
              Name_Vorläufer = ZellInhaltDerZelleOberhalb_von_Name_Aktuell
              Name_Nachfolger = ZellInhaltDerZelleUnterhalb_von_Name_Aktuell
             
              ' hier passiert irgendwas Sinnvolles
              xFunktionBesser = ErgebnisDerSinnvollenVerarbeitung
                                                 
            End Function
            


Eine Lösungsmöglichkeit wäre natürlich, dass ich Spaltenbuchstabe und Zeile der Funktion als Parameterwert übergebe. Wäre jedoch eine ungewollte Krücke.

Eine andere Krücke wäre es, dass ich innerhalb der Funktion ermittle, aus welcher Zelle diese aufgerufen wurde und dann entsprechen die anderen Zelladressen ableite.

Beide Krückenlösungen möchte ich vermeiden, da hierbei diverse Flexiblitäten verloren gehen würden und die Handhabung solch einer Funktion ungewollt umständlich wäre.

Gibt es eine Lösung?
Idealerweise eine Lösung, von der ich sage: "Warum bin ich da nicht selbst drauf gekommen?" oder "Kann man so auf dem Schlauch stehen?"
Gerne darf in der Lösung auch stehen: "Absolutes Anfängerproblem". Oftmals sind ja bekanntlich die einfachsten und trivialsten Lösungen die besten bzw. man sieht hin und wieder den Wald vor lauter Bäumen nicht.

Ich hoffe, dass war jetzt nicht zu wirr und bedanke mich ganz herzlich um Voraus für geniale (e=mc^2)-Lösungen.

Liebe Grüße und ein schönes (Knobel)Wochenende

Jürgen
  

Betrifft: AW: Zelladressen von FunktionsParametern ermitteln von: 1713061.html
Geschrieben am: 14.09.2019 15:07:26

Hi
du kannst als Übergabeparameter auch ein RANGE-Objekt vorgeben.
dann hast du die Zelle und kannst damit weiter arbeiten:

Function xxx(rngNameAkuell as Range, Wert as long)
     dim NameAkuell as string
     dim NameVorgänger as string
     dim NameNachfolger as string
     
     NameAktuell = rngNameAktuell.value
     NameVorgänger = rngNameAktuell.Offset(-1, 0).value
     NameNachfolger = rngNameAktuell.Offset(1, 0).Value
mit: =xxx(A2;B2)

beachte jedoch, dass dann diese Formel nicht neu berechnet wird, wenn sich der Vorgänger(A1, A3) oder Nachfolger ändert.
eine Neuberechnung von Formeln erfolgt nur, wenn sich eine Zelle ändert, welche in den Übergabeparametern der verwendeten Funktionen angegeben sind.

Gruß Daniel
  

Betrifft: und was erwartest du als Resultat der UFD? (owT) von: 1713062.html
Geschrieben am: 14.09.2019 15:25:27


  

Betrifft: keine Ahnung, mir doch egal von: 1713071.html
Geschrieben am: 14.09.2019 16:02:29

das Thema der Frage war doch ein ganz anderes.
und bei VBA-gut wird Jürgen den Rest seiner Funktion schon im Griff haben.
ich muss ja nicht jedem auch noch beim Schuhezubinden helfen.
Gruß Daniel

  

Betrifft: AW: keine Ahnung, mir doch egal von: 1713072.html
Geschrieben am: 14.09.2019 16:18:26

;-)

Dann scheine ich EtoPHG doch nicht falsch verstanden zu haben... und ja, Schuhezubinden wäre nun tatsächlich etwas, was zwar vor langer Zeit erforderlich war und in diversen Jahren möglicherweise dann altersbedingt wieder notwendig werden wird... akut ist das Schuhezubinden jedoch nicht erforderlich (weder physisch noch im übertragenen Sinne VBA-mäßig, wenngleich hin- und wieder sicherlich der Eindruck entstehen mag).

Nochmals Danke (auch für das Schmunzeln) und Liebe Grüße

Jürgen

  

Betrifft: oops, die Resultat? Frage sollte an Jürgen gehen. von: 1713063.html
Geschrieben am: 14.09.2019 15:26:29


  

Betrifft: AW: oops, die Resultat? Frage sollte an Jürgen gehen. von: 1713068.html
Geschrieben am: 14.09.2019 15:57:14

Hallo EtoPHG,

möglicherweise sind in Deiner Frage Buchstabendreher enthalten oder ich stehe auf dem Abkürzungsschlauch ;-), weswegen meine Antwort evtl. nun komplett daneben liegen kann.

Das gewünschte Resultat der Funktion lässt sich nicht in halbwegs kurze Worte fassen, weswegen ich auch "nur" eine extrem abgespeckte Beispielvariante dargestellt hatte. Letztlich sind die Vorgänger- und Nachfolge-Werte jedoch von Bedeutung für Gruppenwechsel, Abhängigkeiten, Medianberechnungen und diversen Dingen mehr, die außerhalb einer Funktionslösung (nur mit reinen Excel-Funktionen) sehr langsam, sehr unübersichtlich und nicht ausreichend flexibel werden würden.

Liebe Grüße

Jürgen

  

Betrifft: AW: Zelladressen von FunktionsParametern ermitteln von: 1713065.html
Geschrieben am: 14.09.2019 15:45:09

Hallo Daniel,

ganz arg superlieben Dank für die superschnelle Antwort.

Dies kann durchaus die Lösung sein und ist obendrein auch schön einfach.
Mit "Application.Volatile" kann die Teilproblematik der Vorgänger- bzw. Nachfolger-Änderungen gelöst werden, wo ich jedoch mal testen muss, ob sich hierbei ggf. irgendwelche Problematiken hinsichtlich Rechnerlast oder Berechnungsreihenfolgen ergeben könnten.

Liebe Grüße und nochmals Danke und Dickes Lob

Jürgen

  

Betrifft: AW: Zelladressen von FunktionsParametern ermitteln von: 1713069.html
Geschrieben am: 14.09.2019 15:58:17

Rechenlast kann schon ein Problem sein.
Formeln, die eine Volatile Funktion enthalten, werden bei jeder Änderung in Excel neu berechnet, auch wenn diese in einer anderen Datei, die du parallel geöffnet hast, stattfindet.
Obs problematisch wird, hängt davon ab, wieviele volatile Formeln du hast und wie hoch die Berechnungszeit der einzelnen Formeln ist.
außerdem sind selbstgeschriebene volative Funktionen extrem lästig beim Testen im Einzelstepmodus.

wenn du die Anzahl der Übergabeparameter reduzieren willst, kannst du auch Zellbereiche oder Arrays übergeben:

Function xxx(rngNamen as range, Wert as long) as string
dim NameAktuell as string
dim NameVorgänger as string
dim NameNachfolger as string
NameAktuell = rngName(2).value
NameVorgänger = rngName(1).Value
NameNachfolger = rngName(3).value
...
End Function
mit =xxx(A1:A3;b2)
Gruß Daniel
  

Betrifft: AW: Zelladressen von FunktionsParametern ermitteln von: 1713070.html
Geschrieben am: 14.09.2019 15:59:20

Hallo Jürgen,

wie willst Du denn den Vor- und Nachfolger ermitteln, wenn Du die Funktionsparameter als Strings definierst - diese Dinger sind doch doof wie Bohnenstroh und kennen nur deren Inhalt, wissen aber nichts darüber, woher sie kommen. Ich übergebe Objekte in der 1. Stufe und nur die Hilfsfunktionen bekommen nur den Inhalt der Objekte zu Gesicht.
Bei mir sieht die Funktions 'xFunktionBesser' mit 2 weiteren Hilfsfunktionen so aus:

 Function xFunktionBesser(Name_Aktuell As Range, Wert As Range) As String
     'Exit Function
     Dim Name_Vorlaeufer As String
     Dim Name_Nachfolger As String
     Dim Summe As Double
   
     Name_Vorlaeufer = VorNachfolger(Name_Aktuell.Offset(-1, 0).Value)
     Name_Nachfolger = VorNachfolger(Name_Aktuell.Offset(1, 0).Value)
 
     Summe = WertErmittlung(Wert.Offset(-1, 0).Value) + _
             WertErmittlung(Wert.Offset(0, 0).Value) + _
             WertErmittlung(Wert.Offset(1, 0).Value)
     
     'hier passiert irgendwas Sinnvolles
    xFunktionBesser = Name_Vorlaeufer & "|" & Name_Aktuell.Value & "|" & _
         Name_Nachfolger & " - Summe: " & Summe
                                      
 End Function
 
 Private Function VorNachfolger(ByVal xName As String) As String
     Select Case Trim(xName)
         Case "", "Name": xName = "-"
     End Select
     VorNachfolger = xName
 End Function
 
 Private Function WertErmittlung(ByVal xWert As Variant) As Double
     WertErmittlung = IIf(IsNumeric(xWert), xWert, 0)
 End Function
Gruß von Luschi
aus klein-Paris
  

Betrifft: AW: Zelladressen von FunktionsParametern ermitteln von: 1713073.html
Geschrieben am: 14.09.2019 16:55:50

Hallo Luschi,

beim allerersten Blick auf Deine Antwort hatte ich kurz gestutzt, weil darin nahezu deckungsgleiche Passagen/Fragmente meiner "tatsächlichen" Funktion enthalten sind und ich kurz nachsah, ob ich ggf. via Copy&Paste etwas zu viel beitragsmäßig übergeben hatte.

War dann jedoch nicht so (und die Deckungsgleichheit hielt sich dann ebenfalls in sehr engen Grenzen... war der erste Blick... beim zweiten Blick dann komplett anders ;-) ).


Mir ging's bei der Fragestellung primär darum, dass ich versucht habe, ein möglichst kurzes Beispiel darzustellen (beinahe Pseudo-Code), welches in meiner dargestellten Form natürlich weder toll noch sonderlich funktionsfähig oder gar sinnvoll ist/war. Letztlich war das Beispiel ein Auto, bei dem noch nicht einmal Räder vorhanden sind, sondern evtl. nur das linke hintere Bremslicht, bei dem obendrein auch noch die Lampe durchgeschmort ist.

Aber Nichtsdestotrotz finde ich genau dies an diesem Forum zusätzlich toll, dass oftmals noch Ergänzungen kommen, die einen persönlich möglicherweise zwar nicht tangieren oder wo man sagt: "Olle Kamellen.", die aber dazu führen, dass hin und wieder dann doch Dinge dabei sind, die einen weiter bringen oder die man aus Macht der Gewohnheit schon immer umständlich und schlecht gelöst hatte.

Insofern finde ich es wirklich gut und toll, dass Du das Beispiel aufgepimpt hast.

Aktuell bin ich sogar am Überlegen, ob ich die Funktion sich selbst einfügen lasse, um die Problematik der Nicht- bzw. langsamen Verarbeitung bei sich ändernden Werten außerhalb "Name_Aktuell As Range" oder Arrays zu umgehen.

Wünsch' Dir noch ein Schönes Wochenende und nochmals vielen Dank und liebe Grüße

Jürgen

  

Betrifft: AW: Zelladressen von FunktionsParametern ermitteln von: 1713074.html
Geschrieben am: 14.09.2019 17:04:09

Du willst weder die Zellen als Range übergeben (somit auch ihre Addressen), noch dürfen deren Adressen innerhalb der Funktion ermittelt werden ? Wie und wann denn ? Durch Zauberei ?

"da hierbei diverse Flexiblitäten verloren gehen würden und die Handhabung solch einer Funktion ungewollt umständlich wäre" - Welche "Flexiblitäten" denn ? In wiefern umständlich ?

  

Betrifft: AW: Zelladressen von FunktionsParametern ermitteln von: 1713090.html
Geschrieben am: 14.09.2019 18:24:53

Missverständnis: Zauberei wäre sicherlich die Ideallösung (Modul Psi), da Zellinhaltsveränderungen bei der "Range-Offset"-Thematik von nicht im Funktionsaufruf enthaltenen Zellen Probleme mit sich bringen (NichtBerechnung oder ungewollte Reihenfolge bei der Berechnung oder Geschwindigkeitsverlust oder ...).

Es ist aber auch Okay, wenn es keine anderen Lösungen gibt. Dann ist es halt so. Manchmal reicht es auch aus, wenn man bestätigt bekommt, dass es bestimmte Dinge oder eine vermeintliche Ideallösung einfach nicht gibt, abgewogen werden muss und ggf. nur eine ungeliebte "Variante" möglich ist... vergleichbar mit Mathematik, bei der es ebenfalls hin und wieder ohne ungeliebte Iterationsverfahren bis hin zum "reinen Ausprobieren" keine anderweitig bekannten (Poincaré-Vermutung, bei der ich weder den Beweis der Unmöglichkeit, noch das nicht mögliche Mögliche verstanden habe... gefühlt bin ich bereits nach den ersten Sätzen gescheitert) und obendrein bessere Lösungen gibt.

Insofern passt alles und ich bin positiv überrascht, wie schnell gut erläuterte Lösungsvorschläge kamen, die nicht neu sein müssen, sondern die ggf. "nur" aussagen: "Hier ist eine prinzipielle Lösung (bekannt oder unbekannt spielt keine Rolle)."

Und dafür nochmals vielen lieben Dank

LG Jürgen

Beiträge aus dem Excel-Forum zum Thema "Zelladressen von FunktionsParametern ermitteln"