Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save Cell Properties into an Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save Cell Properties into an Array
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 -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save Cell Properties into an Array
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. -- HTH... Jim Thomlinson " wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save Cell Properties into an Array
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 '<<============= --- Regards, Norman wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save Cell Properties into an Array
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? -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save Cell Properties into an Array
Personally I would use specialcells like Norman Suggested. So it wouldn't
be a consideration. Activesheet.UsedRange.SpecialCells(xlFormulas).Int erior.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 -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... 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? -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save Cell Properties into an Array
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto show properties dialog box on save | Setting up and Configuration of Excel | |||
Excel Properties Tab on Doc Save | Excel Discussion (Misc queries) | |||
Save with preset Printer Properties | Excel Discussion (Misc queries) | |||
Save As Dialog does not prompt for Web File Properties | Excel Programming | |||
Changing the File Save Properties | Excel Programming |