disable VB recalculation

**jmhultin** · 05-12-2010, 04:54 PM

I am using some large workbooks that have some formulas that have some VB components to them. I'm VB illiterate but pretty adept at the rest of Excel. There are a number of formulas that contain some components that I don't recognize, and I'm told by the author of the workbook that those are VB functions, and reference data outside the workbook that I'm using. And that's fine, I'm not trying to calculate anything myself - I just need to understand the flow of the workbook.

I'm using 2007, the file is xls, and running in compatibility mode. The problem is that sometimes, but not always, I will get reference and name errors in those cells with the VB formulas (and all dependent calculations). Often the workbook will be fine for 10 o 20 minutes, then suddenly all the errors will show up. Other times I can't even open the workbooks without getting the errors. I've tried disabling macros to no avail. The recalculation setting doesn't make any difference.

I might mention that when I open any of these workbooks, a VB edit screen automatically pops up, and I have to "OK" a compilation error dialog box and a box that says "this command will stop the debugger" before I can actually view the data I'm interested in.

**DonkeyOte** · 05-13-2010, 04:51 AM

#NAME? errors typically arise for the following reasons:

1 - XL is interpreting a string as a named range which it can't locate - eg =REPT(Apple,1) ... meant "Apple" but XL sees Apple as being a Named Range (generally speaking) and can't locate it.

2 - the function used is not found eg VLOOKUPX(10,A1:B10,2,0) ... VLOOKUPX doesn't exist

It sounds here as though no2 is the culprit... in terms of User Defined Functions (UDFs) the #NAME? will result because either

a) Macros are disabled (ie the UDF can not be accessed)

b) UDF doesn't exist (eg VLOOKUPX is neither UDF nor standard function)

c) Though Macros are enabled and UDF exists it (UDF) is stored in an incorrect place in the VB Editor (UDFs must be stored in Standard Modules)

It's hard for us to deduce which of the above is to blame but obviously a) is a common cause.

Originally Posted by jmhultin

...when I open any of these workbooks, a VB edit screen automatically pops up, and I have to "OK" a compilation error dialog box and a box that says "this command will stop the debugger" before I can actually view the data I'm interested in.

This obviously does not sound good but we'd need more info - if you have the ability to click "Debug" do so and post back with whatever appears in yellow.

This is one of those things that could well prove very difficult for anyone here to pinpoint, the more info. you can provide the better.

**jmhultin** · 05-14-2010, 06:34 AM

Thanks for your help. I've now found that if I open it in 2003,I don't get the debugger messages, and I seem to be able to work with the workbook much longer before I get the error messages.

It's not exactly a solution, but it's working for me in the present circumstances.

Thanks again.

**DonkeyOte** · 05-14-2010, 11:36 AM

If / when you open in XL2007 and it debugs - in the VB Editor go to Tools -> References ... see if anything listed is denoted as MISSING and post back with your findings.

I suspect there are a few errors but you might find that a control / library is being used in the original that is not available in XL2007 by default (eg Calendar Control, Office Web Components etc...)

Let us know.