Efficent way of setting sparse array of formulas?

[Josh Sale]

I'm looking for an efficient way to set the formulas on a range of cells.

My situation is that I have a VBA array that contains one formula in R1C1
format for each cell of the array. Some of the cells in the array contain
empty strings indicating that the corresponding cell has no formula.

If is use code like:

myRange.FormulaR1C1 = myFormulaArray

Then the cells associated with elements of the array that contain empty
strings lose their value ... and I want to preserve the values in those
cells.

Right now, I'm using two nested loops to iterate through myFormulaArray
looking for non-empty strings and then setting the formula's a cell at a
time. It works but is slow as the array grows in size.

I've looked at the FormulaArray property and its description looks
tantalizingly like what I'm looking for. But it doesn't seem to work either
.... but maybe I'm doing something wrong here since I don't really understand
what a FormulaArray is.

Anybody got any suggestions?

TIA,

josh

p.s. Ideally the solution would work on XL97 and later ... but I could live
with a solution that only worked with later versions of Excel.

 

[Tushar Mehta]

Consider the following algorithm:

Create a VBA array, say OrigArray, that contains the values of the
myRange. This is a single statement along the lines of

dim OrigArray
OrigArray=myRange.values

[though there might be a limit of how many elements XL97 supports.]

Now, loop through MyFormulaArray and for each element that should
remain unchanged replace it with the corresponding element in
OrigArray.

Finally, transfer MyFormulaArray to myRange as you are doing right now.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

As far as I know, FormulaArray will only return a set of values, not formulas.

One thought (untested): perhaps you could adapt the "simple" way like this:
1) Copy your range (myRange) to a new location (temporarily - you could even
create a new sheet for this and delete the sheet when done) - I will call
this new range CopiedRange
2) Now try CopiedRange.FormulaR1C1 = myFormulaArray
3) Using CopiedRange.SpecialCells xlCellTypeFormulas you could find the
cells that now contain the formulas. Using a For Each you could loop through
only these cells and copy them to the corresponding cells in the original
range.
4) Clean it up by deleting the temporary range and you should be done.

Like I said I haven't tested this to see if there are any unexpected
pitfalls, but it might be worth a try. It would save a whole bunch of
looping and presumably execution time.

 

[Josh Sale]

Thanks for the suggestion Tushar.

I guess I would have to time it to see if its really any faster since your
algorithm still requires the nested loop and examination of each element of
the array. What it does do is to remove the setting of each individual cell
of the worksheet where there is an array.

Without testing, my guess is that the payback would verify depending on the
sparsity of the formulas in the array. For example, if 1 in 1000 elements
contain a formula, then my existing solution might be preferable. However
if 5 in 10 contain a formula then your solution might be preferable.

I guess I'm goint to have to do some testing.

josh

Consider the following algorithm:

Create a VBA array, say OrigArray, that contains the values of the
myRange. This is a single statement along the lines of

dim OrigArray
OrigArray=myRange.values

[though there might be a limit of how many elements XL97 supports.]

Now, loop through MyFormulaArray and for each element that should
remain unchanged replace it with the corresponding element in
OrigArray.

Finally, transfer MyFormulaArray to myRange as you are doing right now.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I'm looking for an efficient way to set the formulas on a range of cells.

My situation is that I have a VBA array that contains one formula in R1C1
format for each cell of the array. Some of the cells in the array
contain
empty strings indicating that the corresponding cell has no formula.

If is use code like:

myRange.FormulaR1C1 = myFormulaArray

Then the cells associated with elements of the array that contain empty
strings lose their value ... and I want to preserve the values in those
cells.

Right now, I'm using two nested loops to iterate through myFormulaArray
looking for non-empty strings and then setting the formula's a cell at a
time. It works but is slow as the array grows in size.

I've looked at the FormulaArray property and its description looks
tantalizingly like what I'm looking for. But it doesn't seem to work
either
... but maybe I'm doing something wrong here since I don't really
understand
what a FormulaArray is.

Anybody got any suggestions?

TIA,

josh

p.s. Ideally the solution would work on XL97 and later ... but I could
live
with a solution that only worked with later versions of Excel.

 

[Josh Sale]

Very creative Dale! Thanks I like it.

josh

As far as I know, FormulaArray will only return a set of values, not
formulas.

One thought (untested): perhaps you could adapt the "simple" way like
this:
1) Copy your range (myRange) to a new location (temporarily - you could
even
create a new sheet for this and delete the sheet when done) - I will call
this new range CopiedRange
2) Now try CopiedRange.FormulaR1C1 = myFormulaArray
3) Using CopiedRange.SpecialCells xlCellTypeFormulas you could find the
cells that now contain the formulas. Using a For Each you could loop
through
only these cells and copy them to the corresponding cells in the original
range.
4) Clean it up by deleting the temporary range and you should be done.

Like I said I haven't tested this to see if there are any unexpected
pitfalls, but it might be worth a try. It would save a whole bunch of
looping and presumably execution time.

 

[Guest]

If your main concern is speed = efficiency look again at Tushar's answer.
moving data into an array / working /and writing back is almost always
faster than mechanically doing things in "Excel proper".