Skip to content

😎 Array functions that are similar JavaScript functions. Example: Push, Pop, Shift, Unshift, Sort, length, toString.

License

Notifications You must be signed in to change notification settings

todar/VBA-Arrays

Folders and files

NameName
Last commit message
Last commit date

Latest commit

Β 

History

48 Commits
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 

Repository files navigation

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.

Buy Me A Coffee


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

  1. Import ArrayFunctions.bas file.
  2. 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

About

😎 Array functions that are similar JavaScript functions. Example: Push, Pop, Shift, Unshift, Sort, length, toString.

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Sponsor this project

Packages

No packages published

Languages