Prevent a UDF from calculating when File opens...

I have a UDF that references a network file. The UDF is set up so that it won't calc unless you press a toolbar button... Sometimes, I'll be sending these files to other people who don't have access to the network drive that the UDF reference... so I need to prevent the UDF's from calcing when the file opens. I just want them to see whatever value was previously in the cell.

I tried setting calculation to Manual... but as soon as I set it back to automatic, the values go back to 0.

I need to stop the UDFs from performing any operations until I press the button.


LVL 11
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Your previous question discussed the possibility of setting a flag that determined whether the UDF calculated. You didn't like the idea of passing the old value of the cell as an argument to the UDF, although that would address this issue.

Would you consider creating a very hidden set of worksheets that mirror the values on the visible worksheets? If the flag is off, the UDF would get its return value from the hidden worksheets. If the flag is on, all the UDF calls would go to the network drive and get fresh data. Once the workbook had finished updating, the macro that sets this flag would also update the mirror values.


What if you assign this flag to the Names collection. You can then change the value of that object from a button, UDF can test this object to control the Application.Volatile setting in the function.
WATYFAuthor Commented:
Hi all...

As a result of the previous discussion, I decided that I would create a toolbar button to control when the UDF's calc. That part is working fine. It's just the moment when the file opens that a full calculate is forced and that's what's setting all the values to 0.

Unfortunately, I can't use methods such as hidden worksheets because this function was created for "on the fly" calculations. And it would defeat the purpose of the function if the user had to create a hidden worksheet to save the old values and what not.

I'm kind of in a bind... because I've already presented the capabilities of this function to my colleges and my Supervisor, and we've already begun implementing its use... This issue came up only once we started applying it to reports that are circulated outside of this department.

So basically, I need a completely programmatic way of keeping the old values in the cells unless the button is pressed.

leon.. I'm not sure I follow your suggestion. Please elaborate. Thanks.

Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

"Please elaborate."

Normally UDF is not affected by a autorecalculation, so within the function we add Application.Volatile to make them behave just like native Excel function.  If you wrap this statement inside an If loop and test the value in the Names collection you may be able to control the recalculation of your UDF.  Something like:

Public Function UDF() as Long
If Range("blnFlag") = "Recalc" Then
End If

' Rest of you funciton code
End Function

This is a theory which I have not tried, but I think it may be worth a shot.
Try this as a test

Public Function Test() As Integer
    If Application.Names("blnFlag") = 1 Then
    End If
    Test = Range("E2").Value + Range("D2").Value
End Function

You will need to Name/Define  "blnFlag" and set it to 1 for the recalc to work
WATYFAuthor Commented:
I see. Unfortunately the UDF is calcing even though it's not set to Application.Volatile. It's just the calculation that happens when the file opens that I'm trying to stop.

byundt... I was reading through that old question to see if I could glean some useful data out of it... it may be possible for me to implement some of the idea that were mentioned last time... but I'm not totally sure yet.

byundtMechanical EngineerCommented:

The mirrored worksheets aren't created by the user, but rather by the macro for your toolbar button. They only get updated when that button is clicked. This allows you to keep a simple user interface, while retaining a static copy of the most recently updated value.

Application.Caller.Address        will return the cell address that called the UDF.
Application.Caller.Parent.Name      will return the sheet name that called the UDF

Potential problem: if your UDF is used in a formula rather than just returning a value to a cell.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WATYFAuthor Commented:
Oops... LOL... sorry for wasting your guys' time.

I guess I should have tested it out before I assumed it wouldn't work. :o)

You see... I didn't actually TRY to open one of these files from a computer that didn't have access to that network drive (or the UDF).. I just assumed it would return zeros.

But then... I was thinking... the cells in the workbook will be referecing a Custom Function... but outside users won't have that Function "installed".... So THEN I realized that it was even worse then I thought, and that it would just return "?NAME"... because the cells would contain an unrecognized Function... in which case I was totally screwed... because remember... the function is NOT resident in the FILE... it's an Add-in that only certain users will have installed. Which means that all these coding options are irrelevant... because an outside user (without the Add-in) wouldn't have any of the code available to them.

But THEN... I decided to actually try it out and see what really happens.

Well... it turns out, that because the UDF is an Add-In... if you open a file containing that UDF on a computer that doesn't have the Add-in installed, it will LEAVE the value in the cell but the FORMULA bar will show that it is trying to link to "C:\Documents and Settings\Application Data\Microsoft\Add-ins" (or whatever) which is the folder where the add-in is saved to.

SO... The end result is... there IS no problem. :oP (go figger)

If the user has the Add-in installed,... they can just click on the Update button to populate the cells. And if they don't have the Add-in installed, then it will leave the value in the cell and show a formula that is trying to link to an Add-in that doesn't exists. (which is the same thing that happens when you open a file with LINKS to a file that doesn't exist).

Anyway... sorry to waste your time. :o) I'll split the points for all those who offered help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.