User defined function

Pocho

New Member
Joined
Sep 25, 2006
Messages
12
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Joined
Jul 30, 2006
Messages
3,656
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

New Member
Joined
Sep 25, 2006
Messages
12
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

New Member
Joined
Sep 25, 2006
Messages
12
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.
 
Joined
Jul 30, 2006
Messages
3,656

ADVERTISEMENT

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

New Member
Joined
Sep 25, 2006
Messages
12
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?
 
Joined
Jul 30, 2006
Messages
3,656

ADVERTISEMENT

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
 
Joined
Jul 30, 2006
Messages
3,656
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

New Member
Joined
Sep 25, 2006
Messages
12
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

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
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:
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.
 

Forum statistics

Threads
1,137,205
Messages
5,680,184
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top