I have been tasked to create a tool using MS Access with VBA that performs a large number of calculations which involve the TDIST and TINV functions in excel. They perform the T-Distribution and Inverse T-Distribution.
The problem I have is that although they are functions in excel, they are not functions in Visual Basic.
My current work around solution is attached.
This has been working fine up until now, as i have not been dealing with the number of records i currently need to go through and can only run through around 2 records a second (as there are a large number of calulations to do per record).
My questions is:
Does anyone have the mathematical Public Function for calculating the TDIST and TINV available? Or if there is a speedier solution that the one I have linked below? Hopefully around 10 to 20 times quicker.
Thanks for your Time,
Public Function jzTINV(intProb As Double, intFree As Double) As Double
' Calculates the Inverse T-Distribution using a work around. As the formula TINV is not in Visual Basic,
' and it is quite complex mathematics, i have created this work around.
' It is a little slow, but works (around 5 to 10 calcs a sec, depending on computer)
Dim intOut As Double
Dim objExcel As Object
Dim objWorkbook As Object
Dim objWorksheet As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.cells(1, 2) = intProb
objExcel.cells(1, 3) = intFree
objExcel.cells(1, 1) = "=TINV(b1,c1)"
intOut = objExcel.cells(1, 1).Value
jzTINV = intOut