STDEV: Different results. Bug-like in Excel 2010

Hello,

I am using VBA for a model which requires calculation of Standard Deviation.
The model already exists in Excel, using STDEV application function, so the VBA model is expected to give the same result.

However, using VBA's Worksheet.applicationfunction.STDEV gives a different result from the STDEV result computed in the cells. Both computations were done on the same data.

I have used the 6 different functions available in Excel, the results are as follows:
STDEV = 0.0052071061943
STDEVA = 0.0052071061943
STDEVS = 0.0052071061943
STDEVPA = 0.0052037969431
STDEVP = 0.0052037969431
STDEVP = 0.0052037969431

The 3 functions available in VBA also give different results, none of which agree with any of the ones computed in Excel. VBA Results:
STDEV = 0.0052038062902
STDEVS = 0.0052038062902
STDEVP = 0.0052005033345


The temporary solution I have adopted is to have a formula in a designated cell, compute STDEV using the Excel function, and then have VBA pick the result from that cell.

Has anyone else noticed this problem?
Is there an explanation for this?
Is there a better workaround?

Thank you

Yemi

Thank you Glenn for your response,
Yes, I noticed the difference between Stdev-sample and Stdev-population.
The problem is that using these same functions with Application.Worksheetfunction.StdevA,P or S in VBA produces different results.

I'd like to know how others handle this.

Thank you

Thanks Andy, You are right! It seems something else is responsible for the differences.
I am looking but have not yet found the cause.
I am computing the STDEV on an array in VBA. I see no reason why it should be different from what is returned when i write the array values to worksheet cells and compute STDEV to check.

I will let you know if i find the source of the diff.
Thank you

I have found the source of the problem!

myArray is dimmed to hold more data than the sample I need to compute STDEV, some lines in my procedure store only 500 of the data to myArray and then compute STDEV(myArray)...despite the empty array elements.

I expected the result to be same as what happens in Excel, where STDEV function does nothing differently when you include empty cells.

I have now dimed my array 1 to 500, and the result is exactly the same as what Excel returns in the Worksheet.

Thank you all for your helpful responses.STDEV 1075414.xlsm