Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1260to1264
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

UDF arbeitet volatile | Herbers Excel-Forum

UDF arbeitet volatile
27.04.2012 14:21:31
Klaus Becker

Hallo,
ich arbeite gerade an einem sehr umfangreichen Excel-Projekt.
Einige Problemstellungen hab ich mit User Defined Functions (UDF) gelöst, d.h. ich übergebe aus einem Excelblatt werte mittels einer eigenen Funktion an VBA.
Nu habe ich folgendes Problem:
Wenn ich an EINER Stelle auf dem Blatt werte ändere, wird auf ALLEN Zellen die diese Funktion beinhalten, eine Neuberechnung vorgenommen. Das geht stark auf die Performance :-(
Ich habe beobachtet, dass eine kleine Demo mit einer UDF ebenfalls ALLE Zellen neu berechnet hat, auch nachdem mein eigentliches Projekt geschlossen war.
Ich habe nirgends Application.Volatile eingesetzt!!
Nach einem Neustart von Excel macht meine Demo das nun nicht mehr, d.h. dort werden nur die Zellen neu berechnet, die geänderte Zellen verrechnen.
In meinem eigentlichen Projekt ist das leider immer noch der Fall :-(
Kennt jemand das Problem?
Ich weiss, dass das bei bedingten Formatierungen der Fall ist. Änderst Du irgendwo etwas, werden alle Berechnungen zur Verfärbung neu durchgeführt. Sogar, wenn Du in einer anderen Mappe arbeitest!
Kann es sein, dass Zellen, die eine bedingte Formatierung enthalten, den Inhalt der Zelle (Formel) mitreißt und den dann auch neu berechnet?
Das ganze ist deshalb so schlimm, weil ich 36 Blätter mit 31 Tagen und 30 Mitarbeitern mit jeweils mehreren Formeln vorliegen habe. Das dauert dann gern mal 4 Sekunden, bis der Zellzeiger nach der Eingabe die nächste Zelle erreicht.
Ich weiss nicht, wo ich bei der Analyse des Problems ansetzen soll
Habt Ihr eine Idee?
Klaus

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: UDF arbeitet volatile
27.04.2012 14:30:18
ransi
HAllo Klaus
Zeigst du uns mal deine UDF ?
Evtl. kann man dann schonmal was sagen.
ransi
Glaub ich nicht, dass dürfte wie beschrieben ...
27.04.2012 14:38:21
Luc:-?
…ein Problem aus den Xl-Tiefen sein, bei dem uU nur KalkModus manuell hilft (dann muss man ggf sein eigenes Neuberechnungspgm schreiben!).
Gruß Luc :-?
AW: Glaub ich nicht, dass dürfte wie beschrieben ...
27.04.2012 16:37:43
Klaus Becker
Tja, es scheint mir auch so, dass da was in den Tiefen nicht stimmt.
ich habe meine UDF sofort nach ein paar Debug.Prints mit End

Function verlassen, die tut also nix.
Dennoch wird sie xfach aufgerufen, wenn sich irgendwo in der Mappe eine Zelle verändert :-(
Das macht eine andere Test-UDF, die einfach einen Wert verdoppelt, nicht!
Der Aufruf der UDF erfolgt allerdings mit vielen Parametern, viele davon sind Namen, die auf  _
einem anderen Blatt als Vorgaben hinterlegt sind. Andere sind relative und gemischte Bezüge.
Kann das damit zu tun haben?
Hier der Aufruf der UDF aus den Zellen:
alle_zuschlaege(C$7;Feiertabelle2; C14; C15; C16; C17;C18;C19;C$5;Zuschlag.Sa; Zuschlag.Sa. _
Start;Zuschlag.Sa.Ende; Zuschlag.So;Zuschlag.So.Start;Zuschlag.So.Ende; NachtZu1Proz; NachtZu1Start;NachtZu1Ende;NachtZu2Proz;NachtZu2Start;NachtZu2Ende)
Hier das ganze aus der Sicht der UDF:

Function Alle_Zuschlaege(feiertagstyp As Integer, feiertabelle2 As Range, beginn1 As Single,  _
ende1 As Single, beginn2 As Single, ende2 As Single, stunden_brutto As Single, stunden_netto As Single, wochentag As String, Zuschlag_Sa As Single, Zuschlag_Sa_Start As Single, Zuschlag_Sa_Ende As Single, Zuschlag_So As Single, Zuschlag_So_Start As Single, Zuschlag_So_Ende As Single, NachtZu1Proz As Single, NachtZu1Start As Single, NachtZu1Ende As Single, NachtZu2Proz As Single, NachtZu2Start As Single, NachtZu2Ende As Single)
Alle_Zuschlaege = 1 'Dummy-Rückgabewert
Debug.Print Time, "Alle Zuschläge was here ----------------------------------------------------- _
Debug.Print Time, Application.Caller.Parent.Name, Application.Caller.Address
Debug.Print Time, "----------------------------------------------------------------------------- _
End Function

Ich werde mal nach und nach einige Parameter weglassen und sehen, was passiert.
Ist doch besser, der UDF die Parameter mitzugeben, als sie im Code direkt aus den Zellen des Vorgabenblattes auszulesen, oder?
Klaus
Anzeige
Ja, einer Fkt übergibt man idR Argumente, weil ...
27.04.2012 18:47:07
Luc:-?
…das nicht nur immer möglich, sondern auch flexibler ist, Klaus.
Ansonsten wie unten gesagt, erst mal KalkModus manuell einstellen, da kann man auch besser prüfen, weil dann nur das berechnet wird, was man gerade prüfen will.
Gruß Luc :-?
AW: UDF arbeitet volatile
27.04.2012 15:04:05
Klaus Becker
Da das ganze sehr komplex ist und mit unterunter-Funktionen arbeitet, Namen aus anderen Blättern übergeben wrden etc. muss ich das Problem noch ein wenig isolieren und dann ggf. einen Auszug posten. Es muss ja nachvollziehbar sein.
Ich habe allerdings einen Verdacht. Ich habe soeben folgendes in meiner kleinen Demo beobachtet:
Wenn ich am VBA-Code etwas ändere, werden alle Zellen neu berechnet, die die UDF verwenden, sobald ich einen Wert verändere.
Ab dem zweiten Wert wird dann nur noch die eine Zelle neu berechnet.
Vielleicht habe ich das bei meinem richtigen Projekt nicht genau genug beobachtet. Immerhin hab ich das beim Debuggen festgestellt, und da ändert man ja schon mal was ;-)
Ich habe vielleicht etwas paranoid überreagiert. Ich hatte nämlich schon einmal so einen ähnlichen Fall, da hatte ich viel mit INDIREKT() gearbeitet. Diese Funktion ist immer volatile, und ich musste damals das ganze Projekt umbauen, weil es zu langsam wurde. Bei UDFs in Bedingten Formatierungen ebenfalls.
Also: Ich teste noch mal
Danke für die schnellen Antworten
Anzeige
Tja, das könnte es sein, ...
27.04.2012 15:59:32
Luc:-?
…Klaus,
deshalb in der 1.Testphase KalkModus besser auf manuell stellen. Später kann man den AutoKalkModus ja immer noch testen.
Luc :-?
AW: UDF arbeitet volatile
27.04.2012 17:44:24
Klaus Becker
Ich habe den Fehler isoliert!
Ich habe die

Function kleingeschrumpelt, bis nur noch 1 Parameter und nur Debug.Print übrig war:

Function Alle_Zuschlaege(feiertagstyp As Integer)
Debug.Print Time, "Alle Zuschläge was here ----------------------------------------------------- _
Debug.Print Time, Application.Caller.Parent.Name, Application.Caller.Address
End Function
Immer noch wurde die Funktion in allen Zellen aufgerufen, egal, wo in der Mappe ich eine Zelle verändert hatte.
Dann dachte ich, es könne vielleicht an dem Variablentyp liegen und habe ihn verändert:
Function Alle_Zuschlaege(feiertagstyp As Range)
Debug.Print Time, "Alle Zuschläge was here ----------------------------------------------------- _
Debug.Print Time, Application.Caller.Parent.Name, Application.Caller.Address
End Function
Nun ist die UDF nicht mehr volatile!!
Hat jemand dafür ne Erklärung?
Ich habe versucht einen kleinen Beispiel-Codeschnippsel basteln, der das demonstriert.
Außerhalb meiner Excelmappe kann ich den Fehler aber leider nicht provozieren.
Kann da eine Umgebungsvariable gesetzt sein?
Welche fallen Euch ein?
Application.Calculation = xlCalculationAutomatic heißt ja nicht dass Blatt 7 Formeln auf Blatt1 anschubst
Eine Manuelle Berechnung wäre eine aufwendige Alternative, oder?
Bei Änderung auf dem Blatt --> das Blatt neu berechnen
Oder mit Intersect Bereiche einzeln?
Ich kann das ja auch mit As Range lassen und in der

Sub umwandeln.
Bleibt ein saures Gefühl, dass ich nicht mehr Herr im Programmhaus bin...
Tschuldigung, dass ich das mit der Formatierung hier nicht hinbekomme.
Ich habs aus nem Editor reinkopiert und die Formatierung des Textes verändert sich selbsständig. _
Klaus

Anzeige
Das Letzte zuerst: Du hast im Text ...
27.04.2012 19:14:00
Luc:-?
…Schlüsselworte benutzt, Klaus,
auf die die Forumssoftware anspringt, denn die setzt 1. nicht voraus, dass man sich mit HTML auskennt und prüft 2. nicht so ausführlich, ob nach allgemeinen Prozedurbezeichnern auch irgendwann ein Pgm oder wenigstens ein End kommt und setzt deshalb dann automatisch die Zitat-Tags.
Deine Beobachtung ist interessant, denn das erklärt mir ähnliche Effekte, die ich mitunter auch beobachten konnte. Allerdings verwende ich Range nur dann, wenn auch nur ein Range-Objekt Sinn macht, weil ich seine Eigenschaften auslesen will, sonst dürfte Variant reichen, das man auch weglassen kann. Da darf dann alles vorkommen. Ungeeignete Argumentwerte bzw -objekte kann man ja im PgmCode ausschließen. Range hat den Nachteil, dass dein eigentl Argument immer in einem solchen stehen muss. Du kannst dann nicht einfach eine Zahl oder eine Fml direkt als Argument eintragen! Den Fehler haben völlig unnötigerweise auch die MS-Pgmierer gemacht, die die Fkt RANG (nach-)pgmiert haben. Alle alten (originären) XlFktt verwenden nämlich Variant, sonst könnten die nämlich nicht mit Ausdrücken arbeiten.
Da du ursprgl Integer verlangt hast, kann es durchaus sein, dass bei irgendeiner Veränderung in allen Zellen, die deine udF enthalten, eine Überprüfung gestartet wird, ob diese einschränkende Bedingung noch erfüllt wird (sonst müsste nämlich auf Fehlerwert umgeschaltet wdn), was ja auch Konsequenzen für alle darauf aufbauenden Berechnungen hätte. Es scheint hier also in 1.Linie um die FehlerRegie von Xl zu gehen. Die ist wohl stets volatil und reagiert als Erste. Mit Variant-Argumenten gibt's dieses Problem bestimmt nicht, denn da ergibt sich ein Fehler erst bei Fktsabarbeitung, nicht schon bei ihrem Aufruf und genau der scheint hier geprüft zu wdn. Ruf mal eine udF mit falschem Argument auf nachdem du zuvor einen Haltepkt auf die 1.Kommandozeile der Fkt gesetzt hast! Du wirst merken, der wird gar nicht erst erreicht!
Gruß Luc :-?
Anzeige
AW: Das Letzte zuerst: Du hast im Text ...
02.05.2012 14:04:57
Klaus Becker
Hi Luc!
Ich habe das jetzt mal mit VARIANT gegengetestet.
Und das Problem scheint vom Tisch zu sein !!!
Ich vermeide sonst eigentlich VARIANT, da das ja angeblich die Performance verschlechtert.
Aber so schlecht, wie mit INTEGER kann es ja gar nicht werden ;-)
Ich habe bei meinem Testen auch bemerkt, dass das übergebene Parameter auf eine Zelle mit Formel verweist.
Trage ich dort einen festen Wert ein z:B. 1, so besteht das Problem ebenfalls nicht.
Hätte diese eine volatile Funktion enthalten, wäre das ja ein Grund. Dort steht aber lediglich:
=WENN(ISTFEHLER(SVERWEIS(C6;Feiertage;1;FALSCH));0;SVERWEIS(C6;Feiertabelle;2;FALSCH))
Ich habe ISTFEHLER im Verdacht, wenn ich an Deine Ausführungen zur internen Fehlerüberprüfung von Excel denke.
Ich bin aber schon so in Zeitnot geraten, dass ich jezt erstmal nicht weiter nachforsche, sondern in die Tasten haue, damit das Ding endlich fertig wird.
Da in den Parameterzellen lediglich Werte von 1 bis 5 stehen, hielt ich INTEGER für das richtige Format.
Aber die Lösung mit VARIANT ist absolut o.k. , denke ich. Kann höchstens sein, dass ich irgendwo anders
einen Typ-Konflikt bekomme, aber man kann die Typen ja konvertieren:

CInt(vStr)
Ich versuch das jetzt an der Original-Datei.
Hab vielen Dank Luc!!
Ach ja. Wegen des Zitat-Tags:
Ich hatte den Text in einem Editor vorgeschrieben und im Forum eingefügt.
Das

< pre >
wurde mir bei den ersten Beiträgen meiner Meinung nach nicht angezeigt.
Wie soll ich es dann löchen?
In dieser Nachricht hab ich es ja bewusst gesetzt.
Klaus
Anzeige
Wieder das Letzte zuerst: Das wird auch ...
02.05.2012 16:09:17
Luc:-?
…nicht angezeigt, Klaus,
weil das die Forumssoftware im Nachhinein hinzufügt. Kannst du also auch nicht löschen, sondern nur vermeiden, indem du diese Begriffe nicht isoliert (als einzelen Worte) benutzt und ansonsten das Zitat-Tag setzt.
Das mit der schlechteren Performance bei Variant mag zwar sein, die Vorteile wiegen das aber bei udFktt auf. Die ursprgl XlFktt wurden ja wohl auch so pgmiert, weshalb mir beinahe unverständl ist, warum spätere MS-Pgmierer das nicht beibehalten haben — möglicherweise hptsächl aus dem von dir genannten Grund. Glücklicherweise haben sie aber den FktsBestand nicht analog geändert.
Was ISTFEHLER betrifft, glaube ich zwar nicht, dass das der HptGrd ist, wäre aber möglich. Auf jeden Fall darf aber bei der XlÜberprüfung der Eingangsvariablen einer (ud)Fkt kein Fehler entstehen, denn dann wird die gar nicht erst berechnet und es setzt Folgefehler, weshalb ich vermutete, dass dem eine eigene volatile Primärregie zugrunde liegen könnte, weil das unnötige Rechenarbeit erspart.
Da bei Variant alles erlaubt ist, entfällt diese Überprüfung für ein solches Argument.
Viel Erfolg! Gruß Luc :-?
Anzeige
AW: Wieder das Letzte zuerst: Das wird auch ...
02.05.2012 18:41:24
Klaus Becker
Hallo Luc,
ich habe die letzten Stunden darauf verwendet, in der Originaldatei überall Variant einzusetzen, was leider eine lange Kaskade von Änderungen in den benutzten Unterfunktionen ausgelöst hat :-(
Am Ende hat alles nichts genützt, sobald ich die Funktionen verschachtel, ist das ganze wieder Volatile ....grrrr.
Ich werde jetzt einen Workaround basteln. Ich habe das an anderer Stelle schon mal ähnlich gemacht:
Ich schalte für ein Prototyp-Blatt die automatische Berechnung aus:
Worksheets("Tabelle1").EnableCalculation = False

Dann mach ich von dem Blatt per VBA 12 Kopien für die Monate JAN-DEZ und ersetze in jedem Blatt die Formeln, die sich zur Laufzeit nicht ändern durch Werte. Somit habe ich statische Parameter, die dann ohne Probleme als Eingabewerte für meine UDF dienen. Dass das funktioniert hatte ich händisch an einigen Stellen ausprobiert (z.B. bei der ISTFEHLER-Formel).
Bei den so veränderten Blättern kann ich dann die automatische Berechnung wieder einschalten.
Worksheets("Tabelle1").EnableCalculation = True 

Zum Glück sind die dynamischen Zellen, die meine UDF verwendet von den Usern auszufüllen, und nicht Ergebnis einer Formel, die meine UDF in den Volatile-Abgrund reisst. Ich hoffe das bleibt auch so.
Alternativ hatte ich auch schon mal per VPA die Gleichheitszeichen vor den Formeln gelöscht und als Text in ausgeblendete Spalten weggebunkert, um einige Berechnungen auszusetzen.
Bei Bedarf hab ich sie dann mit "=" davor wieder rüberkopiert.
So richtig zufrieden bin ich mit der Lösung nicht, aber sie hat wenigstens ein definiertes Ende.
Ich habe das Gefühl, dass ich mich sonst noch tagelang tot suche, Vermutungen anstelle und Beispiele erstelle, die meine Vermutungen beweisen sollen.
Vielleicht forsche ich auch noch ein wenig weiter, es würde mich ja schon interessieren, wieso der meine UDF dauernd neu berechnet. vielleicht vererbe ich ja auch durch eine Lange Kette von Formeln und Zellen eine volatile Funktion. Nur komisch, dass es in einer Situation mit VARIANT statt INTEGER funktioniert hat.
Die Ursache muss ja auch nicht monokausal sein...
LG
Klaus
Anzeige
Sicher! Viel Erfolg! Gruß owT
02.05.2012 23:37:03
Luc:-?
:-?

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige