Excel VBA Array

Max_Pow · 12. November 2019 um 16:24

Hallo,

für das unten genannte Makro zum Befüllen eines Arrays mittels einer Schleife bekomme ich folgende Fehlermeldung:

**Laufzeitfehler 9

Index außerhalb des gültigen Bereichs**

Warum?

Wie mache ich es richtig?

Und wie wende ich „Option Base 1“ richtig an, so dass der Array mit 1 zu zählen anfängt?

Vielen Dank im Voraus für die Antworten.

**Sub test()

Dim sheets As Variant
sheets = Array()

For i = 0 To ActiveWorkbook.sheets.Count - 1
sheets(i) = ActiveWorkbook.sheets(i + 1).Name
Next i
End Sub

Besten Gruß

Max**

Chewpapa_22ca98 · 12. November 2019 um 16:24

Hi,

**Sub test()

Dim sheets As Variant
sheets = Array()

For i = 0 To ActiveWorkbook.sheets.Count - 1
sheets(i) = ActiveWorkbook.sheets(i + 1).Name
Next i
End Sub**

Entweder schreibst Du:

Dim Sheets(10)
um Sheets als Array zu dimensionieren und je nach angegebener Base ab einem bestimmten Index zu beginnen.
Beispiel:

Option Base 7
Dim Sheets(9) As Variant

Dann besteht Dein Array Sheets aus:
Sheets(7)
Sheets(8)
Sheets(9)

OK?

Damit ist Option Base auch gleich erklärt?

Die andere, variable Arrays:

Dim Sheets() As Variant
Redim Sheets(ActiveWorkbook.sheets.Count)

Du kannst aber auch schreiben:

Dim Sheets(0 to 9)

oder

Dim Sheets(1 to ActiveWorkbook.sheets.Count - 1)

Oder auch die Variante mit () und Redim mit der Syntax von (Start to End)

Viele Möglichkeiten. Aber Du hast das Array Sheets gar nicht gültig dimensioniert. Deshalb der Fehler.

Gruß Chewpapa

Reinhard_fb8cb7 · 12. November 2019 um 16:24

Option Base 7
Dim Sheets(9) As Variant
Dann besteht Dein Array Sheets aus:
Sheets(7)
Sheets(8)
Sheets(9)

OK?

Hallo Rainer,

das klappt so in VB? Ui. In Vba ist bei Option Base nur 0 oder 1
erlaubt.

„Sheets“ ist ein Name der von Vba selbst benutzt wird.
Er bezeichnet alle Blätter einer Mappe, auch z.B. Diagrammblätter.
„Worksheets“ ist ähnlich, betzeichnet aber nur Tabellenblätter.
@Anfrager, also bitte solche Namen nicht vergeben.

Gruß
Reinhard

Chewpapa_22ca98 · 12. November 2019 um 16:25

Hallo Reinhard,

das klappt so in VB? Ui. In Vba ist bei Option Base nur 0 oder
1
erlaubt.

Du hast Recht, es ist nur 0 oder 1 erlaubt. Ich hab’s verwechselt mit:

Dim Arr(7 to 10) As Variant

Das geht ja, und ich hab’ daraus geschlossen, daß das mit Base auch gehen sollte, ohne zu testen. Danke für die Korrektur!

Gruß Rainer

Max_Pow · 12. November 2019 um 16:26

Hi Chewpapa,

vielen Dank für die Antwort. Dank dir funktioniert es jetzt mit folgendem Code

**Sub test()

Dim blätter As Variant
blätter = Array()
ReDim blätter(ActiveWorkbook.sheets.Count)

For i = 0 To ActiveWorkbook.sheets.Count - 1
blätter(i) = ActiveWorkbook.sheets(i + 1).Name
Debug.Print blätter(i)
Next i

End Sub**

Wenn ich allerdings das Option Base 1 dazuschreibe, wie folgt:

**Sub test()

Option Base 1
Dim blätter As Variant
blätter = Array()
ReDim blätter(ActiveWorkbook.sheets.Count)

For i = 0 To ActiveWorkbook.sheets.Count - 1
blätter(i) = ActiveWorkbook.sheets(i + 1).Name
Debug.Print blätter(i)
Next i

End Sub**

erscheint die folgende Fehlermeldung unter Markierung der Zeile Sub test() :

Fehler beim kompilieren.
Innerhalb einer Prozedur ungültig.

Wie kann ich das beheben?

Beste Grüße

Max

Reinhard_fb8cb7 · 12. November 2019 um 16:26

Wenn ich allerdings das Option Base 1 dazuschreibe, wie folgt:
erscheint die folgende Fehlermeldung unter Markierung der
Zeile Sub test() :

Fehler beim kompilieren.
Innerhalb einer Prozedur ungültig.

Wie kann ich das beheben?

Hallo Max,

in ein Modul kannst du Prozeduren einfügen. Eine „Sub“ ist eine
Prozedur genau wie eine „Function“ oder ein(e) „Property“.

Mit dieser Fehlermeldung teilt dir der Debugger mit daß sich
die Codezeile „Option Base 1“ verbotenerweise innerhalb einer
Prozedur befindet.

Lösche die Zeile dort und schreibe sie ganz nach oben in’s Modul,
unter „Option Explicit“.

Gruß
Reinhard

Chewpapa_22ca98 · 12. November 2019 um 16:26

Hallo

erst mal schön, daß es geholfen hat.
Noch eine Anmerkung und eine Korrektur.

**Sub test()

Dim blätter As Variant
blätter = Array()
ReDim blätter(ActiveWorkbook.sheets.Count)

For i = 0 To ActiveWorkbook.sheets.Count - 1
blätter(i) = ActiveWorkbook.sheets(i + 1).Name
Debug.Print blätter(i)
Next i

End Sub**

Die Zeile:

blätter = Array()

bewirkt nichts. Die kann den Fehler verursachen.

Eine Variable, die Variant deklariert ist kann man durch das passende Redim zum Array machen. (So wie Du es geschrieben hast.) Dadurch wird Deine Variable zum Array, nicht durch die Zeile:

blätter = Array()

Wie du wohl angenommen hast.

Übersichtlicher ist es aber, das Array als solches zu deklarieren.
Hinter Next muss keine Variable stehen.
Dann sieht der Code so aus:

Sub test()
Dim blätter() As Variant

ReDim blätter(ActiveWorkbook.sheets.Count)

For i = 0 To ActiveWorkbook.sheets.Count - 1
blätter(i) = ActiveWorkbook.sheets(i + 1).Name
Debug.Print blätter(i)
Next

End Sub

Bis hier her sollte jetzt alles klar sein?

Nun noch mal zu Option Base.

Mit Base 1 legst Du fest, daß alle Arrays, die so deklariert werden, wie Du es getan hast mit dem Index 1 beginnen.
Dann gibt es kein …

blätter(0)

Mit Deinem

For i = 0 …
blätter(i)

rufst Du alle Blätter mit dem Index auf der in i stegt. Deine Schleife beginnt bei Null (For i = 0 …)
Das erste Feld, auf das Du zugreifst ist also:

blätter(0)

Wenn Du mit …

Option Base 1

… festlegst, daß es dieses Feld im Array nicht geben soll, kann das nur zu einem Fehler führen.

Du legst erst fest, dieses Feld gibt es nicht und dann greifst Du darauf zu.

Wenn Du unbedingst Option Base 1 schreiben willst, dann muss Deine Schleife auch bei 1 beginnen:

For i = 1 …

Gruß Chewpapa

Chewpapa_22ca98 · 12. November 2019 um 16:26

Hallo Reinhard,

Mit dieser Fehlermeldung teilt dir der Debugger mit daß sich
die Codezeile „Option Base 1“ verbotenerweise innerhalb einer
Prozedur befindet.

ahh, das war’s. Die Fehlermeldung hab’ ich nicht ganz verstanden.

Aber wenn Option Base 1 mit dem ursprünglichen Code an der richtigen Stelle steht, gibt es wieder ein ‚Out of Range‘. Man kann Index Null nicht erst verbieten und dann verwenden wollen.

Gruß Rainer

Reinhard_fb8cb7 · 12. November 2019 um 16:26

Hallo Rainer,

Mit dieser Fehlermeldung teilt dir der Debugger mit daß sich
die Codezeile „Option Base 1“ verbotenerweise innerhalb einer
Prozedur befindet.

ahh, das war’s. Die Fehlermeldung hab’ ich nicht ganz
verstanden.

Aber wenn Option Base 1 mit dem ursprünglichen Code an der
richtigen Stelle steht, gibt es wieder ein ‚Out of Range‘.
Man kann Index Null nicht erst verbieten und dann verwenden
wollen.

Ja, bei Benutzung von =Array(…) *glaub*. Dim setzt sich drüber weg.

Option Explicit
Option Base 1

Sub tt()
Dim N(0 To 8)
N(0) = "klappt"
MsgBox N(0)
End Sub

Gruß
Reinhard

Chewpapa_22ca98 · 12. November 2019 um 16:26

Hallo Reinhard,

Ja, bei Benutzung von =Array(…) *glaub*. Dim setzt sich
drüber weg.

Dim N(0 To 8)

Es liegt am (Low To High), nicht am Dim.

Mit …
Option Base 1
Dim N(8)
… beginnt das Array bei N(1)

Gruß Rainer

Max_Pow · 12. November 2019 um 16:33

Jetzt funktioniert es einwandfrei auch mit Option Base 1. Vielen Dank für eure Antworten.