Home

Limitationen von Arrays, Matrizen und Matrixformeln

Inhaltsübersicht

Allgemeines über Array-Limitationen

Die drei Basis-Limitationen von Arrays
   Allgemeines
   Verfügbarer Speicher
   Die "Gesamte Spalte"-Regel
   Maximale Anzahl Matrixformeln

Die weiteren Limitationen von Arrays
   Übersicht der wichtigsten Limitationen
   Maximale Anzahl Array-Dimensionen
   Limitationen der Transpose-Methode
   Limitationen der MTRANS-Tabellenfunktion
   Limitationen der CopyFromRecordset-Methode
   Limitationen der LINEST-Tabellenfunktion
   Limitationen der MINV-Tabellenfunktion
   Limitationen der FormulaArray-Eigenschaft

Der kuriose Bug mit der Anzahl Zeichen in einem Array

Die Design-Einschränkung von Range.Calculate bei Matrizen

Problem bei Übergabe und Redimensionierung eines Arrays (Laufzeitfehler 10) 

Beispiele von Matrixformeln

Tabellenfunktionen, die Arrays verwenden

Eingabe einer Matrixformel in eine Zelle
   Formeleingabe von Hand
   Formeleingabe mittels VBA-Code
   Neu-Eingabe mittels VBA-Code simulieren
   Automatische Konvertierung von Zellbezügen

Berechnen des Speicherbedarfs eines Arrays

Programmieren mit Arrays und Matrixformeln 

Daten an Microsoft Excel übergeben

Weitere Informationsquellen 
   Allgemeine Informationen
   Bugs und Probleme

Allgemeines über Array-Limitationen

Auf dieser Seite werden sämtliche Limitationen und Spezifikationen von Arrays erklärt, d.h. von Matrixformeln, Tabellenmatrizen und Datenfeldern. Der englische Ausdruck 'Array' kann sowohl mit 'Matrix' als auch mit 'Datenfeld' übersetzt werden. Excel-Benutzer verwenden gewöhnlich die Übersetzung 'Matrix', da eine Array-Formel (Array Formula) eben eine Matrixformel ist. VBA-Programmierer dagegen tendieren eher zu 'Datenfeld', da in der Programmiersprache eine Array-Variable eine Datenfeldvariable ist.

Weitere Informationen über Microsoft Excel-Limitationen finden Sie hier:

Weitere Informationen Alle Limitationen in Microsoft Excel finden Sie hier: Limitationen in Microsoft Excel Alle Limitationen im VBA-Editor von Microsoft Excel finden Sie hier: Limitationen im VBA-Editor (VBE) Die Limitationen in Excel-VBA und -Objektmodell befinden sich hier: Limitationen in Excel-VBA und -Objektmodell Die speziellen Limitationen von Pivot-Tabellen werden hier vorgestellt: Limitationen in Pivot-Tabellen Die Limitationen von Excel-Diagrammen sind hier beschrieben: Limitationen von Diagrammen Die Limitationen von AutoFormen werden hier vorgestellt: Limitationen in AutoFormen

Fehler in der Dokumentation von Microsoft Excel 97

In der Online-Hilfe von Microsoft Excel 97 ist die maximale Grösse von Arrays auf einem Arbeitsblatt mit 6'553 Elementen angegeben. Diese Information ist falsch. Die Grösse ist durch drei Basis-Limitationen begrenzt. In den Referenzen von Microsoft Excel 2000, 2002 und 2003 ist die Spezifikation korrekt angegeben.

Hier der original Wortlaut aus der Microsoft Excel 97-Online Hilfe, der wie gesagt eine falsche Spezifikation wiedergibt:

Dieser Text stammt aus der Referenz von Microsoft Excel 2002:

Die drei Basis-Limitationen von Arrays

Allgemeines

In Microsoft Excel sind Arrays/Matrizen in einem Arbeitsblatt durch drei Basis-Limitationen begrenzt:
1. Den verfügbaren Arbeitsspeicher
3. Die "Gesamte Spalte"-Regel
2. Die maximale Anzahl Matrixformeln

Nachfolgend werden diese drei Limitationen vorgestellt.

1. Verfügbarer Arbeitsspeicher

Im allgemeinen existiert keine maximale Grösse bezüglich eines Arrays auf einem Arbeitsblatt. Die Grenze liegt lediglich beim verfügbaren Arbeitsspeicher. Aus diesem Grund können Sie sehr grosse Arrays erstellen, die mehrere hunderttausend Zellen umfassen.

2. "Gesamte Spalte"-Regel

Obwohl ein Array sehr gross sein darf, ist es nicht möglich, ein Array über eine gesamte Spalte oder über mehrere Spalten zu erstellen. Weil die Berechnung einer Matrixformel, die sich auf sämtliche Zellen einer Spalte bezieht, verhältnismässig lange dauern würde, erlaubt Microsoft Excel die Verwendung dieser Art Arrays nicht.

Wenn Sie beispielsweise die Matrixformel "=SUMME(WENN(C:C=0;1;0))" in die Zelle A1 eingeben, so liefert die Formel als Ergebnis den Fehlerwert "#ZAHL!". Wenn Sie die Matrixformel "=SUMME(WENN(C1:C65536=0;1;0))" eingeben, wird die Formel automatisch in "=SUMME(WENN(C:C=0;1;0))" umgewandelt, weil C1:C65536 sämtliche Zellen der Spalte C umfasst. Die Formel gibt wiederum den Fehlerwert "#ZAHL!" zurück.

Hinweis zu Microsoft Excel 12
Bei Microsoft Excel Version 12 ist die hier beschriebene "Gesamte Spalte"-Regel nicht mehr gültig. Es ist problemlos möglich, sämtliche Zeilen einer oder mehrerer Spalten zu referenzieren.

3. Maximale Anzahl Matrixformeln

Ein Arbeitsblatt kann in Microsoft Excel 2000 und neuer maximal 65'472 Maxtrixformeln enthalten, die sich auf ein anderes spezifisches Arbeitsblatt beziehen. Bei Microsoft Excel 97 liegt die maximale Anzahl bei 32'704 Matrixformeln.

So kann beispielsweise das Blatt Tabelle1
- 65'472 Matrixformeln mit Bezug auf Tabelle2,
- 65'472 Matrixformeln mit Bezug auf Tabelle3 und
- 65'472 Matrixformeln mit Bezug auf Tabelle4
enthalten. Die Tabelle1 besitzt somit total 196'416 Matrixformeln. Wenn Sie also ein Tabellenblatt mit mehr als 65'472 Maxtrixformeln auf ein anderes spezifisches Tabellenblatt besitzen, so müssen Sie die Quellzellen auf mehrere Tabellenblätter aufteilen. Wenn Sie mehr als 65'472 Matrixformeln eintragen, ist es möglich, dass die die maximale Anzahl übersteigenden Matrixformeln automatisch verschwinden.

Hinweis zu Microsoft Excel 12
Bei Microsoft Excel Version 12 gibt es keine maximale Anzahl Matrixformeln mehr. Die maximale Anzahl ist lediglich durch den verfügbaren Arbeitsspeicher begrenzt.

Tipp: Maximale Anzahl Matrixformeln merken
Die Zahl 65'472 kann man sich nicht gut merken. Es gibt einen Trick, wie man sich diese Zahl einprägen kann: Als Hilfe können Sie die Anzahl Zeilen eines Tabellenblattes nehmen (also 65'536) und davon die Zahl 64 abziehen. Das Resultat ist 65'472, was der maximalen Anzahl Matrixformeln entspricht.

Die weiteren Limitationen von Arrays

Übersicht der wichtigsten Limitationen

Hier eine Liste der wichtigsten Limitationen von Arrays und Matrixformeln in Microsoft Excel.

Maximale Anzahl Array-Dimensionen

Datenfelder können maximal 60 Dimensionen besitzen.

Beispiele

Eine Dimension:
Dim MyArray(10)

Zwei Dimensionen:
Dim MyArray(10, 10)

Sechs Dimensionen:
Dim MyArray(10, 10, 10, 10, 10, 10)

Limitationen der Transpose-Methode

If you decide to use Excel's Transpose method instead of the TransposeDim function to transpose the array, you should be aware of the following limitations with the Transpose method:

• The array cannot contain an element that is greater than 255 characters.
• The array cannot contain Null values.
• The number of elements in the array cannot exceed 5461.

Limitation "Maximale Anzahl Array-Elemente bei Transpose"

Wieso liegt die maximale Anzahl Elemente bei genau 5'461? Meistens besitzen doch Limitationen eine in der Computerwelt häufig vorkommende Zahl wie zum Beispiel 32, 255, 1'024, 32'767 oder 65'536! Die Frage, weshalb die maximale Anzahl bei exakt 5'461 Elementen liegt, hat mich in der Tat sehr lange beschäftigt.

Alle 16-bit Excelversionen (z.B. Excel 4.0):
- Array-Definition wird in einem 64 KB Speichersegment abgelegt (64 KB = 65'536 Bytes)
- Wert ist in 20 Bits gespeichert
- 3'276 Elemente (65'536 Byte / 20 Bits = 3'276.80 = 3'276)

Ältere 32-bit Excelversionen (z.B. Excel 7.0):
- Array-Definition wird in einem 64 KB Speichersegment abgelegt (64 KB = 65'536 Bytes)
- Wert ist in 24 Bits gespeichert
- 2'730 Elemente (65'536 Byte / 24 Bits = 2'730.66 = 2'730)

Neuere 32-bit Excelversionen (ab Excel 95):
- Array-Definition wird in zwei 64 KB Speichersegmenten abgelegt (64 KB = 65'536 Bytes)
- Wert ist in 24 Bits gespeichert
- 5'461 Elemente (2 * 65'536 Byte / 24 Bits = 5'461.33 = 5'461)

Die Transpose-Methode wird immer dann benötigt, wenn ein sogenanntes "Column-Array" zurückgegeben werden soll, d.h. ein Datenfeld, das genau 1 Spalte und >1 Zeilen enthält. Es handelt sich somit um ein eindimensionales Array, dessen Elemente auf die Zellen einer einzelnen Spalte transponiert werden müssen.

Die Transponierung ist notwendig, da die Zellen einer Tabelle grundsätzlich von links nach rechts und dann von oben nach unten indexiert sind; und nicht von oben nach unten und dann von links nach rechts. Die Indizes sehen somit folgendermassen aus:

Zeile 1: Zelle A1=Index 1, Zelle B1=Index 2, Zelle C1=Index 3, ... , Zelle IV1=Index 256
Zeile 2: Zelle A2=Index 257, Zelle B2=Index 258, ... , Zelle IV2=Index 512
Zeile 3: Zelle A3=Index 513, ... , Zelle IV3=Index 768
Zeile 4: Zelle A4=Index 769, ... , Zelle IV4=Index 1024
usw.

Ein Column-Array für die Spalte A mit 4 Elementen wird mittels Transpose auf die vier untereinander stehenden Zellen 1, 257, 513 und 769 verteilt.

Eigene benutzerdefinierte Transpose-Funktion (TransposeDim)

Diese kleine Funktion ersetzt die Transpose-Methode:

Public Function TransposeDim(v As Variant) As Variant
  Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
  Dim tempArray As Variant
  Xupper = UBound(v, 2)
  Yupper = UBound(v, 1)
  ReDim tempArray(Xupper, Yupper)
  For X = 0 To Xupper
    For Y = 0 To Yupper
      tempArray(X, Y) = v(Y, X)
    Next Y
  Next X
  TransposeDim = tempArray
End Function

Limitationen der MTRANS-Tabellenfunktion

Maximale Anzahl Elemente in der MTRANS-Tabellenfunktion (engl. TRANSPOSE); Durch den verfügbaren Speicher begrenzt (alle Excelversionen)

Limitationen der CopyFromRecordset-Methode

In Microsoft Excel 97 kann die CopyFromRecordset-Methode des Range-Objektes zusammen mit einer DAO-Datenquelle verwendet werden. In diesem Fall existiert die Limitation, dass maximal ca. 4'096 Felder eingelesen werden können. In Microsoft Excel 2000 und neuer können neben DAO-Datenquellen auch ADO-Datenquellen verwendet werden. Bei ADO-Datenquellen existiert die Limitation nicht.

Anzahl Felder, die mit der "CopyFromRecordset"-Methode eingelesen werden können

Excel 97:    ca. 4'096 Felder (mit DAO)
Excel 2000: Unlimitiert (mit ADO), ca. 4'096 Felder (mit DAO)
Excel 2002: Unlimitiert (mit ADO), ca. 4'096 Felder (mit DAO)
Excel 2003: Unlimitiert (mit ADO), ca. 4'096 Felder (mit DAO)

Limitationen der LINEST-Tabellenfunktion

Limitationen der MINV-Tabellenfunktion

• Wenn eine Zelle der Matrix leer ist oder Text enthält, so gibt MINV den Fehlerwert #WERT! zurück.
• MINV liefert ebenfalls #WERT!, wenn die Anzahl Zeilen und Spalten der Matrix nicht identisch ist.
• #WERT! wird auch zurückgegeben, wenn die Matrix grösser als 52 Spalten x 52 Zeilen ist.

Wird eine quadratische Matrix bestehend aus mehr als 52 Spalten/52 Zeilen verwendet, gibt die Funktion den Fehlerwert "#WERT!" zurück. Die maximale Matrixgrösse liegt bei 52 Spalten und 52 Zeilen, weil 52 * 52 = 2'704 Zellen gibt (sprich Array-Elemente). Da zwei Matrizen in der Funktion verwendet werden, muss mit 2 multipliziert werden, d.h. 2'704 * 2 = 5'408 Elemente. Ein Array kann in Excel maximal 5'461 Elemente enthalten. Ein Matrix mit 53 Spalten/53 Zeilen wäre bereits zu gross, da 53 * 53 * 2 = 5'618 Elemente gibt, also mehr als die maximal erlaubten 5'461 Elemente.

Anmerkung des Autors
Es darf angenommen werden, dass die Tabellenfunktion MDET (engl. MDETERM) aufgrund ihrer Ähnlichkeit zur MINV-Funktion die gleiche Spezifikation besitzt. MDET nimmt ebenfalls eine quadratische Matrix entgegen.

Limitationen der FormulaArray-Eigenschaft

Limitation in VBA bei Zuweisen einer Matrixformel

Wegen eines Bugs im Objektmodell von Excel 97, Excel 98 (Mac) und Excel 2000 muss bei der Benutzung von Matrixformeln mit VBA eine zusätzliche Limitation berücksichtigt werden. Bitte beachten Sie, dass dieser Bug bis heute nicht korrigiert wurde.

Der Bug betrifft die FormulaArray-Eigenschaft des Range-Objektes (bzw. des Cell-Objektes). Matrixformeln können in Excel ebenfalls bis zu 1'024 Zeichen lang sein, nicht aber, wenn sie die Matrixformel einer Zelle anhand der FormulaArray-Eigenschaft zuweisen, weil in diesem Fall wegen des Bugs eine Matrixformel nur maximal 255 Zeichen lang sein darf.

Beispiel

Hier ein kleines Beispiel dazu:

Die aktive Zelle enthält die Matrixformel

  =SUMME(WENN(B1:D20<>0;1))

Die Zellformel kann in VBA beispielsweise mit "MsgBox ActiveCell.FormulaArray" (anstelle MsgBox kann auch eine andere Ausgabe-Anweisung verwendet werden) abgefragt werden und liefert als Ergebnis die englische Formel:

  =SUM(IF(B1:D20<>0,1))

Erstellt man eine mehr als 255 Zeichen enthaltende Matrixformel und weist diese der aktiven Zelle beispielsweise mit dem VBA-Code

  ActiveCell.FormulaArray = "=<Formel mit mehr als 255 Zeichen>"

zu, so erhält man den Laufzeitfehler 1004 mit dem Meldungstext "Unable to set the FormulaArray property of the Range class".

Weitere Informationen zu diesem Problem erhalten Sie hier:
  XL97: Run-Time Error Using FormulaArray Property
  http://support.microsoft.com/default.aspx?scid=kb;en-us;Q192989
  XL98: Run-Time Error Using FormulaArray Property
  http://support.microsoft.com/default.aspx?scid=kb;en-us;Q192990
  XL2000: Run-Time Error Using FormulaArray Property
  http://support.microsoft.com/default.aspx?scid=kb;en-us;Q213181

Weitere Informationen Limitationen-Fachartikel: Maximale Länge von Formeln (Artikel xllimit4)

Der kuriose Bug mit der Anzahl Zeichen in einem Array

When you run a Microsoft Visual Basic for Applications (VBA) macro to transfer data from a VBA array that contains strings of data to a range of cells in Microsoft Excel 2000 or in Microsoft Excel 2002, the data may be truncated (cut off).

Note
In Microsoft Office Excel 2003, you may receive the following error message when you run the VBA macro in the Visual Basic Editor:

Run-time error '1004':
Application-defined or object-defined error

If you run the VBA macro from your Excel worksheet (on the Tools menu, point to Macro and then click Macros), you may receive the following error message:

Microsoft Visual Basic: 400

This problem may occur when the VBA array is longer than 1,823 characters in length.

To work around this problem, populate each cell in your worksheet one at a time from the array, instead of populating the whole range at one time. To do this, use a VBA macro that is similar to the following example:

Sub PopulateRangeWithArray()
  Dim x
  ReDim x(1 To 2, 1 To 2)
  x(1, 1) = String(2000, "a"): x(1, 2) = String(5000, "b")
  x(2, 1) = String(17000, "c"): x(2, 2) = String(33000, "d")
  MsgBox Len(x(1, 1)) & "," & Len(x(1, 2)) & "," & Len(x(2, 1)) & "," & Len(x(2, 2))
  Range("a1").Value = x(1, 1)
  Range("b1").Value = x(1, 2)
  Range("a2").Value = x(2, 1)
  Range("b2").Value = x(2, 2)
End Sub

Weitere Informationen über diesen Bug finden Sie hier:
  XL2000/2002/2003: Data May Be Truncated After 1,835 Characters When You Transfer Array Data to Cells in an Excel Worksheet
  http://support.microsoft.com/?scid=kb;en-us;832136
  Ein interessanter, noch nicht behobener Bug in Microsoft Excel (Stand 22.07.2004).

Die Design-Einschränkung von Range.Calculate bei Matrizen

When you use the Range.Calculate method to calculate formulas in a Microsoft Excel workbook, you may receive the following error message:
Run-time error '1004':

Calculate method of Range class failed

This behavior may occur if the range that you are trying to calculate contains only part of an array range.

To work around this behavior, calculate the entire array range. To do this, use the following Microsoft Visual Basic for Applications (VBA) macro code:

Sub CalcTheRange()
  SafeCalcRange ThisWorkbook.Sheets("SheetName").Range("A2:F4")
End Sub
 
Sub SafeCalcRange(rng As Range)
  For Each cl In rng.Cells
    If cl.HasArray Then Set rng = Union(rng, cl.CurrentArray)
  Next cl
  rng.Calculate
End Sub

This behavior is by design. This behavior is consistent with user interface behavior because you cannot change part of an array range.

Weitere Informationen über diese Spezialität finden Sie hier:
  XL2002: You Receive an Error Message When You Use the Range.Calculate Method to Calculate Formulas
  http://support.microsoft.com/?scid=kb;en-us;825011

Problem bei Übergabe und Redimensionierung eines Arrays (Laufzeitfehler 10)

Wenn ein ...

This behavior will occur if the following conditions are all true:
- You create two macros, where one macro passes a variable to the other macro.
    and
- The variable that is passed to the other macro is an element of an array.
    and
- The second macro accepts the variable by reference, not by value.
    and
- The second macro uses the ReDim method to redimension the array.

This behavior is by design of Microsoft Excel.

Beispiel

Kopieren Sie folgenden VBA-Code in ein Codemodul und führen Sie Macro1 aus.

Dim MyArray() As Integer

Sub Macro1()
  ReDim MyArray(10)
  Macro2 MyArray(1)
End Sub

Sub Macro2(x)
  ReDim MyArray(20)
End Sub

Sobald die Programmzeile "ReDim MyArray(20)" ausgeführt werden soll, erscheint der Laufzeitfehler 10 "Datenfeld ist unveränderlich oder momentan gesperrt":

Abbildung: Laufzeitfehler 10 "Datenfeld ist unveränderlich oder momentan gesperrt"

Lösungsvariante 1

Dim MyArray() As Integer

Sub Macro1()
  ReDim MyArray(10)
  Macro2 MyArray(1)
End Sub

Sub Macro2(ByVal x)
  ReDim MyArray(20)
End Sub

Lösungsvariante 2

Dim MyArray() As Integer

Sub Macro1()
  ReDim MyArray(10)
  Dim z
  z = MyArray(1)
  Macro2 z
End Sub

Sub Macro2(x)
  ReDim MyArray(20)
End Sub

Lösungsvariante 3

Dim MyArray() As Integer

Sub Macro1()
  ReDim MyArray(10)
  Macro2 (MyArray(1))
End Sub

Sub Macro2(x)
  ReDim MyArray(20)
End Sub

When you pass a variable from one macro to another macro in Microsoft Excel, the actual value of the variable is not passed between macros. Instead, a reference to the value's location in memory is passed; this is called passing a value by reference. You can also pass a value by value by using the keyword ByVal.

If you pass an element of an array from one macro to another by reference, and then attempt to redimension the array by using the ReDim method, you receive the error message in this article. This behavior occurs because the array's size is locked when a macro refers to it. Only when the reference is released can the array be redimensioned.

Weitere Informationen über dieses Verhalten finden Sie hier:
  XL97: Error When Passing an Array Value Between Macros
  http://support.microsoft.com/?scid=kb;en-us;158746

Beispiele von Matrixformeln

Tabellenfunktionen, die Arrays verwenden

Eingabe einer Matrixformel in eine Zelle

Formeleingabe von Hand

Wenn Sie eine Matrixformel eingeben, schliesst Microsoft Excel die Formel automatisch in geschweifte Klammern ({}) ein. Die Matrixformel darf maximal 1'024 Zeichen lang sein.

Vorgehen
So geben Sie eine Matrixformel in eine Zelle oder einen Zellbereich ein:

1. Soll die Matrixformel nur ein einzelnes Ergebnis liefern, klicken Sie auf die Zelle, in die Sie die Matrixformel eingeben möchten. Soll die Matrixformel mehrere Ergebnisse liefern, markieren Sie den Zellbereich, in den Sie die Matrixformel eingeben möchten.
2. Geben Sie die Matrixformel ein.
3. Drücken Sie STRG+UMSCHALT+EINGABE.

Formeleingabe mittels VBA-Code

In VBA-Programmen wird eine Matrixformel anhand der FormulaArray-Eigenschaft des Range-Objektes in eine Zelle eingetragen.

Beispiel
Diese Codezeile trägt die Matrixformel "=SUMME(WENN(B1:B65535=0;1;0))" in die Zelle A1 des aktiven Arbeitsblattes ein:

Range("A1").FormulaArray = "=SUM(IF(RC[1]:R[65534]C[1]=0,1,0))"

Bitte beachten Sie, dass eine der FormulaArray-Eigenschaft zuzuweisende Matrixformel maximal 255 Zeichen lang sein darf. Vor der Formeleingabe sollte daher die Länge der Matrixformel überprüft werden (siehe Kapitel Programmieren mit Arrays und Matrixformeln).

Neu-Eingabe mittels VBA-Code simulieren

Um per VBA die Editierung und Eingabe einer Matrixformel zu simulieren, kann folgender VBA-Code eingesetzt werden:

Sub ReenterArrayFormula()
  Range("A1").Select
  SendKeys "{F2}"
  SendKeys "^+{Enter}"
End Sub

Automatische Konvertierung von Zellbezügen

Bei Eingabe eines Zellbezuges, der eine ganze Spalte darstellt, wird der Bezug automatisch konvertiert.

Beispiel
Der Zellbezug "C1:C65536" wird von Microsoft Excel automatisch in "C:C" konvertiert.

Wenn Sie beispielsweise die Matrixformel "=SUMME(WENN(C1:C65536=0;1;0))" eingeben, wird aus der Formel automatisch "=SUMME(WENN(C:C=0;1;0))". Da ganze Spalten nicht erlaubt sind, liefert die Formel als Ergebnis den Fehlerwert "#ZAHL!".

Weitere Informationen Speicher: Excel-Fehlermeldungen infolge ungenügendem Speicher

To Do...

' Check the array for contents that are not valid when
' copying the array to an Excel worksheet
For iCol = 0 To fldCount - 1
  For iRow = 0 To recCount - 1
    ' Take care of Date fields
    If IsDate(recArray(iCol, iRow)) Then
      recArray(iCol, iRow) = Format(recArray(iCol, iRow))
      ' Take care of OLE object fields or array fields
    ElseIf IsArray(recArray(iCol, iRow)) Then
      recArray(iCol, iRow) = "Array Field"
    End If
  Next iRow 'next record
Next iCol 'next field

' Transpose and Copy the array to the worksheet,
' starting in cell A2
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = TransposeDim(recArray) 

Berechnen des Speicherbedarfs eines Arrays

Tabellenblatt-Zellbereiche

Ein Zellbereich eines Tabellenblattes mit mehreren Spalten und Zeilen wird als zweidimensionales Datenfeld abgebildet. Die Untergrenze für die Indizes der beiden Array-Dimensionen ist 1 (für Excel-Zellbereiche).

Da der Wert einer Zelle (Value-Eigenschaft) nicht zwingend eine Zahl enthalten muss, sondern auch Buchstaben bzw. längere Texte oder Fehlerwerte vorkommen können, kann ein Bereich-Array weder als Zahl noch Text deklariert werden. Stattdessen erfolgt die Deklaration mit dem Datentyp Variant (der seinerseits intern den benötigten Datentyp verwendet).

Dieses Beispiel deklariert ein statisches Datenfeld fester Grösse für einen Zellbereich von 5'000 Zeilen x 26 Spalten (Spalten A-Z):

 Dim avarBereich(1 To 5000, 1 To 26) As Variant

Der beanspruchte Speicherbedarf wird so berechnet:

Formel
  Speicherbedarf = Anzahl Zeilen * Anzahl Spalten * Bytes des Datentyps

Berechnung
  Speicherbedarf = 5'000 * 26 * 16
  Speicherbedarf = 2'080'000 Bytes

Anmerkung
Die Default-Einstellung für VBA-Arrays ist 0. Bei Bedarf sollte daher die Anweisung "Option Base 1" verwendet werden. Die Basis eines mit der Array-Funktion oder dem Schlüsselwort "ParamArray" erstellten Datenfeldes ist Null. Die Option Base-Anweisung hat keinen Einfluss auf "Array" oder "ParamArray".

VBA-Datenfelder

Datenfelder eines beliebigen Datentyps benötigen 20 Bytes im Speicher, 4 Bytes für jede Datenfelddimension und die Anzahl an Bytes, die für die eigentlichen Daten benötigt werden. Der für die Daten benötigte Speicher kann durch Multiplikation der Anzahl an Datenelementen mit der Grösse eines einzelnen Elements ermittelt werden. Die Daten in einem eindimensionalen Datenfeld, das 4 Elemente vom Typ Integer mit jeweils 2 Bytes enthält, belegen somit 8 Bytes. Insgesamt benötigt das Datenfeld die 8 Bytes für die Daten zuzüglich 24 Bytes für deren Verwaltung, total also 32 Bytes.

Ein Wert vom Typ Variant, der ein Datenfeld enthält, benötigt 12 Bytes zusätzlich zu dem Speicher, der für das Datenfeld selbst benötigt wird.

Programmieren mit Arrays und Matrixformeln

In diesem Kapitel möchte ich ein paar Tipps und Beispiele zum Thema "Programmieren mit Arrays" vorstellen.

Informationen bezüglich Benennen von Datenfeldvariablen erhalten Sie auf dieser Seite:

Weitere Informationen Phil's Office Secrets: Namenskonventionen, Richtlinien und Konventionen

Eingabe einer Matrixformel simulieren

Um per VBA die Editierung und Eingabe einer Matrixformel zu simulieren, kann folgender VBA-Code eingesetzt werden:

Sub ReenterArrayFormula()
  SendKeys "{F2}"
  SendKeys "^+{Enter}"
End Sub

Überprüfen, ob Variable ein Array enthält

Anhand der TypeName-Funktion und dem Like-Operator können Sie feststellen, ob eine Variable ein Array enthält:

If Not TypeName(varInputArray) Like "*()" Then
  MsgBox "Keine Array-Variable.", vbInformation
End If

Array erstellen

Array mit der Array-Funktion von VBA erstellen.

If Not TypeName(varInputArray) Like "*()" Then
  MsgBox "Keine Array-Variable.", vbInformation
End If

Hinweis zur Array-Funktion

Die untere Grenze für ein mit der Array-Funktion erstelltes Datenfeld wird bestimmt durch die in der Option Base-Anweisung festgelegte untere Grenze, es sei denn, Array wird mit dem Namen der Typenbibliothek qualifiziert (z.B. VBA.Array). In diesem Fall wird Array nicht durch Option Base beeinflusst.

Matrixformel in Zelle eintragen

ActiveCell.FormulaArray = "=SUM(IF(RC[1]:R[65534]C[1]=0,1,0))"

strArrayFormula = "=SUM(IF(RC[1]:R[65534]C[1]=0,1,0))"
If Len(strArrayFormula) > 255 Then
  MsgBox "Matrixformel zu lang! Maximal 255 Zeichen erlaubt.", vbInformationen
Else
  ActiveCell.FormulaArray = strArrayFormula
End If

Ausführliche Informationen über diese Limitation erhalten Sie in Abschnitt Limitationen der FormulaArray-Eigenschaft.

Überprüfen, ob Zelle eine Matrixformel enthält

Aktuelle Matrix selektieren

Grösse einer Matrix abfragen (Anzahl Zellen)

Die Grösse einer Matrix bzw. die Anzahl Zellen kann anhand der Count-Eigenschaft von CurrentArray.Cells festgestellt werden:

Adresse einer Matrix abfragen (Zellbereich-Adresse)

Grösse einer Array-Dimension ändern

Bei mehrdimensionalen Arrays ist normalerweise nur Änderung der letzten Dimension möglich. Diese VBA-Prozedur redimensioniert bei einem zweidimensionalen Array beide Dimensionen:

Sub FreeArray()
  Dim intCol As Integer, intRow As Integer, intCounter As Integer
  Dim arr()
  Application.ScreenUpdating = False
  ReDim arr(1 To 2, 1 To 10)
  For intRow = 1 To 2
    For intCol = 1 To 10
     arr(intRow, intCol) = "Spalte " & intCol & " - " & "Zeile " & intRow
    Next intCol
  Next intRow
  MsgBox "Letzter Wert:" & Chr(10) & arr(2, 10)
  Workbooks.Add
  Range("A1:J2") = arr
  For intCounter = 1 To 11
    Cells(3, intCounter) = "Spalte " & intCounter & " - " & "Zeile 3"
  Next intCounter
  For intCounter = 1 To 3
    Cells(intCounter, 11) = "Zeile " & intCounter & " - " & "Spalte 11"
  Next intCounter
  arr = Range("A1").CurrentRegion
  ActiveWorkbook.Close savechanges:=False
  MsgBox "Letzter Wert:" & Chr(10) & arr(3, 11)
End Sub

Überprüfen, ob ein Array redimensioniert werden kann

Beschreibung

Code

Private Declare Function CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, src As Any, ByVal length As Long) As Long

Public Function IsArraySafe(ArrayVal As Variant) As Boolean
  If (VarType(ArrayVal) And vbArray) Then
    Dim lpSafeArray As Long
    Dim nDimensions As Long
   
    CopyMemory lpSafeArray, ByVal VarPtr(ArrayVal) + 8, 4
    CopyMemory nDimensions, ByVal lpSafeArray, 2
   
    If nDimensions > 0 Then
      IsArraySafe = True
    Else
      IsArraySafe = False
    End If
  Else
    Debug.Assert False
    'ArrayVal doesn't look to contain an Array
  End If
End Function

Sample Usage

If Not IsArraySafe(nArray()) Then
  Redim nArray(100)
End If

Anzahl Parameter von ParamArray abfragen

Function ArrayCount(ParamArray arr() As Variant) As Integer
  ArrayCount = UBound(arr) + 1
End Function

Sub TestCall()
  MsgBox ArrayCount(5, 7, 9, 11, 13)
End Sub

Deklaration von Arrays

Redim Preserve

Array als Prozedur-Argument deklarieren

Array als Parameter einer VBA-Prozedur übergeben

Übergabe mit ByRef versus ByVal

Meldung: "Datenfeldargument muß vom Typ ByRef sein"

Datenfelder, die mit Dim, ReDim oder Static deklariert wurden, können nicht mit ByVal übergeben werden. Für diesen Fehler gibt es die folgende Ursache und Lösung:

Dim Var1 As Variant
Var1 = FeldAlt()

In-process server

Array in Spalte konvertieren

Wie man Arrays in Spalten konvertieren kann, wird auf dieser Internetseite von Chip Pearson detailliert vorgestellt:

Excel: Arrays To Columns
http://www.cpearson.com/excel/arr2col.htm

Mehrere selektierte Zellbereiche in ein Array bzw. einen einzigen Zellbereich umwandeln

In Microsoft Visual Basic for Applications, you can create a custom function that will turn a nonadjacent selection of cells into an array.

This is beneficial with many of the built-in Microsoft Excel functions that require a single range or an array as input and the data on the worksheet is not contained in a contiguous range.

Function MakeArray(ParamArray CellAddress()) As Variant
  ' Declaration of function variables.
  Dim Temp As Variant
  Dim TheArray() As Variant
  Dim Count As Integer, Ver As Integer
  Dim W As Integer, X As Integer, Y As Integer, Z As Integer
  ' Initialize the Count variable.
  Count = 1
  ' Set the variable Ver = 0 if the version of Microsoft Excel is
  ' greater than 8 (8 is Microsoft Excel 97 for Windows).
  If Left(Application.Version, Len(Application.Version) - 1) >= 8 Then
    Ver = 0
  Else
    Ver = 1
  End If
  ' Set variable X from Ver to the total number of arguments in
  ' the CellAddress array.
  For X = Ver To UBound(CellAddress, 1)
    ' Temp equals the first element of the CellAddress array.
    Set Temp = CellAddress(X)
    ' Test Temp to see whether it is an array.
    If IsArray(Temp) Then
      ' If Temp is an array, set Y from 1 to the total number
      ' arguments in the Temp array's first dimension.
      For Y = 1 To UBound(Temp.Value, 1)
        ' If Temp is an array, set Z from 1 to the total number
        ' arguments in the Temp array's second dimension.
        For Z = 1 To UBound(Temp.Value, 2)
          ' ReDimension TheArray, Preserving any existing
          ' values, from 1 to Count.
          ReDim Preserve TheArray(1 To Count)
          ' TheArray, element Count equals Temp, element Y in the
          ' first dimension by element Z in the second dimension.
          TheArray(Count) = Temp(Y, Z).Value
          ' Increment the Count variable by one.
          Count = Count + 1
        Next Z
      Next Y
    Else
      ' If Temp is not an array, proceed from here.
      ' ReDimension TheArray, preserving any existing
      ' values, from 1 to Count.
      ReDim Preserve TheArray(1 To Count)
      ' TheArray element Count equals Temp.
      TheArray(Count) = Temp
      ' Increment the Count variable by one.
      Count = Count + 1
    End If
  Next X
  ' Return TheArray to our function MakeArray.
  MakeArray = TheArray
End Function

Weitere Informationen von Microsoft:
  Custom Function to Transpose Nonadjacent Range
  http://support.microsoft.com/?scid=kb;en-us;134406

Ergebnis einer Matrixformel in Array-Variable speichern

Dieses Beispiel zeigt, wie das Ergebnis einer Matrixformel in einer Datenfeldvariable abgelegt wird. Dazu wird eigentlich nur eine einzige Codezeile benötigt. Die restlichen Codezeilen werten lediglich allfällig auftretende Fehler aus.

Public Sub EvaluateArrayFormula()
  Dim varArray As Variant
  varArray = Application.Evaluate("MINVERSE(A1:C3)")
  If IsError(varArray) = True Then
    Select Case Application.Evaluate("ERROR.TYPE(MINVERSE(A1:C3))")
      Case 1
        MsgBox "Fehler #NULL! aufgetreten!"
      Case 2
        MsgBox "Fehler #DIV/0! aufgetreten!"
      Case 3
        MsgBox "Fehler #WERT! aufgetreten!"
      Case 4
        MsgBox "Fehler #BEZUG! aufgetreten!"
      Case 5
        MsgBox "Fehler #NAME? aufgetreten!"
      Case 6
        MsgBox "Fehler #ZAHL! aufgetreten!"
      Case 7
        MsgBox "Fehler #NV aufgetreten!"
      Case Else
        MsgBox "Unbekannter Fehler aufgetreten!"
    End Select
  End If
End Sub

Daten in mehrere Zellen gleichzeitig eintragen

In dem folgenden Beispiel wird der Text "Auftrag ID" in die Zelle A1, "Artikel" in Zelle B1 und "Menge" in Zelle C1 eingetragen:

ActiveSheet.Range("A1:C1").Value = Array("Auftrag ID", "Artikel", "Menge")

Daten an Microsoft Excel übergeben

Transferring data cell by cell can be a perfectly acceptable approach if the amount of data is small. You have the flexibility to place data anywhere in the workbook and can format the cells conditionally at run time. However, this approach is not recommended if you have a large amount of data to transfer to an Excel workbook. Each Range object that you acquire at run time results in an interface request so that transferring data in this manner can be slow. Additionally, Microsoft Windows 95 and Windows 98 have a 64K limitation on interface requests. If you reach or exceed this 64k limit on interface requests, the Automation server (Excel) might stop responding or you might receive errors indicating low memory. This limitation for Windows 95 and Windows 98 is discussed in the following Knowledge Base article:

216400 PRB: Cross-Process COM Automation Can Hang Client Application on Win95/98

Once more, transferring data cell by cell is acceptable only for small amounts of data. If you need to transfer large data sets to Excel, you should consider one of the solutions presented later .

Use Automation to Transfer an Array of Data to a Range on a Worksheet

An array of data can be transferred to a range of multiple cells at once:

Sub TransferData()
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add

   'Create an array with 3 columns and 100 rows
   Dim DataArray(1 To 100, 1 To 3) As Variant
   Dim r As Integer
   For r = 1 To 100
      DataArray(r, 1) = "ORD" & Format(r, "0000")
      DataArray(r, 2) = Rnd() * 1000
      DataArray(r, 3) = DataArray(r, 2) * 0.7
   Next

   'Add headers to the worksheet on row 1
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")

   'Transfer the array to the worksheet starting at cell A2
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
End Sub

If you transfer your data using an array rather than cell by cell, you can realize an enormous performance gain with a large amount of data. Consider this line from the code above that transfers data to 300 cells in the worksheet:

   oSheet.Range("A2").Resize(100, 3).Value = DataArray

This line represents two interface requests (one for the Range object that the Range method returns and another for the Range object that the Resize method returns). On the other hand, transferring the data cell by cell would require requests for 300 interfaces to Range objects. Whenever possible, you can benefit from transferring your data in bulk and reducing the number of interface requests you make.

Weitere Informationsquellen

Allgemeine Informationen

XL97: Maximum Array Size in Microsoft Excel
http://support.microsoft.com/?scid=kb;en-us;166342

XL97: How to Use Dynamic Arrays in a Macro
http://support.microsoft.com/?scid=kb;en-us;142134

XL2000: Maximum Array Size in Microsoft Excel
http://support.microsoft.com/?scid=kb;en-us;166342

XL97/2000: Beschränkungen beim Weitergeben von Arrays an Excel mithilfe der Automatisierung
http://support.microsoft.com/?scid=kb;de;177991

XL97/2000: Limitations of Passing Arrays to Excel Using Automation
http://support.microsoft.com/?scid=kb;en-us;177991

XL97/2000/2002: Some Worksheet Functions Do Not Allow Array Constants
http://support.microsoft.com/?scid=kb;en-us;214286

XL98 (Mac): Some Worksheet Functions Do Not Allow Array Constants
http://support.microsoft.com/?scid=kb;en-us;192222

XL98 (Mac): How to Use Dynamic Arrays in a Macro
http://support.microsoft.com/?scid=kb;en-us;142134

XL2000: How to Use Dynamic Arrays in a Macro
http://support.microsoft.com/?scid=kb;en-us;213619

XL2000: Custom Function to Turn Nonadjacent Cells into an Array
http://support.microsoft.com/?scid=kb;en-us;213403

XL2000/2002/2003: Description of the limitations for working with arrays in Excel 2000, Excel 2002, and Excel 2003
http://support.microsoft.com/?scid=kb;EN-US;166342

XL97/2000/2002/2003: Description of arrays in Sub procedures in Visual Basic for Applications
http://support.microsoft.com/?scid=kb;en-us;843145

How To Pass a Visual Basic Array to an Excel Worksheet
http://support.microsoft.com/?scid=kb;EN-US;153090

Excel: Returning Arrays
http://www.cpearson.com/excel/returnin.htm
Kurzer aber sehr guter Artikel zum Thema "Arrays" von Pearson Software Consulting mit VBA-Codebeispielen.

Excel: Arrays To Columns
http://www.cpearson.com/excel/arr2col.htm

Custom Function to Transpose Nonadjacent Range
http://support.microsoft.com/?scid=kb;en-us;134406

Bugs und Probleme

XL97: Run-Time Error Using FormulaArray Property
http://support.microsoft.com/?scid=kb;en-us;192989

XL97: Cannot Paste Array Formula in Its Original Cell
http://support.microsoft.com/?scid=kb;en-us;158080

XL97: Compile Error Passing ParamArray
http://support.microsoft.com/?scid=kb;en-us;157351

XL97: ParamArrays are Always Zero-Based
http://support.microsoft.com/?scid=kb;en-us;158222

XL97: Error When Passing an Array Value Between Macros
http://support.microsoft.com/?scid=kb;en-us;158746
» Ein interessantes, schwer nachvollziehbares Verhalten bei Redimensionierung eines Arrays.

XL98 (Mac): ParamArrays are Always Zero-Based
http://support.microsoft.com/?scid=kb;en-us;182647

XL98 (Mac): Compile Error Passing ParamArray
http://support.microsoft.com/?scid=kb;en-us;184703

XL2000: ParamArrays are Always Zero-Based
http://support.microsoft.com/?scid=kb;en-us;213567

XL2000: Compile Error Passing ParamArray
http://support.microsoft.com/?scid=kb;en-us;213783

XL2000: Benutzerdefinierte Funktion gibt bei grossem Array "#WERT!" zurück
http://support.microsoft.com/?scid=kb;de;250828

XL2000: Custom Function Returns #VALUE! If Returning Array Larger Than 5,461 Elements
http://support.microsoft.com/?scid=kb;en-us;250828

XL2000: Run-Time Error Using FormulaArray Property
http://support.microsoft.com/?scid=kb;en-us;213181

XL2000: Excel May Fault in MSO9.DLL When Calling an XLL Function that Takes an Array
http://support.microsoft.com/?scid=kb;en-us;304442
Dieses Problem lässt sich mit einem Hotfix beheben.

XL2002: Error Message When You Run the Conditional Sum Wizard
http://support.microsoft.com/?scid=kb;en-us;817775

XL2002: Array Formula with Error Not Flagged
http://support.microsoft.com/?scid=kb;en-us;282158

XL2000/2002/2003: Data May Be Truncated After 1,835 Characters When You Transfer Array Data to Cells in an Excel Worksheet
http://support.microsoft.com/?scid=kb;en-us;832136
» Ein äusserst interessanter Bug in Microsoft Excel, der noch nicht behoben ist (Stand 27.09.2004).

Haben Sie Fragen, Anregungen oder einen Fehler entdeckt?
Kontakt zum Autor

Zuletzt aktualisiert am 2.02.2006 / 19:00 Uhr
Zuletzt kontrolliert am 14.03.2010 / 17:00 Uhr
© 2002-2010 by Philipp von Wartburg, Schweiz
Alle Rechte vorbehalten