repeatedly calling Custom Function as Formula in a cell unnecessarily

[pradee]

Hi guys,

I have Created Custom Function with three arguments and all three are range values from two different sheets.
the custom function is returning the double values and it is getting placed in the cell correctly,

Now the thing is, when i delete or perform other operations in the tool (With the other Sheets except those two), that custom function (which is a formula for a cell) is getting called unnecessarily which is increasing the execution time of the tool.

those cells are referring to other sheets in the cells, but am sure that it is not changing any values in the cells.

the strange thing what i noticed is, custom function (Formula in the cell) is getting called even i am deleting other sheets in the workbook (through code as well as manual deletion)

Please help me with this, it is increasing execution time of the tool

Thank You

 

[Yongle]

your function is rather complex
- will look later in the week when I have time

 

[RoryA]

You made the function volatile (which you have to because of the way it's been written), so it will always recalculate when you change something.

 

[Yongle]

You said in post#1

The strange thing what i noticed is, custom function (Formula in the cell) is getting called even i am deleting other sheets in the workbook (through code as well as manual deletion). Please help me with this, it is increasing execution time of the tool

and now you are saying...

@Yongle I applied your method that you have suggested, but the scenario is, when i delete sheet it is assigning the value of variable 'Apply' to True
and it is terminating the function.

I am confused - the function stops when a sheet is deleted - - have I misunderstood your requirements

 

[pradee]

Hello Again, @Yongle i have fixed the issue with your suggestion, i have implemented it little more,
i have set the variable Apply = True only when user is going to change those three parameters (three ranges)

i have used change event, here is my code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim I As Integer
    Dim lnRow As Long, lnCol As Long
    lnRow = 2
    lnCol = Sheets("Node Pairing").Cells(lnRow, 1).EntireRow.Find(What:="Curve", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
    Set KeyCells = Range(Cells(2, lnCol), Cells(2, lnCol).End(xlDown))
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then
            Apply = True
    End If
End Sub

Thanks for the suggestion

 

[Yongle]

thanks for the feedback

 

[pradee]

@Yongle sorry for late reply,

Actually there there are multiple cells which are using the formula, when i delete the sheet, it will call 'Workbook_SheetBeforeDelete' and it will set Apply ="True"
and it will go for the custom function, and in the custom function==> since Apply="True" it will enter the condition and it will set Apply ="False" and it will exit the function
this is done for single cell.

for the next cell (which includes custom formula) since the value of variable Apply="False", it won't exit the function now, now it will recalculate everything once again

like wise i have dynamic number of cells which is using customised formula

I hope you understood my explanation

 

[pradee]

@Yongle can you look at my one more thread "Cant Enter Break Mode at this time"

URL: https://www.mrexcel.com/forum/excel-questions/1069925-cant-enter-break-mode-time.html

am getting same error again for same set of code

 

[Yongle]

Reply to post#16

Yes I understand and here is a suggestion

Change the condition in the UDF to

If Apply = True Then Exit Function

and then you need something to reset the value of Apply to False after the sheet has been deleted
- a button
- a macro triggered by a short cut
- an event
eg use sheet 1 and use the a DoubleClick event like this
Goes in sheet code window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Apply = False
End Sub

 

[Yongle]

Another approach would be to not use Boolean variable in the UDF but to set Calculation mode to MANUAL and then user recalcuates with {CTRL}{ALT}{F9} etc