Evaluate Text String As A Function

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

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?

I have a spreadsheet that looks like the below that monthly can expand and contract both vertically and horizontally:

1/1/1999 1/2/1999 1/3/1999 GOOG 1 3 4 MSFT 6 7 9 EBAY 5 7 8

I am looking to use some series of functions that will allow me to automatically create something like the below automatically when I put in a new set of data.

Ticker date price GOOG 1/1/1999 1 GOOG 1/2/1999 3 GOOG 1/3/1999 4 MSFT 1/1/1999 6 MSFT 1/2/1999 7 MSFT 1/3/1999 9 EBAY 1/1/1999 5 EBAY 1/2/1999 7 EBAY 1/3/1999 8

I was thinking I may be able to use some combination of indirect and counta (for the columns) but have been unable to piece it together.

Thanks
Christian

Hey everyone,

This forum has been great in the past so I thought I'd asked another question.

Lets say I want to retrieve the balance sheet of a company using a dynamic web query that references a cell with a ticker symbol.

For example here's the balance sheet for google:
http://www.zacks.com/research/report...ype=abs&t=goog

If I replace goog with ["Ticker"] and click import, I'm stuck with a completely different screen from Zachs that returns an error for invalid lookup. The problem is that none of the tables I can select on the page turn into the balance the sheet after I set the parameter to my ticker cell. If I import the entire page it works, but that would be hell to deal with.

I know one way to deal with this is by making a macro of me importing the balance sheet for a particular security and then modifying the URL in VBA to reference the ticker cell on my worksheet. Is it possible to do it another way? Preferably a way where the moment I enter a new ticker symbol and click enter, it automatically retrieves the new balance sheet (like normally would happen if I click the parameter check boxes)?

Thanks a ton for any help!

Hi,
I need help in with my excel workbook.
I've created a sheet which contains text data,
a)Company Name(goog,yhoo etc),
b)Time Frame(Daily,Weekly,Monthly),
c)Ratio(P/E,EPS etc),
d)Price(Open,Close,High,Low)

I want a vba code, which will see the e.g goog ,daily,p/e,close from a range of cells and find the workbook in the name of goog,checks the sheet for ratio (P/E) and in the sheet checks for the column close.and copy the whole column infrom that workbook into my existing sheet. this process should be automated, means when the range e.g a1:d4 will contain any value e.g goog ,daily,p/e,close, it must automatically repeat the whole process and each the range is changed with the values, it must delete the old records or eaither save them in the new sheet.

Regards

Dear all,


I really could not find a simiar problem to mine, so I highly welcome any guidance from you:


String Example: "=APP|DATA!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 real-time 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 20-50 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 only-changing 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 automatically-updated formulas like "=APP|DATA!YHOO.Ask", "=APP|DATA!YHOO.Bid", "=APP|DATA!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 if-then 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 newly-created 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 "=APP|DATA!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 real-time 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)
' Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APP|DATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APP|DATA!'" & 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 real-time data to make and return numerous if-then calculations.

Currently, it does not because the FunctionEvaluate above returns "N/A".


Lastly, my attempts to formulate DDE strings and get the real-time data below failed:

=INDIRECT("APP|DATA!'" & $A1 &".Ask'")

=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")

=INDIRECT(CONCATENATE("APP|DATA!'",$A1,".Ask"))

=INDIRECT(CONCATENATE("=APP|DATA!'",$A1,".Ask"))

="=APP|DATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is not recognized by Excel here

=eval("=APP|DATA!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("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))


Since the old-macro 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: "=APP|DATA!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 real-time 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 20-50 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 only-changing 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 automatically-updated formulas like "=APP|DATA!YHOO.Ask", "=APP|DATA!YHOO.Bid", "=APP|DATA!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 if-then 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 newly-created 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 "=APP|DATA!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 real-time 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)
' Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APP|DATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APP|DATA!'" & 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 real-time data to make and return numerous if-then calculations.

Currently, it does not because the FunctionEvaluate above returns "N/A".


Lastly, my attempts to formulate DDE strings and get the real-time data below failed:

=INDIRECT("APP|DATA!'" & $A1 &".Ask'")

=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")

=INDIRECT(CONCATENATE("APP|DATA!'",$A1,".Ask"))

=INDIRECT(CONCATENATE("=APP|DATA!'",$A1,".Ask"))

="=APP|DATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is not recognized by Excel here

=eval("=APP|DATA!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("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))


Since the old-macro 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

Here's my abbreviated code:

Private Sub Worksheet_Change(ByVal Target As Range)
For each cell in Target
If cell.column = 1 and cell.row > 1 Then
ticker = CStr(UCase(Trim(cell.value)))
Call LinkRef (ticker)
End if
Next cell
End Sub


Sub LinkRef(ticker)
MsgBox ticker
End Sub

Of course the full procedure does more than display the message box.
The problem I'm having is that if the value in the target cell begins
with a special character, like $ or % as in $DJI or %GOOG, the sub
routine does not seem to execute. I've tried removing the UCase(),
Trim() and Cstr() to see if any of those could be the culprit, but no
such luck. Any insight into what might be the cause here? Any
solutions?

Thanks.

Dear all,

I really could not find a simiar problem to mine, so I highly welcome any guidance from you:

String Example: "=APP|DATA!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 real-time 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 20-50 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 only-changing 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 automatically-updated formulas like "=APP|DATA!YHOO.Ask", "=APP|DATA!YHOO.Bid", "=APP|DATA!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 if-then 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 newly-created 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 "=APP|DATA!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 real-time 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:
VB:

Function FunctionEvaluate(Symbol, Field) 
     '  Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
     '  APP|DATA!'Yhoo.Ask'  Example for Yahoo Ask price
    Dim Command As String 
    Command = "=APP|DATA!'" & Symbol & "." & Field & "'" 
    FunctionEvaluate = Evaluate(Command) 
End Function 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



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 real-time data to make and return numerous if-then calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".

Lastly, my attempts to formulate DDE strings and get the real-time data below failed:
=INDIRECT("APP|DATA!'" & $A1 &".Ask'")
=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APP|DATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APP|DATA!'",$A1,".Ask"))
="=APP|DATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APP|DATA!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("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))

Since the old-macro 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

Kadir

Dear all,

I really could not find a simiar problem to mine, so I highly welcome any guidance from you:

String Example: "=APP|DATA!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 real-time 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 20-50 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 only-changing 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 automatically-updated formulas like "=APP|DATA!YHOO.Ask", "=APP|DATA!YHOO.Bid", "=APP|DATA!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 if-then 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 newly-created 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 "=APP|DATA!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 real-time 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)
' Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APP|DATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APP|DATA!'" & 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 real-time data to make and return numerous if-then calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".

Lastly, my attempts to formulate DDE strings and get the real-time data below failed:
=INDIRECT("APP|DATA!'" & $A1 &".Ask'")
=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APP|DATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APP|DATA!'",$A1,".Ask"))
="=APP|DATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APP|DATA!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("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))

Since the old-macro 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

Whenever I get information from finance.yahoo.com or from my job and put it into excel all the information seems to go in one cell and numerous rows. (I think its b/c I am putting information that isnt' meant to be in excel and forcing it to open up there.)

For Example in cell A1 I will have
"AIG , AMER INTL GROUP I , 66.08 , 1:16pm , 169.455B , 16.736 , 2.01 , 9.47 , 0.78"

Cell A2 I will have
GOOG , GOOGLE , 508.9 , 1:21pm , 158.845B , 22.361 , 17.511 , 26.29 , 1

What I want to see is cell A1 as AIG and cell B1 as 66.08 and cell A2 - GOOG and cell B2 508.9. I don't even want the rest of the stuff.

Is there any way or formula I can use to just pull the information out of cell and have it separated for me?

Cannot reliably predict whether Excel will treat cell as a string or formula when I have a string: '=Sum(A1:A4) [say]
...and proceed to Search/Replace '=Sum with =Sum;
Sometimes will evaluate and sometimes will not.

>>My VBA solution is as follows -
but I would like to know a native Excel solution (if it exists).
'-----
Sub Toggle_Formula()
Dim oCell As Range
'This sub will toggle a cell between string/formula
'Sometimes you can achieve this with a search replace...sometimes not
'...and I cannot determine exactly when or why this Excel behaviour changes.
'Hence this function - which seems to work as required.

For Each oCell In Selection
If oCell.Formula = oCell Then
'Note this expression will force Excel to evaluate!!
'=============================
oCell.Formula = oCell.Formula
'=============================
Else
oCell.Formula = "'" & oCell.Formula
End If
Next oCell
End Sub

Here is a sample UDF that determines if the varible is a number Code:

Function isnum(temp As Variant)
Evaluate ("isnumber(temp)")
End Function

This is just a "sample" UDF to ask my question. What I want to do is have the evaluate function use the UDF varible (temp) in the isnumber function. I have tried using replace with the evaluate to no avail.

This problem seems even more difficult to me as temp could be cell reference, a string or a number. If temp is a string replace should put the string wrapped with "" in its place and if temp is a number or a cell replace should put the number or cell w/o "" .

Thanks in advance,

Tom

Hello,

I currently use a free add-in 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 add-in, 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 need some help creating a UDF that will evaluate the results of a
string that has been created using concatenate() that includes an
argument to be substituted in the resulting text string.

Concatenate results with the following text string:

(LogCCoef*LN(x))+LogBCoef

LogCCoef and LogBCoef are named excel formulas that return values. The
"x" is the placeholder for the argument that I would also pass to UDF
in the form a cell reference. The UDF would look like:

EvaluateThis(ModelFormula,b12)

Assuming:
ModelFormula (a named formula) is:
=concatenate((LogCCoef*LN(x))+LogBCoef)
LogCCoef (a named formula) returns: -6.35
LogBCoef (a named formula) returns: 55.8
Value in cell b12 is: 400

The UDF would return 17.75 [(-6.35*ln(400))+55.8]
Could someone provide some guidance?

GH

Hi there everyone,

I have a spreadsheet with a column of "ticker codes" for listed companies, i.e. "YHOO" for Yahoo!. I have approximately 30,000 individual codes in random order in the column. For example;
ABLSQ
ATVI
ARXX
AKAM
ARTC

I have coded 5,600 of these "ticker codes" 1 - 5,600 (For those "ticker codes" that are NOT in the list of 5,600 coded "ticker codes", I want to code them -9). For example the first 4 are;
ID Ticker
1 AIR
2 ADCT
3 ALO
4 AMR

My goal is to put some sort of formula in the "ticker code" column to identify if a particular cell is one of the 5,600 and otherwise code it -9. I thought about an "if statement" but I can't construct it.

Any thoughts on where to start?

Thank you!
Liv

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!

This is an example of what my project is. Basically each fund has an ID Tag that I need. My problem is explained in the picture below. Basically, I can't use VLOOKUP since I have multiple times I invest in the same fund in different portfolios. So excel doesn't know what to look at. I need to find some way where I can connect my program to excel and make the extraction easy.

My program gives me all my funds in a list by portfolio, but without spaces. In my portfolio view tab I have extra rows that are empty because some portfolios have 10 funds while others have 4. I have to include the maximum amount of rows for the biggest possible combination of the portfolios.

So my question is how could I link up what the program gives me with my tab in a way that it understands I have some rows that are empty and needs to take that into account when searching for the ID's.

The funds are broken up too. So like there is a section for Investment-Grade and another for High-Yield. When it notices a name like this, it needs to stop with the ID's etc...

I know this is difficult to explain and understand without the actual file, but I can't supply this due to confidentiality at my work.

Thanks if anyone can help with this. I'm just trying to bounce ideas around right now. I have it working, but I rigged it in a way that isn't very beneficial to us and a bit complex. I'm hoping to have a better system in place than what I did which was manually typing in the ID's with empty rows where they were needed.





Sheet1

A B C D E F G H I J K L M N 1 2 Portfolios Portfolio Views 3 4 View A View B View C View D I have a delete rows button which removes any row with 0's. So when you view port A you don't have to see the 0's. 5 A B C D Portfolio Portfolio Portfolio Portfolio 6 INTC AAPL CSCO PALM INTC AAPL CSCO PALM 7 AAPL BBY BBY INTC AAPL BBY BBY INTC 8 BBY PALM GOOG BBY PALM GOOG 0 9 GOOG AAPL GOOG 0 AAPL 0 10 INTC 0 0 INTC 0 11 12 13 14 Program 15 16 A There are no spaces in between the portfolios. I can't make the program have empty rows for what the maximum portfolio could hold. 17 INTC 18 AAPL 19 BBY So how do I build a formula where I can lookup the fund's info by portfolio and it can understand that I have empty rows in a few places? 20 GOOG 21 B 22 AAPL I'm having trouble because it doesn't know which AAPL to look up sine AAPL is in 3 of the portfolios. It is also causing problems because some portfolios have 3 funds while others have 5 or 2. I can't do a standard VLOOKUP. 23 BBY 24 PALM 25 C 26 CSCO 27 BBY 28 GOOG 29 AAPL 30 INTC 31 D 32 PALM 33 INTC

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Does Excel have any 'built-in' 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?

All,


String Example: "=APP|DATA!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. APP|DATA!FIELD1.DETAIL1, APP|DATA!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