Save Cell Properties into an Array

[dasmith]

I often read and write chunks of Excel data by reading them into a
variant array, process the data, and then write the changes back. As
most know this is a very effecient method.

I would like to do the same think with a range of cell properties. For
example I would like to be able to individually check which cells on a
sheet contain a formula. I am currently reading the cells into a range
object and looping through each cell in the range. The ones with a
formula I am applying a special format.

Is it possible to read the .HasFormula property into a variant array
and loop through the array instead of through the range. I think it
might be faster but cannot get it to work.

Any help or direction is appreciated.
Thanks
Darryl Smith

 

[Tom Ogilvy]

You can read the formula into an array, but not any properties.

You can then check each item for a leading =

constants will also be read into the array.


v = Range("A1:F30").Formula

from the immediate window:

v = Range("A1:F30").Formula
? v(1,1)
=TRUNC(RAND()*100+1)
? v(1,2)
49

 

[Guest]

The only way to do it would be to loop through the range of cells and create
the array by accessing the property of each cell (to the best of my
knowledge). This would defeat the purpose of making the code faster and more
efficient though, so I would have to say it will not work for you.

 

[Norman Jones]

Hi Darryl,

I am currently reading the cells into a range
object and looping through each cell in the range. The ones with a
formula I am applying a special format

'=============>>
Public Sub Tester004()
Dim Rng As Range

On Error Resume Next
Set Rng = Selection.SpecialCells(xlFormulas)
On Error GoTo 0

If Not Rng Is Nothing Then Rng.Font.Bold = True

End Sub
'<<=============

 

[Guest]

This must be why they pay you the big money... I never even thought to do
that. As a guess though how much faster would it be than just looping through
the range and adding a format as the OP requested. Lots? A little? Not much
if any?

 

[Tom Ogilvy]

Personally I would use specialcells like Norman Suggested. So it wouldn't
be a consideration.


Activesheet.UsedRange.SpecialCells(xlFormulas).Interior.ColorIndex = 3

as an example.

Still the array approach is useful for other things and I have used it
before. However, not for properties that I am aware of.

To save you some time, v = Range("A1:F10").Text

doesn't work. V holds NULL

 

[dasmith]

Thanks all for replying.
I use the technique for reading formulas into an array all the time but
only to save them to a database. I never thought about using to check
to see if the cells contained a formula. I also use special cells all
the time which is extremely powerful to filter you data and should be
considered one of the top performance techniques if you have to read
specific data from a sheet. I typically use it with a filter to get
rows of a specific type then extract a range by selecting
..SpecialCells(xlVisible)

Again I missed applying the xlFormulas option to special cells in this
case.

The main question I was unsure about was saving properties to an array,
which was answered by Tom. If he thinks it cant be done, then it cant
be done. This is unfortunate as it would be a powerful method of
passing Excel properties between tiers. I like to keep my business
tier knowing nothing about Excel. When there are cases when business
logic is included in the way a cell is formated (ie color, hidden,
locked), I find I sometimes do business logic in the UI tier.

As far as the techniques to solve this problem were presented, I did
the following timing tests on a large block of cells:

Test 1: Array of Formulas = .75 sec
Test 2: Loop through cells and check .HasFormula = 2.6 sec
Test 3: Loop through range of cells = 1.62 sec
Test 4: SpecialCells(xlFormulas) = 0.09 sec (Normans suggestion)
Test 5: SpecialCells(xlformulas) and then loop through range = .34 sec

Test 4 obviously the fastest method. If you need more flexibility,
combine it with looping through the range for additional steps you can
do to the range of cells.

Thanks again Tom and Norman for you insight.

Darryl Smith