different results between VBA t_test and native excel t_test

Question

this is my first foray into VBA. the follow subroutine computes a t-test for two columns of data on Sheet1.

the problem is this subroutine returns a value different from what i get when i manually run "=T.TEST(A1:A41,B1:B96,2,3)" in, say, cell D1 on the worksheet. (the numbers in the table don't really matter. i've tested with real data as well as 1 to 41 in column A1:A41 and 1 to 96 in column B1:B96.) can you confirm this? is there a bug in the code? thanks.

Sub dummy_ttest()
    Dim rng0 As Range
    Dim rng1 As Range

    Set rng0 = Sheets("Sheet1").Range("A1:A41")
    Set rng1 = Sheets("Sheet1").Range("B1:B96")

    Dim td0() As Double
    Dim td1() As Double
    
    ReDim td0(rng0.Count) As Double
    ReDim td1(rng1.Count) As Double

    Dim i As Integer
    Dim v As Variant

    'copy rng0 to td0
    i = 0
    For Each v In rng0
       td0(i) = v.value
       i = i + 1
    Next v

    'copy rng1 to td1
    i = 0
    For Each v In rng1
       td1(i) = v.value
       i = i + 1
    Next v

    Dim myttest As Double
    myttest = Application.WorksheetFunction.T_Test(td0, td1, 2, 3)

    MsgBox myttest

End Sub

it would be easier for us to confirm if you would provide test data and what the two results are. That way we do not need to create our own test data that may return results that are not reproducible. — Scott Craner, Aug 31 '20 at 18:40
One thing I can see is that your arrays are one element bigger than your ranges. — BigBen, Aug 31 '20 at 18:41
try declaring the arrays as variants and then bulk load them: `td0 = Sheets("Sheet1").Range("A1:A41").Value` and `td1 = Sheets("Sheet1").Range("B1:B96")` and skip the loops. — Scott Craner, Aug 31 '20 at 18:46
@ScottCraner when i put a sequence of 1 to 41 in A1:A41, and 1 to 96 in B1:B96, the subroutine gives 4.14E-13. the excel function done in the worksheet gives 3.23E-13. — 4mla1fn, Aug 31 '20 at 18:53
@ScottCraner i tried your bulk load suggestion. wow, that was all i needed. i now get the correct value. big thanks! — 4mla1fn, Aug 31 '20 at 18:57
@BigBen you're correct. when i ReDim to one size smaller, it worked correctly. much appreciated. i don't understand but will read up on ReDim. thanks much! — 4mla1fn, Aug 31 '20 at 19:04
The issue is that your arrays are 0-based, but the range is one-based. Your array is equivalent to `ReDim td0(0 to rng0.Count) As Double`, but the range has 1 to `rng0.Count` cells. It's not an issue of `ReDim` at all. — BigBen, Aug 31 '20 at 19:05

score 3 · Accepted Answer · answered Aug 31 '20 at 18:56

Use variant arrays and bulk load them:

Sub dummy_ttest()
    Dim rng0 As Range
    Dim rng1 As Range

    Set rng0 = Sheets("Sheet1").Range("A1:A41")
    Set rng1 = Sheets("Sheet1").Range("B1:B96")

    Dim td0() As Variant
    Dim td1() As Variant
    
    td0 = rng0.Value
    td1 = rng1.Value

    Dim myttest As Double
    myttest = Application.WorksheetFunction.T_Test(td0, td1, 2, 3)

    MsgBox myttest

End Sub

BigBen · Answer 2 · 2020-08-31T19:32:27.057

Scott has an excellent answer but adding context / converting my comment to an answer:

One thing I can see is that your arrays are one element bigger than your ranges

The issue is that your arrays are 0-based, but the range is one-based. Your array is equivalent to ReDim td0(0 to rng0.Count) As Double, but the range has 1 to rng0.Count cells. It's not an issue of ReDim at all.

The range A1:A41 has 41 cells, but your array has 42 elements; 0 to 41 means you have one too many. So in your current approach, you never actually populate the last element of the array and thus it is 0 by default.

You can (and should) specify the lower bound of your arrays, i.e.

ReDim td0(0 to rng0.Count - 1) As Double '<~ 0-based

or

ReDim td0(1 to rng0.Count) As Double '<~ 1-based

From the ReDim docs:

When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.

@BigBen ah, i thought the ReDim() parameter was saying how many elements are in the array but it seems to actually specify what the last index is. so when you start from 0, yeah it will be +1. — 4mla1fn, Aug 31 '20 at 19:24

different results between VBA t_test and native excel t_test

2 Answers2