VBA: Array Examples
MS has a new KB article with some basic code examples for filling an array with spreadsheet data, and writing data to a spreadsheet from an array:
Sample Visual Basic macros for working with arrays in Excel
Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Get answers right away at our AE Excel Support Forums!
on thing that caught me out with arrays a while back was in vb6 you can use array1 = array2 to copy an array, but not in vb5. Depending on how big the array is i now code using a VB5 method, or use an if, if it’s a big array – the VB6 way is quicker.
Also when working with the worksheet it’s worth baering in mind the SpecialCells method which can save time (http://j-walk.com/ss/excel/tips/tip07.htm)
Thanks for the link.
From the kb article, transpose an array was new to me.
Yeah, I only seen that a few weeks ago too( in Excel 2002 VBA), you can use it to fill a combo etc – thats the example given in the book. I think that’s what Bullen, et al. mean when they talk about VBA developers and Excel developers – theres no way would have sat down and thought, mumm, i could use the transpose wsk fuction here, but hey, you live and learn!
How to convert a word to a number