Excel VBA function returning #Name?

[RogADog]

This relates to Excel97 running on NT4.

I have a VBA function I coded that is used in a number of worksheet cells. Its been working quite happily for the last couple of years. Coding for the function is in a normal module in the workbook it is used in.

The workbook (call it wb1) is fairly bulky so the coding and forms for certain of the VBA systems it uses, are in separate workbooks. I'm in course of changing one of these (call that wb2). At the end of the system in wb2, it changes the values in certain cells in wb1 which happen to be precedent cells of the cells using the VBA formula. When it is run the cells using the VBA function are all returning #Name? afterwards. The only way that I seem to be able to clear these values is by saving and closing the file and reopening it, whereupon the function recalculates itself producing some proper results.

I've tried inserting Range.Calculate into my code without success.

Does anyone know how to force the function to calculate itself?

Cheers

Roger

PS TGIF

 

[PHV]

No (Active|This)Worbook or ActiveSheet issue in your wb2's macro(s) ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RogADog]

Thanks PH

Having thought about the problem over the weekend and spent some time consulting the bottom of my glass, I'd come up with a similar conclusion as you, that it had to do with which was the active workbook. Unfortunately that didn't work, so rather than waste any more time I've taken the route round and replaced my VBA function with a number of worksheet array functions.

Problem not solved but overcome.

Thanks again.

Roger

 

[xlbo]

Within the function ( I am assuming by VBA formula you mean User Defined Function)

have

Application.volatile

as the 1st line. This will cause the function to recalc when the workbook recalcs

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question