Reference
Array shapes and return values
Read the return contract for the specific function before combining calls.
Shape summary#
| Operation | Accepted shape | Successful output |
|---|---|---|
| Filter | 1D or 2D | 2D, rows 1..matches; source column bounds |
| Sort | 1D or 2D | Same rank and bounds as input |
| Unique | 1D or 2D (first column only) | 2D, rows 1..unique, column 1 |
| GroupBy | 2D | 2D, rows 1..groups, columns 1..2 |
| Finance functions | 1D or 2D (first column only) | Double, or Long for Count |
| Transpose | 2D | 2D with bounds exchanged |
| DimExpand | 1D | 2D, source indices as rows, column 1 |
| DimReduce | 2D vector | 1D with surviving-axis bounds |
| DimSplit | 2D | 1D outer of 1D rows, bounds retained |
| DimMerge | 1D outer of 1D rows | 2D using first row’s inner bounds |
| InsertColumns | 2D target; 1D or 2D new columns | 2D, original lower bounds |
| InsertLines | 1D row or 2D target; 1D row or 2D new rows | 2D for ordinary valid array inputs |
| DeleteElement | 2D | 2D, selected upper bound decreased |
| FillColumn | 1D/2D target and column vector | 2D |
| Change_Lbound | 1D or 2D | Same rank and values, new bound on chosen axis |
Empty is not an empty array#
Many functions exit without assigning their Variant result when input is unsupported. That yields Empty. Filter with no matches and Unique with only errors also return Empty. Use IsArray before LBound or UBound. A dynamic array that has never been ReDim’d is still not a valid allocated vector.
There is no uniform error contract: GroupBy returns the original input for a nonarray or non-2D argument; Filter returns the original when its first criterion is Empty; DeleteElement returns the original for an out-of-range deletion; InsertLines has passthrough cases for nonarray arguments. Consult each function’s behavior section rather than interpreting every nonarray return as a successful result.
Bounds are part of the contract#
Most functions copy and preserve existing bounds, but some create 1-based output. Use UBound minus LBound plus one to calculate lengths. A VBA.Array literal is explicitly 0-based. A rectangular multi-cell Range.Value2 array is 1-based. Do not chain operations with hardcoded indices unless their contracts guarantee those indices.
Missing values and comparisons#
Empty, Null, an empty string, and an Excel error are distinct. Finance skips Empty, Null, and errors and recognizes numeric text with IsNumeric. Filter performs its own matching rules. Unique and GroupBy use a binary-comparison Dictionary and do not use Filter’s normalization rules. Sort uses native comparisons and provides no error-value ordering.
Copying and side effects#
Array-transform functions take their main data ByVal and ordinarily return a new array without rearranging the caller’s array. Exchange explicitly takes both arguments ByRef and swaps them, while its return remains Empty. Application-state helpers change settings for the Excel application. Array copies containing object references do not clone the objects themselves.
Dimension detection limit#
DimCounter probes dimensions 1 through 10. It returns the count when a probe fails. If all ten succeed, it falls through with the default Integer result 0. Thus it reports 0 for arrays with ten or more dimensions. General high-dimensional array support is not provided by this library.