Search The Web

Today's Headlines

Monday, May 10, 2010

Microsoft Access Tips & Tricks: Excel Functions In Access

As you may have noticed, Microsoft Excel has a lot more functions than Access VBA does natively. In particular, Excel has a large number of financial and statistical functions that I find sorely missing in native Access VBA. In this post, I will explain a simple method of having those functions available in Access without having to recreate their functionalities by coding them up yourselves.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields, finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, and great circle initial headings.

Microsoft Access has access to the functions coded up in other applications as long as the other application exposes those functions to Access as references. References are libraries of functions that many applications choose to make available to other applications. Microsoft Access can take advantage of such exposed functionality to incorporate them directly into its own programming.

To be able to use Excel functions in Access VBA, one has to set a reference to the Excel library. The steps to doing this are explained below for Access 2003. The procedure is probably quite similar for higher or lower versions of Access which I don't have. References can sometimes hide under obscure names, so a little experimentation may be needed before you find the right one.
  1. First, open the VBA window by hitting Alt+F11 from within Access
  2. Select Tools->References from the menubar. You will get a window of available references (please see the image below), with a few references selected already and many others not selected
  3. Now scroll down and put a checkmark in the box next to "Microsoft Excel 11.0 Object Library" (please see the next image below). Note the location of the object library in the box below the list of references. It will come in handy if and when you want to set the reference programmatically rather than manually (as shown towards the end of this post)
Access References WindowThat is it. You are done. You can now use Excel functions in your VBA coding just as if they were natively available in Microsoft Access!

Note that the reference you have set is at the database level, not at the application level. Thus, if you want to use Excel functions in Access VBA in another database, you have to go through the same process in the other database. The settings do not carry over across databases automatically.

Also, you would have noticed that the list of references available to Access in the references window is huge. Most of them are not selected by default. But the window is a glimpse into the power of interconnected applications on your PC. There are references that make even Windows Media Player functions available to your Access database, so you can choose to create your own custom music library and have the music play from within Access by calling the appropriate WMP functions!
Set reference to MS Excel Library
How do you use Excel functions from within Access VBA once you have set the reference? The code sample below shows you how that is done:

Public Sub xlMedian()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
MsgBox objExcel.Application.Median(1, 2, 5, 8, 12, 13)
objExcel.Quit
Set objExcel = Nothing
End Sub
If the reference to the Excel library has been set correctly in Access, this function will find the median using the Excel median() function and put the results in a message box. You can obviously put the result into your own variables and use as you see appropriate rather than sending it out to a message box.

Note that after we use the Excel function, we close the virtual Excel application we opened in the procedure above. Applications that are opened by automation are not closed when you exit the subroutine or function in which they are opened. You have to get in the habit of closing them at the end of the function or subroutine yourself. If you open and leave open too many such automation objects, your computer's memory will be taken over by such opened and never-closed objects, and this can lead to slow performance or even a crash. So, make sure you use the Quit method to close any instance of Excel you open from within Access when you are done using it.

Moreover, in the example above, all the numbers passed to the median() function were hard-coded. But you can use any variables you have access to as the arguments to this function call. You can use variables within scope as well as form controls, recordset fields, report controls and any other variables that you would normally be able to use with a native Microsoft Access VBA function. In fact, there is practically no difference between using a function that is available through a reference and using a native Access function. In fact, the native Access functions are available within VBA only because the reference to the Access library is turned on by default in Access (it is the reference that reads "Visual Basic For Applications", at the top of the list of references when you open the references window as explained in the steps earlier).

What about Excel functions that require a range of cells to operate on rather than single arguments? Examples of this include vlookup() for instance. You can use such functions by passing it an array any place such a function requires a range of cells. If the range of cells required is two-dimensional, you have to pass it a two-dimensional array.

Unfortunately, you can not use Excel functions from within SQL even after you have set the reference correctly in Access. The reference allows VBA to access Excel functions after opening a virtual excel application. Such a facility is not available from within SQL.

But a simple solution does exist for being able to call Excel functions from within SQL: they have to be called indirectly through a user-defined function. Thus, you would create a function in Access VBA that calls the appropriate Excel functions as shown in the example above. You can then call that function directly from within SQL, thus enabling the SQL to call Excel functions, albeit indirectly.

I will finish this post by showing how you can set a reference to the Excel library programmatically from within Access. This can be useful when you are deploying a solution to many PC's and don't want to go to each PC and set the reference manually. If you are sure about the version of Access and Excel that is available on each of the PC's on which your application is going to be run, you can have your application set the reference automatically.

This is where noting down the location of the reference library when you set the reference manually comes in handy. On my PC, when I set the reference, the location box read "c:\program files\microsoft office\office11\excel.exe". This location is used to set the reference programmatically (actually, the reference is added to the list of existing references using the AddFromFile method of the Access references object).
Function AddRefs()
On Error Resume Next
With Access.References
.AddFromFile "c:\program files\microsoft office\office11\excel.exe"
End With
End Function
This can be done in some code behind the opening form so that the reference is set as soon as the application is started and is available anytime after that.

The code above has been checked in Access 2003. It should work in any version of Access from Access 97 on up (as long as you use the correct path to the Excel library), but please do let me know through the comments if you have problems using the code.

Hope this post has been helpful in solving any problems you might have had with using Excel functions in Access. If you have any problems or concerns with the VBA code in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!

7 comments:

Bryn_OfficeTeam said...

Thank you for posting these great tips and tricks for Microsoft Access! I am sure many users will appreciate this!

You should share your knowledge with the community of Microsoft Access users over on Facebook:
http://facebook.com/microsoftaccess

Cheers,
Bryn
MSFT Office Outreach Team

Blogannath said...

Thank you very much for your encouraging comments. I don't have a facebook account (and that will probably not change anytime soon), but thank you very much for the suggestion. Please feel free to post links to my MS Access articles to the facebook community as appropriate.

Bryn_OfficeTeam said...

Will do! Have you tried the new Microsoft Access 2010 Beta?

If not, here is a link to get you up and running on the free trial:
http://www.microsoft.com/office/2010/en/whats-new/default.aspx

Thanks again,
Bryn

Blogannath said...

No, unfortunately, I have not yet had a chance to try out the beta yet. Thank you for the link. Maybe I will try it once I get some time and clear out some space on my hard drive!

Anna hussy said...
This comment has been removed by the author.
Macys said...

Great tips and tricks for Microsoft Access ! Thanks for your post

George Mousavi said...

Excel Training NYC have experienced staff who deal with you better individually to learn and clear your basic concepts.

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web