Preventing calculation of a custom function

[Thebatfink]

Hi,

I have defined some custom functions in one of my workbooks which trigger from a value in a single cell. I have a requirement to have these "turned off" in some instances to prevent accident calculation (to retain whatever is in the cells at the time of initial calculation as this could change with time - and capture someone changing the trigger cell accidentally) so came up with an idea to incorporate a toggle button onto the worksheet.
My idea was simply adding the following line at the top of the function..

If toggle.Value = False then Exit Function

The code works as expected and exits the function early however the cells continue to update and populate with zeros

Is there a way I can trap this and prevent these particular cells (and ONLY these cells and NOT the whole workbook) containing the custom functions from updating and retain their value even if the trigger cell is changed? (My only other idea is to copy and paste special the values of the cells triggered by the toggle false, and then rewrite the formulas to the cells toggle true. Preventing the updating would be cleaner though I think?)

Thanks!
Batfink

 

[Richard Schollar]

Hi

I think you would need to assign a value to the function and turn on iterative calculation like this:

If toggle.Value = False then 
   FunctionName = ThisCell.Value   'assumes using xl2003 or higher, otherwise use Application.Caller
  Exit Function
End if

and make sure you have iterative calculation enabled in the options.

Instead you could overwrite the formulas with values and write them back as formulas when you want them to calculate.

 

[Thebatfink]

Thanks Richard..

By 'thiscell.value' I guess I have to hardcode the cell in the code in this case and theres no way to capture the cell the function is actively running in automatically?

Thanks for the help!

 

[Richard Schollar]

No - ThisCell is an object just like ThisWorkbook, so it will refer to the cell in which the function resides (at least I'm pretty sure it functions like this - don't have xl2003 or above to test as at work on 2002). Alternatively, you could code that line as:

FunctionName = Application.Caller.Value

You might prefer to code up a sub to specifically calculate cells which don't contain your UDF (eg if these ranges are easily available) as in:

Range("A:D").Calculate  'eg if UDF in col E

 

[Thebatfink]

thiscell.value caused an error, but I manually assigned the range as each function is only used in one cell at the moment. In doing so I noticed that when the toggle is false and the functionname is set to the cells value, it gives a circular reference?

Is this to be expected or a result of what my function is doing? When I look at the cell, its populated with the correct information / formula for the function and it shouldn't be circular as far as I can tell?

Any ideas why this is happening?

(btw with 2007 it didn't like thiscell.value)
Thanks!

 

[Richard Schollar]

You need to turn on iterative calculations to avoid the circular reference problem.

I would go with Application.Caller if ThisCell doesn't work.

 

[Thebatfink]

Hi,

Whats the iteration calculation for? Not something I've ever used. How do I know how many iterations to use? I don't understand why recalculating the formula would resolve a circular reference when one doesn't exist from what I can see?? How will setting this affect the rest of the spreadsheet for normal calculations?

I'm a little confused to be honest.

Thanks.

 

[Thebatfink]

Ok, so I googled found the iteration option and set it to 1 with the default 0.001 value. This corrected the circular problem I was getting and from what I can gather what we've been trying to achieve has worked. I didn't like the thought of having to get users of the worksheet to have to change settings though so I added a workbook_open event and a workbook_beforeclose event to set this iteration option and then remove it.

I am worried though about what this is actually doing having this option set to 1 exactly as I have no idea what knock on effects it could cause to other things on the workbook? Is it just for dealing with circular references?!

Thanks!

 

[Jaafar Tribak]

I have defined some custom functions in one of my workbooks which trigger from a value in a single cell

You could also use the Selection_change event to know when the trigger cell has been changed and act accordingly.

So if the Trigger cell is E4 then you could write something along these lines :

Private Sub Worksheet_Change(ByVal Target As Range)

    If Toggle.Value Then
        If Union(Target, Range("E1")).Address = Target.Address Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If

End Sub

 

[Thebatfink]

Yeah that could work but I was keen to keep it encompassed within the function its self so as to not hardcode cell locations incase anyone wanted to play with layouts. The original solution works just fine now, I'm just concerned as to what iterations is actually doing its not making sense to me whats happening!

Thanks for the tip though, can't have too many good ideas!