Custom Microsoft Access Features: How to borrow features from the Microsoft Excel library

The definition of any function regardless of the application is the ability to calculate and process existing variables or values ​​to return a single value (the answer).

All we do is simply call the function (by name) and place some parameters (some are optional) and let the system reveal the response upon return. Where this is used within the framework of Microsoft Access can be one of many places, including tables, queries, forms and reports.

Custom Access database features will either be a user who writes VBA code that compiles routines and other predefined Access functions to use a custom feature for these objects. Another way to build a custom feature is to simply borrow from another application (if it exists) and not reinvent the wheel!

The simple steps for building an Access database feature

So here you can do just that, showing you the following simple steps to borrow other features in this case from Microsoft Excel.

The example I want to show you is to borrow Microsoft Excel Correct feature not found in Microsoft Access. This function simply returns the initial activation of a word, ie. “access to the database“To”Access to the database“.

1. Create a new module that takes you to the Visual Basic Editor view.

2. Select from the menu bar Tools and then References … to view the references screen and scroll down to “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’re good to go.

Now in a query, you can call this feature that will appear in the Expression Builder tool under the database name in the module name (i.e., Module1 if it was not named).

When packing proper function around a field, ie Correct ([CompanyName]), words are converted to upper case letters.

Custom Microsoft Access features are easy to build and use. See what other Excel features can serve you well.