No automatic evaluation of VBA function

[benwin]

I am stuck with a worksheet which displays a strange behavior.
"Automatic calculation" is set.

Let's say "A1" contains either 0 or 1 and A2 some random number.

B1 contains =IF(A1;"True";"False")
C1 contains =IF(A1;f1(A2);f2(A2))

with f1 and f2 being VBA functions.

When I open the worksheet or change the entry in A1, B1 is automatically updated whereas C1 is not.
However, when I enter C1 by pressing key "F2" and press "Enter", also C1 is evaluated appropriately.

Any ideas why this is happening, or how I could have C1 evaluated automatically? I'd appreciate any help.

In case this is relevant: f1 and f2 are declared with optional arguments:

Please Login or Register  to view this content.

[JosephP]

does your function code contain anything like

Please Login or Register  to view this content.

[benwin]

No, there's no such code in my function.
For testing purpose, I've implemented that line

Please Login or Register  to view this content.

and it doesn't change the behavior.
It would be interesting, if it did, but the "volatile" approach wouldn't really fit my demands (the function is called quite often on that sheet and I don't want it to compute whenever unrelated cells change.)

I forgot to mention two possibly important points though:

1. After I force the evaluation of C1 (via "F2" and "Enter") once, it is automatically updated whenever I change A1 or A2.

2. When I save & close the file after C1 is evaluated, and then open it again, C1 contains the value 0. This value does definitely not stem from either f1 or f2! I have absolutely no explanation for this.

[JosephP]

I was not suggesting you add such code but rather that it would have caused the problem you mention. currently the only thing that makes sense to me is that your function code is wrong somehow (for example referring to cells in the code that are not passed as arguments) but since you have not posted it I can't be specific.

[benwin]

Thank you for the quick replies!

The code is too complex to post it here, but I do have a suspicion ...
I'll get back to you tomorrow. If it is what I suspect it is, it could be interesting for others as well.

[benwin]

The issues here were two identically named functions - e.g. f1() - in a global Add-In and the workbook's VBAProject.

Strangely, when the workbook is opened, the Add-In function is executed.
When referenced data change (here: A1 or A2) again the function from the Add-In is executed.
However, when the formula is executed manually ("F2" & "Enter") the function from workbook's module is executed and remains active henceforward.

Saving, closing and reopening has the Add-In function referenced again.

I don't have a solution yet. The identical function names must be kept.

[Kyle123]

Identical function names sound like a bad idea to me - tbh I didn't even know that was possible in VBA. I suggest that you start looking for a workaround

[benwin]

Actually, I thought this was possible, with a clear priority for functions in workbook modules over functions in Add-Ins.

Are you certain that this is not the case?
It would be far less convenient to build a workaround. I keep updating the Add-In on a regular basis, but will attach the functions to a workbook if someone else is going to use it on a different machine. Sometimes, like here, different version produce different results.

Is it possible to block functions of one specific Add-In from being executed within one specific workbook (i.e. without unloading the Add-In completely)?

[Kyle123]

Nope. I'm not sure at all though tbh I've never tried; it just seems surprising - multiple functions with the same name and parameters returning different results sounds like confusion at best and a disaster at worst

It sounds like you need to implement some version control so that you're always using the latest version regardless of whether the code resides in a module or add-in. This should be relatively straightforward - if you do this a lot it might be worth looking at some additional tools to help out. I use TortoiseSVN - subversion for windows, this allows you to make regular revisions to your code, all of which are saved and you can roll back all your code to a given version which is all held centrally, you can then use whichever version you want but it will be consistent.

[JosephP]

were the functions originally called from the add-in?

[benwin]

I see your point Kyle, but I do not want to use the latest version under all circumstances. If I provide someone else with a workbook, I want it to work without an AddIn. Therefore I copy the functions from my AddIn into a module of that workbook. The workbook in itself is then consistent and shall remain that way.
When I reopen that workbook later on my PC, it is still working, independent of any modifications I applied to my AddIn.

At least, that's the behavior I aspire, and it would work that way, if identically named workbook functions were prioritized above AddIn functions.

@Joseph: I'm not sure I understand your question. (Though, admittedly, it looks very simple.) The VBA functions are called from the worksheet, and some nested functions are called. These are located in the same module as the main function.

[JosephP]

if the code only existed in the add-in at the time you entered the formulas into the cells, the full path and file name of the add-in is part of the function call (you can see this by moving the add-in and reopening the file). simply adding code to the workbook will not reliably override that-you have to convert the formulas to text,save,close and reopen the file then convert the text back to formulas after addin the code to the workbook. in my experience this is the only foolproof method if you want to use the same function names-I had similar issues with a particular com add-in many years ago.

[benwin]

That's a great explanation!
Although I wonder why it switches to the workbook-version of the function once I manually evaluate the respective cell.

I stumbled on this path issue a while ago in a different context, and was really upset, that even on VBA-level you have no control over these "hidden" path variables.
No tools at the UI either. We're just bound to use workarounds like the one you just described.

If someone knows a cleaner way I'd be happy to hear about it.

[JosephP]

http://www.jkp-ads.com/Articles/FixLinks2UDF.asp

[benwin]

Interesting read, thank you!
But really, why MS doesn't provide a more convenient solution is beyond me. Relative paths, and easy alteration of those can't be too difficult to implement.

Anyway, the approach with corrected paths (your post #12) doesn't work together with the redundant function names.

For testing purposes, I disabled the AddIn. As expected, next time I opened the workbook, the function calls contained the path to the disabled AddIn and evaluation led to errors. (The suggested auto-updating of sources didn't work - apparently the updater doesn't search the workbook modules for function substitutes.)

Just removing the obsolete path from the function calls an everything worked smoothly. The function out of the workbook module was called.

Save & close & reopen -> everything remains just fine.

Save & close & Activate AddIn & reopen -> Excel again prefers the AddIn functions and does neglect those from the workbook module. It doesn't even ask or provides a message that paths are altered. It just does.

This is odd.

[JosephP]

isn't that what you want? disable the add-in, remove the path, save and send the file out. then when you get it back if your add-in is enabled, the file will pick it up. if that's not the behavior you want then you'll need to change the function names or prefix them with the project name

[benwin]

The "pick it up part" without even asking or giving me an option is the annoying one.

Obviously, in my case, renaming all functions and function calls would be a workaround.
But this would happen as well if I opened someone elses workbook with functions which coincidently have the same name as functions in any of my AddIns. No message, no warning - just different and most likely wrong results, at least when the arguments are compatible.

I might be missing something, but if this behavior is unalterable, it is worrysome.

[JosephP]

I concur-I'm gonna do some checking to see if it was the same in earlier versions then I'll get it filed as a bug although I doubt it will be fixed

[benwin]

Oh, thanks a lot!

Btw. "sandy, ut" means Utah, right? Do you sleep sometimes?

[JosephP]

correct :-)
I sleep maybe 4 hours a night then play catch-up at weekends

[JosephP]

ok so I tested in 2003 and could not reproduce this problem-the code always defaulted to the workbook not the add-in. then I opened that .xls file in 2010 and still no problems. then I saved it as a .xlsm and reopened it and it immediately defaulted to the add-in code. saving as an .xlsb once again removed the problem so it must be an issue with the xml formats. I'll get someone to report this as a bug but don't expect too much

[benwin]

Thanks again for your efforts! I'm glad you could reproduce this.

Have a good nap then
___
Ben