Start Hilfe und Support Microsoft Office EXCEL 2010 VBA: Die Verwendung von ParamArray

EXCEL 2010 VBA: Die Verwendung von ParamArray

E-Mail PDF

Mit ParamArrays unter VBA kann man Prozeduren und Funktionen mit einer flexiblen Anzahl von Parametern bestücken

Kontext

Dieses Beispiel baut auf dem Artikel Anzahl Zeilen und Spalten eines Range-Objektes bestimmen auf. Wer mag, kann dort zunächst einmal beginnen. Aber man kann den Umgang mit ParamArrays auch verstehen, wenn man den Artikel nicht gelesen hat.

Aufgabenstellung

Das Beispiel aus dem Artikel Anzahl Zeilen und Spalten eines Range-Objektes bestimmen wird aufgegriffen und erweitert. Eine Summenfunktion in EXCEL erlaubt es, dass eine beliebige Anzahl von Parametern (oder auch Zellen aus einer Tabelle oder einem Arbeitsblatt) übergeben werden, aus denen dann die Summe berechnet wird. Das Beispiel in dem oben genannten genannten Artikel beherrscht lediglich die Übergabe eines Parameters. In dem folgenden Beispiel wird die Funktionalität unter Zuhilfenahme von ParamArray so erweitert, dass die Summenfunktion aus EXCEL fast vollständig abgebildet wird.

paramarray_beispiel

Alle markierten Felder sollen an die selbstgeschriebene Summenfunktion "MeineSumme" übergeben und in die Summe mit einbezogen werden. Als Referenz zum Testen kommt die Summenfunktion von EXCEL zum Einsatz. Der Aufruf der Funktionen sieht wie folgt aus:

  • Das Original: =SUMME(A2:B3;B5;C5:C7;D9;B11:D11;C13)
  • Die Fälschung: =MeineSumme(A2:B3;B5;C5:C7;D9;B11:D11;C13)

Entwicklung der Funktion "MeineSumme" unter Zuhilfenahme von ParamArray

Am Beginn einer Funktion steht die Deklaration der Funktion mit Funktionsnamen:

Function MeineSumme(ParamArray vBereich() As Variant) As Double

...

End Function

Hierbei wird dann auch die Deklaration ParamArray verwendet, die der Funktion sagt, dass die Anzahl der zu verwendenden Parameter dynamisch bzw. flexibel ist. Dass es sich um einen Array handelt, zeigt die Syntax () hinger der Variablen, in unserem Fall vBereich(). Übrigens ist der Datentyp der Variablen im Kontext mit ParamArray in VBA immer vom Typ Variant. Ich habe es der Vollständigkeit halber mit definiert, ist aber nicht notwendig.

Möchten Sie einen weiteren festen Parameter übergeben, stellen Sie diesen bitte vor ParamArray, als Übergabeparameter vor ParamArray definiert sind diese Parameter verbindlich, die Anzahl der Parameter in der ParamArray-Variablen ist dynamisch [Beispiel: Function MeineSumme(ByVal vFaktor as Double, ParamArray vBereich() As Variant) As Double ], dabei ist vFaktor nun eine verbindlich zu übergebene Variable vom Typ Double, in vBereich() ist die Anzahl der zu übergebenden Parameter flexibel mit unbestimmtem Typ.

Wenn man Option Explicit verwendet (was ich aus Fehlervermeidungsgründen immer tue), ist zunächst einmal die Variablendeklaration erforderlich:

  Dim lAnzahlEintraege As Long  ' Dieser Variablen wird später die Anzahl der Elemente des Arrays vBereich() zugewiesen (Ganzzahl)
  Dim lZaehlerAnzahlEintraege As Long  ' Laufvariable für die For..Next-Schleife (Ganzzahl)
  Dim dSumme As Double  ' Variable, die die Summe enthält (reelle Zahl)

Es wird ermittelt, wie viele Einträge das Array enthält:

  lAnzahlEintraege = UBound(vBereich())  ' Die Anzahl der übergebenen Parameter wird bestimmt
                                         ' ACHTUNG: da VBA die Indizierung bei 0 beginnt, kommt als Wert
                                         ' Anzahl-der-Einträge - 1 heraus, also bei 6 Elementen 5
                                         ' Eine Schleife muss somit bei dem Zäler 0 beginnen

Für jeden Eintrag (es handelt sich jeweils um eine Range), wird über ein Schleifenkonstrukt die (selbstgeschriebene) Funktion SummeRange aufgerufen. Wie diese Funktion arbeitet, lesen Sie unter Anzahl Zeilen und Spalten eines Range-Objektes bestimmen. Die Zwischensummen werden in der Variablen dSumme aufsummiert.

  For lZaehlerAnzahlEintraege = 0 To lAnzahlEintraege  ' Hier werden die Werte in den einzelnen Ranges aufsummiert
    dSumme = dSumme + SummeRange(vBereich(lZaehlerAnzahlEintraege))
  Next lZaehlerAnzahlEintraege

Dann bleibt noch die Aufgabe, das berechnete Summenergebnis als Rückgabewert der Funktion zu übergeben.

  MeineSumme = dSumme

Der vollständige Programmcode

Function SummeRange(ByVal vBereich As Range) As Double

  Dim lZeilen As Long
  Dim lSpalten As Long
  Dim lZaehlerZeilen As Long
  Dim lZaehlerSpalten As Long
  Dim dSumme As Double

  lZeilen = vBereich.Rows.Count  ' Die Anzahl Zeilen werden bestimmt
  lSpalten = vBereich.Columns.Count  ' Die Anzahl Spalten werden bestimmt

  dSumme = 0  ' Wert Initialisieren, ist eigentlich in VBA bei erstmaliger Verwendung nicht notwendig,
              ' aber eine alte Gewohnheit

  For lZaehlerZeilen = 1 To lZeilen  ' Hier werden die Werte in den einzelnen Zellen (Felder in den Ranges) aufsummiert
    For lZaehlerSpalten = 1 To lSpalten
      dSumme = dSumme + vBereich.Cells(lZaehlerZeilen, lZaehlerSpalten).Value
    Next lZaehlerSpalten
  Next lZaehlerZeilen

  SummeRange = dSumme

End Function


Function MeineSumme(ParamArray vBereich() As Variant) As Double

  Dim lAnzahlEintraege As Long
  Dim lZaehlerAnzahlEintraege As Long
  Dim dSumme As Double

  lAnzahlEintraege = UBound(vBereich())  ' Die Anzahl der übergebenen Parameter wird bestimmt
                                         ' ACHTUNG: da VBA die Indizierung bei 0 beginnt, kommt als Wert
                                         ' Anzahl-der-Einträge - 1 heraus, also bei 6 Elementen 5

  dSumme = 0  ' Wert Initialisieren, ist eigentlich in VBA bei erstmaliger Verwendung nicht notwendig,
              ' aber eine alte Gewohnheit

  For lZaehlerAnzahlEintraege = 0 To lAnzahlEintraege  ' Hier werden die Werte in den einzelnen Ranges aufsummiert
    dSumme = dSumme + SummeRange(vBereich(lZaehlerAnzahlEintraege))
  Next lZaehlerAnzahlEintraege

  MeineSumme = dSumme

End Function

Schlussbetrachtung

Auch dieses Beispiel können Sie sich herunterladen. Und nun noch der Wermutstropfen: Noch immer kann diese Funktion nicht alles das, was die SUMME von EXCEL kann. Übergibt man in MeineSumme z.B. =MeineSumme(A2:B3;B5;C5:C7;D9;B11:D11;C13;5), steigt diese Funktion mit einer Fehlermeldung aus, weil die Zahl 5 keine Range ist. Das Beispiel gilt es also noch zu erweitern. Aber dass erfolgt vielleicht an einem anderen Abend.


Zuletzt aktualisiert am Montag, den 04. November 2013 um 18:44 Uhr  


Machen Sie mit

Sie haben ein schönes Bild gemacht von Happerschoß, das auch andere interessieren könnte. Dann sollte es auf dieser Internetseite von Happerschoß erscheinen. Bitte melden Sie sich direkt über die Kontaktseite oder natürlich auch unter Info@happerschoss.net.