[VBA] For Each ...Next met multidimensionele array

[YellowOnline]

Ik heb enige tijd geleden een Powershell-scriptje gemaakt waar wat VBA bij te zien komt omdat er een Excelbestand aangemaakt wordt. Nu wordt me gevraagd nieuwe functionaliteit toe te voegen en duik ik weer in VBA, een taal waar ik niet vertrouwd mee ben. Momenteel bijt ik even mijn tanden stuk op het volgende.

Gegeven de volgende range:

	K	L	M	N	O	P	Q	R
1	Microsoft Office 2010	29/08/2012	Sales	PC254	doejohn	Doe	John	[e-mail]
2	Microsoft Office 2010	14/03/2012	IT	PC150	doejane	Doe	Jane	[e-mail]
3	Autodesk AutoCad	16/07/2012	Management	PC320	doejunior	Doe	Junior	[e-mail]

Ik maak een array op de volgende manier:

Visual Basic .NET:

intLastRow = Worksheets(10).UsedRange.SpecialCells(11).Row
Range = "$K$1" & ":$R$" & intLastRow
arrUserData = ActiveSheet.Range(Range).Value

In deze array zit inderdaad de informatie die ik wil.

code:

arrUserData(0,0)
Microsoft Office 2010

arrUserData(2,5)
Junior

Waar ik vast zit is bij mijn loop:

Visual Basic .NET:

For Each objUserData In arrUserData
        Debug.Print objUserData
Next

Op deze manier krijg ik de data terug als volgt

code:

1. Microsoft Office 2010
2. Microsoft Office 2010
3. Autodesk AutoCad
4. 29/08/2012
5. 14/03/2012
6. 16/07/2012   
7. IT
(etc)

Dat is niet wat ik wil natuurlijk. Wat ik voor ogen had is een equivalent als de ForEach in Powershell

C#:

ForEach ($objUserData in $arrUserData)
    {
    Write-Host $objUserData
    }

Waarbij het resultaat zou zijn

code:

1. Microsoft Office 2010    29/08/2012  Sales   PC254   doejohn Doe John    [e-mail]
2. Microsoft Office 2010    14/03/2012  IT  PC150   doejane Doe Jane    [e-mail]

Natuurlijk, in PoSh is dat een mooi object met properties dat ik terug krijg. Dat zou ik eigenlijk ook voor mekaar willen krijgen in VBA, zodat ik de aparte properties kan opvragen. Mijn bedoeling is om een If in die loop te steken als filter, maar eerst moet ik uitvinden hoe om te gaan met mijn data natuurlijk.

Tips en oplossingen zijn welgekomen.

---

Ik heb zelf al een oplossing gevonden, nl. op onderstaande manier:

Visual Basic .NET:

intEndCounter = intLastRow
For intCounter = 1 To intEndCounter
    If arrUserData(intCounter, 1) = Application Then
            Debug.Print arrUserData(intCounter, 2)
            Debug.Print arrUserData(intCounter, 3)
            Debug.Print arrUserData(intCounter, 4)
            Debug.Print arrUserData(intCounter, 5)
            Debug.Print arrUserData(intCounter, 6)
            Debug.Print arrUserData(intCounter, 7)
            Debug.Print arrUserData(intCounter, 8)
    End If
Next

[ Voor 10% gewijzigd door YellowOnline op 30-08-2012 15:39 ]

[F_J_K]

Inderdaad. Kan overigens zonder 'lelijke' EOlns met Debug.Print arrUserData(intCounter, 2); Spc(1); Debug.Print arrUserData(intCounter, 3); Spc(1); etc

Of natuurlijk een geneste loop maken 2-8. Eigenlijk is de array ook niet nodig aangezien je immers direct de cellen kunt uitlezen: dus geen array maken maar direct

Visual Basic:

For i = startRij To maxRij
   For j = startKolom To maxKolom
       'doe je ding met bijv Cells(i,j)
   Next j
Next i

En inderdaad, PS doet dat makkelijker

[YellowOnline]

F_J_K schreef op donderdag 30 augustus 2012 @ 14:04:
Inderdaad. Kan overigens zonder 'lelijke' EOlns met Debug.Print arrUserData(intCounter, 2); Spc(1); Debug.Print arrUserData(intCounter, 3); Spc(1); etc

Of natuurlijk een geneste loop maken 2-8. Eigenlijk is de array ook niet nodig aangezien je immers direct de cellen kunt uitlezen: dus geen array maken maar direct

Visual Basic:

For i = startRij To maxRij
   For j = startKolom To maxKolom
       'doe je ding met bijv Cells(i,j)
   Next j
Next i

En inderdaad, PS doet dat makkelijker

Oh, die Debug.Print (die ik via jou heb leren kennen als ik me niet vergis) gebruik ik enkel om in de VBA-editor te knoeien. In de finale code wordt dat naar cellen weggeschreven natuurlijk.

Wat betreft je alternatief: zou er een performanceverschil tussen beiden zijn?

[ Voor 4% gewijzigd door YellowOnline op 30-08-2012 14:16 ]

[F_J_K]

Ach wel ja, geef mij maar de schuld

Als je genoeg geheugen hebt, zou een array inderdaad misschien wat sneller kunnen zijn. Maar IMHO wil je toch al geen Excel meer gebruiken als het over hoeveelheden gaat dat je dat kunt merken. Verschil is in ieder geval "inhoudelijk": de array kan achter de feiten aanlopen als de spreadsheet verandert. Daarom zou ik, behalve als er functionele voordelen zijn, direct vanuit de brontabel werken.

[YellowOnline]

F_J_K schreef op donderdag 30 augustus 2012 @ 14:56:
Ach wel ja, geef mij maar de schuld

Als je genoeg geheugen hebt, zou een array inderdaad misschien wat sneller kunnen zijn. Maar IMHO wil je toch al geen Excel meer gebruiken als het over hoeveelheden gaat dat je dat kunt merken. Verschil is in ieder geval "inhoudelijk": de array kan achter de feiten aanlopen als de spreadsheet verandert. Daarom zou ik, behalve als er functionele voordelen zijn, direct vanuit de brontabel werken.

Ik denk dat het in mijn geval dan niets uitmaakt: het Excel-bestand wordt door een powershell script (dat een verbinding met de DB maakt) maandelijks gegenereerd en de data is daardoor statisch in de XLSM zelf.

[Lustucru]

F_J_K schreef op donderdag 30 augustus 2012 @ 14:04:
En inderdaad, PS doet dat makkelijker

Dit doet toch precies hetzelfde?

code:

Dim c as range
for each c in [range]
debug.print c.value
next c

Waarbij hij bij mij keurig ook eerst de rijen afwerkt en dan pas kolommen. Zo niet heb je altijd nog:

code:

for each r in range.rows
 for each c in r
next c
next r