MrExcel.com - Your One Stop for Excel Tips & Solutions

VBA: Formula to variable as string

Posted by Andrej Beden on June 29, 2001 12:27 AM

I have a problem. I have a cell with formula (excel 95). Wen I wont to get this formula into variable in VBA macro as string, i got message for Error 2015.

used VBA code:
textformula = CStr(ActiveCell.FormulaR1C1)
result:
Error 2015

How to get this formula into variable as string?

Any ideas or suggestions would be appreciated!!

Andrej

Posted by mseyf on June 29, 2001 6:45 AM

Do you get the error with every formula you try to convert? all of the following work in Excel97:

strFormula = CStr(ActiveCell.FormulaR1C1)
strFormula = "'" & ActiveCell.FormulaR1C1
strFormula = ActiveCell.Formula

MS Knowledge mentions Error 2015 in Excel97 in relation to dates, but nothing in Excel95.

-Mark

Posted by Andrej Beden on June 30, 2001 8:20 AM

Thanks Mark!

Yes, the problem was with formulas with dates in them. I have no problems with other Formulas.
I have tried all yours suggestions without success so I stil need solution to solve this problem.

Posted by mseyf on July 02, 2001 8:36 AM


can you post an example of a formula that gives you an error? I don't have any experience with Excel95, but might be able to figure something out

Mark

Posted by Andrej Beden on July 03, 2001 12:13 AM

Here is an exemple:
in cell A1 is Date or nothing;
in cell A2 is "yes" or "no" or empty
in cell A3 is formula:
=IF(A2 = "No","Some text",CONCATENATE("Text text text text",DAY(A1),".",MONTH(A1),".",YEAR(A1)," Loooooooong, looooooooooong, looooooooooong, looooooooooong, loooooooooooong teeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeext"))

If "long text" in formula is shorter, this formula works, but I can not change the text!

Regards,
Andrej

Posted by Mark Seyfried on July 05, 2001 8:02 AM

Have you tried assigning a name to the text and using the name instead of the text? (Insert>Name>Define) Or putting the text in an out of the way cell and referencing the cell instead?

Just a couple of thoughts

Mark

Posted by Andrej Beden on July 06, 2001 6:00 AM

Thanks Mark, but i'm still without solution.

The formula is unchangeable for me because it is as is in cell.

Andrej