Eigene Excel-Funktion (Makros) allgemein zur Verfügung stellen

Soll eine selbst erstellte Funktion in allen Tabellen zur Verfügung stehen ist das recht einfach mittels eines AddIns zu verwirklichen.

Ein paar Schritte müssen dafür getan werden

1. Excel-Datei mit Makros erstellen mit der/den Funktion(en)
    Der Dateiname ist frei wählbar. In diesem Bsp. wurde Funktionensammlung.xlsm gewählt
    Die Excel-Datei enthält 2 Funktionen. Es werden durch eine zusätliche Funktion beim Start die beiden Funktionen registriert.
    Dies hat zur Folge dass die Funktionen eine eigene Kategorie bekommen und die einzelnen Felder einen Hinweistext bekommen.
    Folgende Funktionen werden zur Verfügung gestellt
    -  RegEx-Funktion aus dem VBA-Bereich in den normalen Excel-Funktionen zur Verfügung stellen.
    -  Datum in englischer Schreibweise darstellen.  

2. Excel-Datei als .xla in den Ordner C:\Users\[USER]\AppData\Roaming\Microsoft\AddIns speichern

3. Datei > Optionen => Menüband anpassen
    Häckchen bei Entwicklertools setzen und mit OK abschließen

4. In Excel auf das Register Entwicklertool wechseln und Add-Ins anklicken. In dem Dialog-Fenster Funktionensammlung aktivieren und mit OK abschließen.    addin aktivieren

Ab jetzt stehen in jeder Excel Datei die beiden Funktionen zur Verfügung.

 

eigene funktionen

   function regex 

 

Download: Funktionensammlung.xlsm

Der VBA-Code der in ein Modul gespeichert werden muss.

Option Explicit
'Funtion liefert die Treffer zurück
' https://www.vba-tutorial.de/applikation/regexp.htm


' Es muss ein Verweis auf folgende Bibliothek vorhanden sein "Microsoft VBScript Regular Expression 5.5"
' Aufrufen via VBA-Menü -> Extras -> Verweise.., nach unten scrollen und anklicken
' c:\Windows\ysWOW64\vbscript.dll

' Text       -> Der Text der durchsucht werden muss
' Suchmuster -> Ein Regex-Ausdruck als Suchmuster
' (|) ODER Ausdruck
' {x,y} Länge des Suchmusters minimum, maximum
' [0-9] Zahl von 0 bis 9
' [a-zA-Z] Buchstaben a bis z, Groß und Kleinschreibung

' Email RegEx-Pattern als voreingestelltes Suchmuster
Const cDefautlPattern = "([a-z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,6})"

Const cCategory = "AddIns-Funktionen"

Const cMacroName1 = "RegEx"
Const cMacroName2 = "ConvertDate"

Function RegEx(Txt$, Optional ByVal SearchPattern$ = cDefautlPattern, Optional All = False, Optional MatchSeparator$ = "|", Optional IgnoreCase = True, Optional Multiline = False)
  Dim RegularExpression As New RegExp
  Dim TrefferSammlung As MatchCollection, Treffer As Match
  Dim sTemp$
  
  With RegularExpression
    .Global = All
    .Multiline = Multiline
    .Pattern = SearchPattern
    .IgnoreCase = IgnoreCase
    Set TrefferSammlung = .Execute(Txt)
  End With
    
  For Each Treffer In TrefferSammlung
    'Debug.Print Treffer
    sTemp = sTemp & IIf(sTemp = vbNullString, vbNullString, MatchSeparator) & Treffer
  Next
  RegEx = sTemp
  
  Set TrefferSammlung = Nothing
  Set RegularExpression = Nothing
End Function

Function ConvertDate(Datum, Optional shortMonth As Boolean) As String
  Dim arr, arrMonth
  If Datum <> vbNullString Then
    arr = Split(Datum, ".")
    arr(0) = CInt(arr(0))
    Select Case arr(0)
        Case 1, 21, 31
          arr(0) = arr(0) & "st"
        Case 2, 22
          arr(0) = arr(0) & "nd"
        Case 3, 23
          arr(0) = arr(0) & "rd"
        Case Else
          arr(0) = arr(0) & "th"
    End Select
    ' Monat
    If shortMonth Then
      arrMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    Else
      arrMonth = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
    End If
    arr(1) = arrMonth(arr(1) - 1) & ", "
    ' Zurück packen
    ConvertDate = Join(arr, " ")
  Else
    ConvertDate = Datum
  End If
End Function


' Wird beim öffnen dieser Arbeitsmappe geladen
Public Sub RegisterMyFunction()
  Application.MacroOptions _
    Macro:=cMacroName1, _
    Description:="Per Suchmuster Textteile auslesen", _
    Category:=cCategory, _
    ArgumentDescriptions:=Array( _
      "Feld welches durchsucht werden soll", _
      "Suchmuster (default): " & cDefautlPattern, _
      "Alle Treffer anzeigen (default = FALSCH)", _
      "Trennzeichen bei mehreren Treffern (default = |)", _
      "Groß-/Kleinschreibung ignorieren (default = WAHR)", _
      "Text ist mehrzeilig (default = FALSCH)")
            
  Application.MacroOptions _
    Macro:=cMacroName2, _
    Description:="Datum als Text in englisher Schreibweise", _
    Category:=cCategory, _
    ArgumentDescriptions:=Array( _
      "Feld mit dem Datum", _
      "Monat Kurzschreibweise  (default = FALSCH)")
End Sub

 

Damit die Funktionen korrekt dargestellt werden muss in DieseArbeitsmappe noch die Funktion RegisterMyFunction eingetragen werden damit diese beim Start aufgerufen werden.

Private Sub Workbook_Open()
  RegisterMyFunction
End Sub

 

Hauptmenü

Anmeldeformular