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

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

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

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

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

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