VBA-Programmierung in Microsoft Excel

Eigene Funktionskategorien erstellen

StandardModule: basMain

Die nachfolgende Lösung stammt von Laurent Longre:

Const Lib = """c:\windows\system\user32.dll"""
Option Base 1

Private Function Multiply(N1 As Double, N2 As Double) As Double
  Multiply = N1 * N2
End Function

==========================================

Private Function Divide(N1 As Double, N2 As Double) As Double
  Divide = N1 / N2
End Function

==========================================

Sub Auto_open()
  Register "DIVIDE", 3, "Numerator,Divisor", 1, "Division", _
    "Divides two numbers", """Numerator"",""Divisor """, "CharPrevA"
  Register "MULTIPLY", 3, "Number1,Number2", 1, "Multiplication", _
    "Multiplies two numbers", """First number"",""Second number """, _
    "CharNextA"
End Sub

==========================================

Sub Register(FunctionName As String, NbArgs As Integer, _
  Args As String, MacroType As Integer, Category As String, _
  Descr As String, DescrArgs As String, FLib As String)

  Application.ExecuteExcel4Macro _
  "REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") _
  & """,""" & FunctionName & """,""" & Args & """," & MacroType _
  & ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"
End Sub

==========================================

Sub Auto_close()
  Dim FName, FLib
  Dim I As Integer
  FName = Array("DIVIDE", "MULTIPLY")
  FLib = Array("CharPrevA", "CharNextA")
  For I = 1 To 2
    With Application
      .ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
      .ExecuteExcel4Macro "REGISTER(" & Lib & _
        ",""CharPrevA"",""P"",""" & FName(I) & """,,0)"
      .ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
    End With
  Next
End Sub

    

Beiträge aus dem Excel-Forum zu den Themen UDF und Anwendung