MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

User Function - is optional calculation possibe?

  Asked By: Ella    Date: Oct 12    Category: MS Office    Views: 808

I have a user defined function which I'm calling a couple of hundred
times and it's taking a long time to run (which is fine because it's
doing a lot of retrieving values from files).

Most of the time I don't need to recalculate these values but I do
need to use <Ctrl><Alt><F9> on other areas of the worksheet which, of
course, recalculates these functions.

What I'd like to be able to do is to add another parameter which I
could set in the worksheet as FALSE most of the time and TRUE when I
need these functions recalculating.

I had a little try with something like
if newparameter = FALSE then end
but this returns #VALUE (or zero when I tried it earlier - odd!)

Obviously I can turn them into values most of the time and only put
the formula there when I need to update the values but I'm hoping for
something a little more elegant.



3 Answers Found

Answer #1    Answered By: Raju Srinivas     Answered On: Oct 12

It's generally not a problem to just add  another parameter  and then in
the function  call on the sheet point it at a specific cell.

For example, if the function is...

Function fncMyFunction(sp1 As String, bl2 As Boolean)
MsgBox sp1
MsgBox bl2
End Function

Then this...
....in A1 and TRUE in C1 will report "Test" and TRUE.

Answer #2    Answered By: Neil Turner     Answered On: Oct 12

My function  currently has 6 parameters, (one of which is a longish
list of file names and the other parameters specify what information
to bring back from these files).

There's about 200 incidences of it on the current worksheets which
takes about 30 minutes to recalculate (this could be marginally
improved on, but isn't unreasonable given what the function is doing).

I'm happy to add  another parameter, but I want it to leave the cell
alone (without reevaluating the function) if the 7th parameter  is set
to FALSE and only redo it if the new parameter is TRUE.

sort of ...
if parameter7=FALSE then
exit function without altering the value from last recalculation
go through the lengthy process using the first six parameters
end if

sadly, I can't get it to leave the cells alone.

Shieldinng a range from the effects of <Ctrl><Alt><F9> would work for
me if this is possible - suspect not though.

As an interim (or possibly final!) measure, I knocked this up...

Sub ReDdoBlock()
'needs a block highlighting, the top left cell formula gets copied
'to each other cell in the block and then turned into a value
For Each thingy In Selection.Cells
z = z + 1
If z = 1 Then
theformula = thingy.FormulaR1C1
thingy.FormulaR1C1 = theformula
answer = thingy.Value
thingy.FormulaR1C1 = answer
End If
Next thingy
End Sub

which might be useful elsewhere too.

Answer #3    Answered By: Katrina Edwards     Answered On: Oct 12

I'm still not sure what you mean. Can you not use "exit
function" to exit the function  if a cell is false?

if blah blah then
exit function
end if

> exit function without altering the value from last recalculation
What value are we talking about?

This snippet...
With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With
.. is a recording of turning automatic calculation  off.

Also maybe you need to specify which type of parameter  you are
using.... ByRef or ByVal. They are both in help.

Didn't find what you were looking for? Find more on User Function - is optional calculation possibe? Or get search suggestion and latest updates.