Download VBA and Access samples

This sample shows how you can use Windows-API-Functions to put an icon for your Microsoft Access Database into the Windows systray (official name: Notification Area). In addition to that it shows a couple of associated features like hiding the Access Application Window, restoring the windows by double clicking the icon and showing a context menu for the icon.

This SystraySample is a Microsoft Access Database with open source code. To gain access to the source code hold the [SHIFT]-Key when opening the database file.

This sample was created by myself and I am sole owner of all intellectual property rights. You are granted permission to use the sample source code or parts thereof in your own applications without attribution or royalty. However the redistribution of this sample on its own, without any significant modification, is not permitted without my written consent.

If you enhance this sample I would be pleased, if you make your enhancements available to the community as well.

Download For MS Access 2000 (42KB) - [Version: 0.8.2 published 2001-11-24]
Download For MS Access 97 (55KB) - [Version: 0.8.2.1 published 2001-12-24]

Enhanced Version for Balloon Tool Tips

Upon a request from a visitor of this website I created an enhanced version of this sample to show you how to combine the Systray-Icon with Balloon Tool Tips that were new to Windows 2000.

This Version of the sample will only work on computers, which have installed version 5.0 (or higher) of the Shell32.dll. It is included in Windows 2000 and Microsoft Internet Explorer 5. This sample file is only tested with Microsoft Access 2002 (XP), Access 2003 and Access 2016, but it should work with Access 2000 as well.

Download Systray-Balloon (ca. 40KB) - [Version: 0.9.0.1 published 2006-03-05]

Note regarding newer Windows versions

This sample still works on newer versions of Windows (7-10). However, there is a bit of a problem when the Notification Area Icon is displayed in the overflow section of the Notification Area. It will not respond to mouse events then and it will not show the context menu or the form window on double click.

The cause is that these functions are invoked in the DetailSection-MouseMove-Event, but only if the mouse cursor is over the Notification Area in the Taskbar, which it isn’t if the icon is in the overflow section.

It might be a solution to this problem, if you would create (and open) an additional, invisible form that only handles all the mouse events from the icon in the Notification Area. You need to use this forms window handle (hWnd) in the NOTIFYICONDATA structure.

This is just an untested idea yet. I will try to implement this if find the time.

This is a sample application to show how you can sync the scroll bars of two different Microsoft-Access-Forms with Win32-API-Functions. E.g. if you move the scroll bar in one form with the mouse, the scroll bar in the second form is moved to the same position automatically.

I created this sample application as a proof-of-concept inspired by a question in one of the Access-Newsgroups. I did not find the time to comment the source code yet. If you want to use and adapt the code of this sample to your own requirements you will have to take some time to dig into the source.

The sample is only available for Access 2000 onwards (should work in any version thereafter) because Access 97 did not have the AddressOf-Operator, which is required to create callback functions to the Win-API. If you want to use this sample with Access 97 you could try to implement it using undocumented functions of the Office-97-DLLs. Michael Kaplan published a sample on his website that shows this approach.

This sample was created by myself and I am sole owner of all intellectual property rights. You are granted permission to use the sample source code or parts thereof in your own applications without attribution or royalty. However, the redistribution of this sample on its own, without any significant modification, is not permitted without my written consent.

If you enhance this sample I would be pleased if you make your enhancements available to the community as well.

Download in Microsoft-Access-2000-Format (43KB) - [published 2003-08-21]

Update for Access 2007+

I noticed the original sample did not work with Access 2007 and newer. Here is a fixed version for Access 2007 and later. This version of the download should work with older Access versions as well, but I have none installed anymore to test it. So I leave the old version available for download as well.

Download for Microsoft Access 2007 and later - [published 2016-03-30]

Update and Video - Reusable Component

I just published a video that shows me converting my Sync-Scrollbars-Sample to a reusable component. The new version created in that video contains a single class module you can just copy to your project and use right away with no intermingled dependencies. Also I added an additional sample form, showing how to use it with subforms instead of independent forms.

As you can see in the video, I took a couple of lazy shortcuts and did not clean-up the code, yet. So, this sample will only work in the 32Bit editions of Access 2010 (and newer).

Download V2 with reusable component for Microsoft Access 2010 and later - [published 2018-09-23]

The 'ROT13Wandler' is a small utility to encode text with the ROT13-"Algorithm" (ROTate by 13). This algorithm is not a cryptographic encoding technique, but a very simple encoding algorithm that encodes text by moving every character 13 position in the alphabet. 'A' becomes 'N', 'B' becomes 'O' and so on.

ROT13 is commonly used in the Usenet to post articles which contain text that should not be readable at the first glance. For example a summary of a movie that has just been released, or the solution to riddle posted in the same article. If someone reads the entire text "by accident" he wouldn't have fun any more solving the riddle or watching the movie. So the text has to decode before it is readable. Almost any Newsreader supports decoding ROT13-Text but there are some products, e.g. Microsoft Outlook Express, that do not support encoding text with the ROT13-Algorithm.

So this utlity may be helpful for anyone who wants to encode text with the ROT13-Algorithm but has no build in feature to do this in his newsreader software. Any text may be encoded by two simple Copy&Paste operation. Just copy the readable text in the ROT13Wandler, encode it and copy the encoded text back to programm where you want to use it.

The utility is available as a precompiled binary runing on all 32-Bit Microsoft Windows operating systems. You may as well download the complete sourcecode of the utility. Because the source is quite simple, I hereby do grant you the permission to use, change and redistribute the sourcecode of the ROT13Wandler without any limitation. - Anyway, it would be nice if you send me a short Email if you redistribute the sourcecode or the utility itself.

Download BinaryWin9x/WinNT (166KB) - [Version: 0.1.1.2 / Released 2003/12/28]
Download Sourcecode (10KB) - [Version: 0.1.1.2 / Released 2003/12/28]

This is a VBA code sample to show how you can use the High-Level Monitor Configuration Functions of the Windows API. These function are available in Windows Vista and later.

For now this sample only shows how to read / set the brightness (SetMonitorBrightness) and contrast (SetMonitorContrast) settings of the monitor. It still might be a good starting point if you want to use other monitor settings that are supported by that Windows API.

The sample database was tested in Microsoft Access 2010 and 2013 it will probably work in most other current versions of Microsoft Access as well. The core functionality is contained in a single VBA class module, which should work in any VBA enabled application and is not limited to Microsoft Access.

While most modern monitors will support this API, some older monitors will not. It works with my Samsung SyncMaster P2770HD, but it does not with an older LG Flatron L2000CE.

You can use code from this sample in your own application as long as the included copyright notices is left unchanged. You may not redistribute this sample itself without explicit permission from the author.

Download the monitor settings sample (AccDB format, 31 KB)

This is very simple demo to show you how to get and set the current state of the keyboard (Caps lock, Num lock and Scroll lock). Its core functionality is a class as a simple wrapper around the GetKeyboardState, SetKeyboardState and keybd_event Windows API functions.

This is based on the sample code published by Microsoft as KB177674 (previously published under Q177674).

In my opinion the original sample in the MSKB is a little bit hard to understand for beginners. There are all three lock states handled in a single procedure and it’s not very intuitive to spot where the current values are read and where the new values are set.

Unless you understand it completely, the original code is hard to adapt or integrate into your own solution. So I took that original sample code, simplified it and created a reusable class around the code. You can import this class into your own project and used there without any changes to the internal workings of the class.

If you do want to reuse this code in your application, you should not just copy&paste the visible source code, but import the Keyboardstate class module into your database. Either directly via the External Data button in the Access Ribbon or by using the menu "File" -> "Export file"  / "File" -> "Import file" in the VBA-Editor.  

The Keyboardstate class has the VB_PredeclaredId Attribute set to True, which you would lose when copying only the code visible in the VBA-Editor.

This sample is available as a Microsoft Access 2003 MDB file. The simple demo form, which shows how to use the class is an Access Form, but the class module itself should be usable in any VBA capable application without any modification.

Update 2018-02-19:

You can now check if any key (VBA.KeyCodeConstants) is held down using the new method KeyIsDown of the class module. - This is not included in the Sample-Form of the demo.

I license my work in this sample to the public without any copyright under the CC0-License. – So you can do with it whatever you like.

Dowload the Keyboardstate Sample

Microsoft Office Automation Constants - VBA Modules

If you are using Automation of one or more Microsoft Office Application (e.g. Outlook, Word or Excel, PowerPoint) with Late Binding in your VBA-Project, you should re-declare the required Enums/Constants from the used Office Object Libraries in your own project. By using the constant instead of just hardcoding their values, you make your code much more readable.

The Enums/Constants and their values are visible in the VBA Object Browser and can easily be copied into your own project. - No rocket science, just some grunt work.

If you need only a few constants, it can be done in a couple of minutes. If you deal with a larger Automation-Solution, it will become pretty tedious.

To save you the trouble, I extracted the Enums from the Microsoft Office 2013 Object Libraries into ready-made VBA-Modules. You can just import these modules into your VBA-Project and have got all the Constants available instantly.

Microsoft Outlook

Controlling Microsoft Outlook via automation is a pretty obvious solution to many requirements. Sending emails or reading received emails from the inbox is required frequently. Synchronizing Contacts, Appointments or Tasks is not unusual too. For all these ends the Outlook Object Library is required.

Download the module with the Constants/Enums for the Microsoft Outlook 15 Object Library (Outlook 2013) here: modMSOutlook15_Enums

Microsoft Word

Access Reports cover a wide range of requirements for outputting data to the printer or to a PDF file already. Still, there are some cases where you explicitly need to create a Word-Document or where the layout options of an Access Report are not sufficient. In those cases using the Word-Object-Library to automate Word, rather than doing a simple a Word-Mail-Merge, will the means of choice.

Download the module with the Constants/Enums for the Microsoft Word 15 Object Library (Word 2013) here: modMSWord15_Enums

Microsoft Access

Microsoft Access is not an obvious candidate for automation of any kind, be with or without Late Binding. Still, recently I needed to write a couple of VB-Scripts to automate Access to do some utility tasks linked to my development work.  Then it is very helpful, to have those constants from the Access object library available. VB-Script does not support Enums, though. I just copy the raw constant without the Enum/End-Enum-Part to my script there.

Download the module with the Constants/Enums for the Microsoft Access 15 Object Library (Access 2013) here: modMSAccess15_Enums

Microsoft PowerPoint

Another less obvious candidate for automation is Microsoft PowerPoint. But, believe it or not, I actually had a client a couple of years ago who wanted me to integrate an automated PowerPoint presentation into his Microsoft Access database. Though certainly not widely used, it in some situations it can be a really powerful concept to visually present data from Access in polished and digestible PowerPoint slides.

Here is the module with all the automation Constants/Enums from the Microsoft PowerPoint 15 Object Library. modMSPowerpoint15_Enums

Microsoft Office (Object Library)

All the Microsoft Office applications use some shared classes, enums and constants defined in the Microsoft Office Object Library. The most frequently used objects and constant from this library are those of the CommandBar- and the Ribbon-Objects (Strictly speaking, the latter are interfaces.) You might need them to extend the user interface of any Microsoft Office application.

Download the module with the Constants/Enums for the Microsoft Office 15 Object Library (Office 2013) here: modMSOffice15_Enums

Microsoft Excel

The most obvious library of the Microsoft Office family is, of course, the Excel Automation Object Library. You would not need to automate Excel if you just want to use the Excel file format for data exchange with other applications. However, I’ve used Excel-Automation in countless projects to export data for further analysis in Excel itself. In this use case you will most likely need to apply some neat formatting and Layout to the exported data in the resulting Excel-Sheet. - Enter Excel automation.

Download the module with the Constants/Enums for the Microsoft Excel 15 Object Library (Excel 2013) here: modMSExcel15_Enums

Other Office versions

You can use these modules when programming against a previous version of the Microsoft Office Application as well. Naturally, you shouldn’t actually use any constant that was newly introduced with Office 2013 in a call to the object library then.

The same applies to Office 2016. You can use the modules, but if you want to use any constant new in Office 2016, you need to add that constant manually. – I probably will be adding similar modules for Office 2016 in the future as well.

Other Applications

With the release of the module containing the Excel Automation constants the modules I had planned are complete now.

If you’ve got some ideas of other common libraries that might be useful for other people as well, then do not hesitate to send me an email (at phil@codekabinett.com) with your suggestion. I’ll see what I can do about it.

So, while there is no other specific module being planned for release, you can still subscribe to my newsletter to be notified when the next module(s) becomes available for download.

This sample is a VBA code module. It shows how to use several aspects of the Windows API.

• How to use the EnumDateFormats function to retrieve the date format configured in the Windows Regional Settings; either for the current user or system default.
• How to write/use application-defined callback functions required by some Windows APIs.
• How to convert a String Pointer (LPTSTR) to a VBA String.
• How to write API function declarations compatible to the old 6.0 version of VBA (Office 2000 to 2007) as well as the new 7.0 VBA version (Office 2010 onward) with the API declarations supporting 64bit as well as 32bit.

To try-out/use the code …

1. Download the ZIP and extract the file.
2. Import the extracted modWindowsDateFormat.bas file into the VBA-Project in your host application (Access, Excel, Word, whatever)
3. Call the GetDateFormat function to retrieve the date format.

The module was written and tested in Access 2010 32bit. It should work in any VBA enabled Microsoft Office Application (starting with Office 2000) and it should be compatible to 32bit as well as 64bit editions.

Download: modWindowsDateFormat   (uploaded 2017-10-30)

You can use this module in your own applications free of charge. Republishing this sample without significant changes or additions requires the written permission of the author.

I just came across a thread on the Access World Forums, about the ChooseFont-Dialog-API in 64-Bit VBA Applications. While there are a couple of samples available showing how this API can be invoked to show the Font-Dialog, which work in 32-Bit-VBA, there seems to be no sample available for 64-Bit.

The sample code posted on that AWF-Thread was originally written by Terry Kreft and revised by Stephen Lebans. - I very much admired Stephen Lebans’ inspiring VBA and API wizardry, when I started working with Access and VBA in the late 1990ties and early 2000s.  

As you might have noticed, I wrote an extensive article on how to use Windows API functions in 64-Bit-VBA. So, I simply could not resist to verify, if applying the information and guidelines from that text and from my presentation on 64-Bit-VBA and the Windows-API at Access-DevCon 2018 would be enough to make the sample code written by heroes of my early Access/VBA days work in a modern 64-Bit-Environment.

So here is the result. I Uploaded an archive containing the original code by Terry Kreft/Stephen Lebans (“modFontDialog_Org.bas”, as posted on the AWF-Forum) as well as my own revised version for VBA7/x64 (“modFontDialog_VBA7.bas”).

This sample code will allow you to invoke the Choose-Font-Dialog from the Windows Common Dialogs Library ("comdlg32.dll") and select any font currently installed on your computer, including additional font attributes such as bold, italic, underline, the font size, and color.

The code directly invokes the required function from the comdlg32.dll using Declare-Statements and does not require any references to be set in your VBA project.

I did not conduct a serious code review and do not take any responsibility for any defects in the code. I just adapted the code to VBA7-Standards, so it will not work in any version of Microsoft Access prior to Access 2010. – Oh, and maybe I should mention: It works in 64-Bit-VBA!

Download modFontDialog.zip (uploaded 2019-01-26)

How to use the module: Create a form, put a text box control on the form, add a button with a Click-Event-Procedure, then call the test_DialogFont function from your event procedure and pass the textbox into the function.

In current versions of Microsoft Access there is a search text box in the navigation bar at the bottom of Access forms. This is a nice feature if your application uses an Access backend and your form displays relatively few records. However, with ODBC database backends or large number of records in a form, the usability of this feature can be horrible. It automatically starts to search once you type in there. With a large ODBC table from a remote backend, you just type in one letter and Access may freeze for a long time (minutes). The status bar displays “Searching. Press CTRL+BREAK to stop.” – Nice try; CTRL+BREAK does not work here…

So, in some scenarios it would be a sensible idea to disable this search box to make your user use a customized search dialog that is working more efficiently with the data your application. Unfortunately, you can only enable or disable the navigation bar of the form as a whole. Customization of the navigation bar is not possible.

Once again, it’s Windows API to the rescue! That problem was a nice little challenge to fiddle with the Windows API to disable or hide that search box control with VBA code. I came up with a solution that reliably disables the search box. It also mostly hides it on Access 2016 and newer, only the thin border of the search box will remain. With older versions, you’ll still see a grey disabled box instead of the search box. – You probably can improve that by figuring out the correct color code, depending on the current color scheme, for the navigation bar background.

To use my implementation to hide the navigation bar search box, import my module into your VBA project and then call HideNavigationBarSearchBox Me.Hwnd for the form you want to hide the search box in its Form_Load event.

Download modHideSearchBox (uploaded 2021-01-21)

The Collaboration Data Objects (CDO) library is a COM library included with Microsoft Windows since Windows 2000 to enable software developers to quickly implement email sending capabilities in their applications and scripts.

(If you are unsure about how to use it, see my text on Sending Emails with VBA and CDO.)

Even though the DLL library is included with Windows, it still is often used with Late Binding. If you do that, you cannot use the constants declared in the library directly in your code.

I strongly discourage to just use the raw constant values in your code instead of the named constants. This would make your code much less readable. A better approach is to (re-)declare the original constants in your own project. To make this even more appealing, I exported the all the constants from the CDO library (CDOSYS.dll) to a ready-made VB module (.bas file). You can either import this module into your VBA project or just copy and paste the contents of the module file.

There are two different “flavors” of the module available in the downloadable ZIP archive. The first one, cdo_constants_public.bas, contains all enums and constants declared as Public. You add this to your project and then use all the constants everywhere. This is simple and easy because all the constants will be in the global namespace, but it will also be causing “namespace pollution” by flooding your intellisense dropdown with all those constant names.

I personally would rather use the second one, cdo_constants_private.bas, which contains all the same constants, but they are all declared as Private. You would need to create a single module using the CDO functionality and include the constants declarations in that module. This will bloat that one module, but the bloat will not spill elsewhere to your project.

Download cdo_constants.zip (uploaded 2021-07-08)

The FileCopyEx function of the Windows API is a function to copy files asynchronously. It runs the copy operation in the background giving the user the option to cancel the operation while it is running. This very useful if the user wants (or has to) to copy large files and you want to give him the option to change his mind while the copy process is still running, not having to wait until it finishes.

An interesting aspect of using the FileCopyEx function is that it uses a callback function in VBA to inform the calling application about the progress of the copy operation and to provide an option to cancel the operation. – API callback functions is a topic I will get back to in more detail in the future.

I was recently approached by Peter Doering to help with the adaption of his implementation of the FileCopyEx function to 64bit. As basis for the code adaption, he provided a small sample Access file showing the functionality.

After finishing the 64bit adaption, I decided to publish the sample application with my modifications and a couple of enhancements. This sample requires VBA7 (Office 2010 and later) to run. With VBA7 the sample will run in 32bit as well as 64bit environments.

The original VB code for the API was published by Randy Birch, who kindly granted me permission to publish my 64bit adaption here.

Download AsyncFileCopyDemo (uploaded 2021-10-29)