Language: Deutsch English















Last Update: 2017 - 03 - 20





Download VBA and Access samples

Table of Contents

  • Access-SystraySample
  • Access-SyncScrollBarsSample
  • ROT13 Wandler
  • Monitor Settings Sample
  • Keyboard state sample
  • MS Office Automation - VBA-Modules with constants



  • Access-SystraySample

    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.



    Back to the TOC

    Access-SyncScrollBarsSample

    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 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 Mircosoft-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 Mircosoft Access 2007 and later - [published 2016-03-30]



    Back to the TOC

    ROT13 Wandler

    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]



    Back to the TOC

    Monitor Settings Sample

    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)



    Back to the TOC

    Keyboard state sample

    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.

    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



    Back to the TOC

    MS Office Automation - VBA-Modules with constants

    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.



    Back to the TOC

    Subscribe to my newsletter

    *

    I will never share your email with anyone. You can unsubscribe any time.



    © 1999 - 2016 by Philipp Stiefel