Dem folgenden Makro liegt eine Anfrage zugrunde, die nach einem Kurs per Mail einging. Es geht im folgenden um eine energetische Summation zur Berechnung des resultierenden Gesamtpegels aus gemessenen Schalldruckpegeln. Die Anzahl der Meßstellen und die Lage in der Datei sind nicht vorher gegeben. Man kann zwar davon ausgehen, dass der Nutzer die Werte in einer übersichtlichen Form speichert, aber man kann ihm die Form nicht vorschreiben.
In dieser Situation kann man den Nutzer aber einen Bereich (Range) auswählen lassen, in dem beliebig viele Meßstellen eingetragen wurden. Das ist z.B. mit Hilfe des Funktionsassistenten möglich. Im nächsten Bild sehen Sie zunächst die Formel und ein Kontrollbeispiel:
Beispiel 4-2. Summation des Schalldruckpegels
Function DGesamt(Bereich As Range) As Double ' Die Funktion erhält einen Bereich übergeben, der ' beliebig groß sein kann. Der Nutzer muss ihn nur ' im Funktionsassistenten richtig eintragen. ' Die Funktion gibt eine reelle Zahl zurück. ' ' Es folgt die Variablendeklaration: Dim Summe As Double Dim i As Integer Dim Zahl As Variant ' Initialisierung Summe = 0 ' Hier wird eine sehr interessante und nützliche ' Konstruktion eingesetzt: Die "For Each"-Schleife ' führt einen Block von Befehlen über einen gesamten ' Bereich aus, ohne dass der Programmierer diesen ' Bereich mit Zeilen / Spalten angeben muss. ' ' Summe = 0 For Each Zahl In Bereich If (IsNumeric(Zahl) = True) Then If (Zahl > 0) Then Summe = Summe + (10 ^ (Zahl / 10)) Else Summe = Summe + 0 End If End If Next Zahl ' Die Schleife wird im Anschluss im laufenden Text erklärt. ' Wenn die Summe einen Wert > Null annimmt, dann wird die ' Berechnung 10*lg(Summe) durchgeführt. ' Bitte wundern Sie sich nicht, dass in der entsprechenden ' Programmzeile log als Funktionsname steht - so heisst in VBA ' und auch in anderen Sprachen der natürliche Logarithmus ln(x). ' Nach den Logarithmengesetzen gilt ja, dass lg(x)=ln(x)/ln(10) ' ist. So hilft man sich, weil es die lg-Funktion eben in VBA ' nicht gibt. In VBA übersetzt schreibt ' man dann y = Log(x)/Log(10) und meint damit y=lg(x) If (Summe > 0) Then DGesamt = 10 * (Log(Summe) / Log(10)) Else DGesamt = 0 End If End Function
Alle Zellen in diesem Bereich (Range) werden nun in einer Schleife abgearbeitet. In der Schleife wird jeder Eingangswert einem Test unterzogen. Die Funktion IsNumeric gibt True zurück, wenn es sich beim aktuellen Wert um eine Zahl handelt. Wenn das nicht der Fall ist, wird der nächste Wert geholt.
Wenn es aber bei diesem Wert um eine Zahl handelt, dann kann diese immer noch negativ oder gleich Null sein. Auch das wird nun geprüft. Eine negative Zahl oder eine Null darf nicht in die Summe eingehen. Wenn der aktuelle Wert also gleich oder kleiner als Null ist, wird zu der Summe lediglich eine Null hinzugezählt. Ansonsten wird die vorgeschriebene Formel umgesetzt.
Jetzt muss das Makro gründlich getestet werden. Dazu verwendet man zuerst die gegebenen Eingangs- und Ausgangswerte aus der Aufgabenstellung. Diese Werte bekommen Sie als Tabelle zur Verfügung gestellt.
Darüber hinaus muss das Makro robust gegenüber jeglicher Art von Fehleingaben sein. Daher testen Sie es bitte auch mit erkennbar unsinnigen Angaben, wie z.B. Zeichenketten und negativen Zahlen. Es muss mit der Kombination aus gültigen und ungültigen Eingabewerten funktionieren. Es muss mit einer einzigen Zahl richtig funktionieren und auch mit sehr vielen Zahlen.
Ein Kontrollbeispiel
Programmieren Sie eine Funktion, die einen beliebigen Abschnitt der Tabelle übergeben bekommt und aus allen Zahlen das arithmetische Mittel bestimmt. Machen Sie die Funktion sicher gegenüber Fehleingaben und testen Sie die Funktion mit verschiedenen Eingabewerten.
Beispiel 4-3. Arithmetisches Mittel
Function mittel(Bereich As Range) As Double ' ' Deklarationen, Quelltext ... ' End Function
Warum ist es sinnvoll, eine solche Funktion selbst zu programmieren ? Sicher lernt man auf diese Weise die Sprache VBA besser kennen. Aber der Hauptgrund ist eigentlich, dass diese Funktion robuster sein wird, als die Originalfunktion aus Excel. Und eine selbst geschriebene Funktion ist meist besser an die Anforderungen des Nutzers angepasst.