VBA writing code for standard deviation and Range

Question

I am trying to write a function in VBA that will calculate the standard deviation and range of three numbers. If all of the numbers are determined to be --- then the funtion should output --- as the answer. If ALL THREE of them are not --- then the standard deviation or range should be calculated with the remaining number while IGNORING the --- number. The numbers are inputted by the user. Here is the code I have written for the range and standard deviation calculation. By using the built in excel WS function which already ignores inputs if they are not numbers I was able to bypass that tricky bit. However as is visible I have been unable to get the code to recognize when all three are --- to register as ---.

FUNCTION TO CALCULATE RANGE -------------------------------

Function Range(ParamArray ObservedValues())

While ObservedValues(i) = "---" Range = "---" Wend

    Max = Application.WorksheetFunction.Max(ObservedValues)
    Min = Application.WorksheetFunction.Min(ObservedValues)

    Range = Max - Min

End Function

---

FUNCTIONS TO CALCULATE STANDARD DEVIATION (multiple attempts)

FIRST ATTEMPT

Function SD(ObservedValueA, ObservedValueB, ObservedValueC, ObservedMean)

If ObservedValueA = "---" And ObservedValueB = "---" And ObservedValueC = "---" Then SD = "---" End If

If ObservedValueA <> "---" Then
    a = 1
    x = ObservedValueA - ObservedMean
        Else
        a = 0
        x = 1
End If

If ObservedValueB <> "---" Then
    b = 1
    y = ObservedValueB - ObservedMean
        Else
        b = 0
        y = 1
End If

If ObservedValueC <> "---" Then
    c = 1
    Z = ObservedValueC - ObservedMean
        Else
        c = 0
        Z = 1
End If

SD = Sqr((1 / (a + b + c)) * x ^ 2 * y ^ 2 * Z ^ 2)

End Function

SECOND ATTEMPT

Function SD(ParamArray Number_Input())

While Number_Input = "---" SD = "---" Wend SD = Application.WorksheetFunction.StDev(Number_Input)

End Function

Answer 1

Say your 3 values to compute the sd of are in A1:A3

Function sd()
    Dim Val As Variant
    Val = [STDEV.S(IF(NOT(ISNA(A1:A3)),A1:A3))]
    If IsError(Val) Then
        sd = "---"
    Else
        sd = Val
    End If
End Function