Dictionaries
Dictionaries are a versatile key-value store designed for use within Excel. They allows users to store and retrieve data efficiently using object handles. Keys in an a dictionaryh can be numbers, strings, or dates, providing flexibility in how data is organized. Values can be of any type, including other XlBlocks objects, enabling the creation of complex, nested data structures.
Dictionary Functions
XBDict_Build
Build a dictionary from a two ranges
=XBDict_Build(keys, values, [onErrors])
| Parameter | Type | Description |
|---|---|---|
| keys | XlBlockRange |
A range of data to use for dictionary keys |
| values | XlBlockRange |
A range of data to use for dictionary values |
| onErrors | String |
Optional Error handling ('drop' or 'error') |
Returns
A dictionary
XBDict_BuildFromArray
Build a dictionary from an array
=XBDict_BuildFromArray(range, [onErrors])
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A two column range of data to use for dictionary keys and values |
| onErrors | String |
Optional Error handling ('drop' or 'error') |
Returns
A dictionary
XBDict_BuildTyped
Build a dictionary of a given key type from two ranges
=XBDict_BuildTyped(keys, keyType, values, [onErrors])
| Parameter | Type | Description |
|---|---|---|
| keys | XlBlockRange |
A range of data to use for dictionary keys |
| keyType | String |
The data type of the dictionary keys |
| values | XlBlockRange |
A range of data to use for dictionary values |
| onErrors | String |
Optional Error handling ('drop' or 'error') |
Returns
A dictionary
XBDict_BuildTypedFromArray
Build a dictionary of a given key type from an array
=XBDict_BuildTypedFromArray(range, keyType, [onErrors])
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A two column range of data to use for dictionary keys and values |
| keyType | String |
The data type of the dictionary keys |
| onErrors | String |
Optional Error handling ('drop' or 'error') |
Returns
A dictionary
XBDict_BuildFromLists
Build a dictionary from two lists
=XBDict_BuildFromLists(keyList, valueList)
| Parameter | Type | Description |
|---|---|---|
| keyList | XlBlockList |
A list of keys to use in constructing the dictionary |
| valueList | XlBlockList |
A list of values to use in constructing the dictionary |
Returns
A dictionary
XBDict_BuildFromFile
Build a list from the contents of a file
=XBDict_BuildFromFile(filepath, delimiter, [trimStrings], [ignoreEmpty])
| Parameter | Type | Description |
|---|---|---|
| filepath | String |
A filepath |
| delimiter | String |
A delimiter |
| trimStrings | Boolean |
Optional Trim strings |
| ignoreEmpty | Boolean |
Optional Ignore empty strings |
Returns
A dictionary
XBDict_Get
Get the contents of a dictionary as a range
=XBDict_Get(dict, [includeHeader], [keyColumn], [valueColumn])
| Parameter | Type | Description |
|---|---|---|
| dict | XlBlockDictionary |
A dictionary |
| includeHeader | Boolean |
Optional Include header in output (FALSE) |
| keyColumn | String |
Optional The name to use for the key column |
| valueColumn | String |
Optional The name to use for the value column |
Returns
A range
XBDict_GetValue
Get a value from a dictionary
=XBDict_GetValue(dict, key)
| Parameter | Type | Description |
|---|---|---|
| dict | XlBlockDictionary |
A dictionary |
| key | Object |
A key for the dictionary |
Returns
An object
XBDict_GetKeys
Get the keys of a dictionary as a range
=XBDict_GetKeys(dict)
| Parameter | Type | Description |
|---|---|---|
| dict | XlBlockDictionary |
A dictionary |
Returns
A range
XBDict_GetValues
Get the values of a dictionary as a range
=XBDict_GetValues(dict)
| Parameter | Type | Description |
|---|---|---|
| dict | XlBlockDictionary |
A dictionary |
Returns
A range
XBDict_ContainsKey
Get the keys of a dictionary as a range
=XBDict_ContainsKey(dict, key)
| Parameter | Type | Description |
|---|---|---|
| dict | XlBlockDictionary |
A dictionary |
| key | Object |
A key for the dictionary |
Returns
A boolean
XBDict_Count
Get the number of entries in a dictionary
=XBDict_Count(dict)
| Parameter | Type | Description |
|---|---|---|
| dict | XlBlockDictionary |
A dictionary |
Returns
An integer
XBDict_Dim
Get the number of entries in a dictionary as a string
=XBDict_Dim(dict)
| Parameter | Type | Description |
|---|---|---|
| dict | XlBlockDictionary |
A dictionary |
Returns
A string
XBDict_ToTable
Create a table from a dictionary
=XBDict_ToTable(dict, [keyColumnName], [valueColumnName], [valueColumnType])
| Parameter | Type | Description |
|---|---|---|
| dict | XlBlockDictionary |
A dictionary |
| keyColumnName | String |
Optional The name to use for the key column |
| valueColumnName | String |
Optional The name to use for the value column |
| valueColumnType | String |
Optional The type to use for the value column |
Returns
A table