Evaluate result of CONCATENATE()

[rocketslinger]

How can I evaluate the result of CONCATENATE() function as a formula i
another cell?

I.E.

A1 = D8 (cell reference)
B1 = > (logical operator)
C1 = Constants!$B$5 (cell reference on another sheet)

CONCATENATE(A1, B1, C1) = "D8>Constants!$B$5"

I would like to evaluate the result, "D8>Constants!$B$5", as a formul
in another cell.

Any help would be appreciated. Thanks

 

[Jason Morin]

You could as a UDF:

Function Eval(scell As String)
Application.Volatile
Eval = Application.Evaluate(scell)
End Function

and call as:

=Eval(A1&B1&C1)

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi
you may use the following user defined function:
Public Function eval_it(eval_str)
eval_it = Application.Evaluate(eval_str)
End Function

use is in your example like
=eval_it(A1&B1&C1)

 

[JE McGimpsey]

One way:

Can't do it via worksheet functions, but can use a UDF (assuming that
"D8" is text rather than a cell reference):

Public Function Evaluator(sFunction As String) As Variant
On Error Resume Next
Evaluator = Application.Evaluate("=" & sFunction)
If Err Then Evaluator = CVErr(xlErrValue)
On Error GoTo 0
End Function


Call as

=Evaluator(A1 & B1 & C1)

 

[Charles Williams]

Because Application.evaluate assumes that cell references without worksheet
qualifiers always refer to the active sheet, it is better to use
worksheet.evaluate rather than application.evaluate

Public Function Evaluator(sFunction As String) As Variant
On Error Resume Next

Evaluator = application.caller.parent.Evaluate("=" & sFunction)

If Err Then Evaluator = CVErr(xlErrValue)
On Error GoTo 0
End Function

that way you don't get different answers depending on what the active sheet
is !


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Harlan Grove]

Because Application.evaluate assumes that cell references without
worksheet qualifiers always refer to the active sheet, it is better
to use worksheet.evaluate rather than application.evaluate

Public Function Evaluator(sFunction As String) As Variant
On Error Resume Next

Evaluator = application.caller.parent.Evaluate("=" & sFunction)

If Err Then Evaluator = CVErr(xlErrValue)
On Error GoTo 0
End Function

....

Now for my quibble. The initial = isn't necessary in Evaluate method calls,
and if there were an initial = in the udf's formula argument, adding another
one leads to a formula syntax error. Better not to prepend the = in the
Evaluate method call.

 

[Charles Williams]

Hi Harlan,

Now for my quibble. The initial = isn't necessary in Evaluate method calls,
and if there were an initial = in the udf's formula argument, adding another
one leads to a formula syntax error. Better not to prepend the = in the
Evaluate method call.

I agree, and whilst we are quibbleing <vbg> the function should be volatile.

Here is my version:

Public Function EVAL(theInput As Variant) As Variant
'
' if worksheet UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If not IsEmpty(theInput) then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(theInput)
Else
vEval = Application.Evaluate(theInput)
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function
regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[rocketslinger]

Thanks everyone for the valuable input.

Is ther any way to call this UDF from a worksheet cell without goin
the VBA route

 

[Harlan Grove]

I agree, and whilst we are quibbleing <vbg> the function should be
volatile.

....

No, no, no! More qquibbles!

There's no need to make the udf volatile in VBA. In this particular case,
just add a do-nothing optional argument. Add nothing to the code inside the
udf. If the optional argument is omitted or is itself nonvolatile, the udf
will behave nonvolatile. If it's a volatile expression, e.g., NOW(), then
the udf will behave volatile. Cruder would be leaving the udf as-is except
for removing the Application.Volatile statement, then using an idiom like

=IF(NOW(),udf_call_here(...))

=udf_call_here(...)+0*NOW()

=udf_call_here(...)&LEFT(NOW(),0)

I prefer ad hoc volatility.

 

[Charles Williams]

...
...

No, no, no! More qquibbles!

There's no need to make the udf volatile in VBA. In this particular case,
just add a do-nothing optional argument. Add nothing to the code inside the
udf. If the optional argument is omitted or is itself nonvolatile, the udf
will behave nonvolatile. If it's a volatile expression, e.g., NOW(), then
the udf will behave volatile. Cruder would be leaving the udf as-is except
for removing the Application.Volatile statement, then using an idiom like

=IF(NOW(),udf_call_here(...))

=udf_call_here(...)+0*NOW()

=udf_call_here(...)&LEFT(NOW(),0)

I prefer ad hoc volatility.

But if you are making a general-purpose UDF it seems to me that it has to be
volatile.

the reason is that you are mostly feeding it a string argument (or a range
that resolves to a string), and Excel will not detect when what the string
refers to changes (ie string references are not detectably part of the
dependency chain (like INDIRECT)). Excel will of course detect when the
string itself changes, but thats probably only a minor subset of the changes
that will occur.

I agree that in the case when the user knows exactly what they are doing
they can manipulate the function with Now() or an optional argument saying
treat this as volatile, but in general this seems a bit too risky for me.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Charles Williams]

Thanks everyone for the valuable input.

Is ther any way to call this UDF from a worksheet cell without going
the VBA route?

Well its written in VBA, but you can call it from a worksheet cell.
There is no equivalent native Excel function (INDIRECT is the closest but it
only resolves references).

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm