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

 

[PaddyD]

Tom,

Welcome to the board!

If this is all within excel, then check out the help file for indirect:

If a1 contains the text b1, and b1 contians the value 5, then

=indirect(a1)

returns 5.

and

=indirect("A1")

returns b1

and

=indirect(indirect("A1"))

returns 5

Hope this helps,

Paddy
This message was edited by PaddyD on 2002-08-12 16:38

 

[tkremen]

Thanks for the idea. The problem with =indirect is that it will still just return the contents of a cell, which is still just a character string, not something that will actually execute. It's as if I need to change the way Excel interprets the contents of the cell; don't interpret as a string of characters with an equal-sign on the front; interpret as a function.
Any other suggestions?
Tom

 

[PaddyD]

care to post full details of the problem...

Paddy

 

[tkremen]

A5 contains: mktlink|price!'[h1]
A6 contains: ibm
A7 contains: i
A8 contains: n
A9 contains: ;3'

A4 is: =contatenate("="&A5&A6&A7&A8&A9)
which results: =mktlink|price!'[h1]ibmin;3'

Note that cell A4 is the result of the =CONCATENATE function, and therefore is just a character string (that happens to LOOK like a function). But, I want Excel to interpret the contents of A4 as a function (which would then execute) and return a numerical value, not just display the character string.

A possibility, I guess, is to write another function with A4 as the target, and which will return something Excel will execute. Someone in France, I think, wrote MOREFUNC which contained the =EVAL() function which should do this, but it doesn't always work.

Thanks. Is this clearer?
This message was edited by tkremen on 2002-08-12 17:08
This message was edited by tkremen on 2002-08-12 17:15

 

[PaddyD]

what function is

=mktlink|price!'[h1]ibmin;3'

meant to be? or have I missed something?

Paddy

 

[tkremen]

"mktlink" is supplied by a provider of stock market information. It is an add-in function. The provider has trouble spelling Excel, so they can't even understand what I'm trying to ask. I can see their software if I go to Insert, Function, and look in the "Paste function" panels.

But, their software is not so important at this point. How can I get Excel to execute the contents of A4 (in the example)?

 

[Legacy 98055]

Hi.
If you do not mind a VBA solution(it might be the only way?)
From the worksheet which will contain this function...
Right click on your worksheet tab, click view code, paste this in<pre>
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
[A4].Formula = "=" & [A5] & [A6] & [A7] & [A8] & [A9]
Application.EnableEvents = True
End Sub</pre>
Tom
This message was edited by TsTom on 2002-08-12 17:23

 

[Aladin Akyurek]

On 2002-08-12 17:20, tkremen wrote:
"mktlink" is supplied by a provider of stock market information. It is an add-in function. The provider has trouble spelling Excel, so they can't even understand what I'm trying to ask. I can see their software if I go to Insert, Function, and look in the "Paste function" panels.

But, their software is not so important at this point. How can I get Excel to execute the contents of A4 (in the example)?

Can you describe the syntax of mktlink? What its arguments are?

 

[tkremen]

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