lists in VBA? 6

[1DMF]

How do i declare and use a List in VBA?

I can't seem to find a simple String List collection in VBA ?

I'm assuming the basic List collection exists in VBA?

It doesn't seem to exist in the intelisense as an option?

Do i need to just create a variable as variant in VBA?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

a simple String List collection
What do you mean ?
You may use an Array, a ListBox, a Collection, a Scripting.Dictionary, ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

What do you mean ?

well in VB it would be...

Dim aList As New List(Of String)

Can't seem to find 'List' as a collection type?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

And how a "List" should be different than an Array ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

well as i understand it arrays in VBA have to be dimensioned and indexes used to add an element to the array, list don't and have built in methods for adding data or removing items from the list and designed specifically for 'for each' loops.

i want a List of strings, not an array of strings as i'm rewriting my code to be Object Orientated, a standard array is not an object, a list is.

i take it from all this List's don't exist in VBA?




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

So, use either a Collection or a Scripting.Dictionary object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

hmmm, I thought collection was the library type for 'Collections' not an actual object data type.

I seem to be going round in circles having studied Java, work daily with VBA and am currently studying VB.

So similar yet so different, thanks for pointing me in the right direction.

Though it seems you don't declare the data type of the elements in the Collection?




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Lists are not supported in vba. You have a couple of options here depending on what you want to do.
In VB/VBA arrays are faster and more efficient than working with collections. But collections are easier

1)If working with arrays you can build a module to do the common array functions. I have a very large module I keep in my code library to allow you to do things like
- dynamically add to an array\
- dynamically remove
- dynamically dimension
- sort
- remove from location
etc.
Some of the code I wrote, some I got from others but I have about 30 common array functions.

2)You can just use a collection or a dictionary object. The dictionary is like a collection but with added features. See:

http://support.microsoft.com/kb/187234/en-us

3)But if you really want to mirror a list I would build it as a custom collection. you should be able to fake all the properties and methods. You could also build a custom listbox/combobox class that allows you to bind to your custom "list".

See in this article how to build a custom collection.

http://www.databaseadvisors.com/new...ng custom collections in microsoft access.asp

Basically you fake the add, remove, count, item methods/properties

 

[1DMF]

Hey Majp,

I think 'collection' will do the job, seems to be of type variant and has the main methods I want such as 'add', 'remove', 'count' etc.

I simply want to add email addresses to a list (so no duplicates if using a list object) and then later loop using 'for each' and attach to the BCC field.

I've created public properties and methods to handle all this and am now working on the actual EmailWrapper.send method.

I then want to create two versions of the EmailWrapper class, one using CDO 1.2.1 and one using Outlook Object Model, then if possible in VBA check what version of office is running and dynamically reference the right EmailWrapper class, that way I can rewrite the main DB code to use the EmailWrapper class for my email requirments which won't change and just dynamically link in the correct class depending on whether CDO is present or not.

Seems to me the only way round the nightmare that is MS Outlook 2010 and the missing CDO 1.2.1. while having an office running mixed versions of MS Office 2003, 2007 & 2010.






"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[strongm]

>specifically for 'for each' loops

VB arrays work fine with For Each loops

>well in VB it would be...
>>Dim aList As New List(Of String)

Not in Classic VB or VBA it wouldn't. That's a VB.NET construct (and .NET class library object)

Mind you, you can use various of .NET's collection objects in classic VB ... here's one that closely matches your suggested requirements:

[blue]    Dim myList As Object
    Dim myItem As Variant
    
    Set myList = CreateObject("System.Collections.ArrayList")
    myList.Add "Alpha"
    myList.Add "Gamma"
    myList.Add "Beta"
    
    Debug.Print myList(0) ' extract specified item from list, just like an array item

    For Each myItem In myList ' iterate List
        Debug.Print myItem
    Next
    
    myList.Sort
    
    For Each myItem In myList ' iterate List
        Debug.Print myItem
    Next
    
    myList.Reverse
    
    For Each myItem In myList ' iterate List
        Debug.Print myItem
    Next[/blue]

 

[1DMF]

i'm confused, i'm doing a straight VB course not .NET and that's how we have been taught to create List collections?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[strongm]

For marketing reasons Micrsoft quietly dropped the .NET from VB.NET some time ago, so it is now known simply as VB. It is, however, still VB.NET and nothing to do with classic VB

The Dim statement you gave is a VB.NET List constructor. So yes, either you are confused or your course is confused.

Either way, there is no List object or class in classic VB/VBA

As MajP has suggested, if you want to stick with (classic) VB classes only, then a Collection is pretty much the closest thing to a .NET List. The VBScript library's Dictionary object is closer still (and pretty much equivalent to Java's HashMap). Neither support all the functionality of the .NET List class, e.g methods such as sort, reverse and insert.

 

[MajP]

Strongm,
Any chance you can explain this a little more? Or point to some references? I googled for some more understanding, but all examples are to the more traditional references to Excel, Word or other automation servers or FSO.

When I do this:
CreateObject("System.Collections.ArrayList")
is this a .net class exposed as a com object?
I know how to use it to return an automation object, but guess I did not really understand how it works.

Mind you, you can use various of .NET's collection objects in classic VB

How can you determine what is exposed, or make something exposed. Can I create a custom class in .net and expose it in vba? Thanks.

 

[strongm]

>is this a .net class exposed as a com object?


Yep, but it isn't really documented.

You can see all the .NET System classes that are exposed as (late binding) ActiveX objects starting in the registry at HKEY_CLASSES_ROOT\System.AccessViolationException through to HKEY_CLASSES_ROOT\System.Version

Once you've found something you might be interested in you can look it up in MSDN.

So, for example, my ArrayList (List itself is not directly available, but ArrayList implements the same interface, IList) is System.Collections.ArrayList, which is documented on MSDN here. Note that certain limitations of VB mean that you cannot use all the functionality of the class - for example you can only use the default New constructor, since VB cannot pass a parameter with New

 

[1DMF]

Well what d'ya know eh, been learning .NET and never knew it

I used collection and it's working fine, so thanks for the help guys.

i appreciate the 'Dictionary' already covered this on the course as part of the 'Collections' framework, i find it an odd name, it's hashmap key/value pair , a dictionary gives far more info on a word than you would associate with a hashmap

Directory might have been a better choice of word, but MS has always been a bit bewildering with some of their naming conventions!

Though it does seem I've started learning VB at the right time, looks like classic VB had some serious limitations for even simple things such as collections.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[strongm]

Somehow didn't actually put the link in to the ArrayList documentation on MSDN in my last post, so here it is:

http://msdn.microsoft.com/en-us/library/system.collections.arraylist.aspx

 

[joel009]

1DMF - What/where/how are you taking classes for VB (VB.NET)? I need some of that!

Joel

 

[1DMF]

It's via the Open University..

http://www.open.ac.uk

I'm studying for a Diploma in Computing, my first 'compulsary' module was 'Object Orientated Programming with Java (M255)'.

I'm currently doing my second module (you can choose out of a bunch of subjects), this one is 'Designing Applications with Visual Basic (MT264)'

Not sure what i'll study next as i need 4 modules to make the 120 points needed for the Diploma, but it's the best thing i did sending myself back to school.

I have already implemented OO production code in my web app using OOP (Object Orientated Perl) as well as my first class in VBA, which was released yesterday in the production application as an EmailWrapper ready to move over to MSO2010 and replace CDO.

without the basic grounding in OO design and coding principles, i'd still be scratching my head wondering how the hell i would make all this stuff work!

Oh and of course none of it would have been possible without the support and encouragement from the fantastic peeps here on Tek-Tips, I owe TT and its members a serious debt of gratitude!



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[kjv1611]

I just learned 2 things (unless I just forgot - highly possible ). I just learned from strongm about the modern "VB" really being "VB.NET" with the naming of .Net dropped.

Second thing I learned, though it does seem oddly familiar, is about the Open University. Best I can tell so far... is that please really just about free online classes? At first I thought it was sort of a "free trial" and then pay when you get serious. Either way, it looks like a pretty good program.

By the way, one of the Ivy League schools here in the states has some of their computer programming and introduction to computers classes available online for free (the videos of said classes, and notes I think). But I can't find the link at the moment.

 

[1DMF]

There are free courses and tutorials out there, but i'm not sure they give you a recognised academic qualification at the end of them.

The OU has been going for years, and is part of British society and culture even if people don't realise, they are closley linked with the BCC and are responsible for many 'learning' type programs.

They are certainly worth considering if you want to learn at your own pace, without attending school/college (well except for tutor days, but even those are optional!) and the pricing isn't too bad either.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts