Excel Formulas

[Wahab]

Hi
Is there anyway I can use Excel formulas in Access? If Yeas then How? Pls.
Thanks

 

[Arvin Meyer [MVP]]

Hi
Is there anyway I can use Excel formulas in Access? If Yeas then How? Pls.
Thanks

Access can call an Excel function. Here's an example using the Days360
function:

Public Function XL360(DateStart As String, DateEnd As String) As Double
Dim objXL As New Excel.Application
XL360= objXL.WorksheetFunction.Days360(DateStart,DateEnd)
Set objXL = Nothing
End Sub

?XL360("1/1/07", "12/31/07")
360

 

[AnandaSim]

Public Function XL360(DateStart As String, DateEnd As String) As Double ....
End Sub

Uh, you mean End Function.

And if the example is early bound.

The call needs to load the Excel.exe file into memory and release it
every time - this causes a split second pause every invocation on my
system.

Might be worthwhile leaving excel.exe in memory until the batch of
invocations are complete.

HTH
Ananda

 

[Arvin Meyer [MVP]]

Uh, you mean End Function.

oops, I certainly did. That's what I get for typing code directly into a
newsgroup post.

And if the example is early bound.

The call needs to load the Excel.exe file into memory and release it
every time - this causes a split second pause every invocation on my
system.

Might be worthwhile leaving excel.exe in memory until the batch of
invocations are complete.

If used in a loop, I'd agree that you wouldn't destroy your Excel object
until the end of the function, but it's never a good idea to leave an object
dangling possibly in scope. VBA has a bad habit of not always cleaning up
after itself. I typically use Early Binding because it's slightly faster and
also easier to code with Intellisense. Many programmers will use early
binding to write the code, then switch to late binding in production. For
the code I provided, the difference in speed would be totally unnoticeable.
But in a loop or more complex procedure it would make a slight difference.

If calling multiple times, the Windows OS would usually cache Excel.exe in
memory, not to unload it completely between calls unless you have quite some
time between those calls, or your computer is running out of memory. I
generally have my clients to put enough RAM in their machines that even
virtual memory is rarely used.

 

[AnandaSim]

oops, I certainly did. That's what I get for typing code directly into a
newsgroup post.

I mess up like that often....

If used in a loop, I'd agree that you wouldn't destroy your Excel object
until the end of the function, but it's never a good idea to leave an object
dangling possibly in scope. VBA has a bad habit of not always cleaning up

Agree. How about the function used in a query or an interactive Form
or a Report? Any tips?

also easier to code with Intellisense. Many programmers will use early
binding to write the code, then switch to late binding in production. For
the code I provided, the difference in speed would be totally unnoticeable.

I do that too. However, if you declare app enums in a global constants
file (discussed in a recent thread) then you can early bind for
editing and dev and switch to late bind for production / deployment to
machine SOE or non SOE outside of your control.

If calling multiple times, the Windows OS would usually cache Excel.exe in
memory, not to unload it completely between calls unless you have quite some
time between those calls, or your computer is running out of memory. I
generally have my clients to put enough RAM in their machines that even
virtual memory is rarely used.

Yes, I saw that. First invocation was a fraction of a second slower
than subsequent invocations. I have 2Gb RAM on my PC - there was still
hard disk movement in subsequent invocations.....

Thanks

Ananda

 

[Arvin Meyer [MVP]]

Yes, I saw that. First invocation was a fraction of a second slower
than subsequent invocations. I have 2Gb RAM on my PC - there was still
hard disk movement in subsequent invocations.....

I also use 2 GBs, and a 3.0 MHz processor and only see hard drive activity
when I'm opening something new, and then, usually only for a very brief
period. I just looked and even with several things open, I have about 1.5 GB
unused.