User defined function

[Pocho]

Hello,
I am new to VBA and I have written macros that have created data pages for securities. Due to memory limitations, I put just 50 sheets per workbook. What I want to do is create a function that will return data from one of the pages in the appropriate file(workbook). Can a workbook be opened with a function? If so, will multiple function calls happen in parallel (this would be a problem as the computer would crash if all the files were opened at once)?

Thanks for any insight.

Pocho

 

[stanleydgromjr]

Welcome to the board,

It is possible to get information from a closed spreadsheet/workbook.

What is the name and path of the file/spreadsheet/workbook?
example: C:\My Documents\Excel\filename.xls

What is the sheet name where the data exists in the file?

What is the cell address of the data?


Have a great day,
Stan

 

[Pocho]

That is great news! I was starting to think I have worked hours on uselesss code and I would have to figure out how to use access as a backend (which I will do eventually - but I'm under the gun to produce some results from what I have done). Now you offer a glimmer of hope. The path is easy enough, but I still wonder if what I want to acomplish is possible. There is a master file from which the data files are created with a macro using an inteface with a data provider (bloomberg). The master file is broken into workbooks of 50 worksheets each which contain price data for every day since the security started trading. The worksheets are labeled with the ticker of the security, and the origional master fund keeps the name of the data file where the data for that security is found. The idea is that the UDF finds the name of the file with the relevant data (I thought I'd use vlookup), then opens that file and accesses the data. Again with vlookup finds the price for the date passed into the function (vlookup again). So the function takes two arguments, ticker and date. The master file tells the function what data file to get the data from (vlookup by ticker) and the tickr also gives the name of the worksheet to acces, then the date is used to get the price. The function returns the price. Can this be done? the path is just c:\model for the directory where the master fund and data files reside. Can vlookup or any other matching function be used on a closed file? Can a function open a file if that has to be done? I could use a boolean variable to keep only 1 data file open at a time - if the workbook calling the UDF has multiple cells calling the UDF and the function calls are not sequencial. I have had a terrrible time finding out what a function can and can not do and since I'm a begginer, I can't be sure if it's not possible or if I have syntax issues. So, I do not know the cell, I would have to be able to look that up. Thanks for helping me out.

Pocho

 

[Pocho]

OK, overnight I did think of a way to solve the problem even if Vlookup does not work on a closed file and a function can not open a workbook. Really changed thing to be able to access a closed file. You see, the master file can remain open (no memory issue it only has 1 worksheet). So vlookup to get the data file is simple. Then, I could use todays date and some mathematical calculations to get the cell number. I would prefer to use vlookup if that is possible if only to keep the code more readable. However, now that I know that it is possible to access a closed file, that may be far more efficient.

Let's say the data file is "c:\Model\data1.xls" and I want to access cell B10 or [2,10], what is the best way to access that cell from the closed file?

Thanks a bunch.

 

[stanleydgromjr]

Adjust the below formula:

Adjust the Sheetname below, with the correct sheet name in 'data1.xls'.

='C:\Model\[data1.xls]Sheetname'!$B$10

Then copy and paste the adjusted formula, above, with the correct sheetname, into a cell in a test spreadsheet.

The cell that you pasted the new formula into should display the correct information from 'data1.xls'


I am using Windows XP Professional SP2, and Excel 2003 SP2,

Have a great day,
Stan

 

[Pocho]

Thank you, that works on the worksheet.

I will have to implement it with a UDF, however. Will that change any syntax? Also, can the file name and Sheet name be variables?

Lastly, is it true that referencing with R1C1 will make it faster?

 

[stanleydgromjr]

You are welcome.

If you attempt to implement a UDF, it will change the syntax of the formula.

The file name and sheet name can probably be variables in a UDF.

I do not believe that referencing with R1C1 will make it faster.


Have a great day,
Stan

 

[stanleydgromjr]

You are welcome.

If you attempt to implement a UDF, it will change the syntax of the formula.

The file name and sheet name can probably be variables in a UDF.

I do not believe that referencing with R1C1 will make it faster.


Have a great day,
Stan

 

[Pocho]

So I am begining to wonder, again, if it is possible to get to the closed workbook data from within a UDF.

I tried:

dummy = Workbooks("c:\Model\data1.xls").Worksheets("ABNHYBU LX").Range(Cells(2, 2)).Value

where dummy is a string

and it did not seem to work. Is that the syntax you had in mind or do you have different idea?

I also came accross this:

http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

Can you understand what he is doing and is it relevant to my problem? Seems to me he only wants to get to data from a workbook which you just demonstrated possible!

Sorry for all the back and fourth, but as I said I am a novice and at this point in over my head. I do appreciate the help.

 

[dcardno]

Why do you want to do this with a UDF, rather than a formula (probably a Vlookup)?

I would suggest the following approach:
> Maintain a table, showing the stock ticker, the workbook that the relevent data is located in, and the tab (worksheet name) where it is listed
> Create a VBA procedure (not a UDF) that will accept a ticker - this could be supplied from an input box, or it could just be listed in a cell. Optionally, you could supply a to/from date range in the same way.
> The VBA routine will refer to the table noted above, and get the workbook / tab for the desired ticker. It will assemble the VLookup formula in the way shown above, something like warning - air code:

stForm = "=VLOOKUP(" & [rc-1] & "," & stPath &"\[" & stFile & "]" & stTab & "!$E$7:$F$500,2,true)"
selection.formula = stForm

where stPath, stFile, and stTab are the file path (either maintained in the reference table or just supplied as a constant) and the file and tab name as maintained in the reference table, and $E$7:$F$500 (or whatever) is just the known location of the dates and trade (or volume, etc) values in your data table. This formula could be copied down as far as required, or the lookup cells could be populated with the dates you are interested in based on any to/from dates supplied to the VBA procedure.

This routine would have to be run whenever you wanted to get data for a new (or different) ticker - but could be set up to copy the links for the ticker in the active cell (or a designated cell), so you could run it once for each ticker you were interested in.

I would move quickly to transfer the data to Access (or to the Open Office equivalent, which has the advantage of being free) - a downside of this worksheet-based approach is that it is very difficult to know that the reference formula is incorrect. If you used a database backend the requested ticker will be more visible and obvious, and the whole system will be more robust.