UDF Continually Recalculates

[Marmit424]

Hey,

Does anyone know how to stop a UDF from recalculating unless its target cells change? My UDF below recalculates constantly. Thanks!

Function Custom_SplitandRemove3(InitialRange As String, RemoveMatchesFromThisRange As String)


On Error GoTo Troubleshooter1


'Accounts for handling if either string is blank
If InitialRange = Empty Or InitialRange = "" Then 'Or InitialRange = 0
    GoTo Troubleshooter_EmptyFirstValue
End If
If RemoveMatchesFromThisRange = Empty Or Trim(RemoveMatchesFromThisRange) = "" Then
    GoTo Troubleshooter_EmptySecondValue
End If


'Setup
Z = 0
b = 0
n = 0
Dim NewArray() As String
ReDim NewArray(0 To 5000)


'Splits values by ;
SplitValuesInitialRange = Split(InitialRange, ";")
SplitValuesSecondaryRange = Split(RemoveMatchesFromThisRange, ";")


'Checks to see if there are any matching values between arrays, if there are matches, this removes them
For Each InitialValue In SplitValuesInitialRange
    If WorksheetFunction.Trim(InitialValue) <> "" Then
        For i = 0 To UBound(SplitValuesSecondaryRange)
            If WorksheetFunction.Trim(InitialValue) = WorksheetFunction.Trim(SplitValuesSecondaryRange(i)) Then
                TheresAMatch = True
            End If
        Next i
        If TheresAMatch = False Then
            NewArray(n) = WorksheetFunction.Trim(InitialValue) & "; "
            n = n + 1
        End If
        TheresAMatch = Empty
    End If
Next InitialValue
If n > 0 Then
    ReDim Preserve NewArray(0 To n - 1)
End If


'Builds new array with removed duplicates back into a string
For b = 0 To n - 1
NewString = NewArray(b) & NewString
Next b


'Clears a few variables (not needed)
b = 0
n = 0
Erase NewArray


'If string one matched string two, nothing is left so put "", otherwise input the New String
If NewString = Empty Then
    Custom_SplitandRemove = ""
    Else
    Custom_SplitandRemove = NewString
End If
NewString = Empty


Exit Function


'If there is an error, show it with this
Troubleshooter1:
MsgBox ("Something went wrong" & vbCrLf & Err.Description & vbCrLf & Err.Number & vbCrLf & Err.Source & vbCrLf & Error & vbCrLf & vbObjectError & vbCrLf & InitialAddress & " " & RemoveMatchesAddress)
Exit Function


'Handles Empty First or Second Strings
Troubleshooter_EmptyFirstValue:
Custom_SplitandRemove = ""
Exit Function
Troubleshooter_EmptySecondValue:
Custom_SplitandRemove = Trim(InitialRange)
Exit Function


End Function

 

[shg]

May we know what the code does, along with an example?

 

[shg]

Speculating ...

Alan; Barb; Cain; Dana; Eric; Fran; Gary; Hana; Ivan; Jane; Kent; Leah
Kent; Zuni; Ulis; Hana; Jane; Fran; Rene; Alan; Mark; Cain; Eric; Vera
A3: =Remove(A1,A2)

[td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td] [td="bgcolor:#C0C0C0"]

1​

[/td] [td="bgcolor:#C0C0C0"]

2​

[/td] [td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#CCFFCC"]Barb; Dana; Gary; Ivan; Leah[/td]

Function Remove(ByVal sInp As String, ByVal sRem As String) As String
  Dim asRem()       As String
  Dim i             As Long

  sInp = ";" & Replace(sInp, " ", "") & ";"
  sRem = Replace(sRem, " ", "")

  If Len(sInp) = 0 Then
    Exit Function

  ElseIf Len(sRem) = 0 Then
    Remove = sInp

  Else
    asRem = Split(sRem, ";")

    For i = 0 To UBound(asRem)
      sInp = Replace(sInp, ";" & asRem(i) & ";", ";")
    Next i

    Remove = Replace(Mid(sInp, 2, Len(sInp) - 2), ";", "; ")
  End If
End Function

 

[shg]

More compactly,

Function Remove(ByVal sInp As String, ByVal sRem As String) As String
  Dim asRem()       As String
  Dim i             As Long

  sInp = ";" & Replace(sInp, " ", "") & ";"
  sRem = Replace(sRem, " ", "")

  If Len(sInp) Then
    asRem = Split(sRem, ";")
    For i = 0 To UBound(asRem)
      sInp = Replace(sInp, ";" & asRem(i) & ";", ";")
    Next i
    Remove = Replace(Mid(sInp, 2, Len(sInp) - 2), ";", "; ")
  End If
End Function

 

[Marmit424]

Thank you!!!

You guessed correctly

I discovered that upon altering a UDF it becomes volatile at the point of alteration until you save the workbook, close, and reopen it. It functions as expected now.

Thank you for the compact version!

 

[shg]

Y'er welcome.

 

[shg]

I discovered that upon altering a UDF it becomes volatile at the point of alteration until you save the workbook, close, and reopen it. It functions as expected now.

I don't think so.

All of the cells that use it will recalculate once, and then return to their usual soporific state.