Using a string as a formula (for an array)

[Muttley]

Hi all,

I'm working with arrays within VB but would like the Excel user to be able
to specify basic formulas that can be used - i.e for each row, add column 1
to column 2 and store in column 3. Now, I have a way of converting the
"excel" formula to an "array" forumula in VB, however this formula is a text
string.

i.e. my entry in the third column is: "test_csv(1,1)+test_csv(1,2)" (as
text) whereas I want that to be the formula used and the entry to be the
result

is there anyway I can use this string to specify a formula?

Thanks in advance!

 

[Charles Williams]

You can use the Evaluate method, but it has a number of quirks so needs
careful handling.

See http://www.decisionmodels.com/calcsecretsh.htm


Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Muttley]

Thanks for the quick reply! Evaluate doesn't seem to like the use of array
calcs (I get an error 2029) so looks like it's a no go. Maybe I'll use a
macro to get it to calculate in excel but this seems inefficient (I'm reading
in csv's that are too large for excel) Ah well!

 

[Joel]

A plus sign will append two string to each other , but the & is more
conventional

I would put in cell A3 the following
=test_csv(A1)&test_csv(B1)

then in VBA code

function test_csv(target as Range) as string

mystr = targtet.text
' add your code here
text_csv = mystr
end function

 

[Charles Williams]

The Evaluate method will calculate an Excel formula string, which can be an
Excel array formula.
It will not evaluate visual basic syntax, which is maybe what you were
looking for (you can do that in Access using EVAL but not in Excel).


Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm