Evaluate Text String As A Function 


Evaluate Text String As A Function  Excel 
View Answers 
I have a number of formulas used to pull data from another system into
Excel.
Three example formulas a
=SystemxBid!GOOG
=SystemxAsk!GOOG
=SystemxLast!GOOG
The formula can be broken up into three arguments. Systemx is the
other system providing data to Excel via DDE links. After the  is the
argument specifying what
data to pull into Excel. And you might recognize the argument after the
! as a
stock ticker. I want to replace the ticker with a cell reference so
that I
can change the value in the cell from GOOG to YHOO (for instance) and
have the formulas recalculate with the data from YHOO instead of GOOG.
If I try to just
replace the ticker with the cell reference, the resulting formula,
=SystemxAsk!'A13', doesn't work.
I'm think there should be a way to construct the formula as a string
(e.g.
="SystemxAsk!" & A13) and then force Excel to evaluate the string as a
function. As is, Excel will construct the string and then display it as
text
in the cell rather than evaluate it as a function. I know I can do a
workaround using VBA, but I'm hoping there is a solution without VBA,
perhaps a built in function.
Is anyone familiar with DDE links or otherwise know a way to fix my
problem?
Thanks for your help!
Excel.
Three example formulas a
=SystemxBid!GOOG
=SystemxAsk!GOOG
=SystemxLast!GOOG
The formula can be broken up into three arguments. Systemx is the
other system providing data to Excel via DDE links. After the  is the
argument specifying what
data to pull into Excel. And you might recognize the argument after the
! as a
stock ticker. I want to replace the ticker with a cell reference so
that I
can change the value in the cell from GOOG to YHOO (for instance) and
have the formulas recalculate with the data from YHOO instead of GOOG.
If I try to just
replace the ticker with the cell reference, the resulting formula,
=SystemxAsk!'A13', doesn't work.
I'm think there should be a way to construct the formula as a string
(e.g.
="SystemxAsk!" & A13) and then force Excel to evaluate the string as a
function. As is, Excel will construct the string and then display it as
text
in the cell rather than evaluate it as a function. I know I can do a
workaround using VBA, but I'm hoping there is a solution without VBA,
perhaps a built in function.
Is anyone familiar with DDE links or otherwise know a way to fix my
problem?
Thanks for your help!
Similar Excel Tutorials
Extract the First Word from a Cell in Excel  User Defined Delimiter Text Extraction  UDF
This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first w ...
This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first w ...
Introducing Logic into Formulas and Functions in Excel
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...
Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Helpful Excel Macros
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Extract the First Word from a Cell in Excel  User Defined Delimiter Text Extraction  UDF
 This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
 This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
Display The Actual Link / Email Address From Links in Excel  UDF
 Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
 Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Hide Formulas in a Worksheet and Prevent Deletion
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Similar Topics
Need some quick help!
I'm trying to reference cells from a collection of separate files without using names. I have a master file with an input cell in which you can enter the ticker symbol of a company. For each ticker symbol, there is a related model. For instance, say that I want to enter cell D15 of my Google model (named GOOG). The Excel syntax would be as follows:
='[GOOG.xls]Commodity Sensitivity'!D15
Is there a way to combine the input of the ticker symbol in my master file with the actual file name? The models are identical in format such that each cell references the same type of metric. For instance, cell D15 refers to the P/E ratio of each company. Rather than having to name each cell, is it possible to utilize my master file ticker input with the generic file cell reference? I tried using the concatenate function but it appears that it only ends up creating a text string which doesn't create a file reference. Please let me know if this sounds wrong.
Thanks!!!
I'm trying to reference cells from a collection of separate files without using names. I have a master file with an input cell in which you can enter the ticker symbol of a company. For each ticker symbol, there is a related model. For instance, say that I want to enter cell D15 of my Google model (named GOOG). The Excel syntax would be as follows:
='[GOOG.xls]Commodity Sensitivity'!D15
Is there a way to combine the input of the ticker symbol in my master file with the actual file name? The models are identical in format such that each cell references the same type of metric. For instance, cell D15 refers to the P/E ratio of each company. Rather than having to name each cell, is it possible to utilize my master file ticker input with the generic file cell reference? I tried using the concatenate function but it appears that it only ends up creating a text string which doesn't create a file reference. Please let me know if this sounds wrong.
Thanks!!!
I want to create a form where I input the stock ticker into a cell (say, GOOG) and click a button to import GOOG stock information from the web. I could start simple, so at this point it is not so important what gets imported as long as it works. I know how to create macros, and I know how to import from the web, but I am unsure how to combines these to get what I am trying to do. Any help is appreciated.
does anybody have any further information on the ILX function in excel? i have searched far and wide but haven't found much...
i know i can get things like last price and yield  was wondering if it is possible to retrieve values like 52 week hi's and low's for a stock symbol. perhaps any other functions? is there a list available anywhere?
i currently use lines like:
=ILXQ!'GOOG,last'
=ILXQ!'GOOG,yield'
was also wondering if there is a way to retrieve my stock symbols from another column in my spreadsheet, for example:
=ILXQ!'A2,last'
where GOOG is the value in A2  i think i need something along the lines of
="ILXQ!'"&A2",last"
is that anywhere close? i cannot get any sort of syntax to work...
any help is much appreciated! thanks!
i know i can get things like last price and yield  was wondering if it is possible to retrieve values like 52 week hi's and low's for a stock symbol. perhaps any other functions? is there a list available anywhere?
i currently use lines like:
=ILXQ!'GOOG,last'
=ILXQ!'GOOG,yield'
was also wondering if there is a way to retrieve my stock symbols from another column in my spreadsheet, for example:
=ILXQ!'A2,last'
where GOOG is the value in A2  i think i need something along the lines of
="ILXQ!'"&A2",last"
is that anywhere close? i cannot get any sort of syntax to work...
any help is much appreciated! thanks!
In a workbook if this function does not find the string its looking for then it returns #Value, but in VBA it doesn't seem to even recognize it as an error.
Here are a few quick examples: The sub breaks, but the function returns "#Value". What I need is a work around so that I can loop a search. Right now 1 failure returns "#Value" even if the first 10 searches in the loop worked. I've been trying to use worksheetfunction.iferror but it only works on the error if its in the workbook environment not while its still in vba.
Sub Macro1()
x = WorksheetFunction.Search("Goog", "Google")
MsgBox (x)
y = WorksheetFunction.Search("Goog", "Yahoo")
MsgBox (y)
End Sub
Function Macro1()
x = WorksheetFunction.Search("Goog", "Google")
Macro1 = x
End Function
Function Macro2()
y = WorksheetFunction.Search("Goog", "Yahoo")
Macro2 = y
End Function
Anyone know how I can get a failed search (y) to return 0, so I can keep a counter going like
x= x+y
Here are a few quick examples: The sub breaks, but the function returns "#Value". What I need is a work around so that I can loop a search. Right now 1 failure returns "#Value" even if the first 10 searches in the loop worked. I've been trying to use worksheetfunction.iferror but it only works on the error if its in the workbook environment not while its still in vba.
Sub Macro1()
x = WorksheetFunction.Search("Goog", "Google")
MsgBox (x)
y = WorksheetFunction.Search("Goog", "Yahoo")
MsgBox (y)
End Sub
Function Macro1()
x = WorksheetFunction.Search("Goog", "Google")
Macro1 = x
End Function
Function Macro2()
y = WorksheetFunction.Search("Goog", "Yahoo")
Macro2 = y
End Function
Anyone know how I can get a failed search (y) to return 0, so I can keep a counter going like
x= x+y
MS EXCEL 2003
The formula to generate the internal rate of return for a dated series is
=XIRR(values, dates, [guess])
The following array formula using XIRR produces the correct result for Values in D4:D9 and the Dates in E4:E9 and satisfying the criteria "Buy" in Column C and "goog" in column B
{=XIRR((C4:C9="Buy")*(B4:B9="goog")*D4:D9,(C4:C9="Buy")*(B4:B9="goog")*E4:E9)}
This is fine for 6 rows.
When I apply this to a longer list of 250 rows the formula does not produce the correct result.
When I step through the Evaluate Formula using the Formula Auditing toolbar, it seems as though the correct process occurs for the first argument of XIRR, but not for the second. Maybe this is to long for the evaluate formula?
In any event what is going wrong?
The formula to generate the internal rate of return for a dated series is
=XIRR(values, dates, [guess])
The following array formula using XIRR produces the correct result for Values in D4:D9 and the Dates in E4:E9 and satisfying the criteria "Buy" in Column C and "goog" in column B
{=XIRR((C4:C9="Buy")*(B4:B9="goog")*D4:D9,(C4:C9="Buy")*(B4:B9="goog")*E4:E9)}
This is fine for 6 rows.
When I apply this to a longer list of 250 rows the formula does not produce the correct result.
When I step through the Evaluate Formula using the Formula Auditing toolbar, it seems as though the correct process occurs for the first argument of XIRR, but not for the second. Maybe this is to long for the evaluate formula?
In any event what is going wrong?
Dear all,
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
eros
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
eros
Dear all,
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Eros
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Eros
Dear all,
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Eros
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Eros
Hello,
I need a macro that will collect information from different worksheets and consolidates it on one tab.
In each worksheets, the number of columns is the same, but the number of rows differs. What I need to macro to do is to take the data + column headings from the first of the 5 source files and paste them into the final worksheet.
For example,
Worksheet 1
Ticker date cusip description ratings funds
aapl 2Feb13 aaaaaa apple aaa 1000
goog 15Feb13 bbbbbb google aaa 1500
yhoo 2Mar13 ccccccc yahoo AA 120
ko 1May13 dddddd coke BB+ 125
GM 12Jul13 eeeeee gen mtrs aa 1550
worksheet 2
date cusip description ratings funds Ticker
2Feb13 aaaaaa apple aaa 1000 AAPL
15Feb13 bbbbbb google aaa 1500 GOOG
2Mar13 ccccccc yahoo AA 120 YHOO
1May13 dddddd coke BB+ 125 KO
12Jul13 eeeeee gen mtrs aa 1550 GM
Now in worksheet 3, which will be the final worksheet, I want to create a macro which basially pulls in all the data from different worksheets and paste them in the following order.
Description Ticker ratings Funds Date Cusip
This example only has about 5 data sets, the actual file will have thousands of rows. Sheet 1 could have 1000, sheet 2 could have 500 and sheet 3 can have 1500. And this will wary on a monthly basis, which is why I don't want to copy and past the macro. This month I might have 1000 rows and next month I'll have 2000, so the macro will only pick up the 1000 not 2000, if I copy and paste.
The heading are the same in all the sheets. Can you please help me create a macro which will copy data from the different sheets using column heading and paste them into the my final sheet. So if I have a heading called "Ticker" i want the final sheet to grab all the information from each sheet under "ticker" and past them into the final sheet.
Also, each work sheet has names (sheet1=BNY sheet 2=UBS..etc..)
Can you please create a macro and show me where I can insert the names of the heading and sheets.
Thank you very much for all of your help. I hope I made sense.
Hello,
I currently use a free addin written by Randy Harmelink to retrieve the price of a stock at a given date. In my spreadsheet, I have a large number of dates listed in date format (ie: 1/1/1998) in Column A, but the actual data for the dates is listed by Excel in the form (ie: 28491) if I change the formatting to general.
For the addin, I typically use the function:
=RCHGetYahooHistory("GOOG",1998,1,1,etc...) which is in the form Year,Month,Day.
What I would like to do is to be able to essentially do this:
=RCHGetYahooHistory("GOOG",A1,etc...) or in other words, pass the date in Column A to the function in the correct format. But i'm not sure how to do this when Excel stores the date in the form '28491' in A1.
How I might accomplish this?
I currently use a free addin written by Randy Harmelink to retrieve the price of a stock at a given date. In my spreadsheet, I have a large number of dates listed in date format (ie: 1/1/1998) in Column A, but the actual data for the dates is listed by Excel in the form (ie: 28491) if I change the formatting to general.
For the addin, I typically use the function:
=RCHGetYahooHistory("GOOG",1998,1,1,etc...) which is in the form Year,Month,Day.
What I would like to do is to be able to essentially do this:
=RCHGetYahooHistory("GOOG",A1,etc...) or in other words, pass the date in Column A to the function in the correct format. But i'm not sure how to do this when Excel stores the date in the form '28491' in A1.
How I might accomplish this?
I am trying to create a UDF that takes a range as input and outputs the same range reference as a string:
for example in an excel file I would enter
=MyFunc(Sheet2!$F6:F$14, arg2)
into a cell. In the VBA code i would like the function to return the range entered in the first argument as a string, so the cell which contains this formula would read:
Sheet2!$F6:F$14
I know I can use the .address function to return the range reference as a string. However, I need to be able to detect if the reference is originally absolute or not so I can specify it in the .address arguments. Further, the .address function does not allow you to specify different column and row absolute styles for either side of the : as is shown above.
I also tried extracting the cell formula as text using the cell.formula argument. I could then trim the string to get the range i required. However, in this case I do not know how to have the function reference the cell that the function is written in (its own cell) as apposed to say the active cell.
There may be a much easier way as well?
Does anyone know how to return an arguments name as written in string form. Really struggling here and any help would be great!
for example in an excel file I would enter
=MyFunc(Sheet2!$F6:F$14, arg2)
into a cell. In the VBA code i would like the function to return the range entered in the first argument as a string, so the cell which contains this formula would read:
Sheet2!$F6:F$14
I know I can use the .address function to return the range reference as a string. However, I need to be able to detect if the reference is originally absolute or not so I can specify it in the .address arguments. Further, the .address function does not allow you to specify different column and row absolute styles for either side of the : as is shown above.
I also tried extracting the cell formula as text using the cell.formula argument. I could then trim the string to get the range i required. However, in this case I do not know how to have the function reference the cell that the function is written in (its own cell) as apposed to say the active cell.
There may be a much easier way as well?
Does anyone know how to return an arguments name as written in string form. Really struggling here and any help would be great!
Does Excel have any 'builtin' ability to interpret a string as a formula, or would I need to implement a VBA function?
In it's simplest form I am thinking of something along the lines:
Cell A1 contains: 5
Cell A2 contains: "*100"
Cell A4 contains: =Evaluate(A1 & A2)
and hence displays 500
I know evaluate doesn't work, but is there something that does this?
In it's simplest form I am thinking of something along the lines:
Cell A1 contains: 5
Cell A2 contains: "*100"
Cell A4 contains: =Evaluate(A1 & A2)
and hence displays 500
I know evaluate doesn't work, but is there something that does this?
Hi.
I'm trying to set up a validation tool for a multidimensional account sequence.
My approach has been to set up the validation as text strings containing IF's and using a UDF to convert the strings to excel formulas.
VB:
My problem boils down to Evaluate() not being able to process the IF or OFFSET functions that I am using, and returns a #VALUE! error.
Am I misunderstanding something about the use of this UDF, or is the problem elsewhere?
Thank you for you time
I'm trying to set up a validation tool for a multidimensional account sequence.
My approach has been to set up the validation as text strings containing IF's and using a UDF to convert the strings to excel formulas.
VB:
Function Evalu(ByVal S As String) As String Evalu = Evaluate(S) End FunctionIf you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
My problem boils down to Evaluate() not being able to process the IF or OFFSET functions that I am using, and returns a #VALUE! error.
Am I misunderstanding something about the use of this UDF, or is the problem elsewhere?
Thank you for you time
I have a problem that recurs every few weeks. Specifically, sometimes Excel will not evaluate certain formulas on a spreadsheet. For instance, cell A1 could have the formula "=2+2" and instead of evaluating and displaying "4", it would simply display "=2+2".
Assume the file is set to AutoCalculate, that I've hit F9 several times, and even that I've hit F2 (to enter the cell) and then Enter, and none of the techniques work. Assume I don't have an apostrophe before the formula (which would turn it into a text string and display precisely as the formula is now displaying). Also assume that I have not used the Ctrltilde (or is it Alt?) shortcut for making visible all formulas on the tab. Does anyone know what is happening?
Often I can input the exact formula in another cell (say, cell B1) and then it evaluates just fine. Or if I put the formula in cell B1 and then Cut+Paste over A1 then the new formula will typically evaluate.
I'd love to know if it is just a bug in Excel (I use 2003) or if there is something I do to trigger it. Any information would be welcome. Thanks in advance.
Assume the file is set to AutoCalculate, that I've hit F9 several times, and even that I've hit F2 (to enter the cell) and then Enter, and none of the techniques work. Assume I don't have an apostrophe before the formula (which would turn it into a text string and display precisely as the formula is now displaying). Also assume that I have not used the Ctrltilde (or is it Alt?) shortcut for making visible all formulas on the tab. Does anyone know what is happening?
Often I can input the exact formula in another cell (say, cell B1) and then it evaluates just fine. Or if I put the formula in cell B1 and then Cut+Paste over A1 then the new formula will typically evaluate.
I'd love to know if it is just a bug in Excel (I use 2003) or if there is something I do to trigger it. Any information would be welcome. Thanks in advance.
All,
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link which reads a field from an external application currently running. There are 200 of these strings for each cell in an Excel Worksheet differing only in FIELD and DETAIL bits. E.g. APPDATA!FIELD1.DETAIL1, APPDATA!lFIELD2.DETAIL2.
I want to define a formula string reading 2 variables stored in different cells and execute the string for each cell. That way, I could change the cells which hold the different bits of the DDE string so that I don't have to manually change the strings for 200 cells separately.
How can I achive this? I tried to use Evaluate in a VBA code but does snot work
I use Excel 2003 so using Evaluate in the cell does not work either, simply Excel does not recognize such a command.
Thanks in advance
Kadir
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link which reads a field from an external application currently running. There are 200 of these strings for each cell in an Excel Worksheet differing only in FIELD and DETAIL bits. E.g. APPDATA!FIELD1.DETAIL1, APPDATA!lFIELD2.DETAIL2.
I want to define a formula string reading 2 variables stored in different cells and execute the string for each cell. That way, I could change the cells which hold the different bits of the DDE string so that I don't have to manually change the strings for 200 cells separately.
How can I achive this? I tried to use Evaluate in a VBA code but does snot work
I use Excel 2003 so using Evaluate in the cell does not work either, simply Excel does not recognize such a command.
Thanks in advance
Kadir
I have the following formula:
=BDDEHIST!'\\<DEFAULT>\amp.INT=DAY.FARD=06/14/07.NEARD=06/14/07.PRDS=1.EXCEL.QE=FLD_LAST'
This is a Reuter's DDE formula that is placed in an Excel cell to be evaluated. When I use this specific formula, I get 64.98 returned in the cell. I want to change this formula so that it is more dynamic. The "amp" in the formula is a stock ticker. I want to replace this with a cell reference (say A1) so that I can change A1 and this formula automatically updates. I also want to change the dates in the formula to refer to other cells (A2 and A3). How can I do this?
Thanks,
joe
=BDDEHIST!'\\<DEFAULT>\amp.INT=DAY.FARD=06/14/07.NEARD=06/14/07.PRDS=1.EXCEL.QE=FLD_LAST'
This is a Reuter's DDE formula that is placed in an Excel cell to be evaluated. When I use this specific formula, I get 64.98 returned in the cell. I want to change this formula so that it is more dynamic. The "amp" in the formula is a stock ticker. I want to replace this with a cell reference (say A1) so that I can change A1 and this formula automatically updates. I also want to change the dates in the formula to refer to other cells (A2 and A3). How can I do this?
Thanks,
joe
I am attempting to replace a specific set of text within a hyperlink with text referenced from another cell. Also, I want to be able to do this accrost several cells.
for instance:
http://moneycentral.msn.com/investor/research/sreport.asp?Symbol=TICKER&FRK=1&Type=Equity
I want to be able to replace the phrase "TICKER" with the text from a cell above it. Doing this acrost a row, so that the hyperlink is in cell B2, and the work replacing "TICKER" is coming from B1. Then the hyperlink in C2 is replaced with text from C1....etc. A formula would be best, I have no experience with visual basic.
Also, on another subject, is there anyway to automatically rename a worksheet after it has been created after getting info from external data, so it could possibly rename the worksheet with the above mentioned Ticker.
I really appreciate any help.
Thanks,
Stephen
for instance:
http://moneycentral.msn.com/investor/research/sreport.asp?Symbol=TICKER&FRK=1&Type=Equity
I want to be able to replace the phrase "TICKER" with the text from a cell above it. Doing this acrost a row, so that the hyperlink is in cell B2, and the work replacing "TICKER" is coming from B1. Then the hyperlink in C2 is replaced with text from C1....etc. A formula would be best, I have no experience with visual basic.
Also, on another subject, is there anyway to automatically rename a worksheet after it has been created after getting info from external data, so it could possibly rename the worksheet with the above mentioned Ticker.
I really appreciate any help.
Thanks,
Stephen
Hi All,
I am new in Excel, but I have some experience in general programming. I
have a problem with DDE dynamic formulas.
I have an application that maintains templates of Excel Formulas in SQL
Server. What I need is to load these formulas in Excel and evaluate them.
These formulas are, in fact, DDE Links to a resource server. The resource
values are updated as they change, and I need that the loaded formulas
respond to the DDE advise event. That is they should change continuosly as
the resource changes.
And more some formulas must be contructed based on a specific cell. For
instance Column 1 has the name of the resource, so the DDE Link formula in
column 2 must be of type =appservertopic!&CONTENTS OF ("C1").
Well if I manually put the formulas in a cell like "=apptopic!item" they work.
The Server is working OK !!!!
To load the formulas from SQL Server is simple I have already done it. The
problem:
 How to contruct the formula dynamically
 How to evaluate it so that it is refreshed as their value changes in
the server
Thanks in advance
I am new in Excel, but I have some experience in general programming. I
have a problem with DDE dynamic formulas.
I have an application that maintains templates of Excel Formulas in SQL
Server. What I need is to load these formulas in Excel and evaluate them.
These formulas are, in fact, DDE Links to a resource server. The resource
values are updated as they change, and I need that the loaded formulas
respond to the DDE advise event. That is they should change continuosly as
the resource changes.
And more some formulas must be contructed based on a specific cell. For
instance Column 1 has the name of the resource, so the DDE Link formula in
column 2 must be of type =appservertopic!&CONTENTS OF ("C1").
Well if I manually put the formulas in a cell like "=apptopic!item" they work.
The Server is working OK !!!!
To load the formulas from SQL Server is simple I have already done it. The
problem:
 How to contruct the formula dynamically
 How to evaluate it so that it is refreshed as their value changes in
the server
Thanks in advance
I know that there Excel has a built in macro calle EVALUATE() which can treat string as formula so I created a User Defined Function evalu() to call the macro.
Code:
To demonstrate how it works, please see figure1.
I named two columns, "Price" and "t". At E3 and F3 I enter the formula I want as text.
Supposing an array formula like "{=evalu(E3)} would work like calculating Price*t on every row.
Figure 1
HTML Code:
Indeed the evalu() function works, plx see figure2. However for formula with reference functions like INDEX, OFFSET, VLOOKUP (column F). The UDF evalu return only the first item (2) . It would have worked if I enter "=index(price, t)" for each row on Column F so I am sure the formula is just fine.
Figure2
HTML Code:
It seems that when the formula string (F3) contains INDEX, the evaluate() macro/ UDF evalu doesn't know to return an array of result.
I once thought of modifying the UDF so that it first calculate the result somewhere else and then return back o the function but EXCEL UDF cannot alter any cells other than the cell that called it (caller).
Doesn't anyone know how to modified the UDf so that it would return the array of result?
It is essential to use the UDF here because I am building a generic pricing worksheets and there are like 100 columns of variables which formulae differs by products. The last solution I can think of is writng a macro then copy the formula string down everytime a formula is changed. However this is not cool
Code:
Function evalu(mystr As String) As Variant evalu = Application.Evaluate(mystr) End Function
To demonstrate how it works, please see figure1.
I named two columns, "Price" and "t". At E3 and F3 I enter the formula I want as text.
Supposing an array formula like "{=evalu(E3)} would work like calculating Price*t on every row.
Figure 1
HTML Code:
A1 B C D E F 2 Named range Formula 3 Price t Price*t Index(Price,t) 4 2 1 =evalu(E3) =evalu(F3) 5 4 2 =evalu(E3) =evalu(F3) 6 6 3 =evalu(E3) =evalu(F3) 7 8 4 =evalu(E3) =evalu(F3) 8 10 5 =evalu(E3) =evalu(F3) 9 12 6 =evalu(E3) =evalu(F3) 10 14 7 =evalu(E3) =evalu(F3) 11 16 8 =evalu(E3) =evalu(F3)
Indeed the evalu() function works, plx see figure2. However for formula with reference functions like INDEX, OFFSET, VLOOKUP (column F). The UDF evalu return only the first item (2) . It would have worked if I enter "=index(price, t)" for each row on Column F so I am sure the formula is just fine.
Figure2
HTML Code:
A1 B C D E F 2 Named range Formula 3 Price t Price*t Index(Price,t) 4 2 1 2 2 5 4 2 8 2 6 6 3 18 2 7 8 4 32 2 8 10 5 50 2 9 12 6 72 2 10 14 7 98 2 11 16 8 128 2
It seems that when the formula string (F3) contains INDEX, the evaluate() macro/ UDF evalu doesn't know to return an array of result.
I once thought of modifying the UDF so that it first calculate the result somewhere else and then return back o the function but EXCEL UDF cannot alter any cells other than the cell that called it (caller).
Doesn't anyone know how to modified the UDf so that it would return the array of result?
It is essential to use the UDF here because I am building a generic pricing worksheets and there are like 100 columns of variables which formulae differs by products. The last solution I can think of is writng a macro then copy the formula string down everytime a formula is changed. However this is not cool
I am working on a mathematical software that requires the user to enter a function which is alphanumeric into a textbox. The problem is that , when the user enters the function , it is taken as a string so the machine cannot evaluate it. eg if the function is f(x) = x^21 and want to evaluate f(2).
please how can i remove the function in the string to normal that can be recognize by excel/vba.
please how can i remove the function in the string to normal that can be recognize by excel/vba.
I'm trying to use the Evaluate function in my UDF and everything's done correctly but it gives me this dreaded error every time. This is just a test UDF, not the one I'm using; something wrong with the Evaluate formula and I really need it to work.
Function test(ctg As String) As Variant
test = Evaluate("CONCATENATE(" & ctg & ")")
End Function
Should result in just returning the input argument, but gives me error.
Function test(ctg As String) As Variant
test = Evaluate("CONCATENATE(" & ctg & ")")
End Function
Should result in just returning the input argument, but gives me error.
Hello, I have an excel addin that performs "behind the scenes" operations that are hidden from the user. Within the addin I have a function
"OPS(form as String)". This function allows the user to enter a formula just like the formula bar in excel. i.e. "OPS(B1 + .05*(B2 + B3) + Sheet2!B1^2)".
I would like to manipulate the input string and replace all cell references with a call to a second function using those cell references as arguments..
i.e. "OPS(B1 + 0.5*(B2 + B3) + Sheet2!B1^2)" = FX(B1) +0.05*(FX(B2) + FX(B3)) + FX(Sheet2!B1)^2"
My first thought was to use find and replace to locate the cell addresses and then replace them with FX( cell address); however, I have not been able to gain any traction.
Does anyone have any suggestions of string manipulations that may be suited for what I am trying to do. ... Is there a standard way of identifying a cell reference within a string?
Thanks in Advance for any help or suggestions.
"OPS(form as String)". This function allows the user to enter a formula just like the formula bar in excel. i.e. "OPS(B1 + .05*(B2 + B3) + Sheet2!B1^2)".
I would like to manipulate the input string and replace all cell references with a call to a second function using those cell references as arguments..
i.e. "OPS(B1 + 0.5*(B2 + B3) + Sheet2!B1^2)" = FX(B1) +0.05*(FX(B2) + FX(B3)) + FX(Sheet2!B1)^2"
My first thought was to use find and replace to locate the cell addresses and then replace them with FX( cell address); however, I have not been able to gain any traction.
Does anyone have any suggestions of string manipulations that may be suited for what I am trying to do. ... Is there a standard way of identifying a cell reference within a string?
Thanks in Advance for any help or suggestions.
Sometimes Excel just stops processing certain formulas on a worksheet and
considers the as plain text (regardless of the fact that the cell contents
starts with an equation sign "="). For example formula "=1+2" doesn't show
anymore as "3" but as "=1+2", as if it's only a string of text .But if I type
the same formula in the cell next to it, it shows the correct result "3".
Also, when I try to evaluate the formula, excel tells me that "the cell
currently being evaluated contains a constant"  so its not about wether my
formulas are "visible" (toolsoptionsviewformulas) or not. The number of my
formulas asre always under the maximum 1024 characters.
considers the as plain text (regardless of the fact that the cell contents
starts with an equation sign "="). For example formula "=1+2" doesn't show
anymore as "3" but as "=1+2", as if it's only a string of text .But if I type
the same formula in the cell next to it, it shows the correct result "3".
Also, when I try to evaluate the formula, excel tells me that "the cell
currently being evaluated contains a constant"  so its not about wether my
formulas are "visible" (toolsoptionsviewformulas) or not. The number of my
formulas asre always under the maximum 1024 characters.
Hi guys first post for me..
Firstly what I trying to do is use SUMPRODUCT to work out the duration of and instances that match B6 and are between the dates C1 and D1 and then divide that number by the amount in instances of B6 and >C1 and
At the moment I have this
=SUMPRODUCT((SYS=$B6)*(SDATE < D1)*(SDATE > C1))+((DUR)/(SUMPRODUCT((SYS=$B6)*(SDATE < D1)*(SDATE > C1))))
but it gives 24:02 and not the 00:09 that it should
SYS = what system did outage effect
SDATE = start date of the outage
DUR = duration of the outage
B6 = SYSTEMX
C1 = Jan
D1 = Feb
Can someone please point me in the right direction
Firstly what I trying to do is use SUMPRODUCT to work out the duration of and instances that match B6 and are between the dates C1 and D1 and then divide that number by the amount in instances of B6 and >C1 and
At the moment I have this
=SUMPRODUCT((SYS=$B6)*(SDATE < D1)*(SDATE > C1))+((DUR)/(SUMPRODUCT((SYS=$B6)*(SDATE < D1)*(SDATE > C1))))
but it gives 24:02 and not the 00:09 that it should
SYS = what system did outage effect
SDATE = start date of the outage
DUR = duration of the outage
B6 = SYSTEMX
C1 = Jan
D1 = Feb
Can someone please point me in the right direction
I am totally new to excel macro
I get code from one of the old forum but really don't know how to make it work in my spreadsheet.
after I cut and paste this in MS visual basic, and try to run it, it keep ask me for the macro name, when I enter test as a name
it will create
Sub test()
End Sub
on top of the function and the function do not work.
what do I miss, or is there any better way to make a sound alert when the cell > certain number
thanks.
here is the code that I get:
Playing a Sound Based on a Cell's Value
Some people like audio feedback. For example, you might want to hear a sound when the value in a particular cell exceeds a certain value. Excel does not support this feature, but it's fairly easy to implement with a custom worksheet function that uses a Windows API function.
For general information about playing a sound file from Excel, click here.
The Alarm function
Copy the code below to a VBA module in your workbook.
'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
NOTE: The Alarm function expects a WAV file (named sound.wav) in the same path as the workbook. You will need to change this statement to match the name (and path) of your actual sound file. If the sound file is not found, the default system sound will be used.
Using the Alarm function in a formula
The Alarm function monitors a cell for a specified condition. If the condition is met, the sound file is played and the function returns TRUE. If the condition is not met, the sound file is not played and the function returns FALSE. The Alarm function takes two arguments:
* Cell: A reference to a single cell (the cell that you are monitoring). Normally, this will be a cell that contains a formula (but that is not required).
* Condition: A text string that describes the condition
Following are examples of formulas that use this function:
=Alarm(A1,">=1000")
The sound will play when the value in cell A1 is greater than or equal to 1,000.
=Alarm(C12,"<0")
The sound will play when the value in cell C12 is negative.
Tips
* The function is evaluated whenever any cell that depends on the reference cell is changed. The sound can get annoying!
* Normally, you will want to use this function in only one cell. If you use it in more than one cell, you will not be able to tell which instance of the function triggered the sound.
I get code from one of the old forum but really don't know how to make it work in my spreadsheet.
after I cut and paste this in MS visual basic, and try to run it, it keep ask me for the macro name, when I enter test as a name
it will create
Sub test()
End Sub
on top of the function and the function do not work.
what do I miss, or is there any better way to make a sound alert when the cell > certain number
thanks.
here is the code that I get:
Playing a Sound Based on a Cell's Value
Some people like audio feedback. For example, you might want to hear a sound when the value in a particular cell exceeds a certain value. Excel does not support this feature, but it's fairly easy to implement with a custom worksheet function that uses a Windows API function.
For general information about playing a sound file from Excel, click here.
The Alarm function
Copy the code below to a VBA module in your workbook.
'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
NOTE: The Alarm function expects a WAV file (named sound.wav) in the same path as the workbook. You will need to change this statement to match the name (and path) of your actual sound file. If the sound file is not found, the default system sound will be used.
Using the Alarm function in a formula
The Alarm function monitors a cell for a specified condition. If the condition is met, the sound file is played and the function returns TRUE. If the condition is not met, the sound file is not played and the function returns FALSE. The Alarm function takes two arguments:
* Cell: A reference to a single cell (the cell that you are monitoring). Normally, this will be a cell that contains a formula (but that is not required).
* Condition: A text string that describes the condition
Following are examples of formulas that use this function:
=Alarm(A1,">=1000")
The sound will play when the value in cell A1 is greater than or equal to 1,000.
=Alarm(C12,"<0")
The sound will play when the value in cell C12 is negative.
Tips
* The function is evaluated whenever any cell that depends on the reference cell is changed. The sound can get annoying!
* Normally, you will want to use this function in only one cell. If you use it in more than one cell, you will not be able to tell which instance of the function triggered the sound.