Solved

The Infamous TDIST & TINV

Posted on 2007-11-20
8
2,191 Views
Last Modified: 2013-11-27
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,

James
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)

    objWorksheet.Activate

    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

    objExcel.Quit

End Function

Open in new window

0
Comment
Question by:EnvAgency
  • 4
  • 3
8 Comments
 
LVL 10

Accepted Solution

by:
LennyGray earned 300 total points
ID: 20318838
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 !!!
0
 

Author Comment

by:EnvAgency
ID: 20319104
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,

James
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 total points
ID: 20319158
Rather than using a worksheet, you can just create an applicaiton object (once) and then use:
objExcel.Worksheetfunction.TInv
 and
objExcel.Worksheetfunction.TDist

which ought to be quicker.
Regards,
Rory
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 20319195
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.

Lenny
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:EnvAgency
ID: 20319228
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
0
 

Author Closing Comment

by:EnvAgency
ID: 31410086
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
0
 

Author Comment

by:EnvAgency
ID: 20321005
Combining the above it increased the speed from 2 records a second to 25 records a second.... so thanks again :)
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 20321508
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

Thanks,
Lenny
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now