Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a string as a formula (for an array)
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a string as a formula (for an array)
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a string as a formula (for an array)
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! "Charles Williams" wrote: 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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a string as a formula (for an array)
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 "Muttley" wrote: 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! "Charles Williams" wrote: 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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a string as a formula (for an array)
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 "Muttley" wrote in message ... 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! "Charles Williams" wrote: 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
String array issues. Need help... | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
Passing a String in Array to Range as String | Excel Programming | |||
Convert a string to an array | Excel Programming | |||
Create a formula into a String then assign string to a cell | Excel Programming |