I published an article last week on how you can use an Access string function that doesn’t really exist in Microsoft Access. In fact, you can build any function you like but using Microsoft Excel which has the function I wanted to utilise saved my having to create a function from scratch.Check it out below how you can do this…
Custom Microsoft Access Functions: How To Borrow Functions From The Microsoft Excel Library
The definition of any function no matter the application is the ability to calculate and process passing variables or values to return a single value (the answer).
All we do is simply call the function (by its name) and place some parameters (some are optional) and let the system reveal the answer by return. Where this is used within the context of Microsoft Access can be one of many places including tables, queries, forms and reports.
Custom Access database functions will either be a user writing VBA code compiling routines and other pre-defined Access functions to utilise a tailored function for these objects. Another way to build a custom function is to simply borrow from another application (if it exists) and don’t re-invent the wheel!
The simple steps to building an Access database function
So here’s how to do just that showing you the following simple steps in borrowing other functions in this case from Microsoft Excel.
The example I’m going to show you is to borrow Microsoft Excel’s Proper function which doesn’t exist in Microsoft Access. This function simply returns the initial capitalisation of a word i.e. “access database” to “Access Database“.
1. Create a new module which will take you into the Visual Basic Editor view.
2. From the menu bar, choose Tools and then References… to display the references screen and scroll down for “Microsoft Excel X.0 Object Library” (where X.0 represents your version and in my case 14.0 = version 2010).
3. Add the following VBA code:
Public Function Proper(field As String) As String Dim xlf As Excel.WorksheetFunction Set xlf = Excel.WorksheetFunction Proper = xlf.Proper(field) End Function
4. Save the changes and you are good to go.
Now in a query, you can call this function which will be listed in the Expression Builder tool under the name of the database in the module name (i.e. Module1 if it were left unnamed).
When you wrap the Proper function around a field i.e. Proper([CompanyName]), it will convert words to initial capitalised letters.
Custom Microsoft Access functions are easy to build and use. See which other Excel functions could serve you well.
Another Tip for you! You may want to build a separate module (give it a name i.e. MyFunctions) and export the module into a ‘bas’ file which can then be distributed and imported to other Microsoft Access databases. Don’t forget to reference the Excel library for each database.
I invite you to keep up to date with my articles and eBooks which covers a lot of details and can be found at http://AccessDatabaseTutorial.com.
From Ben Beitler – “Your Access Database Expert”
Article Source: http://ezinearticles.com/expert/Ben_S_Beitler/840635
Microsoft Access String Function: Borrowing String Functions from Excel
The above example is a basic (perhaps crude) illustration of what can be done and this Access string function is a handy one indeed (well, I think so!).
The only caveat I have however is that it may seem a little slower to execute compared to a built in Access string function as it will need to load the Excel object reference for each new session of Access.
I do provide one on one online coaching in programming with Microsoft Access VBA. Check out my online coaching offer here.