Array Class

Although VBA is a great language to manipulate office objects accordingly, but when it came to array handling  there are lack of method to handle array,

To resolve some of problems i collect and create some method and properties, see the content listed below which you can find in the class module .

Functions available in My Class Module for array handling .

Function Name Description Return Type
Function Offset By This Function You can Extract any Part of an Array.. It works like Excel Offset function Array Class Object
Function FilterArray as name implies , you can Filter array by this function Array Class Object
Function Sort You can sort an array . ACS and DESC order Array Class Object
Function IsArrayFilled you can check weather array is empty or not True/False
Function IsArray2Dimensional Return True if array is 2 D True/False
Function CombineArray Most Important Function to Combine Two Array, Either by Rows or Columns Array Class Object
Function Rows you can Extract a particular Row from an Array Array Class Object
Function Columns you can Extract a particular Columns  from an Array Array Class Object
Function Transpose Just Using Normal VBA Transpose Function to Transpose Array. Limited as VBA Transpose Array Class Object
Function GetUnique You can Get Unique Array List by this Function Array Class Object
Function CopyFromRecordset you can assign an Object input Array from a Recordset Nothing

you can find some properties of an Array object’s Listed below :

Count Read only Return Total Element Count of an array
ColumnsCount Read only Return Columns Count of An array
RowsCount Read only Returns Row Count of an Array
HTMLBody Read only Return HTML Body of an Array Table
How to Use the Class Object:
Sub HowToUseClassArray()
    ‘Create an object of Array Class
    Dim objArray As New ClsArray
    Dim VarResultArray
    ‘Filling Created Object Array
    objArray.InputArray = Range(“A1”).CurrentRegion
    VarResultArray = objArray.GetUnique.InputArray
End Sub
Thanks for Reading
Rajan Verma

6 Comments Add yours

  1. JohnS says:

    Great code! Please can you give an example of using HTMLBody? Thanks…

  2. Hi John,
    HTMLBody returns a HTML Script, you can save this Script in notepad as HTML Format and can see the array table in internet Browser


  3. if download links is not working you can download the class from here


  4. hamilton says:

    Rajan, nice work, can see a lot of use in future, however I am unable to figure out the usage of Function Sort properly. Is it possible to provide an example

  5. hi hamilton,
    thanks for comment

    here is an example to use Sort method of an array class

    Sub MSortTest()

    Dim varSortedArr
    Dim objArr As New ClsArray

    objArr.InputArray = Range(“A1”).CurrentRegion
    varSortedArr = objArr.Sort(1, xlAscending).InputArray

    Range(“A1”).Offset(, UBound(varSortedArr, 2) + 2).Resize(UBound(varSortedArr), UBound(varSortedArr, 2)).Value = varSortedArr

    End Sub

    you can sort a Two Dimension array on any column by using this function, in the above example ,
    you can understand how to use this method

    Rajan verma

  6. Sonveer Thakur says:

    Great Code Rajan… “Ye Kaam Karta Hai.”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s