Thema: Funktion gibt kein Rückgabewert aus - Fehler #wert!

Hallo,
Ich möchte in meiner Liste die Farbe der Bedingten Formatierung auswerten (Zählen) und das über ein Funktionsaufruf innerhalb einer Zelle.
Die Funktion funktioniert soweit auch (Beim Durchlaufen mit F8 wird das richtige Ergebnis ermittelt.
Die Zelle gibt aber den Fehler #Wert! als Rückgabewert. Die Beschreibung des Fehlers (#wert!) leuchtet mir zwar ein, bringt mich aber nicht zu einer Lösung )o;

Function AusbLevel() As Double

Dim Ausb As Double

Dim Zeile, Spalte As Double

Dim Bereich As Range

'Hier wird die akitive Zeile und Spalte ermittelt

Zeile = ActiveCell.Row

Spalte = 11 'Spalte K

Set Bereich = Range(Cells(Zeile, Spalte), Cells(Zeile, 75))

Ausb = 0 ' Level erreicht

For Each b In Bereich

If b.DisplayFormat.Interior.Color = 5287936 Then

Ausb = Ausb + 1

End If

Next b

AusbLevel = Ausb

End Function  

Servus,

versuch es doch mal so:

Function FarbeCount(rng As Range) As Long

    Dim Farbe As Long

    Dim Zelle As Range

    Dim Anzahl As Long

    

    Farbe = 5287936

    Anzahl = 0



    For Each Zelle In rng

        If Zelle.Interior.ColorIndex = Farbe Then

            Anzahl = Anzahl + 1

        End If

    Next Zelle



    FarbeCount = Anzahl

    

End Function

Hier musst du zwar die Range angeben, aber ist dann auch universaler verwendbar.

VG
velo

Moin Frank!
DisplayFormat geht in Formeln nicht.
https://learn.microsoft.com/de-de/office/vba/api/excel.range.displayformat

Gruß Gerd

Hallo Frank!

Sub Start()



Cells(ActiveCell.Row, 10) = FarbeCount(Intersect(ActiveCell.EntireRow, Range(Columns(11), Columns(75))))

End Sub

Function FarbeCount(rng As Range) As Long

    Dim Farbe As Long

    Dim Zelle As Range

    Dim Anzahl As Long

    

    Farbe = 5287936

    Anzahl = 0



    For Each Zelle In rng

        If Zelle.Interior.ColorIndex = Farbe Then

            Anzahl = Anzahl + 1

        End If

    Next Zelle



    FarbeCount = Anzahl

    

End Function

Die gute Vorarbeit von Velo habe ich einfach uebernommen.
Was konkret loest die Aenderung der bedingten Formatierungen bei dir aus; Eingabe von Werten, Formeln ?

Gruß Gerd

P.S.: Zur Zeit macht mir hier die Forensoftware Umlautesalat. Betrifft dies alle?

HI

Wie die Kollegen schon schrieben, MS hat DisplayFormat für die Verwendung in Zellen gesperrt, dh eine Function, die das verwendet, funktioniert nur als Makro, aber nicht in einer Formel in einer Zelle.

Man kann diese Sperre aber austricksen, in dem man nur für den Teil mit DisplayFormat eine eigene Funktion erstellt und diese mit EVALUATE aufruft.
Evaluate berechnet Texte, die wie eine Formel aussehen, als Formel (wobei die Formel in Englisch und mit A1-Adressen geschrieben sein muss).
Dabei wird die oben genannte Prüfung umgangen.

hier mal ein kleines Bespiel:
mit diesem Code in einem allgemeinen Modul:

Function Farbe(Zelle As Range) As Long

Dim txt As String

txt = "FarbeX('" & Zelle.Worksheet.Name & "'!" & Zelle.Address & ")"

Farbe = Evaluate(txt)

End Function



Function FarbeX(Zelle As Range) As Long

FarbeX = Zelle.DisplayFormat.Interior.Color

End Function

kannst du dir mit der Formel =Farbe(A1) die hintergrundfarbe von A1 in einer Zelle anzeigen lassen, dabei wird auch die Bedingte Formatierung berücksichtigt.

Gruß Daniel

Danke !!
Die Lösung habe ich jetzt wie folgt umgesetzt.

Function FarbeX(Zelle As Range) As Long

FarbeX = Zelle.DisplayFormat.Interior.Color

End Function



Function AusbLevel() As Long

Dim Zeile, Spalte As Long

Dim Bereich, Zelle As Range

Dim txt As String

Dim Farbe As Long



'Hier wird die akitive Zeile und Spalte ermittelt

Zeile = ActiveCell.Row

Spalte = 11 'Spalte K



Set Bereich = Range(Cells(Zeile, Spalte), Cells(Zeile, 75))

Ausb = 0 ' Level erreicht

For Each Zelle In Bereich

txt = "FarbeX('" & Zelle.Worksheet.Name & "'!" & Zelle.Address & ")"

Farbe = Evaluate(txt)

If Farbe = 5287936 Then

AusbLevel = AusbLevel + 1



End If

Next Zelle

End Function

Damit wird die Anzahl einer bestimmten Farbe der bedingten Formel ermittelt und in der Zelle ausgegeben.
Aber )o:
Wenn ich diese Funktion in weitere Zellen benutze, dann wird der Wert für alle übernommen, d.h. alle Werte sind gleich (bei Neuberechnung).
Auch eine Änderung in der Tabelle (Änderung der Bedingung) ändert sich der wert erst nach Neuberechnung und dann auch für alle gleich )o:

Hi,
wenn Excel es für möglich halten muss zu rechnen..

Option Explicit



Function FarbeX(Zelle As Range) As Long

    FarbeX = Zelle.DisplayFormat.Interior.Color

End Function



Function AusbLevel(Optional rng As Range = Nothing) As Long

    Dim Zeile, Spalte As Long

    Dim Bereich As Range, Zelle As Range

    Dim txt As String

    Dim Farbe As Long

    Dim lngZ As Long

    'Hier wird die akitive Zeile und Spalte ermittelt

    Zeile = ActiveCell.Row

    Spalte = 11 'Spalte K

    If Not rng Is Nothing Then

        Set Bereich = rng

    Else

        Set Bereich = Range(Cells(Zeile, Spalte), Cells(Zeile, 75))

    End If

    For Each Zelle In Bereich

    txt = "FarbeX('" & Zelle.Worksheet.Name & "'!" & Zelle.Address & ")"

    Farbe = Evaluate(txt)

        If Farbe = 5287936 Then

            lngZ = lngZ + 1

        End If

    Next Zelle

    AusbLevel = lngZ

End Function

sollte es immer unterschiedliche Ertrgebnisse liefern.
hth
Ulf

Hi
deine Beobachtungen (Ergebnis immer gleich), aktueller Wert erst nach Neuberechnung liegt daran, dass du den Zellbereich, der durchsucht wird, in der Funktion fest vorgegeben hast und damit dann auch alle Formeln dieselben Zellen durchsuchen, damit muss das selbe Ergebnis heraus kommen.

du solltet hier den Zellbereich als Parameter übergeben.

Function AusbLevel(Bereich as Range) As Long

Dim txt As String

Dim Farbe As Long

Dim Zelle as Range



AusbLevel = 0 ' Level erreicht

For Each Zelle In Bereich

txt = "FarbeX('" & Zelle.Worksheet.Name & "'!" & Zelle.Address & ")"

Farbe = Evaluate(txt)

If Farbe = 5287936 Then

AusbLevel = AusbLevel + 1



End If

Next Zelle

End Function

und dann den Zellbereich in der Formel mit angeben, dh =AusbLevel(J11:CY11)

hat den Vorteil , dass du damit unterschiedliche Bereiche untersuchen kannst und dass die Neuberechnung der Funktion automatisch angestoßen wird.

Excel berechnet eine Formel immer dann neu, wenn sich ein Zellwert innerhalb des Zellbereichs, der in der Formel verwendet wird, ändert.
Wenn du den Zellbereich in der Funktion fest vergibst, dann versteckst du ihn vor dieser Funktionalität, dh deine Formel weiß nicht, bei welchen Zellen die Neuberechung ausgeführt werden soll.

und nur so neben bei, VBA kennt keine Gruppendimensionierung.
du musst jeder Variablen einzeln ihren Typ zuweisen.
mit: Dim X, Y als Long wird nur Y der Typ Long zugewiesen und X bleibt, da du für X keinen Datentyp angegeben hast, variant.
wenn beide Long werden sollen, geht das auch in einer Zeile, ab so: dim X as long, Y as long

Gruß Daniel

An Alle! Vielen Dank zur Lösung meines Problems. Direkt an Daniel - danke, dass ich den Code direkt übernehmen konnte und für die Erklärung / Weiterbildung (o;

Und noch ein Tipp, nur für den Fall dass tatsächlich mal den Zellbereich direkt in der Funktion angeben willst und nicht in einem der übergebenen Parameter:

ActiveCell ist nicht die Zelle, in der die Formel steht, sondern die Zelle, die bei Ausführung der Funktion von dir markiert ist (und das ist bei Berechnung für alle Zellen die gleiche Zelle)
Wenn du innerhalb der Funktion auf die Zelle referenzieren willst, in welcher die Formel steht, dann nutze Application.Caller

Hier mal ein Beispiel:
diese Funktion erstellen.

Function test() As String

Application.Volatile

test = Application.Caller.Address(0, 0) & " vs. " & ActiveCell.Address(0, 0)

End Function

schreibe dann in mehrere Zellen die Formel =test()
das Ergebnis ist die Adresse der Zelle, in welcher die Formel steht (application.Caller) und die Adresse der ActiveCell.

Gruß Daniel

UUUpps.

nur .Interion.Color, nicht .Interior.ColorIndex !

UUUpps.

nur .Interior.Color, nicht .Interior.ColorIndex !

Hi Gerd,
Die Bedingte Formatierung wird nur durch Eingabe in der Zelle ausgelöst.
Gruß Frank

Moin Gerd, :-)

.... werden bei mir nur in der Vorschau des Beitrages "falsch" angezeigt. Danach richtig. ;-)

Servus
Case

Hallo Gerd
Die Funktion liest m.E. den ColorIndex der Zelle aus. Der wird aber durch die Bedingte Formatierung nicht geändert und somit auch nicht zählbar )o:
Die Prozedur start habe ich noch nicht verstanden. Die soll den Wert in die Zelle schreiben?
Also der Ansatz ist (wenn ich es richtig verstehe) die Funktion ermittelt den Wert und die Sub schreib das Ergebnis in die Zelle. Dann rufe ich die Funktion nicht in der Zelle auf (=FarbeCount("Range")?
Bleibt aber das Problem mit dem Index )o:
Gruß Frank

Ja, und ja ohne "index".
Ich muss mich aktuell um Familienangelegenheiten kümmern u. melde mich später nochmal,
wenn es kein anderer vorher löst.

Gruß Gerd

Danke Case!
Gruss Gerd

Hallo Gerd,
vielen Dank für die schnelle Antwort.
Ich bin davon ausgegangen, dass das Problem beim Datentyp liegt, da der Ausgabewert ja übernommen wurde (In der Funktion selbst wenigstens)
Dann bleibt wohl nur die Prozedur. Da funktioniert es. Die hat nur eine lange Laufzeit und durchläuft den gesamten Bereich. Mit der Funktion wollte ich es auf einzelne Zeilen /Zellen begrenzen und nur bei Änderung dieser)o:

Gruß
Frank

Kleine Korrektur noch:

Zelle.Interior.ColorIndex

mit

Zelle.Interior.Color

austauschen

Servus Velo,
danke für deine Antwort. In der Antwort von Gerd hat er darauf hingewiesen, dass auslesen von DisplayFormat in Funktionen nicht funktioniert )o:
Das ist aber im meinem Fall erforderlich, da die Zellenfarbe über die Bedingte Formatierung gesetzt wird und mit Interior.ColorIndex diese nicht ausgelesen werden kann, bzw. die Zellenfarbe (Interior.ColorIndex) sich bei Bedingter Formatierung nicht verändert.
Danke für die schnelle Antwort. Meine Lösung ist wohl nicht über eine Funktion umsetzbar. Bleibt die Prozedur )o: