Thema: UDF funktioniert bei Aufruf per Sub, sonst Fehler

Hallo,
ich habe Programmiererfahrung, bin aber Anfänger mit VBA und komme mit folgender Geschichte nicht weiter:
Ich möchte eine Funktion schreiben, die mir Daten enstsprechend verschiedener Suchkriterien aus einem anderen Tabellenblatt ausliest. Die Funktion funktioniert wenn ich Sie über ein Sub aufrufe und den Rückgabewert in eine MessageBox schreibe, bei Aufruf aus einem Tabellenblatt wird der Fehler "Ein in der Formel verwendeter Wert ist vom falschen Datentyp" ausgegeben. Wobei es auch für manche Eingabewerte funktioniert hat (nach einer kleinen Änderung jetzt aber auch nicht mehr). Ich hab mich schon totgegoogelt. Irgendwelche Ideen? Bin sehr dankbar für jede Hilfe!
Das ist mein Code:

Function Zähldaten(Knoten As String, TimeStr As String, VonArm As Integer, NachArm As Integer)
As Variant
' Variablen:
' WS_Count = Anzahl Tabellenblätter
' I = Zählvariable
' EndStr = Endung der Tabellenblätter für gesuchten Wochentag
' VMittel = Verkehrsmittel; wird aus TimeStr ausgelesen
' Quelle = Ziel-Worksheet gemäß Knoten und Erhebungstag
' QuelleSS = Worksheet, aus dem Spitzenstunde ausgelesen wird
' temp1, temp2 = Variablen zum Zwischenspeichern der Suchbereiche
' VMCell, TimeCell = Zellen, die Spalte(VMCell) und Zeile(TimeCell) der    gesuchten
' Zelle angeben
Dim WS_Count As Integer
Dim I As Integer
Dim Tageszeit As String
Dim EndStr As String
Dim Quelle As Worksheet
Dim QuelleSS As Worksheet
Dim VMittel As String
Dim TimeCell As Range
Dim VMCell As Range
Dim temp1 As Range
Dim temp2 As Range
Set QuelleSS = ActiveWorkbook.Worksheets("Auswertung_SS")
If Left(TimeStr, 2) = "Di" Then EndStr = ".1" Else EndStr = ".2"
VMittel = Mid(TimeStr, 4, Len(TimeStr) - 7)
If Right(TimeStr, 3) = "MSP" And EndStr = ".1" Then
Tageszeit = QuelleSS.Cells(6, 12).Value
ElseIf Right(TimeStr, 3) = "ASP" And EndStr = ".1" Then
Tageszeit = QuelleSS.Cells(7, 12).Value
ElseIf Right(TimeStr, 3) = "MSP" And EndStr = ".2" Then
Tageszeit = QuelleSS.Cells(8, 12).Value
Else: Tageszeit = QuelleSS.Cells(9, 12).Value
End If
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 3 To WS_Count
If Left(ActiveWorkbook.Worksheets(I).Name, Len(Knoten)) = Knoten And
Right(ActiveWorkbook.Worksheets(I).Name, 2) = EndStr Then
Set Quelle = ActiveWorkbook.Worksheets(I)
Exit For
End If
Next I
' Suche in diesem Datenblatt nach...
With Quelle
' 1. der gesuchten Uhrzeit:
Set TimeCell = .Range("d134:d185").Find(Tageszeit)
' 2. dem Arm aus dem ausgebogen wird
Set temp1 = .Range("j11:ft11").Find(VonArm, LookAt:=xlWhole)
For I = 1 To 8
If temp1.Offset(0, -1).Value = "von Arm:" Then
Exit For
ElseIf I < 8 Then
Set temp1 = .Range("j11:ft11").FindNext(temp1)
Else: Zähldaten = "Von Arm nicht gefunden!"
Exit Function
End If
Next I
' Dem Arm in den eingebogen wird
Set temp2 = .Range(temp1, temp1.Offset(0, 43)).Find(NachArm, LookAt:=xlWhole)
For I = 1 To 5
If temp2.Offset(0, -1).Value = "nach Arm:" Then
Exit For
ElseIf I < 8 Then
Set temp2 = .Range(temp1, temp1.Offset(0, 43)).FindNext(temp2)
Else: Zähldaten = "Nach Arm nicht gefunden!"
Exit Function
End If
Next I
Set VMCell = .Range(temp2.Offset(3, -8), temp2.Offset(3, 2)).Find(VMittel)
Zähldaten = .Cells(TimeCell.Row, VMCell.Column).Value
End With
End Function

…FktsArgumente) ja nun nicht, Vere,
deshalb kann ich nur raten, die Variablen, die das .Find- bzw .FindNext-Objekt aufnehmen sollen, auf Is Nothing zu überprüfen, bevor sie weiterverwendet wdn. Die Methoden könnten ja auch mal nichts finden. Im Übrigen will ich mal hoffen, dass du die Fkt nicht irgendetwas tun lässt, was Fktt in ZellFmln verboten ist → zB irgendwelche Zellen ändern.
Morrn, Luc :-?
Besser informiert mit …

Hallo Vere,
ergänzend zum Tip von Luc solltest du in der Function ActiveWorkbook durch ThisWorkbook ersetzen, wenn die Function in Tabellenformeln verwendet wird. Sonst kann es zu Problemen kommen, wenn mehrere Dateien gleichzeitig geöffnet sind und Excel eine Neuberechnung durchführt. Für deine Zellenformel ist dann ggf.die falsche Arbeismappe die aktive Arbeitsmappe.
Gruß
Franz

…meiner sonst üblichen Empfehlung. Setzt aber voraus, dass die Fkt auch in der Anwendungsmappe steht, Vere;
ansonsten wäre es wohl besser, die betroffene Mappe in die Argumente aufzunehmen. Aber im Prinzip ist es so, dass eine UDF in einer ZellFml immer mit den Daten ihres StandortBlattes arbeitet, wenn nicht explizit etwas anderes angegeben wurde, was du quasi getan hast.
Gruß, Luc :-?

Hallo, :-)
ergänzend zu den Hinweisen von Luc:-? und Franz ist es immer angeraten bestimmte Parameter bei Find immer anzugeben - siehe Hilfe.
Es gibt zahlreiche Themen im Netzt, die darauf hinweisen, dass "FindNext" in UDFs nicht funktioniert und entsprechende Lösungen aufzeigen:
FindNext not working in UDF...
Find and FindNext in VBA
Servus
Case

…passieren könnte, was Xl zu den für eine UDF in einer ZellFml verbotenen Aktionen zählt.
Gruß, Luc :-?

Hallo,
vielen Dank schonmal für die Hilfestellungen.
.FindNext durch .Find zu ersetzen hat leider nichts geändert.
Um ehrlich zu sein, als VBA-Neuling bin ich mir nicht 100%sicher ob die Formel etwas tut was in UDFs nicht erlaubt ist, aber im Grunde suche ich ja nur ein bestimmtes Tabellenblatt und darin zwei Zellen und übergebe am Ende den Inhalt der gesuchten Zelle als Rückgabewert. Ja sicher kann nichts gefunden werden (was eigentlich der Vergleich mit der davorliegenden Zelle auch mit abfragt dachte ich), aber ich habe ja nunmal ein korrektes Ergebnis wenn ich es in einer MessageBox aufrufe, insofern kann das für meine spezifische Eingabe nicht sein (ich habe auch verschiedene getestet). Ich dachte auch daran, dass es irgendwas damit zu tun hat wo die Funktion steht, wie da die Hierarchien sind hab ich nämlich noch nicht wirklich verstanden. Ich hab ein neues Modul angelegt und fertig. Das Sub, was die Funktion aufruft, funktioniert ja aber auch aus dem Worksheet heraus?!
Ich hab mir so eine simple Abfrage irgendwie einfacher vorgestellt...

…Subprozedur aufgerufen wird, Vere,
oder ob in eine Zelle eines Blattes eine Fml eingetragen wird, die eine UDF aufruft. Für die gilt dann das Gleiche wie für alle XlFktt → Xl akzeptiert nur ein reines Rechenergebnis, alle anderen Aktionen mit irgendwie blattändernden Auswirkungen wdn ignoriert! Das kann sowohl auf .Find als auch auf .FindNext zutreffen, denn Letztes ist nur die Fortsetzung von Ersterem, welches lt Case' Empfehlung vollständig parametriert wdn sollte. Dabei reichen der Xl-Steuerung schon bestimmte Charakteristika der Aktion, um sie zu ignorieren, auch wenn gar nichts verändert wird.
Gruß, Luc :-?

Lustigerweise habe ich Wertte bekommen als ich alles in ein neues Sheet kopiert und dann nur Werte einfügen ausgewählt habe. Habe ich jetzt nicht verstanden, aber bis auf weiteres habe ich eh noch ein Sub geschrieben, dass die Ergebnisse in ein Tabellenblatt schreibt - umständlich, aber tut erstmal. Trotzdem, dank eurer Erklärungen habe ich wenigstens mal Grundlegendes zu den Zusammenhängen gelernt, Danke nochmal!

:-?