Standard Deviation in VBA

[ttbuson]

I tried searching for similar threads, but couldn't quite find what I was looking for. I am well aware of Excel's capabilities when it comes to calculating standard deviation. However, I would like to code it myself in VBA. I can't get the below code to work and I have no idea why. Any help would be greatly appreciated. Thanks!

Function variance(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.Value - xbar) ^ 2)
Next i
variance = x / numbers.Count
End Function

 

[kpark91]

It's because of numbers.Value
It's trying to get a value for multiple number of ranges, which excel doesn't have the functionality.

The easiest but inefficient way to approach this is by looping through the range as an object.

Dim c As Range
For Each c in numbers
    x = x + ((c.Value - xbar) ^ 2)
Next c
variance = x / numbers.Count

 

[pgc01]

Hi

You forgot to index the range:

    x = x + (numbers[SIZE=2][COLOR=#FF0000][B](i)[/B][/COLOR][/SIZE].Value - xbar) ^ 2

 

[kpark91]

Ah, didn't know you could also index ranges
Nice!

 

[pgc01]

Hi kpark91

The complete syntax is

    x = x + (numbers.Item[COLOR=#000000][SIZE=2](i)[/SIZE][/COLOR].Value - xbar) ^ 2

but since .Item() is a default property of the Range object for indexing I omitted it.

This is not something I like. There should be no default properties, to avoid confusions. The truth is that I was a bit lazy.

 

[kpark91]

Ah, thank you for the info.

The truth is that I was a bit lazy.

I think all programmers deserve to be a little lazy imo

 

[ttbuson]

Holy moly this was useful, thanks guys! Now I feel like I have to go rewrite all my other code to make it more efficient...

 

[pgc01]

I'm glad it helped. Thanks for the feedback.