PDA

View Full Version : Solved: Function in add-in - How to reference workbook?



JWhite
07-08-2008, 10:09 AM
I have several functions in an add-in which work fine EXCEPT when there are multiple user workbooks open. The functions refer to specific named ranges which are different in each workbook.

My problem is that I've discovered that when the active workbook recalculates, it works fine. But the other open workbooks also recalculate and the functions in those workbooks pick up the values from the ACTIVE workbook.

To make a simple example (which I tried when I was debugging), the function refers to cell "A1" which has different values in Workbook1 and Workbook2. I put a MSGBOX in my formula so that it would tell me the value of "A1" every time it calculated the formula. The formula existed once in each workbook.

When Workbook1 was active, the formula showed that the value of "A1" was the value from Workbook1 even when it was calculating the formula in Workbook2. When Workbook2 was active, it used the "A1" value from that workbook to calculate the formula in Workbook1 and Workbook2.

So is there a way to reference "A1" to specify that it refers to the same workbook or worksheet that the formula is in?

mdmackillop
07-08-2008, 10:29 AM
Can you zip and post your samples?

JWhite
07-08-2008, 12:47 PM
I can give you a very simple example.

Function testfunc()
testfunc = [A1]
End Function

I should have mentioned in my previous post that this problem occurs when I do an "application.calculatefull", which I am forced to do in some circumstances. For example, I have functions which pull data from pivottables and when I load new data into the pivottables I need the functions to recalculate. The only thing that works is "application.calculatefull" which causes all open workbooks to recalculate.

I can see what the problem is. In the example above, the code for the function is in the add-in workbook but if I have "=testfunc()" in two different workbooks, the function always picks up the value in "A1" from the Activeworkbook. Which makes perfect sense. The default is always the activeworkbook and activesheet so the function assumes that's what it's supposed to get. My problem is I don't know how to refer to the "Workbook that has the function in it". I can't say "Thisworkbook" because that refers to the add-in. I'm hoping there's some other syntax that I haven't been able to find that refers to "cell A1 in the workbook that has the formula".

grichey
07-08-2008, 01:04 PM
Can you open your workbook you're working with in a new instance of excel which should make calculatefull only happen for the one workbook in it's own isolated instance of excel (ie the rest of whatever you have open are in the other instance)?

Just a thought.

JWhite
07-08-2008, 01:28 PM
Thanks for the idea but I don't think I can force my users to accept that limitation. I just tested it and you are correct that it does work.

This is going to be a commercial product and I've got to be ready for whatever the users do.

My only other alternative is to find another way to control the recalc's so they don't affect other workbooks. I remember seeing something where you can set a range of cells or a workbook or something so that all cells think they need to be recalculated and if I can do that then maybe a simple recalculate instead of a full recalculate would do it.

As I said in my last post, I had found that a "recalculatefull" in some circumstances but maybe there's another way to do what I need.

JWhite
07-08-2008, 02:10 PM
It looks like I have created my own problem here, in a way. I have some functions which pull data from a pivottable. The pivottable data can be re-loaded and changed at any time and I had found that my functions did not automatically recalculate to reflect the new data. Then I found that by executing an "application.calculatefull" it forced the recalculation. Problem solved - I thought. This was some time ago when I was just getting started with VBA.

Since then I had read about "volatile" functions but I wasn't having any problems so I didn't change my functions to be "volatile". Now I ran into a problem with my original solution, which only occurs when multiple workbooks are open with different values. In this case, the "calculatefull" causes problems in my functions as I outlined above.

Now I've gone back and made my functions "application.volatile" and I find that I really never needed to do the "calculatefull" in the first place. At least, that's what it looks like at the moment.

If anyone has any comments on all this, I would appreciate it. And how do I close this post as "solved"?

mdmackillop
07-08-2008, 03:29 PM
Why not pass the locations to the function
Sub Test()
MsgBox TestFunc(ThisWorkbook.Name, ThisWorkbook.Worksheets(2).Name)
End Sub

Function TestFunc(WB, WS)
TestFunc = Workbooks(WB).Sheets(WS).[A1]
End Function

JWhite
07-08-2008, 07:10 PM
Is there a way I can pass the workbook and sheet without having the user enter it?

As I said, this is a commercial product, about to be installed in the first paying customer (although this affects just one module in the product) and I can't really ask the users to enter that information along with the other parameters they already have to provide.

I think I can work around it but it just seems like there should be some way to refer to the workbook that the function is IN without having to provide it as an input parameter.

TomSchreiner
07-08-2008, 09:35 PM
You should be able to determine the source using Application.Caller.

Function testfunc()
If TypeOf Application.Caller Is Range Then
testfunc = Application.Caller.Parent.Range("A1")
End If
End Function

Just a side note that may impact performance using the evaluate function as in your example. [A1]

This can really slow things down if you are referring to many ranges...

mdmackillop
07-09-2008, 12:22 PM
Is there a way I can pass the workbook and sheet without having the user enter it?
Doesn't my example show that?

grichey
07-09-2008, 12:51 PM
What does the addin actually do? Just curious.

JWhite
07-09-2008, 02:37 PM
Tom - That is exactly what I was looking for. I hadn't seen that before but it answers my question perfectly. I found these useful variations, which you apparently already knew about:

Application.Caller.Address gives me the cell address that the function was called from.
Application.caller.parent.name gives me the sheet name.
Application.Caller.parent.parent.name gives me the workbook name.

This is going to be very useful. Thanks again. And thanks for heads-up on the performance issues related to "evaluate". Looking at my code, I can see I've used it in a lot of places where it was convenient, but not necessary.

Gavin Richey - You asked what the add-in does. It's still proprietary until the product is released (not that you're ever going to hear about it - it's for a very specific market) but it pulls large volumes of data from an SQL database and uses pivottables and specialized functions to let the users manipulate the data. My biggest problems have been allowing for multiple workbooks and pivottables and making sure my add-in subroutines and functions service them all independently without getting them mixed up.

MdMacKillop - Thanks for your input. My question about your example was, are you suggesting that the users input the workbook name and sheet name when they use the function? If there is a way to automatically pass them through without the users having to enter it, that would be great.

mdmackillop
07-09-2008, 03:22 PM
MdMacKillop - Thanks for your input. My question about your example was, are you suggesting that the users input the workbook name and sheet name when they use the function? If there is a way to automatically pass them through without the users having to enter it, that would be great.

No, and is that not what my code does?

JWhite
07-09-2008, 03:41 PM
mdmackillop - I'm probably not qualified to answer this. I tried your function exactly as written. The only way I could make it work was to enter the workbook name and sheet name as function parameters, for example:

=testfunc("Book1", "Sheet1")

Then the function correctly displayed what was in the cell "A1" in the workbook "Book1" and the sheet "Sheet1". That is the result I wanted but I can't ask my users to type in the workbook name and sheet name every time they want to use my function. With the "Application.caller" syntax, I can find out the workbook and sheet names within my own code. Am I missing something?

mdmackillop
07-09-2008, 03:58 PM
I've no idea if you are talking about spreadsheet functions or VBA functions. I had assumed the latter.

JWhite
07-09-2008, 04:58 PM
mdmackillop - OK. That explains the confusion. The functions are in my add-in workbook and they're being referenced from user workbooks as user-defined functions. Now I see what you were getting at. What you suggested works fine if the functions are called from VBA.

I'd like to mark this as "Solved" but I can't see where to do that.

I've gone back and re-coded my functions using the "application.caller" and they work fine. In my function I was able to say:

Dim wb as workbook
Set wb = application.caller.parent.parent

I store workbook-specific values for each workbook in named values, for example, Region. So from that point on I could reference the Region for the workbook as wb.names("Region") and the function picks up the right region for the right workbook. So cool.

mdmackillop
07-09-2008, 11:38 PM
You can mark a thread solved in the Thread Tools dropdown.