Hello all,
I need some advice here..
I've read that if you use vba to read from and write to a range in a sheet, it is much slower, and that it's a much faster (better?) way to load the range to an array first and work with that array instead of directly referring the range in the worksheet for stuff, every time. I tried loading a range to array but I found that the values are the only thing the array get populated with.
Something like:
What I need is to gather other properties about each cell in the range I load and not only cell values. Is it possible to load the individual cells as ranges so that I can then check whatever I need in each cell, via the array? Would that make it faster (better performance wise) as oppose to reading directly from the spreadsheet? Or is it be better if I create a custom type that holds only the specific properties (and load them to array)? I only need: front used in the cell, value of cell, cell formula, cell row, cell column, isRowHidden, and isColumnHidden
I only need these ones so loading an entire cell's (range) property set might be redundant..
Two more things to consider:
1. Range size can vary greatly - at times the range to load could be only a few cells, in other times few hundred or even thousand cells.
2. The loaded properties shouldn't only be static, that is, except for cell row and cell column (which would help me locate the cpecific cell to apply changes to), all other 5 properties should have an affect in the sheet itself (if I load a range where A23 is using Arial, I would be able to set a different font, via a userform).
I'm not concerned about any of the other things I've mentioned like userform and affecting the sheet via a form..
My concern here is:
What is the easiest/best way (performance wise, on the system) to work with potentialy large ranges, where you'd need to read and write to a spreadsheet?
Thanks!
Bookmarks