VBA-Excel: Array Functions – Join() – Converts Array to String

Description:

 Join () Function returns a String that consist of array elements separated by the delimiter provided.

Format:

Join(arrArrayName[,delimiter])

Arguments:
    • arrArrayName
      • Manda­tory
      • Type: Array
      • Array Name which needs to be converted into string.

    Read moreVBA-Excel: Array Functions – Join() – Converts Array to String

    VBA-Excel: Array Functions – LBound and UBound()

    Description:

     LBound and UBound() Functions returns the starting index ( Lower Bound) and Ending index (Upper Bound) of an array.

    Format:

    LBound(arrArrayName [, dimension])

    UBound(arrArrayName [, dimension])

     

    Arguments:

    VBA-Excel: Create Array using Array() Function

    Description:

    You can create an array using Array() function. You need to provide all the elements which you want in an array inside Array() and store it in a variable and that variable will be converted into array.

    Format:

    Array(Element1, Element2, Element3…..ElementN)

    Arguments:

    • Elements
      • Manda­tory
      • Type: Any Data Type

    Read moreVBA-Excel: Create Array using Array() Function

    VBA-Excel: Fill Excel Range Values in a 2D Array

    You can fill a range values from an excel work book into a two dimensional array. All you have to do is assign range values to a variable and then that variable will become array

    Two Dimensional Dynamic array -1
    Two Dimensional Dynamic array -1

    Function FnFillValues()

    Read moreVBA-Excel: Fill Excel Range Values in a 2D Array

    VBA-Excel: Array Functions – Split()

    Description:

     Split() Function returns one dimensional array containing the substrings, after splitting the main string based upon the delimiter provided.

    Format:

    Split(strMainString [, delimiter[,intlimit[, vbCompare]]])

    Arguments:

    VBA-Excel: Arrays – Multi Dimensional Array

    Multi Dimensional array has more than 2 dimensions.

    For creating multi dimensional array, Follow the below status:

    • Declare the multi dimensional Array
    • Store values in array
    • Retrieve values from array

     

    Declare the multi dimensional Array

    Dim arrMatrix(1 To 9, 1 To 3, 1 To 3)

    Store values in array

    Read moreVBA-Excel: Arrays – Multi Dimensional Array

    VBA-Excel: Arrays – One Dimension, Dynamic Array

    Dynamic array means when size of the array is not fixed at the start of the program,  the size changes dynamically.

    Use ReDim, For Resizing the size of the array.

    If you just you ReDim to changing the size of an array, the already stored values in array will be deleted, if you want to keep the old values of an array while resizing the array, use Presereve keyword along with ReDim

    ReDim Preserve arrArrayName(newSize)

    Example: Store all the available values in column A, into an Array.

    For creating one dimensional static array, follow the below steps

    • Declare an Array
    • Resize the array
    • Store values in array
    • Retrieve values from array.

    Read moreVBA-Excel: Arrays – One Dimension, Dynamic Array