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

 

[Pocho]

The reason for doing it with a udf is that there will be a worksheet with thousands of calls to this data. To calculate a return, for example, the formula in the cell would get the price on two days and then use that to calculate an annualized return. Everything would be perfect if it were not for the fact that there is a limit to how many sheets I can have open in a book (this is determined by the amount of memory in the computer). I have just 1.25 GB. I therefore decided I would just keep the data in several files, but it seems my assumption that I could get the data from these books with a UDF was simply wrong.

It sure does seem silly that a workbook can access data from a closed file and a UDF cannot.

 

[stanleydgromjr]

Good news.

Reference:
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!

I have adjusted the above UDF/macro code. With some test data, I was able to pull in a range of cells, A1:O101, from a closed workbook in less then 1 second.


Have a great day,
Stan

 

[stanleydgromjr]

Good news.

Reference:
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!

I have adjusted the above UDF/macro code. With some test data, I was able to pull in a range of cells, A1:O101, from a closed workbook in less then 1 second.


Have a great day,
Stan

 

[Pocho]

My understanding is that a UDF and macro may have similar syntax, but UDF's can't do all what a macro can do. (ei. open a file). Are you saying that with thi code you can access data from a closed file with a UDF. Remember, it is not a solution to have to pull all the data when I open the workbook, because there is too much data for excel to handle at one time (or else I would not have different files).

Thank you