Excel: User-Defined Function nicht aktualisiert!?

Dieses Thema Excel: User-Defined Function nicht aktualisiert!? im Forum "Microsoft Office Suite" wurde erstellt von Ace_NoOne, 26. Apr. 2006.

Thema: Excel: User-Defined Function nicht aktualisiert!? Hallo zusammen, um den jeweils letzten nicht-leeren Wert einer Datenreihe herauszufinden (also z.B. die Werte des...

  1. Hallo zusammen,

    um den jeweils letzten nicht-leeren Wert einer Datenreihe herauszufinden (also z.B. die Werte des aktuellen Monats), habe mich mir folgende User-Defined Function geschrieben:
    Code:
    ' retrieve value from last non-empty row or column in selection
    Function lastValue(rng As Range, searchRows As Boolean)
      Dim firstRow As Integer, firstCol As Integer, _
        rowCount As Integer, colCount As Integer, _
        i As Integer
     -> determine range
      firstRow = rng.Row
      firstCol = rng.Column
      rowCount = rng.rows.Count
      colCount = rng.Columns.Count
     -> default return value
      lastValue = CVErr(xlErrNA)
     -> search rows
      If searchRows = True Then
        For i = (firstRow + rowCount - 1) To firstRow Step -1
          If IsError(Cells(i, firstCol)) = False Then
            If Cells(i, firstCol).value <>  Then
              lastValue = Cells(i, firstCol).value
              Exit For
            End If
          End If
        Next i
     -> search columns
      Else
        For i = (firstCol + colCount - 1) To firstCol Step -1
          If IsError(Cells(firstRow, i)) = False Then
            If Cells(firstRow, i).value <>  Then
              lastValue = Cells(firstRow, i).value
              Exit For
            End If
          End If
        Next i
      End If
    End Function
    Das funktioniert an sich auch sehr gut.
    Allerdings kommt es manchmal zu folgendem seltsamen Fehler: Wenn ich die Datei neu öffne, stimmen plötzlich die Werte der Zellen, welche diese Formel nutzen, nicht mehr. Entweder stehen dort vollkommen falsche Zahlen, oder einach nur #NV.
    Wenn ich die jwlg. Zelle(n) aktiviere und per RETURN den Wert neu berechnen lasse (bzw. im gesamten Tabellenblatt = durch = ersetzen lasse... ), stimmt der Wert dann wieder.

    Ich kann mir dieses Verhalten absolut nicht erklären - hat vielleicht jemand eine Idee, wie das Problem zu beheben ist?


    PS: Die Funktion hätte man sicher besser schreiben können, aber ich bin kein VBA-Profi... (Beispielsweise funktioniert es seltsamerweise nicht mehr, wenn man die nicht leer- und die IsError-Überprüfungen per AND zusammenfasst!?)
     
  2. Hallo Ace_NoOne,

    das ist doch schon sehr gut  :D

    Ich hab die Funktion nochmal etwas übersichtlicher geschrieben.

    Erweitert ist sie um folgende Features:
    - Bereichsangabe mit mehrereren Spalten/Zeilen
    - Bereich kann auf einem anderen Tabellenblatt liegen

    Einschränkung:
    - Bereich kann nicht aus mehreren Bereichen zusammengesetzt sein


    zu deinen Fragen:

    a) Warum das nicht klappt
    If (IsError(Cells(i, firstCol)) = False) And ( Cells(i, firstCol).value <> ) Then

    In der IF-Abfrage werden immer alle Abfragen ausgeführt, und dann erst bewertet. Bei einer Zelle mit #NV, also Fehler, wird die IsError-Abfrage noch ausgeführt, bei Cells(,) <> jedoch bricht der Makro ab, da die Zelle keinen Stringvergleich zuläßt. String und Variant/Fehler sind inkompatibel.

    Es ist schon richtig, wie du es gemacht hast - erst auf Fehler prüfen und dann den String-Vergleich.

    b) falsche Werte beim Öffnen der Datei
    Das kann ich mir so von ferne auch nicht erklären. :(
    Wie häufig kommt das vor? jedes 2.te Mal ?

    Viele Grüße Matjes  :)
    Code:
    Option Explicit
    
    Function LetzterWertImBereich2(myRange As Range, ByColumns As Boolean) As Variant
    
      Dim vonZeile As Long, bisZeile As Long
      Dim vonSpalte As Long, bisSpalte As Long
      Dim l_z As Long, l_sp As Long, x As Long
      Dim ws As Worksheet, r As Range
    
     ->Umfaßt Range nur ein Bereich ?
      If myRange.Areas.Count = 1 Then
        
       ->Worksheet setzen
        Set ws = myRange.Parent
        
       ->Range auf den Benutzten Bereich beschränken
        Set r = Intersect(myRange, ws.UsedRange)
        
       ->Anfangs-/Ende- / Zeile/Spalte
        vonZeile = r.Row
        bisZeile = r.Row + r.Rows.Count - 1
        vonSpalte = r.Column
        bisSpalte = r.Column + r.Columns.Count - 1
        
       ->Suchmodus
        If ByColumns Then
        
         ->letzte Zeile mit Wert suchen
          For l_z = bisZeile To vonZeile Step -1
            For l_sp = bisSpalte To vonSpalte Step -1
              With ws.Cells(l_z, l_sp)
               ->Fehlerwerte (#NV, ..) auslassen, da sonst <> auf die Nase fliegt
                If Not IsError(.Value) Then
                  If .Value <>  Then LetzterWertImBereich2 = .Value: GoTo AUFRAEUMEN
                End If
              End With
            Next
          Next
          LetzterWertImBereich2 = CVErr(xlErrNA)
          
        Else
        
         ->letzte Spalte mit Wert suchen
          For l_sp = bisSpalte To vonSpalte Step -1
            For l_z = bisZeile To vonZeile Step -1
              With ws.Cells(l_z, l_sp)
               ->Fehlerwerte (#NV, ..) auslassen, da sonst <> auf die Nase fliegt
                If Not IsError(.Value) Then
                  If .Value <>  Then LetzterWertImBereich2 = .Value: GoTo AUFRAEUMEN
                End If
              End With
            Next
          Next
          LetzterWertImBereich2 = CVErr(xlErrNA)
        End If
        
      Else
        LetzterWertImBereich2 = #MEHR_ALS_EIN_BEREICH
      End If
      
    AUFRAEUMEN:
      Set ws = Nothing: Set r = Nothing
    End Function
     
  3. Matjes, machst du eigentlich auch irgendwann mal Urlaub?! ;)

    Vielen Dank für die Erklärung zur IF-Abfrage; das war sehr hilfreich.
    Ebenso für die verbesserte (bzw. neu geschriebene) Funktion - so ist's doch schon viel besser einzusetzen...

    Leider kann ich das Problem mit den falschen Werten nicht genauer beschreiben - ich hab' keine Ahnung, was das verursachen könnte.
    Aber ich hab's jetzt per workaround gelöst/umgangen - einfach folgenden Code beim Öffnen des Workbooks ausführen lassen, um die Formeln neu berechnen zu lassen (Application.Calculate hilft seltsamerweise nicht):
    Code:
    Private Sub Workbook_Open()
      Dim ws As Worksheet
      For Each ws In Worksheets
        ws.Cells.Replace What:==, Replacement:==, LookAt:=xlPart, SearchOrder _
          :=xlByRows, MatchCase:=False
      Next
    End Sub
    Ist zwar nicht ganz sauber, aber es funktioniert... (Normalerweise arbeite ich nicht mit solch fiesen Tricks, aber hier ist's mir jetzt egal :p )
     
  4. Hallo Ace_NoOne,

    ich hab noch ein wenig rumprobiert und eine Lösung ohne Workaround gefunden.

    Also ursächlich für den Fehler ist, daß die Function beim Öffnen der Datei noch nicht zur Verfügung steht. Deshalb auch die Anzeige #Name (oder ein wilder wert) und deshalb findet auch das Calculate nicht sein Ziel.

    Neu Konfiguration (Schritte):

    a) Makro aus der Daten-Datei entfernen

    b1) neue Datei anlegen
    b2) Modul hinzufügen
    b3) Makro in das Modul kopieren
    b4) neu Datei unter einem sinnigen Namen im XLStart-Ordner als *.xla speichern

    c)  Excel schliessen

    d) Daten-Datei mit Excel öffnen und Formel korrigieren
    ggf. erscheint jetzt #NAME , d.h. in der Mappe steht die Funktion noch mit dem (nicht sichtbaren) Verweis auf die eigene Datei.
    Die Zelle mit dem Aufruf der Function löschen und Formel neu eingeben.
    Sollte immernoch->Name erscheinen, dann nach dem = und vor dem Funktionsnamen Dateiname und ! eingeben
    Wenn #Name dann nicht mehr erscheint, also ein korrekter Wert angezeigt wird, kann man MeineMakros.xla! wieder entfernen.
    Dann Excel wieder schliessen.

    e) Wenn Excel jetzt geöffnet wird, werden zunächst die *.xla-Dateien aus dem XLStart-Ordner geladen, also auch die Function.

    f) Wenn Du eine Msgbox in die Funktion einfügst, siehst Du das beim Öffnen der Daten-Datei jetzt die Funktion aufgerufen wird und so auch immer ein richtiges Ergebnis in der Zelle erscheint.

    Viele Grüße

    Matjes  ;)
     
  5. Sowas hatte ich schon befürchtet - ist aber auch bescheuert gemacht dann!?
    Vielen Dank für die Anleitung - das werde ich morgen gleich mal ausprobieren. Allerdings wird es damit natürlich etwas unkomfortabel, wenn ich die Excel-Datei verschicken möchte...
     
  6. Hallo Ace_noOne,

    hab noch eine Lösung  ;)

    Mit der Function in einem Modul in der Datenmappe klappt es auch.

    Um zu garantieren, daß die Function geladen ist, wird sie beim Öffnen aufgerufen.

    Ich hab das mal so geschrieben, daß dabei 2 Bereiche übergeben werden,
    was die Function mit einer Fehlerrückgabe quittiert.

    Gruß Matjes :)
    Code:
    Private Sub Workbook_Open()
      Dim v As Variant, r As Range, r2 As Range
      Set r = Worksheets(1).Range(Worksheets(1).Cells(1, 1), Worksheets(1).Cells(1, 2))
      Set r2 = Worksheets(1).Range(Worksheets(1).Cells(3, 1), Worksheets(1).Cells(3, 2))
      v = LetzterWertImBereich2(Union(r, r2), False)
      Set r = Nothing
      Set r2 = Nothing
    End Sub
     
  7. Clever, der Herr! :)
    Nochmals vielen Dank für deine Hilfe!
     
Die Seite wird geladen...

Excel: User-Defined Function nicht aktualisiert!? - Ähnliche Themen

Forum Datum
Excel-Feature gesucht Microsoft Office Suite 11. Okt. 2016
Excel Tabelle Werte zu ordnen Microsoft Office Suite 23. Sep. 2016
Excel: Bereiche auf 'leer' Überprüfen Microsoft Office Suite 15. Sep. 2016
Import Datensatz inkl = und - Zeichen in Excel/Libre CALC Software: Empfehlungen, Gesuche & Problemlösungen 20. Mai 2016
Bestimmter User kann seine Excel Dateien nicht mehr direkt öffnen Software: Empfehlungen, Gesuche & Problemlösungen 16. Apr. 2016