variant array with formula strings to range formulae

[Amedee Van Gasse]

Hello,

I inherited an Excel monster with lots of stale code, recorded macros,
goto-statements (!), you know what I mean. A tumor that has grown and
festered over many years.

So far I've been able to clean up a lot, and speed it up at the same
time. But there is one thing where I get stuck.

Let's say column A contains the following strings:
£='[##]worksheet_name'!$E$4
£='[##]worksheet_name'!$E$5
£='[##]worksheet_name'!$E$6
£='[##]worksheet_name'!$E$7
...
Actually, these are not hardcoded strings but values of an =INDIRECT()
formula. Don't ask, yer head will asplode.

B, C and D are blank except for the first row, which contains the name
of other .xls files, who are always in the same directory as the main
file. Column A functions as a template for columns B, C and D.

The VBA code has to generate the formulae for B, C and D. How did they
do that?

1. copy column A4:A200 to B4:B200 (range(A).select, selection.copy,
range(B).select, selection.paste)
2. in column B, search & destroy^Wreplace every occurence of [##] with
[fileB.xls] (using the replace method on the range object). fileB.xls
is in the top row of column B.
3. in column B, search & replace every occurence of £= with = (again
using the replace method on the range object)
Repeat for column C and D.

3. is actually quite clever, because before the pasted text was just
text, but by removing the £ at the front of the text, Excel evaluates
it as a function.
But in 2. something goes wrong when I changed it from the short
filename to the full filename with ActiveWorkbook.Path: part of the
filename was repeated inside the formula. Looked really buggy...

My fix was to read in column A from range to variant array, loop
trough the array, make the text replacements in the formula template
string, and set the cell.Formula for each individual cell in column B.
My workaround works 100% correct, I'm confident about that. But...
sloooow. Of course, I have to iterate trough every cell in column B to
set the formula. If it were just a value that I had to write, it would
be a simple matter of saying colB=varArray. Bada bing, bada boom! No
such luck.

I have done some research, but I didn't find a way to set the formulae
of an entire range from an array. It seems like it can only be done
with values. Is that true?

Worst case scenario would be that I would have to restore the copy/
paste code and try to fix it, not rewrite it...

 

[JW]

Hello,

I inherited an Excel monster with lots of stale code, recorded macros,
goto-statements (!), you know what I mean. A tumor that has grown and
festered over many years.

So far I've been able to clean up a lot, and speed it up at the same
time. But there is one thing where I get stuck.

Let's say column A contains the following strings:
£='[##]worksheet_name'!$E$4
£='[##]worksheet_name'!$E$5
£='[##]worksheet_name'!$E$6
£='[##]worksheet_name'!$E$7
...
Actually, these are not hardcoded strings but values of an =INDIRECT()
formula. Don't ask, yer head will asplode.

B, C and D are blank except for the first row, which contains the name
of other .xls files, who are always in the same directory as the main
file. Column A functions as a template for columns B, C and D.

The VBA code has to generate the formulae for B, C and D. How did they
do that?

1. copy column A4:A200 to B4:B200 (range(A).select, selection.copy,
range(B).select, selection.paste)
2. in column B, search & destroy^Wreplace every occurence of [##] with
[fileB.xls] (using the replace method on the range object). fileB.xls
is in the top row of column B.
3. in column B, search & replace every occurence of £= with = (again
using the replace method on the range object)
Repeat for column C and D.

3. is actually quite clever, because before the pasted text was just
text, but by removing the £ at the front of the text, Excel evaluates
it as a function.
But in 2. something goes wrong when I changed it from the short
filename to the full filename with ActiveWorkbook.Path: part of the
filename was repeated inside the formula. Looked really buggy...

My fix was to read in column A from range to variant array, loop
trough the array, make the text replacements in the formula template
string, and set the cell.Formula for each individual cell in column B.
My workaround works 100% correct, I'm confident about that. But...
sloooow. Of course, I have to iterate trough every cell in column B to
set the formula. If it were just a value that I had to write, it would
be a simple matter of saying colB=varArray. Bada bing, bada boom! No
such luck.

I have done some research, but I didn't find a way to set the formulae
of an entire range from an array. It seems like it can only be done
with values. Is that true?

Worst case scenario would be that I would have to restore the copy/
paste code and try to fix it, not rewrite it...

Instead of copying and pasting the entire column of data, can you not
just copy A4 into B4, make the necessary formula corrections, and then
Autofill it to B200? Of course, the absolute reference to the row
number would have to be removed, but that wouldn't be a big deal
according to the data given.

All in all, this sounds like a very complicated deal. Why not just
use a formula in B4 instead of VBA to do all of this? Seems like an
nested Indirect reference to B1 to get the book name and assemble the
formula would work to me. Something like:
=INDIRECT("[" & INDIRECT("B1") & "]Sheet1!A1")

 

[Amedee Van Gasse]

Instead of copying and pasting the entire column of data, can you not
just copy A4 into B4, make the necessary formula corrections, and then
Autofill it to B200?  Of course, the absolute reference to the row
number would have to be removed, but that wouldn't be a big deal
according to the data given.

I suppose you are right, but that would mean diving deep inside the
code, where there be dragons...
Anyway I'm not the owner of this piece of wichcraft, I would have to
check with the owner if this wouldn't have any unwanted consequences.

All in all, this sounds like a very complicated deal.  Why not just
use a formula in B4 instead of VBA to do all of this?  Seems like an
nested Indirect reference to B1 to get the book name and assemble the
formula would work to me.  Something like:
=INDIRECT("[" & INDIRECT("B1") & "]Sheet1!A1")

I totally agree that it is waaaay too complicated! And you should see
the variable names and the comments, it's all in French! This thing
has to be used by people all over Europe and the official company
language is English. Aaargh! I'm frustrated, and it's only monday!
Do you really want to know why it was made that way or was that just a
retorical question?
Because if you want to know, it might take a few weeks before I know
the answer. I would have to ask the owner, who would have to ask the
guy he got it from, who would have to ask the original author, and the
last two people work at different plants, in another country.

I think part of the reason why a nested indirect reference isn't used
(except for the keyboard/chair thing), is that this particular
worksheet is generated by yet another piece of VBA code that I have
not yet dared to enter. It would mean that I would have to insert the
code for the nested indirect formula into the module that generates
the sheet. Oh, the humanity!
Another reason why this doesn't work: I have not told the entire
story. There is not one "template" formula, but two!
The value of the first formula is used as a cell comment, and the
value of the second formula is used as the actual cell value.
And yet another reason why I don't want to use Indirect to get the
book name: I don't trust Excel. I want to be 200% sure that it goes
looking in the correct directory for the workbooks. I think (correct
me if I'm wrong) that I can only do that if I use the full path, and
the only reliable method I know is in VBA. Sure, I could use the
INFO("directory") function, but if I'm well informed, the parameter
for the Info function is language dependent.

But thank you anyway for letting me pick your thoughts. I'm going to
use some of it.

 

[Amedee Van Gasse]

And yet another reason why I don't want to use Indirect to get the
book name: I don't trust Excel. I want to be 200% sure that it goes
looking in the correct directory for the workbooks. I think (correct
me if I'm wrong) that I can only do that if I use the full path, and
the only reliable method I know is in VBA. Sure, I could use the
INFO("directory") function, but if I'm well informed, the parameter
for the Info function is language dependent.

Another reason why INFO("directory") cannot be used: it gets the
current *application* directory. I don't want that, I want the current
*workbook* directory.

 

[Amedee Van Gasse]

I wasn't allowed to use the FillRange method, because the range is not
always continuous.
But anyway, I reduced the running time dramatically: from over 2 hours
to just 45 seconds.
And still I see room for improvement... ;-)