Does Excel VBA allow you to use Collections like VB
Posted by Dominic on May 30, 2001 2:46 PM
I am not a VBA fundi, but have been told that it is better to use collections rather than have a macro refer to an Excel table for info. Can this be done in Excel VBA and if yes how?
Posted by Russell on May 30, 2001 4:10 PM
VBA does support collections, but what exactly are you trying to do? To use collections, you must read in the data from somewhere, so using collections may be redundant.
Posted by Dave Hawley on May 30, 2001 9:06 PM
Hi Dominic
Excel is very much Object orientated and has a lot of Objects. Most of these Objects are part of a Collection. For example the Workbook is part of the Workbooks collection the Worksheet is part of the Worksheets collection etc. You would normally use these Collections to access or manipulate a particular Object. For Example
Dim wWsht as Worksheet
For Each wWsht in ActiveWorkbook.Worksheets
if wWsht.CodeName= "Sheet1" then msgbox "Found it"
Next wWsht
But it really depends on what you are wanting to do and what your interpretation of Table is.
Dave
OzGrid Business Applications
