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

 

[James006]

Hello,

Just insert a Boolean variable at the top of your UDF ...

With True / False, you will always master when your UDF will run ... or not ... :wink:

HTH

 

[pradee]

Hi @James006

can you explain bit more about it. how it can be done

 

[James006]

Hi again,

Below is an explanation of Boolean :

https://www.lifewire.com/boolean-value-logical-value-3123388

HTH

 

[pradee]

Hi,

Thanks for replay, i didn't mean to ask about Boolean explanation,
i was asking about how to use it in the custom formula.
bcz when i am deleting the sheet(through code or manually),
i cant change the boolean value in the custom formula, because that function is referring to particular cell as formula

thanks once again

 

[James006]

If the event you want to trap is the deletion of a sheet ....

Your boolean variable should return :

- True when the sheet exists

- False when the sheet does not exist

Hope this clairifies ...

 

[Yongle]

Specifically preventing trigger when you delete a sheet, try this method (I used a simple UDF to illustrate)

- declare public boolean variable Apply at top of module
- add the code in red to your function
- Message Box will show when the UDF triggers (delete this line after testing )


In module containing your UDF

[COLOR=#ff0000]Public Apply As Boolean[/COLOR]

Function Add3ranges(r1 As Range, r2 As Range, r3 As Range)
[COLOR=#ff0000]    If Apply = True Then
        Apply = False
        Exit Function
    End If[/COLOR]

   Application.Volatile
[COLOR=#ff0000]    MsgBox "trigger"
    Apply = False[/COLOR]
    Add3ranges = r1 + r2 + r3
    
End Function

In ThisWorkbook module

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
    Apply = True
End Sub

 

[James006]

@Yongle

Specifically ... it seems to me OP wants the job to be done ... without even posting his UDF ....!!! :wink:

 

[Yongle]

@pradee
You may prefer to puzzle things out for yourself which is better in the long run. Hopefully the information provided has enabled you to solve your issue.

If you need any further help please post your UDF as suggested by @James006 - it makes it easier for us to re-create your situation.
thanks

 

[pradee]

@Yongle and @James006

Sorry I couldn't post my code

Here is my UDF

Public Function SumValues(ByVal PairRng As Range, ByVal FileRng As Range, ByVal RowAddress As Range) As Double
    If Apply = True Then
        Apply = False
        Exit Function
    End If
    Application.Calculation = xlCalculationManual
    Application.Volatile
    'Apply = False
  Dim ColCount As Integer
    Dim NodRowCount As Integer
    Dim nodRow As Integer
    Dim equal As Boolean
    Dim wSheet As Worksheet
    Set wSheet = FileRng.Worksheet
    equal = False


    wSheet.Unprotect


    ncol = 0


    lastNodeColumn = Sheets("Pairing").Cells(2, Sheets("Pairing").Columns.Count).End(xlToLeft).Column
    lastNOdeRow = Sheets("Pairing").Cells(Sheets("Pairing").Rows.Count, 3).End(xlUp).Row
    ColCount = FileRng.Count
    NodRowCount = PairRng.Count
    For nodecolum = 3 To lastNodeColumn - 4
        For j = 4 To ColCount
            If equal = False Then
                For nodRow = 3 To lastNOdeRow
                    If (wSheet.Cells(4, j).Value) Like (Sheets("Pairing").Cells(nodRow, nodecolum).Value) Then


                        ncol = j
                        equal = True
                        Exit For
                    End If
                Next nodRow
            End If


            If equal = True Then
                If InStr(wSheet.Cells(5, j).Value, "Max-Amplitude") > 0 Then
                    nodeval = wSheet.Cells(4, ncol).Value
                    For qp = 3 To ColCount
                        If Sheets("Pairing").Cells(qp, nodecolum).Value = nodeval And Sheets("Pairing").Cells(qp, lastNodeColumn - 2).Value = "Yes" Then
                            found = True
                            Exit For
                        End If
                    Next qp
                    If found = True Then
                        sumValue = sumValue + wSheet.Cells(RowAddress.Row, j).Value
                        found = False
                    End If
                    ncol = 0
                    equal = False
                End If
            End If
        Next
    Next
    SumValues = sumValue
    Application.Calculation = xlCalculationAutomatic
End Function

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

the function which i have written is being used by other cells also, for the first cell (or first call) it is exiting the function
but for the next cell (or next call) the value of variable Apply is set to "False" and it is recalculating everything

i hope you understood the scenario