Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate text strings as a formula
Hi All
I am delving into VB and getting somewhat lost. I have a spread sheet designed so a end user only needs to enter columns of data and a few parameters. However, the way it falls together is that I need to concatenate text and evaluate the string as a formula. In a previous posting I found this suggestion... Sub ConvertToFormula() ActiveCell.Formula = ActiveCell.Value End Sub I entered it into the VBA editor (as a UDF), and it appears in the user defined function list. However it returns the #VALUE! error. I am more familar with matlab and the comparative function in that environment would be... x = eval(concatenate('formula', 'as text')) Thanks in advance Nick Flyger |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate text strings as a formula
Try this
Function eval(pFormula, pText) Application.Volatile eval = Application.Caller.Parent.Evaluate(pFormula & pText) End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nick Flyger" wrote in message ... Hi All I am delving into VB and getting somewhat lost. I have a spread sheet designed so a end user only needs to enter columns of data and a few parameters. However, the way it falls together is that I need to concatenate text and evaluate the string as a formula. In a previous posting I found this suggestion... Sub ConvertToFormula() ActiveCell.Formula = ActiveCell.Value End Sub I entered it into the VBA editor (as a UDF), and it appears in the user defined function list. However it returns the #VALUE! error. I am more familar with matlab and the comparative function in that environment would be... x = eval(concatenate('formula', 'as text')) Thanks in advance Nick Flyger |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate text strings as a formula
Tried it but it isn't quite what I am after. I only have one input argument
which is a concatenated string that I want to evaluate. Can you clarify how I might use your eval formula? I have only started on VB this week so it is fair to say I am pretty illiterate in it's programming. Cheers Nick "Bob Phillips" wrote: Try this Function eval(pFormula, pText) Application.Volatile eval = Application.Caller.Parent.Evaluate(pFormula & pText) End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nick Flyger" wrote in message ... Hi All I am delving into VB and getting somewhat lost. I have a spread sheet designed so a end user only needs to enter columns of data and a few parameters. However, the way it falls together is that I need to concatenate text and evaluate the string as a formula. In a previous posting I found this suggestion... Sub ConvertToFormula() ActiveCell.Formula = ActiveCell.Value End Sub I entered it into the VBA editor (as a UDF), and it appears in the user defined function list. However it returns the #VALUE! error. I am more familar with matlab and the comparative function in that environment would be... x = eval(concatenate('formula', 'as text')) Thanks in advance Nick Flyger |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate text strings as a formula
In that case just reduce it to one argument, and strip the internal
concatenation Function eval(pFormula) Application.Volatile eval = Application.Caller.Parent.Evaluate(pFormula) End Function and as an example of usage =eval("B4*"&10) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nick Flyger" wrote in message ... Tried it but it isn't quite what I am after. I only have one input argument which is a concatenated string that I want to evaluate. Can you clarify how I might use your eval formula? I have only started on VB this week so it is fair to say I am pretty illiterate in it's programming. Cheers Nick "Bob Phillips" wrote: Try this Function eval(pFormula, pText) Application.Volatile eval = Application.Caller.Parent.Evaluate(pFormula & pText) End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nick Flyger" wrote in message ... Hi All I am delving into VB and getting somewhat lost. I have a spread sheet designed so a end user only needs to enter columns of data and a few parameters. However, the way it falls together is that I need to concatenate text and evaluate the string as a formula. In a previous posting I found this suggestion... Sub ConvertToFormula() ActiveCell.Formula = ActiveCell.Value End Sub I entered it into the VBA editor (as a UDF), and it appears in the user defined function list. However it returns the #VALUE! error. I am more familar with matlab and the comparative function in that environment would be... x = eval(concatenate('formula', 'as text')) Thanks in advance Nick Flyger |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate text strings as a formula
yip now I see, it doesn't seem to work when you point to a cell with the text
but will evaluate when you concatenate directly into the eval formula. Thanks for your time nick "Bob Phillips" wrote: In that case just reduce it to one argument, and strip the internal concatenation Function eval(pFormula) Application.Volatile eval = Application.Caller.Parent.Evaluate(pFormula) End Function and as an example of usage =eval("B4*"&10) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nick Flyger" wrote in message ... Tried it but it isn't quite what I am after. I only have one input argument which is a concatenated string that I want to evaluate. Can you clarify how I might use your eval formula? I have only started on VB this week so it is fair to say I am pretty illiterate in it's programming. Cheers Nick "Bob Phillips" wrote: Try this Function eval(pFormula, pText) Application.Volatile eval = Application.Caller.Parent.Evaluate(pFormula & pText) End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nick Flyger" wrote in message ... Hi All I am delving into VB and getting somewhat lost. I have a spread sheet designed so a end user only needs to enter columns of data and a few parameters. However, the way it falls together is that I need to concatenate text and evaluate the string as a formula. In a previous posting I found this suggestion... Sub ConvertToFormula() ActiveCell.Formula = ActiveCell.Value End Sub I entered it into the VBA editor (as a UDF), and it appears in the user defined function list. However it returns the #VALUE! error. I am more familar with matlab and the comparative function in that environment would be... x = eval(concatenate('formula', 'as text')) Thanks in advance Nick Flyger |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate text strings as a formula
Nick,
This version should get over that Function eval(pFormula) Application.Volatile eval = Application.Caller.Parent.Evaluate(CStr(pFormula)) End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nick Flyger" wrote in message ... yip now I see, it doesn't seem to work when you point to a cell with the text but will evaluate when you concatenate directly into the eval formula. Thanks for your time nick "Bob Phillips" wrote: In that case just reduce it to one argument, and strip the internal concatenation Function eval(pFormula) Application.Volatile eval = Application.Caller.Parent.Evaluate(pFormula) End Function and as an example of usage =eval("B4*"&10) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nick Flyger" wrote in message ... Tried it but it isn't quite what I am after. I only have one input argument which is a concatenated string that I want to evaluate. Can you clarify how I might use your eval formula? I have only started on VB this week so it is fair to say I am pretty illiterate in it's programming. Cheers Nick "Bob Phillips" wrote: Try this Function eval(pFormula, pText) Application.Volatile eval = Application.Caller.Parent.Evaluate(pFormula & pText) End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nick Flyger" wrote in message ... Hi All I am delving into VB and getting somewhat lost. I have a spread sheet designed so a end user only needs to enter columns of data and a few parameters. However, the way it falls together is that I need to concatenate text and evaluate the string as a formula. In a previous posting I found this suggestion... Sub ConvertToFormula() ActiveCell.Formula = ActiveCell.Value End Sub I entered it into the VBA editor (as a UDF), and it appears in the user defined function list. However it returns the #VALUE! error. I am more familar with matlab and the comparative function in that environment would be... x = eval(concatenate('formula', 'as text')) Thanks in advance Nick Flyger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Concatenating two text strings, formula is right, cell output is w | Excel Worksheet Functions | |||
converting constant/text to formula and evaluate it | Excel Programming | |||
VBA Function to evaluate hlookup text string as formula | Excel Programming | |||
How to evaluate a text expression as formula ? | Excel Programming |