Using a string as a formula (for an array)

M

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!
 
M

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!
 
J

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top