Need help on Array VBA

Question

I don't understand some parts in the following code:

    For i = 2 To UBound(a, 1)
        txt = Join$(Array(a(i, 3), a(i, 4)), Chr(2))
        If Not dic.exists(txt) Then
            Set dic(txt) = CreateObject("Scripting.Dictionary")
        End If
        dic(txt)(a(i, 1)) = VBA.Array(a(i, 5), a(i, 6), a(i, 2))
        
    Next

Q1: Why set dic(txt) in the loop code
Q2: dic(txt)(a(i,1)) => Why they use (a(i,1))

Thanks alot

It is looping through an array a with a counter of i, it is joining a(i,3) and a(i,4) and if the result of this join txt does not exist in the dictionary dic it creates an entry for txt as the key in the dic then it sets this new or existing entry txt by dic(txt) with the item of a(i,1) to be the array created using vba.array I think (it's friday afternoon) need a beer :-) I think it's getting the last value of a(i, 5), a(i, 6), a(i, 2) and making it accessible in a dictionary via the key a(i, 3), a(i, 4)) — Nathan_Sav
– Nathan_Sav, Commented May 6, 2022 at 15:56
Thanks Nathan, why does he Set dic(txt) = CreateObject("Scripting.Dictionary") in the loop. Normally, i add CreateObject before the loop code — K jack
– K jack, Commented May 9, 2022 at 11:59
It is, the dictionary dic is created outside, but the dictionary within dic, is created when it's needed. — Nathan_Sav
– Nathan_Sav, Commented May 9, 2022 at 12:26

VBasic2008 · Accepted Answer · 2022-05-06 16:34:53Z

1

Dictionary of Dictionaries

Loop through the elements of the 1st dimension (rows) of a multi-dimensional array (a):
```
For i = 2 To UBound(a, 1)
```
Revealing that a is a 2D array, joining the elements of columns 3 and 4 into a string (txt).
```
txt = Join$(Array(a(i, 3), a(i, 4)), Chr(2))
```
or to simplify:
```
txt = a(i, 3) & Chr(2) & a(i, 4)
```
Checking if txt exists as a key in the existing dictionary (dic).
```
If Not dic.exists(txt) Then
End If
```
If it doesn't, add a new dictionary as an item associated with the current key:
```
Set dic(txt) = CreateObject("Scripting.Dictionary") 
```
1. Key is txt,
2. Item is a new (inner) dictionary.
Use the value in the first column of the array (a) as the key in the new dictionary and add a zero-based (VBA. ensures zero-based) array with the values from columns 5, 6, and 2 of array a as the associated item to the new dictionary (of course only if the 1st column value does not already exist in the 'new' (inner) dictionary):
```
dic(txt)(a(i, 1)) = VBA.Array(a(i, 5), a(i, 6), a(i, 2))
```
dic:
- Key is txt
- Item is new (inner) dic:
  - Key is a(i, 1)
  - Item is VBA.Array(...)

answered May 6, 2022 at 16:34

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

K jack Over a year ago

Thanks VBasic2008, i need time to understand your instruction.

Collectives™ on Stack Overflow

Need help on Array VBA

1 Answer 1

Dictionary of Dictionaries

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Dictionary of Dictionaries

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related