Tipp 5.2 - Excel-Funktionen in Access

Wie kann ich eine Excel Funktion in meiner Access-Datenbank verwenden?

In Excel sind viele mächtige Funktionen eingebaut, mit denen man z.B. Finanzberechnungen und statistische Analysen etc. durchführen kann. Wenn Sie das Gleiche auch in Microsoft Access realisieren möchten, haben Sie eine der folgenden drei Möglichkeiten:

  • eine fertige Lösung einkaufen
  • Ihren eigenen Code schreiben
  • mittels Automation aus Access heraus auf die Funktionen in Excel zugreifen

Zuletzt genanntes greifen wir in unserem Tipp auf :o). Als Beispiel nehmen wir die Berechnung der Tage in der Zinsrechnung.

Das Jahr hat in der (bürgerlichen) Zinsrechnung 365 bzw. 366 Tage, die Monate werden genau gerechnet (Januar 31 Tage, Februar 28 bzw. 29 Tage etc.). Europäische Kaufleute rechnen aus Gründen der Vereinfachung das Jahr zu 360 Tagen und jeden Monat mit 30 Tagen. In der kaufmännischen Zinsrechnung wird der Februar mit 30 Tagen berechnet, wenn die Tageberechnung über den Februar hinausgeht. Ist ein Betrag nur bis zum 28. Februar zu verzinsen, wird der Februar nur mit 28 Tagen bzw. im Ausnahmefall mit 29 Tagen angesetzt.

Microsoft Excel stellt Ihnen für die Berechnung der kfm. Zinstage die Funktion Tage360 (Excel-VBA Pendant Days360) zur Verfügung. Für die Verwendung unter Microsoft Access müssen wir grundsätzlich auf die englischen Excel-VBA Bezeichnung zurückgreifen. Der Funktion Days360 können Sie folgende Parameter übergeben:

  • Arg1: Das Startdatum
  • Arg2: Das Enddatum
  • Arg3: Die Berechnungs-Methode (optional).

HinweisHinweis zum letzten Argument: Wenn Sie die Methode auslassen oder False (0) übergeben, wird die Amerikanische Methode (NASD) angewendet. Wenn Sie True (-1) übergeben, kommt die europäische Methode zum Tragen.

Hier nun die Funktion

Beispiel:

'Returns the number of days between two dates based on a 360-day year
Public Function CalcDays360(pdtStart As Date, pdtEnde As Date) As Long
 
  '// =====================================================
  '// Methode   | Berechnet die kfm. Zinstage zwischen
  '               zwei Datumswerten
  '// -----------------------------------------------------
  '// Parameter | pdtStart - Startdatum
  '               pdtEnde  - Enddatum
  '// -----------------------------------------------------
  '// Rückgabe  | Long - kfm. Zinstage
  '// -----------------------------------------------------
  '// Erstellt  | Manuela Kulpa
  '//           | EDV Innovation & Consulting - Dormagen
  '// -----------------------------------------------------
  '// Beispielaufruf:
  '// siehe Anwendungsbeispiel
  '// =====================================================
 
  Dim xls As Object
  Dim lAge As Long
  Dim sFormular As String
 
  On Error GoTo CalcDay360_Err
 
  ' Wurden die Werte korrekt übergeben
  If pdtStart <= pdtEnde Then
    ' Erstelle das Excel-Automatisierungsobjekt (Late Binding)
    ' ein Verweis über Extras - Verweise ist nicht nötig
    Set xls = CreateObject("Excel.Application")
    ' Berechne die Zinstage mit Eigenschaft WorksheetFunction
    ' des Excel-Automatisierungsobjekt
    CalcDays360 = xls.WorksheetFunction.Days360(pdtStart, pdtEnde, True)
  End If
 
CalcDay360_Exit:
  On Error GoTo 0
  ' Speicher freigeben
  If Not xls Is Nothing Then Set xls = Nothing
  Exit Function
 
CalcDay360_Err:
  MsgBox "Fehler " & Err.Number & ": " & _
         Err.Description, vbCritical, _
         "modFinancial.AfaPlan"
  Resume CalcDay360_Exit
 
End Function
Das Anwendungsbeispiel

Beispiel:

' Anwendungsbeispiel
Public Sub ShowCalcDays360()
 
  Dim dtStart As Date
  Dim dtEnde As Date
  Dim lZinstage As Long
 
  dtStart = DateSerial(2006, 1, 1)
  dtEnde = DateSerial(2006, 3, 1)
 
  lZinstage = CalcDays360(dtStart, dtEnde)
  MsgBox "Anzahl der Zinstage laut kfm. Zinsrechnung: " & _
         lZinstage, vbInformation, "Hinweis"
 
  lZinstage = DateDiff("d", dtStart, dtEnde)
  MsgBox "Anzahl der Zinstage normal: " & _
         lZinstage, vbInformation, "Hinweis"
 
End Sub
Weiterführende Erklärung

Die Eigenschaft WorksheetFunction des Objekts Application von Microsoft Excel ist der Dreh- und Angelpunkt zum Aufrufen von Excel-Funktionen aus dem Code heraus, egal ob in Access oder direkt in Excels VBA-Umgebung selbst. Mit dieser Eigenschaft können Sie so gut wie jede Excel-Funktion aufrufen, um Sie in Ihrem Umfeld zu nutzen. Ich wünsche Ihnen viel Spaß beim Ausprobieren!



Zurück Tipp 5.1 - Access und XML | Inhalt | Weiter