Is UDF volatile or formulas?

[ljubo_gr]

Hello dear MrExcel,

Is this formula volatile:


=IF(ISNUMBER(SUBSTITUTE(LEFT(SUBSTITUTE(REPLACE(B33;1;AGGREGATE(15;6;FIND({0\1\2\3\4\5\6\7\8\9};B33;FIND(" ";B33;SEARCH("cmr:";B33)+5));1)-1;"");" ";REPT(" ";20));20);".";"")+0);SUBSTITUTE(LEFT(SUBSTITUTE(REPLACE(B33;1;AGGREGATE(15;6;FIND({0\1\2\3\4\5\6\7\8\9};B33;FIND(" ";B33;SEARCH("cmr:";B33)+5));1)-1;"");" ";REPT(" ";20));20);".";"")+0;0)

=SUMPRODUCT(('sheet1'!$D$8:$D$1440<>"inventura")*('sheet1'!$D$8:$D$1440<>"pretakanje")*MMULT(--('sheet1'!$E$8:$L$1440=List!D33);{1;1;1;1;1;1;1;1}))>0

Or this UDF:

Function LastNumber(G As String) As Variant
  Dim X As Long, Num As Long, Words() As String
  Words = Split(G)
  LastNumber = ""
  For X = UBound(Words) To 0 Step -1
    Words(X) = Replace(Replace(Words(X), ".", ""), ",", ".")
    If Replace(Words(X), "-", "") & " " Like "[Ss][5-14][!0-9]*" Or Replace(Words(X), "-", "") Like "[Ss]1[0-2]*" Then
      LastNumber = ""
      Exit Function
    ElseIf Val(Words(X)) <> 0 Then
      LastNumber = Val(Words(X))
      Exit Function
    End If
  Next
End Function

Thanks!

 

[Joe4]

I believe most native Excel formulas should be volatile.

UDFs are not, unless you specify for them to be.
See: https://technet.microsoft.com/en-us/library/ff195441(v=office.14)

 

[RoryA]

None of those are volatile as far as I can see.

 

[Joe4]

None of those are volatile as far as I can see.

Rory, out of curiosity, what determines whether or not a non-VBA Excel formula is volatile or not?

 

[RoryA]

Typically it's only formulas that have don't take direct inputs for their calculations. e.g. OFFSET, INDIRECT, TODAY, NOW.

 

[BarryL]

the below is a pretty good read on volatility

Volatile Excel Functions -Decision Models

 

[ljubo_gr]

I forgot one more from same sheet:

=SUMPRODUCT(ISERR(SEARCH("inventura";'sheetz1'!$D$8:$D$1440))*('sheetz1'!$E$8:$L$1440))

Isn't MMULT volatile?

 

[ljubo_gr]

Or maybe, =LastNumber() formula is on protected sheet?

 

[RoryA]

No MMULT isn't volatile. Why do you think any of these are volatile and what does a protected sheet have to do with volatility?

 

[ljubo_gr]

=IF(LEN(N4)>8;"";"yaddayaddayaddayadda..200 char")

N4= big If and Sum formula"Allright"
Using 32bit Office2013,