GAMMAINV function

[ctaylor]

I have been able to get the GAMMAINV worksheet function to work within MS Excel 2000. Unfortunately I am experiencing difficulty in calling the function for use within MS Access 2000.

(GAMMAINV function defined: http://office.microsoft.com/assistan...1021033&CTT=98)

I have checked and verified that the "Msowcf.dll" was loaded on the PC as part of the MS Office installation. My reading of the help files shows that this is where the GAMMAINV function is stored.

Whenever I try to compile MS Access VBA code containing the GAMMAINV function, I receive a message telling me "Compile Error: Sub of Function not defined" while highlighting the letters "GAMMAINV" in the VB code window.

I'm thinking ther has to be a way to call this function from MS Excel for use within MS Access.

Any thoughts would be appreciated.

[ctaylor]

IDEA: Perhaps I should be using DDE commands to open MS Excel and use DDEpoke to try and populate cells in the spreadsheet and pass the GAMMAINV analysis through to Excel.

OBSERVATION: Unfortunately I'm having difficulty trying to figure out how DDE functions work before even attempting to modify the sample code to process a GAMMAINV instruction.

This is sample code that both compiles and runs without producing an error message and opening my little message box to let me know that the code ran through to completion, but I still don't think it is working correctly. It opens MS Excel without a problem, but does not appear to populate any cells with values (as observed by physically looking at the spreadsheet). Neither does MS Chart open and show me a graph.

My test code runs from a command button (Command0) placed on a blank form:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim intI As Integer, intChan1 As Integer
Dim strTopics As String, strResp As String, strSheetName As String

On Error Resume Next ' Set up error handling.

intChan1 = DDEInitiate("Excel", "System") ' Establish link.
If Err Then ' If error occurs, Excel may
Err = 0 ' not be running. Reset error
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe", 1
' and start spreadsheet.
If Err Then Exit Sub ' If another error, exit.
' Establish Spreadsheet link.
intChan1 = DDEInitiate("Excel", "System")
End If

' Create new worksheet.
DDEExecute intChan1, "[New(1)]"
' Get topic list, worksheet name.
strTopics = DDERequest(intChan1, "Selection")
strSheetName = Left(strTopics, InStr(1, strTopics, "!") - 1)
' Terminate DDE link.
DDETerminate intChan1
' Establish link with new worksheet.
intChan1 = DDEInitiate("Excel", strSheetName)
For intI = 1 To 10 ' Put some values into
DDEPoke intChan1, "R1C" & intI, intI ' first row.

Next intI
' Make chart.
DDEExecute intChan1, "[Select(""R1C1:R1C10"")][New(2,2)]"
' Terminate all links.
DDETerminateAll

MsgBox "done"

Exit_Err_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Err_Command0_Click

End Sub

(EDIT: Sample code was copied from Microsoft Visual Basic Help files when looking at the EXAMPLE link from the DDERequest function description)

[Creosote]

You WANT to see Excel open up? Why not have it invisible? Anyways, this code will show it for you:

Private Sub Command0_Click()
    Dim Probability As Double
    Dim Alpha As Double
    Dim Beta As Double
    
    txtProbability.SetFocus
    Probability = Val(txtProbability.Text)
    
    txtAlpha.SetFocus
    Alpha = Val(txtAlpha.Text)
    
    txtBeta.SetFocus
    Beta = Val(txtBeta.Text)
    
    GAMMAINV Probability, Alpha, Beta
End Sub

Private Sub GAMMAINV(Probability As Double, Alpha As Double, Beta As Double)
    Dim ExcelApp As New Excel.Application
    Dim ExcelBook As Workbook
    Set ExcelBook = ExcelApp.Workbooks.Add
    Dim ExcelSheet As Excel.Worksheet
    Set ExcelSheet = ExcelBook.Worksheets.Add(, , 1)
    
    
    With ExcelSheet
        .Range("A1") = Probability
        .Range("A2") = Alpha
        .Range("A3") = Beta
        .Range("A4").Value = "=GAMMAINV(A1, A2, A3)"
        MsgBox .Range("A4").Value
    End With
    
    ExcelApp.Visible = True
    'ExcelApp.Quit
    Set ExcelApp = Nothing
End Sub

BTW, it went to an MDE in Access 2002 with my code just fine. If you don't want it visible, comment out the visible property, and uncomment the .Quit (or it would stay an open process). It will prompt you to save it. Or you could use another method to kill the process.

[ctaylor]

thanks for the suggestion. It looks quite interesting but unfortunately the VBA compiler is balking at the following statements.....

Dim ExcelApp As New Excel.Application
Dim ExcelBook As Workbook
Set ExcelBook = ExcelApp.Workbooks.Add
Dim ExcelSheet As Excel.Worksheet
Set ExcelSheet = ExcelBook.Worksheets.Add(, , 1)

I don't think MS Access 2000 understands these terms...

"excel"
"workbook"
"ExcelApp"
"Excelbook"

bummer....... I think the ".application" is more VB (than Access VBA compliant within Office 2000).

[ctaylor]

Creosote, I figured out why the module would not compile.

I needed to go into the VBA module and enable the reference to EXCEL9.OLB (Microsoft Excel 9.0 Object Library).

Once I did this the code compiled and ran a GAMMAINV straight through MS Access.

Thanks greatly! Once again you came to par with awesome advice!

[Creosote]

good deal

[feikwong]

Hi all,

I have try to ddeinitiate("Excel","System") in my code, It prompt me "sub or function not defined"

after I define it as below:
Public Declare Function DDEInitiate Lib "user32" (App As String, container As String) As Integer

"It prompt me : Can't find dll entry point DDEInitiate in user32"

Please help.


Fei