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

Answer 1

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