Forcing cell with user function to update on workbook open?

[Don Wiss]

In a cell I call this function:

Function CalcILFPath() As String
CalcILFPath = Workbooks("CalcILF Add-In.xla").Path
End Function

Sometimes people have copied the add-in to their add-ins folder, and aren't
loading it from the public location. They then don't get updates. We want
to be able to display its location. My problem is this cell does not update
when opening the workbook. First I tried this block of code:

' force recalc on user's add-in path
Application.EnableEvents = False
Application.Iteration = False
Range("AddinPathUser").Calculate
Application.EnableEvents = True

That didn't work. So I tried:

Range("AddinPathUser").Formula = "=CalcILFPath()"

That didn't work either, though I tried this a while back and I can't
remember just what its problem was.

Ideally I would have the formula in a cell and not call a user function.
But I don't know how to do this. Any suggestions?

Sometimes I get this error later in my code:
The macro "Calc ILF Add-In.xla!'Auto_Add' cannot be found.
Note single double quote. What is this error? If I press continue the code
precedes normally.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[NickHK]

Don,
Check out Application.Volatile
As your function has not parameters, it will not be re-evaluated on a
calculation. You could add a dummy parameter that points to a cell with say
Now() in it, so it runs each time.

NickHK

 

[Don Wiss]

Check out Application.Volatile

I had tried that, and it didn't work.

As your function has not parameters, it will not be re-evaluated on a
calculation. You could add a dummy parameter that points to a cell with say
Now() in it, so it runs each time.

The lack of an argument makes sense. Having it point to a cell with =NOW()
worked for me. Though I do have to use the above code on the =NOW() cell to
force it to change, which forces the user function to be called. Having the
above code point to the cell with the user function didn't do the trick.

For someone else this did not work until Application.Volatile was added.

Though all this has a quirk. Before it updates (at least in the instances
when it changes) it briefly flashes #NAME? in the cell. I haven't figured
out why.

I did some more searching on the web for this one. I found the solution.
After an Addins.Installed = True is issued, the add-in has the focus. My
crashing line of code was expecting my workbook to have it. So issuing a
ThisWorkbook.Activate right after the turning on the add-in solved this.

Don <www.donwiss.com> (e-mail link at home page bottom).