General Array Question

[Igwiz]

Hi Guys,

If you are using VB to manipulate Excel data, is it efficient to load the values into arrays first. I have found that in this particular exercise that I am doing it is much easy to do this rather than continually referencing cells. Also, if you refer to a cell value often, is it more efficient to load it into a variable. Given how quickly computers run, it often strikes me how slowly Excel runs through loops that heavily access cells.

Many thanks for your thoughts.

Ig

 

[Jooky68]

It all depends on what you are going to be loading into the arrays. There are sometimes when there is room for a lot of error from loading data in the arrays, while if you have named cells and reference the cells it is much easier. But if you are going to be using large loops and keep referencing cells on different sheets, it is MUCH more efficient to load the data into an array first.

 

[xlbo]

If you need to check a lot of cell values, it is certainly easier to load them into an array 1st
Especially as you can use:

dim arr1 as variant, myRange as range
set myRange = activesheet.range("a1:A" & range("A65536&quot.end(xlup).row)

arr1 = range(myRange).value

to load the range values
then ref by

for i = lbound(arr1) to ubound (arr1)
do stuff
next i

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[combo]

Frequent refering to cells (in reality - ranges) can make code running very slow. But the speed depend on a quality of code, in many cases can be significantly improved.
Here are some hints concerning working with ranges, especially when looping:
- set (temporarily) Application properties: ScreenUpdating=True, Calculation=xlCalculationManual to avoid unnecessary calculations and repainting of the screen,
- use objects dimmed as range: Dim Rng1 As Range,
- avoid activating/selecting worksheets/cells,
- operate on a whole range at once. The simple copy formula is:
Range(Rng1).Copy destination:=Range(Rng2), formula to assign value: Range(Rng1).Value=x (or, simply, Range(Rng1)=x, as Value is the default property),
- create collections of subranges, rows: Rng1.Rows, columns: Rng1.Columns, or subsets of cells: UsedRange.SpecialCells(xlCellTypeConstants,xlNumbers). Loop through them using: For Each...Next.

combo

 

[Igwiz]

Thanks guys. Some interesting points. I am trying to keep my coding efficient which is why I've started using arrays. I think my main problem had been the activating of workbooks.

combo-shouldn't the first line of your tips be ScreenUpdating:=False!?

Ig

 

[combo]

Igwiz, of course it should be set to False, thanks.

combo