When I starting working with VBA 4 years ago arrays were my best friends, they’re easy to use, easy to understand, and getting data to / from an Excel sheet is a piece of cake as cell (0, 1) can map nicely to array position 0, 1. However I’ve developed the opinion that arrays should be avoided in favour of other forms of data lists / collections.
What’s Wrong With Arrays
It’s not that arrays are particularly wrong, it’s more that in comparison to the alternatives they’re limited and frustrating to use and support;
· Size declaration – You have to declare how big you’re array is going to be, even if you don’t know yet. This is a key limitation regardless of the fact you can ‘re-declare’ to increase the size.
· No in-built sorting / filters / search functions. An array is an array, it doesn’t have any methods associated with it. It doesn’t even have it’s only object type.
· It can only hold strings, numeric values, characters etc.
· Moving through an array is arduous with large amounts of data and there’s no standard ‘structure’ an array must take (I can declare a 0 based array, a co-worker may declare a 1 based array, and there’s no standard slot for each piece of information etc)
So What are the Alternatives?
ADODB.RECORDSETS
Instead of using arrays to store string and numerical values I’ve recently been using ADODB.recordsets (recordsets).
Recordsets are slightly more object oriented than arrays, as in essence they are collections of record objects, although they can be manipulated without even using or referring to the record object.
Benefits of Recordsets:
· No size declaration, you can type myRecordset.AddNew to create a new record and populate it with values.
· You define fields to specify what data is where, instead of having to remember which position in the array the address is you can just move to a record and use myRecordset.Fields(“myField”).value to retrieve it.
· You also don’t have to iterate through a recordset to output the contents to a worksheet:
‘ Move to the beginning of the recordset
myRecordset.MoveFirst
‘output the contents to a worksheet starting at cell A1
someWorksheet.Range(“A1”).value = myRecordset.GetRows()
· They have inbuilt search functions
· They’re easier to iterate over if you need to, or you can do bulk updates
· The list goes on....
There are of course limitations to recordsets, they’re frustrating to work out at times, and can be counter intuitive, but I’ve found the benefits of having a more structured set of data are fantastic.
Add Microsoft ActiveX Data Objects Library 2.8 to your VBA references and have a play.
OBJECTS AND COLLECTIONS
If you’ve declared a customer object and given that customer properties such as FirstName, LastName and CustomerID there’s no confusion on how to find that information.
If you combine that benefit with the use of the Collection object you make your code so much easier to understand, use and support.
Collections and object lists figure more in strongly typed object oriented languages such as C# than in VBA, but we can still take advantage of VBA’s generic collection object.
Dim c as New Collection
The more you object orient your code in VBA the more you’ll use collections, and the easier your life will become, you won’t have to pass around huge arrays and remember between procedures which attribute is where, as you’ll have strongly typed objects to work from and the re-use and quality of your code will vastly improve.
Overall object orientation is the best path to take for storing data, however, even the use of recordsets can drastically improve the speed, reliability, supportability and expandability of your code.
No comments:
Post a Comment