Daily posts of Excel tips…and other stuff
Archive for the ‘Collections’ Category.
I’ve never noticed this before, but the Key argument of the Add method for a collection is not case sensitive. That is, this throws an error:
Sub testcollection()
Dim col As Collection
Set col = New Collection
col.Add "me", "me"
col.Add "Me", "Me"
End [...]
Well, it’s almost next year and you know what that means: New vendor files. I need to create a couple hundred labels for next year’s vendor files. I started by exporting all of the bills from all of the vendors for this year. Next, I’m going to count and sum the bills. [...]
I write some data to an Access database, after which I retrieve the Autonumber that Access generates. Prior to writing the data, it sits in a class and all of the classes sit in a collection. The collection needs a unique string for each entry, so I create one until I can the [...]
You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.
What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, [...]
When I was creating sample data for Returning a Limited Collection…, I had to fill a bunch of collections with custom classes. Instead of creating the class and adding it to the collection, I created a function that returned the class and use the function call in the Add method. Here’s an example:
Set [...]
I have a custom class, CGroup, which has children of the type CContact (another custom class). I need to loop through all the CContacts for each CGroup and print out those that meet a certain condition - in this example, those with State property of “NE”. Not all CGroups will have a CContact [...]
Hi everyone,
Some time ago I published a generic Undo handler for use in Excel VBA on this blog.
In that article I promised to write up how the internals of the technique work.
I’ve published a new article on my website that explains how things work.
Also, I’ve added some functionality to each of the pages of the [...]
Hi everyone,
I’ve been fooling around trying to get a tabledriven menu for a
userform to work.
It uses a table on a worksheet to build the menu, like in this screenshot:
Obviously I used a couple of things from Stephen Bullen, Rob Bovey and John Green’s famous book “Professional Excel Development” to build this.
I’m almost there [...]
