Thread: Please look at my VBA ArrayList custom class. Feedback is appreciated!

Hello,

I've done some searching on this forum and others (Mr Excel and OZgrid), and I have not found any custom ArrayList class that works in VBA.

So, I recently developed one. It is based roughly on the source code of a Java arraylist, as seen here:

http://developer.classpath.org/doc/j...st-source.html

It functions similarly to a real ArrayList or a vba collection (you can add or remove elements quite easily). It still may be a little clunky however.

It includes a QuickSort algo that is able to sort a maximum of roughly 15,000 floating point 32-bit random numbers in about 15ms.

The class is about 400 lines of code, so I was unable to paste it directly here. Here is the pastebin version, sorry about that.

http://pastebin.com/Tg1FUYM8

Enjoy, and please provide constructive feedback. Thanks!

Quick update: I've done a lot more on this VBA ArrayList (sort of an ongoing project of mine) since I posted this thread initially. It is now 40% faster than the mscorlib.ArrayList at sorting elements.

I uploaded the source to my website at:
http://www.brianweidenbaum.com/wp-co.../ArrayList.cls

For the latest updates on this project, visit http://www.brianweidenbaum.com/projects/vba-arraylist/

Hi D

This is a 2nd post. Its purpose is to inform only.

I created a class called Providers whose initialize sub loads the following.
(the index is incremented internally in the property)

    ProviderName = "Office2007"
    ProviderValue = "Provider=Microsoft.ACE.OLEDB.12.0;"
    
    ProviderName = "Office2003"
    ProviderValue = "Provider=Microsoft.Jet.OLEDB.4.0;"

    ProviderName = "SQL_SERVER_OLEDB"
    ProviderValue = "Provider=SQLOLEDB.1;"

    ProviderName = "SQL Native Client 9.0 OLE DB provider"
    ProviderValue = "Provider=SQLNCLI;"

    ProviderName = "SQL Server Native Client 10.0 OLE DB Provider"
    ProviderValue = "Provider=SQLNCLI10;"

    ProviderName = "MySQL"
    ProviderValue = "Driver={MySQL ODBC 3.51 Driver};"

    ProviderName = "OracleObsolete"
    ProviderValue = "Provider=msdaora;"

    ProviderName = "Oracle"
    ProviderValue = "Provider=msdaora.1;"

    ProviderName = "Oracle9i"
    ProviderValue = "Provider=OraOLEDB.Oracle;"

I then loaded the class to both your ArrayList and a regular VBA Collection.

It all worked:

Note:
I don't comprehend how to call the LastIndexOf method.

Here is the sub. The WB is attached - jic.

Public Sub MyTest()

    Dim oAR As ArrayList
    Dim oProvider As ProviderClass
    Dim vProvider As Variant
    Dim vOut As Variant
    Dim vOutName As Variant
    Dim vOutValue As Variant
    Dim i As Integer
    Dim iF9 As Integer
    Dim oCollaction As New Collection

    '------------------
    '   Array List Class
    '------------------
    Set oAR = Nothing
    Set oAR = New ArrayList
    
    '------------------
    '   Provider Class
    '------------------
    Set oProvider = Nothing
    Set oProvider = New ProviderClass
    
    '------------------
    '   Test
    '   Return Values of oProvider
    '------------------
    oProvider.ActiveIndex = 3
    vOut = oProvider.ProviderName
    vOut = oProvider.ProviderValue
    oProvider.ActiveIndex = 7
    vOut = oProvider.ProviderName
    vOut = oProvider.ProviderValue
    
    '------------------
    '   oProvider --> Variant --> Not Explored
    '------------------
    Set vProvider = oProvider

    '------------------
    '   Provider Class --> Array List Class
    '------------------
    oAR.Add oProvider
    
    '1
    vOut = oAR.Length
    
    '------------------
    '   Retreive Values from ArrayList Class
    '------------------
    For i = 1 To 9
        With oAR
            .Item(0).ActiveIndex = i
            vOutName = .Item(0).ProviderName
            vOutValue = .Item(0).ProviderValue
            vOutName = .Item(.Length - 1).ProviderName
            vOutValue = .Item(.Length - 1).ProviderValue
        End With
    Next
    
    'I don't know how to use - Error 438 - Object doesn't support this property or method
    If 1 = 2 Then
        vOut = oAR.LastIndexOf(oProvider)
    End If
    
    '------------------
    '   Provider Class --> Collection
    '------------------
    oCollaction.Add oProvider
    '1
    vOut = oCollaction.Count
    
    '------------------
    '   Retreive Values from Collection
    '------------------
    For i = 1 To 9
        With oCollaction
            .Item(1).ActiveIndex = i
            vOutName = .Item(1).ProviderName
            vOutValue = .Item(1).ProviderValue
            vOutName = .Item(.Count).ProviderName
            vOutValue = .Item(.Count).ProviderValue
        End With
    Next
    
    iF9 = 1

    Exit Sub
End Sub

regards
John

Originally Posted by mikerickson

What properties and methods did you give the object?
Can you give an example of how it would be used?
How does it differ from the Dictionary object?

1) Sorry I don't have a complete list of properties and methods on hand, but I did try to emulate as much of the System.Collections.ArrayList as possible. You can glance thru the source code if you are really interested. There are around 25 total methods and properties.

2) You can use it almost the same way as you would use a Collection, except that I think Collections can use Key-Value pairs, and the arraylist cannot, which ties into your 3rd question...

3) Dictionaries, as far as I know, are associative arrays like hashmaps that involve key-value pairs. This arraylist doesn't support key-value pairs.

Originally Posted by JohnM3

Hi d

Thanks for posting your ArrayList class. However, I'm not the brightest light bulb in
the room so I am going to need some _basic_ help. OK?

(a) I created a new Excel(2007) macro WB.
(b) Added a module (relax) and put in a stub Sub with a string variable that said
See Spot Run
(c) Went to your site.
(d) Added a Class Module
(e) Copied the code (all of it) into the Class Module
(f) I got VBA errors on the lines below

'VERSION 1.0 CLASS
'BEGIN
' MultiUse = -1 'True
'End
'Attribute VB_Name = "ArrayList"
'Attribute VB_GlobalNameSpace = False
'Attribute VB_Creatable = False
'Attribute VB_PredeclaredId = False
'Attribute VB_Exposed = False

My questions are:

1 - Am I supposed to comment out the lines above?
2 - Am I supposed to put them someplace else?

I apologize in advance for these basic questions - but I had to ask.

I actually intended for people to right click on the link, download the arraylist.cls file, and then import the file into the VBE. If you do a File->Import File in VBE, and get the arraylist.cls, it will all work automatically- a new class module will appear that compiles on the first try. Sorry for not making that clear on my website. If you do just want to copy and paste the code into your own class module, you will have to delete those first few lines that are automatically generated whenever you save a .CLS file.

I am not grasping HOW TO USE the .arrayCopy method
and would need help.

My arrayCopy method is an imitation of the .Net Array.Copy class method, which is like this:
Copy(Array array1, Int32 index1, Array array2, Int32 index2, Int32 count)
Copies a range of elements from an Array starting at the specified source index and pastes them to another Array starting at the specified destination index. The length and the indexes are specified as 32-bit integers.

I originally planned on keeping it as a private method solely used for moving around ranges of elements internally, but I figured it was somewhat useful, so I made it public.

EDIT - New Post - Updated Sub Main

(1) Added a Range to ArrayList
(2) Added a Variant that contained a Range to ArrayList

Bother (1) and (2) were ACCEPTED but it created errors
in

.Contains
.GetDistinctValues

The Contains function is the main culprit here (getdistinctvalues depends on Contains). Contains is using the syntax:

For each e in elementData
  If e = TestElement Then
    'dostuff
  End If
Next e

Tests of comparison generally cause trouble with objects in VBA, as I've learned. Similar problems will occur if you have an arraylist of objects, and you try using the Sort method, which tries to determine if two objects are <, >, =, etc. Sorry I don't have a fix right now, but feel welcome to try fixing it on your own. I will be setting up a page on sourceforge or github for this project after this weekend.

Trapping Objects

Recall I added a range to your ArrayList Class
but it broke the Contains Method

Perhaps it is NOT the intention of the class to accept
a range object - OTOH - if it is OK - maybe a 8204
check against each element might be required?

The 8204 check seems like a good start. It's unfortunate that using objects in VBA is really clunky.

Thanks a lot for your input. Have a happy New Year!

Originally Posted by JohnM3

Hi D
I don't comprehend how to call the LastIndexOf method.

John, LastIndexOf, just like Contains, uses the syntax that looks like

For each e in elementData
  If e = TestElement Then
    'dostuff
  End If
Next e

It seems that we need to conditionally change the execution of the code depending on whether the element being tested for equality is an object. I'll see what I can come up with next week. Again Happy New Year!

Hi D

Thanks for all your replies and explanations. Along the way, I forgot to say NICE CODE.
Thanks for sharing it.

regards
John