VBA Arrays

A whole bunch of Array functions to make it easier and faster coding. Many functions are to try and mimic JavaScript. Example: Push, Pop, Shift, Unshift, Splice, Sort, Reverse, length, toString.

Other Helpful Resources

www.roberttodar.com About me and my background and some of my other projects.
Style Guide A guide for writing clean VBA code. Notes on how to take notes =)
Boilerplate Boilerplate that contains a bunch of helper libraries such as JSON tools, Code Analytics, LocalStorage, Unit Testing, version control and local network distribution, userform events, and more!
Strings String function library. ToString, Inject, StringSimilarity, and more.
Analytics Way of tracking code analytics and metrics. Useful when multiple users are running code within a shared network.
Userform EventListener Listen to events such as mouseover, mouseout, focus, blur, and more.

List of Available Functions

Function Name	Description
`ArrayAverage`	Returns the average of all the numbers inside an array.
`ArrayContainsEmpties`	Returns `True` if the array contains any empties.
`ArrayDimensionLength`	Returns the dimensionlenght of the array.
`ArrayExtractColumn`	Extracts a column from a 2 dim array and returns it as a 1 dim array
`ArrayExtractRow`	Extracts a row from a 2 dim array and returns it as a 1 dim array
`ArrayFilter`	Uses regex to filter items in a single dim array
`ArrayFilterTwo`	Uses regex to filter items in a two dim array.
`ArrayFromRecordset`	Converts a recordset into a 2 dim array including it's headers
`ArrayGetColumnIndex`	Return the column index based on the header name
`ArrayGetIndexes`	Returns a single dim array of the indexes of column headers
`ArrayIncludes`	Checks to see if a value is in single dim array
`ArrayIndexOf`	Returns the index of an item in a single dim array
`ArrayLength`	Returns the number of items in an array
`ArrayPluck`	Extracts a list of a given property. Must be array of dictionries
`ArrayPop`	Removes the last element in array, returns the popped element
`ArrayPush`	Adds a new element(s) to an array (at the end), returns the new array length
`ArrayPushTwoDim`	Adds a new element(s) to an array (at the end). Must be full row of data
`ArrayQuery`	Saves array in CSV file and allows the ability to run ADODB queries on it.
`ArrayRemoveDuplicates`	Removed duplicates from single dim array
`ArrayReverse`	Reverse array (can be used after sort to get the descending order)
`ArrayShift`	Removes element from array - returns removed element
`ArraySort`	Sort an array
`ArraySplice`	Changes the contents of an array by removing or replacing existing elements and/or adding new elements.
`ArraySpread`	Spreads out an array into a single array. example: jagged arrays, dictionaries, collections.
`ArraySum`	Returns the Sum of a single dim array containing numbers
`ArrayToCSVFile`	Saves a two dim array to a CSV file
`ArrayToString`	Returns a string from a 1 or 2 dim array, separated by optional delimiter and vbnewline for each row
`ArrayTranspose`	Application.Transpose has a limit on the size of the array and is limited to the 1st dim. This fixes those issues.
`ArrayUnShift`	Adds a new element to the begining of the array
`Assign`	Quick tool to either set or let depending on if element is an object
`ConvertToArray`	Convert other list type objects to an array
`IsArrayEmpty`	This function tests whether the array is empty (unallocated). Returns TRUE or FALSE.

How to use

Import ArrayFunctions.bas file.
Set a reference to Microsoft Scripting Runtime as this uses dictionaries for removing duplicates.

Examples

Below are some of the examples you can do with single dim arrays. Note, there are several functions for two dim arrays as well.

'EXAMPLES OF VARIOUS FUNCTIONS
Private Sub arrayFunctionExamples()
    ' For simplicity using `a` as the variable. Otherwise, don't do that in your real code! =)
    Dim a As Variant

    ' Single dim functions that manipulate the array.
    ArrayPush a, "Banana", "Apple", "Carrot" '--> Banana,Apple,Carrot
    ArrayPop a                               '--> Banana,Apple --> returns Carrot
    ArrayUnShift a, "Mango", "Orange"        '--> Mango,Orange,Banana,Apple
    ArrayShift a                             '--> Orange,Banana,Apple
    ArraySplice a, 2, 0, "Coffee"            '--> Orange,Banana,Coffee,Apple
    ArraySplice a, 0, 1, "Mango", "Coffee"   '--> Mango,Coffee,Banana,Coffee,Apple
    ArrayRemoveDuplicates a                  '--> Mango,Coffee,Banana,Apple
    ArraySort a                              '--> Apple,Banana,Coffee,Mango
    ArrayReverse a                           '--> Mango,Coffee,Banana,Apple

    ' Array properties functions.
    ' These get details of the array: index of items, lenght, ect.
    ArrayLength a                            '--> 4
    ArrayIndexOf a, "Coffee"                 '--> 1
    ArrayIncludes a, "Banana"                '--> True
    arrayContains a, Array("Test", "Banana") '--> True
    ArrayContainsEmpties a                   '--> False
    ArrayDimensionLength a                   '--> 1 (single dim array)
    IsArrayEmpty a                           '--> False

    ' Here is an example of a jagged array.
    a = Array(1, 2, 3, Array(4, 5, 6, Array(7, 8, 9)))

    ' Can flatten jagged array with the spread formula. Note this is a deep spread.
    ' This formula also spreads dictionaires and collections as well!
    a = ArraySpread(a)                       '--> 1,2,3,4,5,6,7,8,9

    ' Math function examples
    ArraySum a                               '--> 45
    ArrayAverage a                           '--> 5

    ' Filter use's regex pattern
    a = Array("Banana", "Coffee", "Apple", "Carrot", "Canolope")
    a = ArrayFilter(a, "^Ca|^Ap")

    ' Array to string works with both single and double dim arrays!
    Debug.Print ArrayToString(a)
End Sub

Cheap and reliable Node.js hosting starts at $3/month, and $1/month static HTML hosting

todar / VBA-Arrays

Programming Languages

Labels

Projects that are alternatives of or similar to VBA-Arrays

VBA Arrays

Other Helpful Resources

List of Available Functions

How to use

Examples