If, Elseif, Else statements for a UDF

I'd go with Select Case rather than if, eleseif... always strikes me as easier to read or maintain, but I can't say it would make much difference in performance, given that the options are not that many. An If statement (as far as I know) gets fully evaluated, whereas the Case method would stop evaluating once it reaches the "true" condition.

Your example shows a basic "lookup table" kind of thing, so I might set up a lookup table kind of thing in VBA.
1) Assign array of lookup values (rank divisions) to an array.
2) Use one of Excel's lookup functions (I would use Application.WorksheetFunction.Match()) to find the position of rcell in the array.
3) (optional) assign an array of return values to an array (if you ever want to use something other than position in array for the ranking).
4) Use the result of 2 to return the appropriate value from the array created in 3.

A quick and incomplete function I put together

Function udftest(rcell As Double) As Double
Stop 'for debugging and educational purposes remove when you are satisfied the function is working
lookuparray = Array(-10, -5, -2, -0.5, -0.1) 'fill in the rest, make sure they are in ascending order.
returnarray = Array(1, 2, 3, 4, 5) 'fill in the rest
'note that returnarray may not be necessary, as the current example shows the ranking as the position number in lookuparray
'defining returnarray like this allows for future flexibility if you ever want the rankings to be something other than position in the array.
lookupposition = Application.WorksheetFunction.Match(rcell, lookuparray, 1)
'note that match() function return position based on 1 based array. You could assign this value to udftest if you are happy to always return the position number
'If you use returnarray, note that returnarray is a 0 based array
'that's why I subtract 1 from lookupposition
udftest = returnarray(lookupposition - 1)
End Function

Try

Function Num3Cat(rCell As Range)
    Select Case rCell
    
    Case Is < -0.1: Num3Cat = 0
    Case Is < -0.05: Num3Cat = 1
    Case Is < -0.02: Num3Cat = 2
    Case Is < -0.005: Num3Cat = 3
    Case Is < -0.001: Num3Cat = 4
    Case Is < 0.001: Num3Cat = 5
    Case Is < 0.005: Num3Cat = 6
    Case Is < 0.02: Num3Cat = 7
    Case Is < 0.05: Num3Cat = 8
    Case Is < 0.1: Num3Cat = 9
    Case Else: Num3Cat = 10
    End Select

End Function

Another:

Function Num2Cat(rCell As Range) As Long 'billj

    Dim v As Double, c As Integer
    
    v = rCell.Value
    
    On Error GoTo ErrorHandler
    
    If Not IsNumeric(v) Then Exit Function
    
    Select Case v
    Case Is < -0.1: c = 0
    Case Is < -0.05: c = 1
    Case Is < -0.02: c = 2
    Case Is < -0.005: c = 3
    Case Is < -0.001: c = 4
    Case Is < 0.001: c = 5
    Case Is < 0.005: c = 6
    Case Is < 0.02: c = 7
    Case Is < 0.05: c = 8
    Case Is < 0.1: c = 9
    Case Else: c = 10
    End Select
        
    Num2Cat = c
    
ErrorHandler:
        Exit Function
   
End Function