Calculating Standard Error Vba

Question

I'm trying to add a standard error function into excel using VBA, what I have so far is just a variation of a standard deviation function. I got the function online and changed it so in the last line it's divided by the square root of the total data points. This doesn't seem to work for me.

Edit: When I try to use the function on the Excel spreadsheet, it comes up as 0 regardless of what the data set is.

Function StdErr(numbers As Range) As Double
    Dim i As Integer
    Dim xbar As Double
    Dim x As Double
    xbar = WorksheetFunction.Average(numbers)
    For i = 1 To numbers.Count
        x = x + (numbers.Item(i).Value - xbar) ^ 2
    Next i
    SrdErr = (x / numbers.Count) / (Sqr(numbers.Count))
End Function

Answer 1

Couple problems with your code: (1) the last statement misspells the function name SrdErr instead of StdErr. This is why you get 0. (2) The function definition itself isn't correct, according to Microsoft documentation.

Try this:

Option Explicit

Function StdErr(numbers As Range) As Double
    Dim StdDev As Double
    Dim Size As Integer

    StdDev = WorksheetFunction.StDev_S(numbers)
    Size = WorksheetFunction.Count(numbers)

    StdErr = StdDev / Sqr(Size)
End Function

It's a good idea to include Option Explicit so you can avoid silly spelling mistakes like this.