Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA
Add-Ins
Excel/VBA

Excel-Beispiele:
Erklärungen, Formeln, VBA-Code und mehr

Hinweise zu den Beispielen finden Sie hier: Beispiele


Kategorie: Beispiel > Excel > VBA > UDF (16)

UDF - Benutzerdefinierte Funktionen

(Tipp 164) Nachricht zum Beitrag an Autor Nach oben

Benutzerdefinierte Funktionen, also UDF, sind Funktionen, die man sich selbst im VBA-Editor erstellt. Dazu benennt man sie nicht mit Sub, sondern mit Function. Der Name der Funktion liefert dabei den Rückgabewert. Heißt also eine Funktion "MeineFunktion(...)", schreibt man in die Zelle "=MeineFunktion(...)". Ebenfalls kann man Funktionen einsetzen, um durch Subs bestimmte Berechnungen durchführen zu lassen.

Um zu gewährleisten, dass eine in einem Tabellenblatt eingesetzte UDF immer rechnet, sollte man an den Anfang der Funktion schreiben:

Application.Volatile

Quersumme bilden

(Tipp 165) Nachricht zum Beitrag an Autor Nach oben

Wie kann man die Quersumme einer Zahl in einer Zelle bilden?

Function Quersumme(Zelle As Range) As Integer Dim intI As Integer Application.Volatile Quersumme = 0 If IsNumeric(Zelle) Then For intI = 1 To Len(Zelle) Quersumme = Quersumme + CInt(Mid(Zelle, intI, 1)) Next End If End Function

In die Zellen braucht man dann nur die Formel einzugeben:

=Quersumme(A1)

Zahlen zerlegen in Euro + Cent (in zwei Zellen)

(Tipp 166) Nachricht zum Beitrag an Autor Nach oben

In einer Zelle steht ein Betrag. In einer anderen Zelle möchte ich nur den Eurobetrag, in einer weiteren nur den reinen Centbetrag. Aus 3,25 Euro müßte also in der einen zelle die 3, in der anderen Zelle die 25 stehen. Wie kann man das mittels einer benutzerdefinierten Funktion lösen?

Die benutzerdefinierten Funktionen in ein Standardmodul:

Function WEuro(Zelle) If Not IsNumeric(Zelle) Then WEuro = "" Else WEuro = Fix(Zelle) End Function Function WCent(Zelle) If Not IsNumeric(Zelle) Then WCent = "" Else WCent = (Zelle - Fix(Zelle)) * 100 End Function

In die Zellen braucht man dann nur die Formeln einzugeben:

= WEuro(A1) = WCent(A1)

Oder alles in eine Funktion:

Function EuroCent(Zelle, was) EuroCent = "" If IsNumeric(Zelle) Then EuroCent = IIf(was = 1, Fix(Zelle), (Zelle - Fix(Zelle)) * 100) End If End Function

In die Zellen käme dann
=eurocent(A1;1) für den Eurobetrag,
=eurocent(A1;2) für die Cent.



SVERWEIS: Suchkriterium als Teil

(Tipp 167) Nachricht zum Beitrag an Autor Nach oben

Der SVERWEIS sucht in der ersten Spalte einer Matrix nach einem Suchkriterium, das dem SVERWEIS vollständig mitgeteilt werden muß. Wie kann ich nach einer Zeichenfolge suchen, von der nur der erste Teil bekannt ist?

Die Syntax ist die gleiche wie bei der integrierten Funktion SVERWEIS:

=TeilSverweis(Suchkriterium;Matrix;Spaltenindex)

Function TeilSverweis(Suchkriterium As Variant, Bereich As Range, Spaltenindex As Integer) As Variant Dim rngZelle As Range Application.Volatile TeilSverweis = "" If Suchkriterium <> "" Then For Each rngZelle In Bereich If rngZelle.Column = Bereich.Column Then If CStr(Left(rngZelle.Value, Len(Suchkriterium))) = CStr(Suchkriterium) Then TeilSverweis = Cells(rngZelle.Row, rngZelle.Column + Spaltenindex - 1) Exit Function End If End If Next TeilSverweis = "Nicht gefunden" End If End Function

Werte in einem Bereich zählen

(Tipp 168) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich in einem Bereich die Zahlen zählen, die größer/gleich einer Zahl und kleiner gleich einer anderen Zahl sind?

Am einfachsten ist wahrscheinlich, wenn die integrierte Funktion ZÄHLENWENNS() verwendet wird:

=ZÄHLENWENNS(A1:A10;">2";A1:A10;"<7")

Wenn der Bereich nicht zu groß ist, kann ansonsten auch folgende benutzerdefinierte Funktion verwendet werden:

Function Zaehlen(Bereich As Range, Minimum, Maximum) Dim objZelle As Object, intI As Integer intI = 0 For Each objZelle In Bereich If objZelle.Value >= Minimum And objZelle.Value <= Maximum Then intI = intI + 1 End If Next Zaehlen = intI End Function

In die Zelle muß dann eingegeben werden:

=Zählen(Bereich;kleinste Zahl;größte Zahl)

=Zählen(A1:A100;10;20)

Kalenderwoche nach DIN

(Tipp 169) Nachricht zum Beitrag an Autor Nach oben

Wie kann die Kalenderwoche eines Datums nach DIN berechnet werden?

Diese Funktion stammt von Christoph Kremer:

Function DINKwoche(Datum) Dim tmp tmp = DateSerial(Year(Datum + (8 - WeekDay(Datum)) Mod 7 - 3), 1, 1) DINKwoche = ((Datum - tmp - 3 + (WeekDay(tmp) + 1) Mod 7)) \ 7 + 1 End Function

Zweite Möglichkeit:

Function kw(Datum As Date) As Single kw = Format(Datum, "ww", , vbFirstFourDays) - IIf(Weekday(Datum) = 1, 1, 0) End Function

Pause nur berechnen, wenn anwesend

(Tipp 170) Nachricht zum Beitrag an Autor Nach oben

Ich habe eine Datei zum Führen der Arbeitszeit aufgebaut. Mein Problem sind unsere Pausenregelungen, von 9:00 - 9:15 Uhr und 13:00 - 13:30 Uhr. Die Pausen dürfen nur abgezogen werden wenn derjenige anwesend ist. Das Pause-Feld muß sich also nach den Komm- u. Geht-Feldern richten.

Das sind verschiedene Konstellationen, die ausgewertet werden müssen. Dazu gibt es natürlich verschiedene Möglichkeiten, hier wird mal der Einsatz von Select Case demonstriert.

Das Problem ist, dass es hier keine Variable gibt, deren Wert ausgewertet werden kann. Es sind immer Bedingungen, die in Kombination zutreffen oder eben nicht. Das heißt, statt der Variablen wird True im Select verwendet:

Function Pausenzeit(kommt, geht, P1Beginn, P1Ende, P2Beginn, P2Ende) Dim datErsteZeit As Date, datZweiteZeit As Date Select Case True Case geht < P1Beginn Or kommt > P2Ende Pausenzeit = 0: Exit Function 'geht vor erster Pause oder kommt nach zweiter Pause Case kommt >= P1Beginn And kommt <= P1Ende And geht > P1Ende datErsteZeit = P1Ende - kommt 'kommt in erster Pause, geht nach erster Pause Case kommt < P1Beginn And geht > P1Ende datErsteZeit = P1Ende - P1Beginn 'kommt vor erster Pause, geht nach erster Pause Case kommt >= P2Beginn And kommt <= P2Ende And geht > P2Ende datErsteZeit = 0: datZweiteZeit = P2Ende - kommt 'kommt in zweiter Pause, geht nach zweiter Pause Case kommt < P1Beginn And geht < P1Ende datErsteZeit = geht - P1Beginn: datZweiteZeit = 0 'kommt vor erster Pause, geht in erster Pause Case kommt >= P1Beginn And geht <= P1Ende datErsteZeit = geht - kommt: datZweiteZeit = 0 'kommt und geht in erster Pause End Select Select Case True Case geht < P2Beginn: datZweiteZeit = 0 'geht vor zweiter Pause Case geht >= P2Beginn And geht < P2Ende: datZweiteZeit = geht - P2Beginn 'geht in zweiter Pause Case Else: datZweiteZeit = P2Ende - P2Beginn End Select Pausenzeit = datErsteZeit + datZweiteZeit End Function

In die Zelle muss dann nur noch:

=Pausenzeit(A4;B4;B1;C1;D1;E1)

Formel zeigen und rechnen

(Tipp 172) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich in einer Zelle die Formel anzeigen, die in einer anderen Zelle enthalten ist?

Eine benutzerdefinierte Funktion dazu:

Function Bezug(Zelle) If Zelle.HasFormula Then Bezug = Replace(Zelle.FormulaLocal, "=", "") Else Bezug = "" End If End Function

In die Zelle kann dann z. B. eingegeben werden: =Bezug(A1)

Oder für die Freunde gepflegter regulärer Ausdrücke (Microsoft VBScript Regular Expressions-Objektbibliothek muss eingebunden sein!):

Function Bezug1(Zelle) Dim Regex As New RegExp, regMatches As MatchCollection, regMatch As Match Bezug1 = 0 Regex.Pattern = "^(=)(.*)$" Set regMatches = Regex.Execute(Zelle.FormulaLocal) If regMatches.Count > 0 Then Bezug1 = regMatches(0).SubMatches(1) End Function

Erster Buchstabe in Zeichenfolge

(Tipp 173) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich den ersten Buchstaben in einer Zeichenfolge auslesen lassen?

Am einfachsten geht es sicher mit einem regulären Ausdruck (Microsoft VBScript Regular Expressions-Objektbibliothek muss eingebunden sein):

Function ErsterBuchstabe(strString As String) Dim Regex As New RegExp, regMatches As MatchCollection, regMatch As Match ErsterBuchstabe = "" Regex.Pattern = "^[^A-Za-zÄÖÜäöüß]*([A-Za-zÄÖÜäöüß]).*" Set regMatches = Regex.Execute(strString) If regMatches.Count > 0 Then ErsterBuchstabe = regMatches(0).SubMatches(0) End Function

In die Zelle: =ErsterBuchstabe(A1).

Die folgende Funktion liefert das erste nichtnumerische Zeichen:

Function ErsteNichtZahl(Zelle) Dim intI As Integer If Len(Zelle) = 0 Then ErsteNichtZahl = "" For intI = 1 To Len(Zelle) If Not IsNumeric(Mid(Zelle, intI, 1)) Then ErsteNichtZahl = Mid(Zelle, intI, 1) Exit Function End If Next End Function

In die Zelle: =ErsteNichtZahl(A1).

Die nächste Funktion liefert den ersten Buchstaben einer Zeichenfolge:

Function ErsterBuchstabe1(Zelle) Dim intI As Integer If Len(Zelle) = 0 Then ErsterBuchstabe = "" For intI = 1 To Len(Zelle) If (Asc(Mid(Zelle, intI, 1)) >= 65 And Asc(Mid(Zelle, intI, 1)) <= 90) Or _ (Asc(Mid(Zelle, intI, 1)) >= 97 And Asc(Mid(Zelle, intI, 1)) <= 122) Or _ Asc(Mid(Zelle, intI, 1)) = 196 Or _ Asc(Mid(Zelle, intI, 1)) = 196 Or _ Asc(Mid(Zelle, intI, 1)) = 214 Or _ Asc(Mid(Zelle, intI, 1)) = 220 Or _ Asc(Mid(Zelle, intI, 1)) = 228 Or _ Asc(Mid(Zelle, intI, 1)) = 246 Or _ Asc(Mid(Zelle, intI, 1)) = 252 Then ErsterBuchstabe = Mid(Zelle, intI, 1) Exit Function Else: ErsterBuchstabe = "" End If Next End Function

In die Zelle: =ErsterBuchstabe(1A1) eingegeben werden.

Datum aus Kalenderwoche errechnen

(Tipp 174) Nachricht zum Beitrag an Autor Nach oben

Wie kann man aus einer angegebenen Kalenderwoche das Datum bestimmen? Folgende Daten sind gegeben: 1999 (Jahr) 42 (Kalenderwoche) 1 (Tag)

In die Zelle muss dann eingegeben werden: =TagAusKW(Jahr;KW;Tag).

Function TagAusKW(Jahr As Integer, KW As Integer, Tag As Integer) As Date Dim lngT As LongPtr lngT = DateSerial(Jahr, 1, 4) lngT = lngT - Weekday(lngT, 2) + 7 * KW - 7 If (Year(lngT + 4) = Jahr) Then TagAusKW = lngT + Tag End Function

Formel als Ergebnis einer Formel

(Tipp 418) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich per Formel eine Formel aus einer anderen Zelle anzeigen lassen?

Prüfen, ob in der Zelle eine Formel vorliegt und anschließend die Formel (ohne Gleichheitszeichen) mit dem Ergebnis ausgeben lassen:

Function Bezug(Zelle) If Zelle.HasFormula Then Bezug = Right(Zelle.Formula, Len(Zelle.Formula) - 1) & " = " & Zelle Else Bezug = "" End Function

In die Zelle kommt dann einfach die Formel =Bezug(D7), wobei hier in D7 die eigentliche Formel steht.

Zelle:B7C7D7E7
enthält:1015=B7+C7*2=Bezug(D7)
Ergebnis:  40B7+C7*2 = 40

Zellen mit Farbe zählen

(Tipp 429) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich per Formel Zellen mit Hintergrundfarben zählen?

Siehe Beispieldatei.

Wochendaten zur Zahl einer Kalenderwoche bestimmen

(Tipp 430) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus der Zahl einer Kalenderwoche die entsprechenden Daten der Woche errechnen?

In eine Zelle wird die Zahl einer Kalenderwoche eingetragen. Nach der Eingabe sollen Beginn und Ende der Woche ergänzt werden.

Siehe Beispieldatei.

Letzten Wert in einem Bereich ermitteln

(Tipp 431) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich den letzten Wert in einem Bereich ermitteln?

Siehe Beispieldatei.



Zahl mit Trennzeichen trennen

(Tipp 553) Nachricht zum Beitrag an Autor Nach oben

Eine Zahl, z. B. 8070110, soll nach jeder 0 einen Bindestrich haben, also so: 80-70-110.

Hier ist eine benutzerdefinierte Funktion:

Dazu mit Alt und F11 den Editor aufrufen, ein Modul einfügen und die Function eingeben. In die Zelle kommt dann z. B. die folgende Formel:

String aufteilen

(Tipp 567) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich einen String aufgrund eines Trennzeichens aufteilen?

Die hier dargestellte Funktion stellt eine Alternative zur Funktion Split() dar, die es in niedrigeren Excelversionen noch nicht gab. Alternativ kann die Funktion auch als Tabellenblattfunktion verwendet werden, wenn man Daten - Text in Spalten nicht verwenden kann.

An die Funktion wird übergeben, welcher String aufgeteilt werden soll (strString), der wievielte Eintrag zurückgegeben werden soll (intWelcher) und um welches Trennzeichen es sich handelt (strTrenner).

So könnte die Funktion wie folgt eingesetzt werden:

Das Ergebnis wäre in diesem Fall 78,9bb.