• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2366
  • Last Modified:

The Infamous TDIST & TINV

Hi everyone,

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
    objWorkbook.Close False
End Function

Open in new window

  • 4
  • 3
2 Solutions
I would create a function  that the opens the instance of Excel, setting the workbook and worksheet and later closing the workbook and setting the object to nothing (Set objExcel = Nothing), which you are not doing and can eventually blow-out memory.

Then, within that function call another function that deletes the cells that you are using, passes the values of your current Access record, performs the calculation and returns the results.

In this way, you are not adding the overhead of creating an instance of Excel and the destroy the instance for every record that you perform calculations.

Good Luck !!!
EnvAgencyAuthor Commented:
Thanks alot LennyGray,

Although it has reduced the amount of time from 2 records per sec to 10, it still isn't quick enough. I guess the public function versions of TINV and TDIST is the only solutions.

Wish there was a way to give you some Points, but i'd prefer to keep this open in hope of getting the public function. Sorry

Thanks again,

Rory ArchibaldCommented:
Rather than using a worksheet, you can just create an applicaiton object (once) and then use:

which ought to be quicker.
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

James -

The points are not important. Solving your problem is. I was happy to at least improve performance five-fold!

How about sending the entire array of records to the spreadsheet and then importing the entire array back to Access?

In other words, populate all of the rows (instead of one at a time) and then import all of the results into a temporary table in Access.

EnvAgencyAuthor Commented:
rorya - Yeah that worked, for some reason i remember trying that before and it didn't hmm strange one.

Lenny - Thought about doign that originally, but the number of records may exceed 80k.

Thank you both for your input, I think I got enough now to sort it out.

Thanks again
EnvAgencyAuthor Commented:
Great help cheers,
it would of been nice to get the mathematical function some how, but the solutions provided have speed things up 10 times their amount so I'm happy as Larry :)

Cheers again
EnvAgencyAuthor Commented:
Combining the above it increased the speed from 2 records a second to 25 records a second.... so thanks again :)
Rory or James -

Could you post the final code that you used? I'd like to learn how you used objExcel.Worksheetfunction.TInv and objExcel.Worksheetfunction.TDist

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now