converting text to function

[tkremen]

I may be in over my head here, but what I am trying to do is to dynamically create a call to a stock-quote service using text characters that change frequently. I can create (using =concatenate) the text string that I want to execute (=mktlink|price!'[....'), but it is still just a text string and not a true function, and (somewhat obviously) won't return the desired information, which is a numeric value (e.g. price of a share of stock).
Any ideas on how I can accomplish this?
Thanks,
Tom

 

[Aladin Akyurek]

On 2002-08-12 17:37, tkremen wrote:
TsTom:

Yikes!! That worked! Thanks.

Now, I have more questions! But, I want to think about them a bit more before I ask them. (Homework, so to speak.) I'll "digest" this a bit and experiment some before I get back to anyone.

Thanks very much for all your interest and help.

Tom

EVAL should work too (Hope TsTom doesn't mind my assertion).

 

[squirk]

VBA does seem to be the way.
There is a method in VBA called evaluate which I utilized in coming up with this function. The argument is the cell containing the string that has been concatenated together. Volatile is a method that ensures this function will be recalculated when any cell in the workbook is calculated.

Function eval(myCell As Range)
Application.Volatile (True)
Dim cellVal As String

cellVal = myCell.Value
eval = Application.evaluate(cellVal)
End Function

 

[Legacy 98055]

I always enjoy your assertions Aladin. It seems that when it comes to Excel functions and formulas, I am trapped on the darkside.
(Inside Joke)

 

[Aladin Akyurek]

On 2002-08-12 19:31, squirk wrote:
VBA does seem to be the way.
There is a method in VBA called evaluate which I utilized in coming up with this function. The argument is the cell containing the string that has been concatenated together. Volatile is a method that ensures this function will be recalculated when any cell in the workbook is calculated.

Function eval(myCell As Range)
Application.Volatile (True)
Dim cellVal As String

cellVal = myCell.Value
eval = Application.evaluate(cellVal)
End Function

Well, that's EVAL, although I don't know how Longre coded his EVAL, whether it is in VBA or something else.

 

[Legacy 98055]

Hi.
I could not test this thorougly because I do not have your third party function and am unsure as to how the Evaluate method will react? This is simply a combination of mine and squirk's earlier contributions. The function could easily be made much more flexible, but it seems to serve it's purpose as is.
And as is, it will only accept a range in one column or one row, will concatenate the values in this range in the order from left to right or top to bottom accordingly, evaluate the concatenated string, and hopefully return the value you are seeking.
This was tested on some of Excel's home grown functions with success.
Based on your earlier example using cells A5 through A9, the function would be used in any cell as such: =bldfunct(A5:A9)<pre>
'Build Function
Public Function BldFunc(rng As Range) As Variant
Dim c As Range, strng
Application.Volatile

If rng.Columns.Count > 1 And Not rng.Rows.Count = 1 Then
MsgBox "Expected One Dimensional Range. Choose a range in a single row or column."
BldFunc = "ERROR"
Exit Function
End If

If rng.Rows.Count > 1 And Not rng.Columns.Count = 1 Then
MsgBox "Expected One Dimensional Range. Choose a range in a single row or column."
BldFunc = "ERROR"
Exit Function
End If

For Each c In rng
strng = strng & c
Next

strng = "=" & strng

BldFunc = Evaluate(strng)

End Function</pre>
Tom

Try out Aladin's suggestion using Eval.
It seems to work without the need for a custom function.
=Eval(A5:A9)
This message was edited by TsTom on 2002-08-14 23:14

 

[abesimpson]

I may be in over my head here, but what I am trying to do is to dynamically create a call to a stock-quote service using text characters that change frequently. I can create (using =concatenate) the text string that I want to execute (=mktlink|price!'[....'), but it is still just a text string and not a true function, and (somewhat obviously) won't return the desired information, which is a numeric value (e.g. price of a share of stock).
Any ideas on how I can accomplish this?
Thanks,
Tom