Microsoft Office Excel 2007 - Das Handbuch

Eine benutzerdefinierte Tabellenfunktion erstellen

1119

Makroprogrammierung mit

Excel

wahre Bedingung findet, werden die auf

Then

folgenden Anweisungen ausgeführt. Wenn keine der

ElseIf

-Bedingungen Wahr ist, werden die nach der Anweisung

Else

folgenden Anweisungen ausge-

führt. Bei der Verwendung von

ElseIf

können Sie auf alle

End If-

Anweisungen mit Ausnahme der

letzten verzichten. Nach dem Ausführen der Anweisungen, die nach

Then

oder

Else

folgen, wird das

Programm mit der Anweisung fortgesetzt, die nach

End If

folgt.

Eine benutzerdefinierte Tabellenfunktion

erstellen

Prinzipiell unterscheidet man zwei unterschiedliche Prozedurtypen nach ihren Aufgaben. Beim ers-

ten Prozedurtyp handelt es sich um die so genannten Sub-Prozeduren. Eine solche Prozedur beginnt

immer mit dem Schlüsselwort Sub, gefolgt von dem Namen der Prozedur und endet mit dem

Schlüsselwort End Sub. Sub-Prozeduren führen Aktionen aus, können jedoch kein Ergebnis zurück-

geben. Die bisher vorgestellten Makros sind Beispiele für Sub-Prozeduren.

Der zweite Prozedurtyp wird unter dem Oberbegriff Funktionsmakros zusammengefasst. Mit diesen

Prozeduren können Sie eine Berechnung oder eine Wahrheitsprüfung durchführen und anschlie-

ßend das Ergebnis zurückgeben. Der Umfang der eingebauten Tabellenfunktionen ist zwar groß,

aber unter Umständen fehlt genau die von Ihnen gesuchte Funktion. In solchen Fällen können Sie

mit VBA eine eigene Funktion schreiben und diese in einem Makro aufrufen oder in einem Tabel-

lenblatt für die Berechnung verwenden.

Ein solches Makro beginnt mit dem reservierten Schlüsselwort Function, gefolgt vom Namen der

Funktion und einem Klammernpaar. In diesem Klammernpaar können Argumente an die Funktion

übergeben werden.

Funktionen führen in der Regel mathematische Operationen aus, prüfen bestimmte Sachverhalte

oder liefern einen Verweis auf ein Objekt. Das Ergebnis dieser Operationen wird dem Funktionsna-

men mit dem Zuweisungsoperator »=« zugewiesen. Das Ende einer Funktion wird mit dem Schlüs-

selwort End Function angezeigt.

Die folgende Funktion berechnet die Kalenderwoche nach der in Deutschland gültigen DIN-Norm

1355:

Listing 31.4

Eine benutzerdefinierte Funktion zur Berechnung der Kalenderwoche

Function KwDIN(dat As Date) As Integer

On Error GoTo Err_KwDIN

Dim a As Integer

a = Int((dat - DateSerial(Year(dat), 1, 1) + ((WeekDay(DateSerial(Year(dat), 1, 1)) _

+ 1) Mod 7) - 3) / 7) + 1

If a = 0 Then

a = KwDIN(DateSerial(Year(dat) - 1, 12, 31))

ElseIf a = 53 And (WeekDay(DateSerial(Year(dat), 12, 31)) - 1) _

Mod 7 <= 3 Then

a = 1

End If

KwDIN = a

Exit Function

Err_KwDIN:

KwDIN = CVErr(xlErrValue)

End Function

Kapitel 31

Eigene Makros programmieren

1120

Eigene Funktionen verwenden

Die Funktion kwDIN kann nun z.B. in einem Tabellenblatt verwendet werden. Steht in der Zelle A1

ein Datum, können Sie in der Zelle B1 die Kalenderwoche über die Formel

=kwDIN(A1)

berechnen.

Voraussetzung hierfür ist, dass sich das Modul und das Tabellenblatt in derselben Arbeitsmappe

befinden. Sie erhalten sonst den Fehlerwert #NAME?.

Aber auch in einer anderen Arbeitsmappe kann die Funktion verwendet werden. Bei geöffneter

Arbeitsmappe erweitern Sie den Namen der Funktion um den Hinweis auf den externen Bezug:

Ist die Arbeitsmappe mit der Funktion nicht geöffnet, muss zusätzlich noch der Pfad angegeben

werden, z.B.

Wenn die Arbeitsmappe mit der benutzerdefinierten Funktion geladen ist, lassen

sich die Tabellenfunktionen auch über den Funktions-Assistenten eintragen. Sie finden diese in

der Kategorie Benutzerdefiniert.

Wie werden Kalenderwochen berechnet?

Die Berechnung der Kalenderwoche in Excel erfolgt nach amerikanischen Regeln und damit lei-

der nicht nach der DIN-Norm. Seit der Festlegung der DIN-Norm 1355 im Jahre 1974 ist die

erste Kalenderwoche die Woche eines Jahres, in die mindestens vier der ersten sieben Tage des

Monats Januar fallen. Wichtig ist also die Woche, in welcher der 4. Januar liegt. Seit 1976 ist fer-

ner der Wochenbeginn auf Montag festgelegt.

Es gibt daher immer wieder Jahre, in denen die Berechnung mit der integrierten Tabellenfunk-

tion Kalenderwoche nicht zum gewünschten Ergebnis führt. Auch die Berechnung der Kalender-

woche mit der DatePart-Funktion, etwa

KW = DatePart("ww", d, vbMonday, vbFirstFourDays)

hat ebenfalls ihre Schattenseiten: In unregelmäßigen Abständen, z.B. für den 30.12.1991 und den

29.12.2003, liefert auch diese Funktion ein falsches Ergebnis.

Überhaupt ist das Thema Genauigkeit auch ein guter Grund, eigene Funktionen zu erstellen.

Nicht, dass die Funktionen in Excel sich durch besondere Ungenauigkeit hervortun würden.

Aber darüber, wie genau eine Funktion rechnet (wann etwa in einer finanzmathematischen

Funktion auf wie viele Stellen gerundet wird), schweigen sich die üblichen Quellen aus. So kann

es bei manuellem Nachrechnen durchaus zu unterschiedlichen Ergebnissen kommen, wenn Sie

z.B. mit gerundeten Werten weiterrechnen. In den allermeisten Fällen werden Sie aber kein Prob-

lem mit der Genauigkeit haben. Mit einer benutzerdefinierten Funktion haben Sie die Genauig-

keit jedoch selbst im Griff. Damit allerdings auch die Verantwortung für den korrekten Rechen-

weg.

=Funktionsmappe.XLSM!kwDIN (A1)

=C:\Daten\Funktionsmappe.XLSM!kwDIN(A1)

TIPP