Source – http://technet.microsoft.com/en-us/library/bb727078.aspx
Try to avoid Redim arrays. As far as performance is concerned, if you have a machine that is constrained by physical memory size, it's much better to set the initial dimension of the array to its worst-case scenario—or to set the dimension to its optimal case and Redim as necessary. This does not mean that you should just go out and allocate a couple of megabytes of memory if you know you aren't going to need it.
The code below shows you gratuitous use of Dim and Redim.
- Dim MyArray ()
- Redim MyArray (2)
- MyArray (0) = "hello"
- MyArray (1) = "good-bye"
- MyArray (2) = "farewell"
- ...
- ‘Some other code where you end up needing more space happens, then ...
- Redim Preserve MyArray (5)
- MyArray (3) = "more stuff"
- MyArray (4) = "even more stuff"
- MyArray (5) = "yet more stuff"
It is far better to simply Dim the array to the correct size initially (in this case, that's 5), than Redim the array to make it larger. You may waste a little memory (if you don't end up using all of the elements), but the gain will be speed.
