Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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...

=fncMyFunction("Test",C1)

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

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

else

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

Else

thingy.FormulaR1C1 = theformula

answer = thingy.Value

thingy.FormulaR1C1 = answer

End If

Next thingy

End Sub

which might be useful elsewhere too.

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.

Related Topics:

- User defined functions not recognized
- user defined functions
- User Defined Function didn't work
- user-designed Excel math functions
- User Defined Functions
- Excel User Functions
- cells containing formula that refer to user-defined VBA function
- User Defined Functions for Financial Accounts
- Wait function and Invisible function
- Wait function and Invisible function
- Using an array as an optional parameter
- could not update & Optional feature not implemented
- J2ME Optional Packages
- optional class library
- DISK SPACE CALCULATION
- Constructors in calculation
- adds =round(cell references,5) to allocate the calculation to 5 places
- time calculation
- Pivot Charts - Custom Calculations
- Pivot Tables Calculations
- Disabling Formulas, Not calculation
- Time taken - Calculation
- Time difference calculations across midnight
- Distance calculation
- I need to hide the URL parameters I am using within JSPs