I tried this in microsoft.public.access, but no luck.

I'm instantiating Excel in an MS Access VBA routine and
going at Excel's financial functions via the resulting
Excel.Application object.

But it's kicking the brains out of a certain screen's load time -
where it has to call the same computation with different data 10-20
times to populate a list.

I've got it to where the Excel instance persists and does not have to
be re-created; but it's still too slow.

I'm aware of at least one non-MS .DLL that offers financial functions
to a .NET application. But I'd want to be accessing exactly the
same routines as I am now via the Excel object.

Question: Is there a .DLL or something that I can go to directly and
get exactly the same functions as I'm getting via the Excel object?

On May 28, 12:16*pm, PeteCresswell wrote:
Question: *Is there a .DLL or something that I can go to directly and
get exactly the same functions as I'm getting via the Excel object?

I should add that the VBA code in question is running under MS Access
2003.

Hi Pete,

Have you tried just setting a reference to Excel in the Access VBA code
rather than instantiating Excel?

I would have thought that would be faster (but I have not tested it!)

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"PeteCresswell" wrote in message
...
On May 28, 12:16 pm, PeteCresswell wrote:
Question: Is there a .DLL or something that I can go to directly and
get exactly the same functions as I'm getting via the Excel object?

I should add that the VBA code in question is running under MS Access
2003.

On May 28, 2:01*pm, "Charles Williams"
wrote:
Have you tried just setting a reference to Excel in the Access VBA code
rather than instantiating Excel?

No. It never even occurred to me. Didn't know it was possible.

Or could it be a matter of semantics?

Here's what I mean by "Instantiating Excel":
================================================== ====
Public Function Excel_Start(ByRef theSS As Excel.Application) As
Boolean
3000 DebugStackPush mModuleName & ": Excel_Start: "
3001 On Error GoTo Excel_Start_err

' PURPOSE: - Start an instance of MS Excel or use an existing
instance
' - Leave "theSS" pointing to the Excel Basic engine
' behind the newly-opened document
' ACCEPTS: - Pointer to the spreadsheet TB used by calling routine
' RETURNS: True/False depending on success
'
' NOTES: 1) We do not want to keep opening up new instances of
Excel every time this routine
' is called, so we do the "= Nothing" check to see if
theSS has already been set.
' OTHOH the user may have closed that instance of
Excel, leaving theSS pointing to
' NeverNeverLand. Experimentation shows that an error
2753 is generated in this case.
' Hence the error trap and the "userClosedExcel"
switch.
'
'SAMPLE:
' ?SpreadSheetOpenExisting("D:\Dev\SEI\DataSource
\BuySell.xls", gExcelApp)

3002 Dim userClosedExcel As Long
Dim serverNotExist As Long
Dim oktoproceed As Boolean

Const oleError = 2753
Const rpcServerUnavailable = -2147023174
Const remoteServerNotExist = 462
Const docAlreadyOpen = 1004

Excel_Start_loop:
' ---------------------------------------------------
' Create an instance of Excel

3010 If (theSS Is Nothing) Or (userClosedExcel = 1) Then
3011 Set theSS = CreateObject("Excel.Application")
'3012 With theSs
'3013 .Workbooks.Add
'3014 .ScreenUpdating = True
'3015 .Visible = True
'3016 End With
3019 End If

' ---------------------------------------------------
' Open up the spreadsheet


3999 Excel_Start = True

Excel_Start_xit:
DebugStackPop
On Error Resume Next
Exit Function

Excel_Start_err:
Select Case Err
Case 2772
MsgBox "Unable to locate Microsoft Excel program. Please
notify your administrator", 16, "Cannot Open MS Excel"
Resume Excel_Start_xit
Case oleError, rpcServerUnavailable
If userClosedExcel = 0 Then
userClosedExcel = userClosedExcel + 1
Resume Excel_Start_loop
Else
BugAlert True, "Unable to open MS Excel. Suspect user
may have closed existing instance."
Resume Excel_Start_xit
End If
Case remoteServerNotExist
If serverNotExist = 0 Then
serverNotExist = serverNotExist + 1
Set theSS = Nothing
Resume Excel_Start_loop
Else
BugAlert True, "Unable to open MS Excel. Suspect user
may have closed existing instance."
Resume Excel_Start_xit
End If

Case docAlreadyOpen
BugAlert True, ""

Case Else
BugAlert True, ""
Resume Excel_Start_xit
End Select
Resume Excel_Start_xit 'Shouldn't be needed, but just in
case.....
End Function
================================================== ====




I'll try to give it a shot sometime today and report back.

Thats not what I mean:

in the Access VBA IDE --Tools--References find Microsoft Excel Object
Library and check it

Then you can do stuff like

var=Excel.Application.WorksheetFunction.Sum(2 + 3)

which will return 5

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"PeteCresswell" wrote in message
...
On May 28, 2:01 pm, "Charles Williams"
wrote:
Have you tried just setting a reference to Excel in the Access VBA code
rather than instantiating Excel?

No. It never even occurred to me. Didn't know it was possible.

Or could it be a matter of semantics?

Here's what I mean by "Instantiating Excel":
================================================== ====
Public Function Excel_Start(ByRef theSS As Excel.Application) As
Boolean
3000 DebugStackPush mModuleName & ": Excel_Start: "
3001 On Error GoTo Excel_Start_err

' PURPOSE: - Start an instance of MS Excel or use an existing
instance
' - Leave "theSS" pointing to the Excel Basic engine
' behind the newly-opened document
' ACCEPTS: - Pointer to the spreadsheet TB used by calling routine
' RETURNS: True/False depending on success
'
' NOTES: 1) We do not want to keep opening up new instances of
Excel every time this routine
' is called, so we do the "= Nothing" check to see if
theSS has already been set.
' OTHOH the user may have closed that instance of
Excel, leaving theSS pointing to
' NeverNeverLand. Experimentation shows that an error
2753 is generated in this case.
' Hence the error trap and the "userClosedExcel"
switch.
'
'SAMPLE:
' ?SpreadSheetOpenExisting("D:\Dev\SEI\DataSource
\BuySell.xls", gExcelApp)

3002 Dim userClosedExcel As Long
Dim serverNotExist As Long
Dim oktoproceed As Boolean

Const oleError = 2753
Const rpcServerUnavailable = -2147023174
Const remoteServerNotExist = 462
Const docAlreadyOpen = 1004

Excel_Start_loop:
' ---------------------------------------------------
' Create an instance of Excel

3010 If (theSS Is Nothing) Or (userClosedExcel = 1) Then
3011 Set theSS = CreateObject("Excel.Application")
'3012 With theSs
'3013 .Workbooks.Add
'3014 .ScreenUpdating = True
'3015 .Visible = True
'3016 End With
3019 End If

' ---------------------------------------------------
' Open up the spreadsheet


3999 Excel_Start = True

Excel_Start_xit:
DebugStackPop
On Error Resume Next
Exit Function

Excel_Start_err:
Select Case Err
Case 2772
MsgBox "Unable to locate Microsoft Excel program. Please
notify your administrator", 16, "Cannot Open MS Excel"
Resume Excel_Start_xit
Case oleError, rpcServerUnavailable
If userClosedExcel = 0 Then
userClosedExcel = userClosedExcel + 1
Resume Excel_Start_loop
Else
BugAlert True, "Unable to open MS Excel. Suspect user
may have closed existing instance."
Resume Excel_Start_xit
End If
Case remoteServerNotExist
If serverNotExist = 0 Then
serverNotExist = serverNotExist + 1
Set theSS = Nothing
Resume Excel_Start_loop
Else
BugAlert True, "Unable to open MS Excel. Suspect user
may have closed existing instance."
Resume Excel_Start_xit
End If

Case docAlreadyOpen
BugAlert True, ""

Case Else
BugAlert True, ""
Resume Excel_Start_xit
End Select
Resume Excel_Start_xit 'Shouldn't be needed, but just in
case.....
End Function
================================================== ====




I'll try to give it a shot sometime today and report back.