Workbook content changed when other workbooks are active

[Guest]

Excel 200

I have created a very complex application in Excel using lots of macros, user defined forms and event handling so it e.g. does automatic formatting of worksheets

The application works fine stand-alone but when other completely different worksheets are open and active sometimes formula cells on my application gets a new value typically zero
Using the "CalculateAll" function brings the data back on track but it is quite annoying

Do anybody have an idea why this is happening - and how it can be avoided?

 

[Jan Karel Pieterse]

Hi Kim,

The application works fine stand-alone but when other completely different worksheets
are open and active sometimes formula cells on my application gets a new value typically zero.

Sounds like you have some functions in there that rely on the active workbook/worksheet.

Any used defined functions?

Are you by any chance using defined names that look like this (RefersTo string):

!A1

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Guest]

Yes I do have User defined functions and they are using "Global Data", which means some kind of reference to sheets in the Workbook

I only use very few defined names, with normal descriptive names, as most global references are handled as a combination of these few names and global constants used as look-up table indexe

You gave me a way forward and I will follow that trace. Thanks

Br, Kim

 

[Jan Karel Pieterse]

Hi Kim,

Yes I do have User defined functions and they are using "Global Data", which means some kind of reference to sheets in the Workbook.

That is why I always advise to pass ALL information a UDF needs through the arguments list.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com