Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

Thema

Eigene Funktionskategorien erstellen

Gruppe

Anwendung

Problem

Wie kann ich eigene Funktionskategorien erstellen

Lösung
Geben Sie den nachfolgenden Code in ein Standardmodul ein und weisen Sie ihn einer Schaltfläche zu.

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