Idx

The Idx module lets you easily extract data from nested data structures in VBA. It is inspired by the pluck() family in R.

Tip

See here for the latest (pre)release.

Usage

Supply an array of indices to Arr_Index(), and it will index into (say) a multidimensional array.

' Declare 3D array...
Dim multi(1 To 2, 3 To 4, 5 To 6) As String

' ...and populate it.
multi(1, 3, 5) = "multi(1, 3, 5)"
multi(1, 3, 6) = "multi(1, 3, 6)"
multi(1, 4, 5) = "multi(1, 4, 5)"
multi(1, 4, 6) = "multi(1, 4, 6)"
multi(2, 3, 5) = "multi(2, 3, 5)"
multi(2, 3, 6) = "multi(2, 3, 6)"
multi(2, 4, 5) = "multi(2, 4, 5)"
multi(2, 4, 6) = "multi(2, 4, 6)"


' Index into the array programmatically.
Dim ind() As Variant: ind = Array(1, 4, 5)
Debug.Print Arr_Index(multi, ind)

multi(1, 4, 5)

Do the same for Index() itself, and it will index into any data structure: the multidimensional array…

' Index programmatically...
Debug.Print Index(multi, ind)

' ...or manually.
Debug.Print Index0(multi, 1, 4, 5)

multi(1, 4, 5)
multi(1, 4, 5)

…or a nested array…

' Create a nested array.
Dim nested() As Variant: nested = Array( _
	Array( _
		Array("nested(0)(0)(0)", "nested(0)(0)(1)"), _
		Array("nested(0)(1)(0)", "nested(0)(1)(1)") _
	), _
	Array( _
		Array("nested(1)(0)(0)", "nested(1)(0)(1)"), _
		Array("nested(1)(1)(0)", "nested(1)(1)(1)") _
	) _
)


' Index manually.
Debug.Print Index0(nested, 1, 0, 1)

nested(1)(0)(1)

…or a Collection.

' Create a collection...
Dim clx As Collection: Set clx = New Collection

' ...and populate it.
clx.Add "clx(1)"
clx.Add "clx!key_2", key := "key_2"


' Index by position...
Debug.Print Index0(clx, 1)

' ...or by key.
Debug.Print Index0(clx, "key_2")

clx(1)
clx!key_2

But the true power of Index() is seen with nested data structures of diverse types. Here we "drill down" to the very innermost data and extract it elegantly!

' Wrap the 3D array within the original collection...
clx.Add multi, key := "key_3"

' ...and nest it at the very end.
nested = Array( _
	Array( _
		Array("nested(0)(0)(0)", "nested(0)(0)(1)"), _
		Array("nested(0)(1)(0)", "nested(0)(1)(1)") _
	), _
	Array( _
		Array("nested(1)(0)(0)", "nested(1)(0)(1)"), _
		Array("nested(1)(1)(0)", "nested(1)(1)(1)", clx) _
	) _
)
'		                                            ^^^
'		                                         Insertion


' Index manually...
Debug.Print Index0(nested, 1, 1, 2, "key_3", 2, 3, 6)

' ...or programmatically.
ind = Array(1, 1, 2, "key_3", 2, 3, 6)
Debug.Print Index(nested, ind)

multi(2, 3, 6)
multi(2, 3, 6)

API

Here are all the features provided by Idx, which are useful in both VBA and Excel¹. If you are a developer, and wish to hide these functions from your users in Excel, then look here to activate Option Private for the Idx.bas module.

Metadata

Describe the module itself.

• MOD_NAME: The name (String) of the module.
• MOD_VERSION: Its current version (String).
• MOD_REPO: The URL (String) to its repository.

Array Extraction

Index into arrays, especially multidimensional arrays.

• Arr_Index(): Extract an element programmatically with an array of indices.

Arbitrary Extraction

Index into arbitrary data. This may be either an array or an object with a default member, like a Collection or Dictionary. Or it may be some arbitrary nesting of such data structures.

• Index(): Extract an element programmatically with an array of indices…
• Index0(): …and manually with literal indices.

Utilities

Perform broadly useful tasks.

• Assign(): Assign any value (scalar or objective) to a variable (by reference).
• Arr_Rank(): Get the "rank" (Long) of an array, which is the count of its dimensions².
• Arr_Length(): Get the length (Long) of an array.

Footnotes

1. You may qualify Idx.Index() to avoid clashes with the INDEX() function native to Excel. ↩

2. In VBA an array may have at most 60 dimensions. ↩