Felder in Functions

Computer: Software & Programmierung Visual Basic
#1

Hallo,

an eine Function (auch an eine Sub) kann ich ein Feld aus mehreren Zellen übergeben, z.B.

 funktion(A1:A10)

In der function(feld1) kann ich dann mit z.B. For Each … Next auf das Feld zugreifen. Häufig kommt es aber (bei mir) vor, dass ich auf die Zellen daneben zugreifen will, also auf z.B. B1:B10, C1:C10.
Bisher ist mir noch nichts Besseres eingefallen, als all diese Felder als eigene Variablen an die function zu übergeben

 funktion(A1:A10; B1:B10; C1:C10)

und diese als

 funktion(feld1, feld2, feld3)

zu definieren.

Gibt es eine besseren Weg, ähnlich dem Zugriff über Offset(0, 1), so das ich nur eine Variable/Feld in der Function zu definieren brauche?

Danke
Laika

#2

Gibt es eine besseren Weg, ähnlich dem Zugriff über Offset(0,
1), so das ich nur eine Variable/Feld in der Function zu
definieren brauche?

Hallo Laika,

Offset ist doch genau richtig:

Sub test()
MsgBox MeineSumme(Range(„A1:A10“))
End Sub

Function MeineSumme(Feld1 As Range)
MeineSumme = Application.Sum(Feld1.Offset(0, 1))
End Function

Gruß
Reinhard

#3

Einspruch, Euer Ehren :wink:

Gibt es eine besseren Weg, ähnlich dem Zugriff über Offset(0,
1), so das ich nur eine Variable/Feld in der Function zu
definieren brauche?

Offset ist doch genau richtig:

Sub test()
MsgBox MeineSumme(Range(„A1:A10“))
End Sub

Function MeineSumme(Feld1 As Range)
MeineSumme = Application.Sum(Feld1.Offset(0, 1))
End Function

Was passiert mit dem Ergebnis, wenn sich in Spalte B etwas ändert?

Nichts - weil der Bereich aus der Funktion heraus angesprochen wird.

Bei Funktionen die aus dem Tabellenblatt heraus aufgerufen werden sollten immer alle Bereiche als Parameter übergeben werden auf die sich die Berechnung bezieht - der interne Code ist dann so anzupassen, dass die Berechnung korrekt erfolgt.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#4

Was passiert mit dem Ergebnis, wenn sich in Spalte B etwas
ändert?
Nichts - weil der Bereich aus der Funktion heraus angesprochen
wird.
Bei Funktionen die aus dem Tabellenblatt heraus aufgerufen
werden sollten immer alle Bereiche als
Parameter übergeben werden auf die sich die Berechnung bezieht

  • der interne Code ist dann so anzupassen, dass die Berechnung
    korrekt erfolgt.

Einspruch abgelehnt :smile:,

man kann auf Eingaben in den Offset-Zellen reagieren, hier auf Änderungen in B1:B10.

Entweder so:

Excel: =meinesumme(A1:A10)+JETZT()*0

Function MeineSumme(Feld1 As Range)
MeineSumme = Application.Sum(Feld1.Offset(0, 1))
End Function

Oder so

Excel: =meinesumme(A1:A10)

Function MeineSumme(Feld1 As Range)
Application.Volatile
MeineSumme = Application.Sum(Feld1.Offset(0, 1))
End Function

Aus mir unklaren Gründen brauchte ich gestern dazu noch irgendwo eine Formel wie =C5 o.ä. damit es klappt.
Irgendwas ist mir Volatile *glaub*, ich nehme an es kann die Performance herabsetzen wenn man viele Funktionen derart hat o.ä.

Gruß
Reinhard

#5

Grüezi Reinhard

Was passiert mit dem Ergebnis, wenn sich in Spalte B etwas
ändert?
Nichts - weil der Bereich aus der Funktion heraus angesprochen
wird.

Einspruch abgelehnt :smile:,

Dann dopple ich nochmals nach…

man kann auf Eingaben in den Offset-Zellen reagieren, hier auf
Änderungen in B1:B10.

…denn ich habe nicht gesagt, dass man das nicht kann, sonndern dass man nicht soll

Excel: =meinesumme(A1:A10)+JETZT()*0

Oder so

Excel: =meinesumme(A1:A10)
mit Applicaton.Volatile

Beide Versionen werden bei jeder Neubereichnung in irgend einer geöffneten Mappe von Excel ebenfalls neu berechnet.
Da dies dann ebenfalls wieder eine Neuberechnung auslöst läuft das Spiel jesesmal so weiter bis der interne Stack vollgelaufen ist, und die erneute Neuberechnung abgebrochen wird.

Bei grösseren Mappen und/oder exzessiver Verwenung solch flüchtiger Funktionen kann das dann zu erheblichen Performance-Einbussen führen.

Aus mir unklaren Gründen brauchte ich gestern dazu noch
irgendwo eine Formel wie =C5 o.ä. damit es klappt.

Ja, diese Funktion löste dann die Neuberechnung aus, was dazu führt, dass beide obigen Varianten neu berechnet werden.

Irgendwas ist mir Volatile *glaub*, ich nehme an es kann die
Performance herabsetzen wenn man viele Funktionen derart hat
o.ä.

…glaubs nicht nur, es ist wirklich so.

Eine saubere Funktion soll/darf nicht von solchen (hier) Würgarounds) abhängen sondern muss IMO seriös programiert sein und daher eben alle Bereiche auf die sie sich bezieht als Parameter übergeben erhalten.

Just my 2ç

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#6

@ Reinhard und Thomas
Danke für Eure Hilfe. Hätte ich natürlich auch selber drauf kommen können, ausprobieren können, dass man in Functions mit Offset arbeiten kann … Asche auf mein Programmiererhaupt.
Werde auch Thomas´ Ratschlag befolgen und das nur in Ausnahmefällen bei ganz einfachen Functions verwenden.

Gruss
Laika

#7

Dann dopple ich nochmals nach…

Na gut, na gut, dann geb ich auf, Einspruch akzeptiert:smile:

Beide Versionen werden bei jeder
Neubereichnung in irgend einer geöffneten Mappe von Excel
ebenfalls neu berechnet.
Da dies dann ebenfalls wieder eine Neuberechnung auslöst läuft
das Spiel jesesmal so weiter bis der interne Stack
vollgelaufen ist, und die erneute Neuberechnung abgebrochen
wird.

MS sollte mal die Art der neuberechnung überprüfen daß man das nicht abstellen könnte.

Bei grösseren Mappen und/oder exzessiver Verwenung solch
flüchtiger Funktionen kann das dann zu erheblichen
Performance-Einbussen führen.

Okay, so große Mappen und/oder mehrere davon offen hab eich äu0ßerst selten. kann da also keine Erfahrungswerte aufweisen.

Aus mir unklaren Gründen brauchte ich gestern dazu noch
irgendwo eine Formel wie =C5 o.ä. damit es klappt.

Ja, diese Funktion löste dann die Neuberechnung aus, was dazu
führt, dass beide obigen Varianten neu berechnet werden.

Das wäre ja okay. Nur warum brauchte ich an einem Tag diese Hilfsformel, am anderen Tag nicht?
Aber, lass es bitte gut sein, hat keinerlei Prioritäten für mich.

Und, auch ohne Performance-Problemen bei vielen Funktionen dieser Art, kam es mit Volatile und Jetzt()*0 auch so schon zu Problemen, sodaß ich sie vermeide. Denn wenn ich sie mal brauche, funktionieren sie nicht *weiß ich aus Erinnerung, ohne genau das jetzt ad hoc nachstellen zu können*

Just my 2ç

Och, so isses net, von dir nehm ich gern mehr Cents :smile:

PS: darf ich dich anmailen? Ich hätte da mal eine Frage zu einem anderen Thema der Vba-Programmierung.

Gruß
Reinhard

#8

Grüezi Reinhard

Dann dopple ich nochmals nach…

Na gut, na gut, dann geb ich auf, Einspruch akzeptiert:smile:

Ooooch, da ist mir der Sieg ja fast in den Schoss gefallen :smile:

Beide Versionen werden bei jeder
Neubereichnung in irgend einer geöffneten Mappe von Excel
ebenfalls neu berechnet.
Da dies dann ebenfalls wieder eine Neuberechnung auslöst läuft
das Spiel jesesmal so weiter bis der interne Stack
vollgelaufen ist, und die erneute Neuberechnung abgebrochen
wird.

MS sollte mal die Art der neuberechnung überprüfen daß man das
nicht abstellen könnte.

Hmmm, ich denke dass da mit xl2007 auch einiges gegangen ist.
Wenn man sich die Berechnungs-Bäume mit ihren Abhängigkeiten mal näher ansieht (in der Vorstellung), dann wird rasch klar, dass eine mehrfache Neuberechnung einer einzelnen Zelle durchaus Sinn macht, sobald sie von mehr als einer anderen Zelle abhängt.
Je nach Anwendung werden diese Strukturen sehr schnell äusserst komplex.
Also gilt auch hier: ‚Never change a running system‘.

Aus mir unklaren Gründen brauchte ich gestern dazu noch
irgendwo eine Formel wie =C5 o.ä. damit es klappt.

Ja, diese Funktion löste dann die Neuberechnung aus, was dazu
führt, dass beide obigen Varianten neu berechnet werden.

Das wäre ja okay. Nur warum brauchte ich an einem Tag diese
Hilfsformel, am anderen Tag nicht?
Aber, lass es bitte gut sein, hat keinerlei Prioritäten für
mich.

Das hänt immer stark von der Konstellation ab und ob noch andere Mappen im Hintergrund geöffent sind, die vielleicht neu berechnet werden und das dann auch in der aktuellen Mappe eine Neuberechnung auslöst.

Und, auch ohne Performance-Problemen bei vielen Funktionen
dieser Art, kam es mit Volatile und Jetzt()*0 auch so schon zu
Problemen, sodaß ich sie vermeide.

Ja, das ist korrekt - meiden wie der Vampir den knoblauch ist daher die Devise (obschon es auch Funktionen gibt, in denen ich die JETZT()*0-Aktualisierung empfehle, aber das ist ein ganz anderes thema

Just my 2ç

Och, so isses net, von dir nehm ich gern mehr Cents :smile:

Fein, dann haste hier nochmals 5ç :wink:

PS: darf ich dich anmailen? Ich hätte da mal eine Frage zu
einem anderen Thema der Vba-Programmierung.

Aber klar doch - meine Mail-Adresse hast Du, denke ich?

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#9

Grüezi Laika

Werde auch Thomas´ Ratschlag befolgen und das nur in
Ausnahmefällen bei ganz einfachen Functions verwenden.

Wenn die Bereiche zusammenhängend sind, kannst Du sie ja als Block übergeben und dann intern dennoch mit .Offset() arbeiten.

Wenn immer möglich würde ich ganz auf solche Krücken verzichten, dann kommen sie auch nicht in die Quere wenns mal etwas komplexer wird.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#10

Grüezi wohl Thomas,

  • MVP für MS-Excel -

Was ist ein „MVP“? „Main VIP“, „Master Very Important Person“ … :wink:)

Laika

#11

Hallo Laika,

  • MVP für MS-Excel -

Was ist ein „MVP“?

Ein Experte. :smile:

http://de.wikipedia.org/wiki/Microsoft_MVP

Gruß Rainer