Excel2010, VBA Frage zu 'Union'

Computer: Software & Programmierung Visual Basic
#1

Hallo VBA-Experten,

könnte sich einer mal diese Mappe

http://www.file-upload.net/download-7482352/UnionTes…

anschauen:
Sie besteht aus einer Tabelle und einem kurzen Code im Modul 1.
Dieser Code setzt die 3 Bereiche A1-A3, A5-A7 und A9-A11 zu einer Union zusammen und weist diese Union einer Range-Variablen zu.
Wenn ich den Code laufen lasse, zeigt er mir zuerst richtig an, dass die Union aus 9 Elementen besteht.
Dann zeigt er in einer Schleife alle 9 Elemente einzeln an. Hier zeigt er aber nicht die zusammengesetzten Ranges an, sondern A1-A9.
Wo ist mein Denkfehler?

Danke und Gruß,
Andreas

#2

http://www.file-upload.net/download-7482352/UnionTes…

Dieser Code setzt die 3 Bereiche A1-A3, A5-A7 und A9-A11 zu
einer Union zusammen und weist diese Union einer
Range-Variablen zu.

Dann zeigt er in einer Schleife alle 9 Elemente einzeln an.
Hier zeigt er aber nicht die zusammengesetzten Ranges an,
sondern A1-A9.

Hallo Andreas,

prinzipiell sehe ich das für mich so, deine Variable r stellt
einen Zellbereich aus 9 Zellen dar. Quasi ein Array.

Und dann ist die erste Adresse in dem Array A1.
Egal ob in Wahrheit die Originaladresse mal G17 war.
A1 steht dann nicht für die oberste linke Zelle in einem
Blatt sondern für die oberste linke Adresse in dem Array.

Scheinbar ist die Adresse G17 vergessen worden.
Dem ist aber nicht so wie du an
r.Value=„huhu“ sehen kannst.

Allerdings hakt meine Array-Idee. In einem Array von 9 Zellen
müßte ein Fehler kommen wenn ich das 12-te Element ansprechen
will, sind ja nur 9.

Schau dir bitte nachstehenden Code an, dann weißte daß ich
da auch nix genaues weiß.

Gruß
Reinhard

Sub tt()
Dim r
'Set r = Range("A1:A3,D5:E6") 'ist wie Union
Set r = Union(Range("A1:A3"), Range("D5:smiley:6"))
MsgBox r.Cells(1, 100).Address
MsgBox r.Cells(90, 4).Address
MsgBox r.Cells(3, 1).Address
MsgBox r(100).Address
MsgBox r(4711).Address
MsgBox r(3).Address
MsgBox r(3)(6)(7).Address
r.Value = "huhu"
End Sub
#3

Grüezi Andreas

Dein Code arbeitet mit einem Index als Zähler und wertet diesen Index aus - da Du 9 Werte im Range hast, werden da auch diese 9 Indizes ausgewertet.

Arbeite sauber mit dem Range, statt zu zählen, dann passt das dann auch.
Deine Schleife am Ende sollte dann z.B. so aussehen:

 For Each a In r.Cells
 MsgBox a.Address
 Next a

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#4

Hallo Reinhard,

danke für die Tests.
Die Ergebnisse der ersten 6 MsgBox(en) kann ich irgendwie nachvollziehen.
Aber bei der 7. wird mir mein „Anfängertum“ schlagartig klar.
Was bedeutet denn die Syntax r(3)(6)(7)?
Und wieso kommt da $A$14 raus?
Auch den „huhu“ Effekt kann ich mir nicht ganz erklären. Warum werden nur A1 bis A3 be-huhu-t, nicht mehr und nicht weniger Zellen? Hat das damit zu tun, dass der erste Parameter bei set r der Range („A1:A3“) war?

Gruß,
Andreas

Hallo Andreas,

prinzipiell sehe ich das für mich so, deine Variable r stellt
einen Zellbereich aus 9 Zellen dar. Quasi ein Array.

Und dann ist die erste Adresse in dem Array A1.
Egal ob in Wahrheit die Originaladresse mal G17 war.
A1 steht dann nicht für die oberste linke Zelle in einem
Blatt sondern für die oberste linke Adresse in dem Array.

Scheinbar ist die Adresse G17 vergessen worden.
Dem ist aber nicht so wie du an
r.Value=„huhu“ sehen kannst.

Allerdings hakt meine Array-Idee. In einem Array von 9 Zellen
müßte ein Fehler kommen wenn ich das 12-te Element ansprechen
will, sind ja nur 9.

Schau dir bitte nachstehenden Code an, dann weißte daß ich
da auch nix genaues weiß.

Gruß
Reinhard

Sub tt()
Dim r
'Set r = Range(„A1:A3,D5:E6“) 'ist wie Union
Set r = Union(Range(„A1:A3“), Range(„D5:smiley:6“))
MsgBox r.Cells(1, 100).Address
MsgBox r.Cells(90, 4).Address
MsgBox r.Cells(3, 1).Address
MsgBox r(100).Address
MsgBox r(4711).Address
MsgBox r(3).Address
MsgBox r(3)(6)(7).Address
r.Value = „huhu“
End Sub

#5

Hallo Thomas,

vielen Dank. Mit For Each klappt das.
Aber gibt es auch eine Möglichkeit, gezielt z.B. die 4. Zelle in der Union anzusprechen? Das sollte ja dann A5 sein.
Mit r.Cells(4) klappt es jedenfalls nicht. Da kommt A4 raus.

Gruß, Andreas

#6

Grüezi Andreas

Zähl-Variablen und unzusammenhängende Bereiche passen halt nicht so gut zusammen.

Eine Möglichkeit, die mir spontan einfällt ist diese hier:

 For Each a In r.Cells
 i = i + 1
 If i = 4 Then
 MsgBox a.Address
 Exit For
 End If
 Next a

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#7

Danke Thomas,

so geht’s. Ist zwar bei sehr großen Bereichen etwas länglich, aber damit muss/kann ich wohl leben.

Gruß, Andreas

#8

so geht’s. Ist zwar bei sehr großen Bereichen etwas länglich,
aber damit muss/kann ich wohl leben.

Hallo Andreas,

nachfolgend hängt die Codegeschwindigkeit nicht von der Zellanzahl
von Union ab sondern nur von der Bereichsanzahl.

Gruß
Reinhard

Sub unionTest()
Dim r As Range, i As Integer
Set r = Union(Range("Tabelle1!$A$1:blush:A$3"), Range("Tabelle1!$B$5:blush:B$7"), Range("Tabelle1!$A$9:blush:A$11"))
For i = 1 To r.Count
 MsgBox i & " " & r(i) & " " & r(i).Address & " real: " & RealAdresse(r, i)
Next i
End Sub

Function RealAdresse(ByRef Bereich As Range, ByVal Nummer As Long) As String
Dim A As Integer
While Nummer \> Bereich.Areas(A + 1).Count
 Nummer = Nummer - Bereich.Areas(A + 1).Count
 A = A + 1
Wend
RealAdresse = Bereich.Areas(A + 1)(Nummer).Address
End Function
#9

Perfekt, Reinard,

genau das mit den Areas hat mir gefehlt.

Großen Dank und Gruß,
Andreas

#10

Grüezi Andreas und Reinhard

Ich habe noch versucht, das Ganze ohne ausgelagerten Code zu erreichen, auch das müsste eigentlich gut klappen:

Sub unionTest()
Dim r As Range
Dim a As Range
Dim I As Long
 Set r = Union(Range("Tabelle1!$A$1:blush:A$3"), Range("Tabelle1!$A$5:blush:A$7"), Range("Tabelle1!$A$9:blush:A$11"))
 MsgBox r.Count

 I = 5

 For Each a In r.Areas
 If I 


Mit freundlichen Grüssen

Thomas Ramel
- MVP für MS-Excel -
#11

Danke Thomas,

ich hab’s jetzt noch nicht getestet, aber das sieht auch klasse aus.
Wie es scheint, spornt meine Frage die echten Experten zu Höchstleistungen an.

Gruß, Andreas