Unable to call some excel functions from access

[Acme]

Hi,

I spent all day trying to figure out why sometimes I can call an excel function from access and the value will appear on my form, but for some functions (F.inv, binom.inv), it errors out as "object not supported". I browsed all the available libraries in tools/reference but found nothing that seemed to solve this issue. Can you advise?

Thanks

 

[Rx_]

Lets start by sharing what version of Access / Excel you are using.
Can we assume that you have the Tools References in the code module set to Excel?
Do you have any code to share? Please insert any code you have in Code brackets.

Of interest: We like to check to see if the automation variables are being created and destroyed properly. Or, sometimes if a object is not referenced correctly, it will run the first time, and not the next time. And of course more things to look at.
Leaving work after a 10 1/2 hour day, will be back to look tomorrow.
Give me some code!!

 

[Acme]

Hey Thanks Rx,

Sorry for your long day. I am working all the time trying to build this thing.

I have Access 2013 and Excel 2010. Here is the code:
Public Function CalcInvTestValue() As Double
Dim xl As Excel.Application
Set xl = New Excel.Application
Dim dx As Double
Dim dn As Double
Dim dAlpha As Double
Dim dLowF As Double
Dim dHighF As Double
Dim dLowX As Double
Dim dHighX As Double

dx = Forms![1]![2]![a].Value * Forms![1]![2]!.Value
dn = Forms![1]![2]!.Value
dAlpha = 1 - Forms![1]![2]![c].Value

If dx = 0 Then
dLowF = 0
Else
dLowF = (xl.WorksheetFunction.F.Inv(dAlpha, 2 * (dn - dx + 1), 2 * dx))
End If

If dn = dx Then
dHighF = 0
Else
dHighF = (xl.WorksheetFunction.F.Inv(dAlpha, (2 * (dx + 1)), (2 * (dn - dx))))
End If

If dx = 0 Then
dLowX = 0
Else
dLowX = (dx / (dx + dLowF * (dn - dx + 1))) * dn
End If

If dn = dx Then
dHighX = 1 * dn
Else
dHighX = (((dx + 1) * dHighF) / (((dx + 1) * dHighF) + (dn - dx))) * dn
End If

I have selected the appropriate reference tools I think. Am I missing something?

Thanks

 

[stopher]

In my version of Excel, F.Inv is called FINV. And I can't see a Binom.Inv, only BINOMDEST.

Chris

 

[Rx_]

Good call Chris. I realized after posting yours was different than mine. FINV vs F_Inv

I am using Access 2010 and Excel 2010 at this site.
At home, I am testing Excel 2013 with Access 2013. You indicated Excel 2010 object model is being used.
xl.WorksheetFunction.F_Inv
I think you have a F.INV (dot) instead of the underscore.
Give that a try and please provide feedback.

http://www.mrexcel.com/store/index.php?l=product_detail&p=1
Was looking for a web site to verify this. Amazed how little there is out there.
As I frequent Mr Excel to program VBA from Access - all of our Cloud Reports are in Excel instead of Access Reports - this popped up again. Maybe I will just have to buy it.
It is called: Holy Macro! AMAZING collection of 2,500 VBA examples in a question-and-answer format. Every snippet of VBA you will ever need!
Somehow, I doubt that 2,500 is all that I would ever need! LOL

Please let us know if that works or not. Thanks

 

[Acme]

Thanks guys. I decided to just create my on F statistic table and fieldvalidate the confidence levels to those with F statistics. I will probably try the underscore, but all in all, I prefer to avoid the application calls if possible, even though it reduces the options for the user.

I have another question I was hoping to get help with. In my application, I have a rather complicated form with a lot of calculations that I am trying to streamline (right now a form refresh takes 10 seconds). My immediate issue is that when I click my refresh command button on the form, it refreshes twice!! So it takes 20 seconds. I think this may be because I have some controls that are linked to an event (refresh record data after update). However, my suspicion is not fact. Any ideas? Have you dealt with this before? How can I fix it (hint, I don't want to link all the calculations to the form refresh button). Thanks RX and Stopher.

 

[Rx_]

Is it an Access Form object?
Oh, yeah, people (and even my species) are always running into this kind of situation.

Let me suggest that you post in the Forms section.
It is not to say that VBA won't play a role. But, those pesky events always seem to have some effect on the speed of things.

My forms at this site are part of a very large and complex regulatory system. The - if this category - then these are required, calculated, or other options are endless. I read a book on developing a Rule Engine. Using a custom tool I designed to guide a room of rule-manager subject matter experts. Most everyone has a different situation. But, 10 seconds is an eternity.

If you are using linked tables for example to SQL Server, be sure to mention that. I will look for your posting there to see if someone helps. Speed is important.
Also, - go to your first post and mark it Solved. Using the solution you suggested is one way to solve the situation.

 

[Acme]

This issue was solved be eliminating the excel function call and calculating the cell in access.