Microsoft Excel

Herbers Excel/VBA-Archiv

Array aus einer Zelle

Betrifft: Array aus einer Zelle von: HannesStein
Geschrieben am: 25.10.2020 10:54:55

Hallo
Wie kann man ein Array aus einem Range erstellen, auch wenn es nur eine Zelle ist?

In folgendem Beispiel wird ein Array erstellt und dann weiter verwendet. Erst aus einem Range mit 5 Zellen, das geht. Dann aus einem Range mit 1 Zelle. Die Weiterverarbeitung des Array führt zu einem Fehler. Das ist doch inkonsistent, oder?

In einem leeren Blatt "Aufruf" starten.

Option Explicit

Sub Aufruf()
    Dim a As Range, b As Range
    
    Set a = Range("B2:B6")
    Set b = Range("E5")
    
    'Geht:
    a.Value = "Apfel"
    Debug.Print UBound(Feld(a), 1)
    
    'Geht nicht, weil nur eine Zelle:
    b.Value = "Apfel"
    Debug.Print UBound(Feld(b), 1)
    
End Sub
Function Feld(Zelle As Range) As Variant
    Dim Liste As Range
    
    'Liste erstellen. Zuerst mal eine Zelle
    Set Liste = Zelle(1)    'Erste Zelle der Liste
    
    'Liste wird erweitert, falls unter Zelle noch was steht
    If Liste.Offset(1) <> "" Then Set Liste = Range(Liste, Liste.End(xlDown))

    'Werte der Liste werden in Array kopiert
    Feld = Liste
End Function
Wie muss man die Arrayerstellung, also die Funktion "Feld" in untenstehendem Beispiel, gestalten?
Der Debug.Print-Befehl ist der Nachweis, dass das Array existiert. In Wirklichkeit geht es da erst richtig los mit dem Makro.

Gruss, Hannes

Betrifft: AW: Array aus einer Zelle
von: Daniel
Geschrieben am: 25.10.2020 11:26:52

Hi
Ist halt so.
Besteht der Zellbereich nur aus einer Zelle, dann erstellt die Automatik auch eine Einzelwertvariable.

Hier hilft dann nur die spezielle Programmierung:
If Liste.Count = 1 then
    ReDim Feld(1 to 1, 1 to 1)
    Feld(1, 1) = Liste
Else
    Feld = List
End if 
Oder mit folgendem Trick, der zunächst den Zellbereich erweitert, damit auf jeden Fall ein
Array entsteht und dann die Zusätzlichen Daten entfernt:
Feld = Liste.Resize(, 2)
Redim preserve Feld(1 to ubound(Feld, 1), 1 to 1)
Gruß Daniel

Betrifft: AW: Noch'n Beispiel
von: Sulprobil
Geschrieben am: 25.10.2020 11:47:13

Function Feld(Zelle As Range) As Variant
    Dim i As Long
    With Application.WorksheetFunction
    i = Zelle.Count
    ReDim v(1 To i) As Variant
    If i = 1 Then
        v(1) = Zelle
    Else
        v = .Transpose(.Transpose(Zelle))
    End If
    Feld = v
    End With
End Function


Betrifft: AW: Noch'n Beispiel
von: HannesStein
Geschrieben am: 27.10.2020 20:34:49

Hi danke für Eure Antworten. Es geht also nir mit Fallunterscheidung. "ist halt so" ok.

@Daniel:
Dein Code scheitert wegen "Function Feld" am "ReDim Feld".
Aber mit "BlaBlaBla" statt "Feld" und dann "Feld=BlaBlaBla" geht es.
Ergebnis ist ein zweidimensionales Array.

@Sulprobil
Dein Code funktioniert. Ergebnis ist ein eindimensionales Array.
Nur, warum 2x Transpose? "v = Zelle" genügt doch, oder?
Und das ReDim unter das If stellen, denn wenn i mehr als 1, übernimmt es sowieso die Größen von Zelle.

Hannes

Betrifft: AW: Nicht ganz
von: Sulprobil
Geschrieben am: 27.10.2020 20:51:48

Hallo Hannes,

Wenn Du einem Variant lediglich die nicht-transponierte Range zuweist, erhältst Du etwas anderes.
Setze einen Stoppunkt und guck Dir die Struktur an, schlage ich vor.
Oder schau einmal hier:
https://www.vitoshacademy.com/vba-excel-how-to-convert-range-to-array-easily/

Viele Grüße,
Bernd

Betrifft: AW: Nicht ganz
von: HannesStein
Geschrieben am: 28.10.2020 15:51:59

Hi Bernd, ich weiss nicht, wie Du das meinst.

Bin Deinem Vorschlag nachgegangen und habe festgestellt, dass in beiden Fällen v ein 2-dim-Array mit den Größen 1-5, 1-1 ist. Ich konnte keinen Untershied feststellen.

Auch verstehe ich leider nicht, was Du mit dem Link sagen willst, den Du hier geschrieben hast.

Hannes