Code Library

Mike Wolfe

Sep 29, 2020 • 4 min read

#clsApp Routines

Environmentally Friendly Access: (Nov 5, 2020) Using the Windows Registry to manage Production, Development, and Testing environments in #VBA and #MSAccess.

The Global Form: (Jan 25, 2021) Storing global variables on a hidden form has some distinct advantages over storing them in VBA.

How to Check if VBA is Running in 64-bit Mode: (Dec 20, 2021) A simple function (or class property) that returns whether the VBA code is running under 32-bit mode or 64-bit mode.

Automatically Cleaning Up Temporary Files on Program Exit: (Dec 22, 2021) A dead-simple way to clean up temporary files without having to worry about waiting until they are no longer in use.

clsApp: My Application-Wide MS Access Singleton Class Module: (Jul 11, 2022) I use many class modules in my applications, but this is the one I cannot live without.

3 Ways to Get and Set the TitleBar Text for an MS Access Application: (Jul 12, 2022) Here are three ways to set the Application Title for an MS Access application. My preferred method allows getting and setting with a single line of code.

#clsRegOp Routines

RegOp Class for 64-bit VBA: (Feb 17, 2021) Updating a classic VBA registry reading and writing class module for 64-bit compatibility.

RegOp Quick Tip: (Feb 18, 2021) Be respectful of your user's registry. Store all your applications' settings in a dedicated publisher subkey.

#clsRibbon Routines

GetAttr(): Ribbon XML String-Building Helper Function: (Jun 24, 2022) When you're building strings in code with lots of optional values--such as ribbon XML attributes--this simple function helps keep your code readable.

#clsStatus Routines

Don't Settle for the "Status" Quo: (Nov 6, 2020) Wherein I wrap Access's built-in status bar functions inside a class module for more readable code.

#CodeGeneration Routines

Strongly-Typed Collections: The Easy Way: (Mar 27, 2021) Creating a strongly-typed collection class in VBA requires jumping through code export-import hoops. What if you didn't have to, though?

Converting the DataTypeEnum Values to Equivalent VBA Types: (Oct 5, 2021) Two simple functions to convert DataTypeEnum values to descriptive strings and their VBA data type counterparts.

SetPredeclaredId(): Change the Hidden PredeclaredId Attribute of a VBA Class Module: (Oct 5, 2022) This simple function overcomes the lack of a hidden attribute editor for PredeclaredId values in the VBA development environment.

Writing Boilerplate Code by Hand is for Suckers: (Oct 17, 2022) Writing repetitive code may be a necessary evil in VBA, but it's a lot less tedious if you generate most of it in VBA itself.

Create a Class Module from a String in Microsoft Access: (Oct 18, 2022) Generate VBA class modules from strings during design time using this simple function in Microsoft Access.

GenerateTVClass(): Auto-Create a TempVars Class Module: (Oct 21, 2022) The culmination of my TempVars series, this article shows you how to generate a TempVars class module from a local table.

#Date Functions Routines

"Convenience" Date Functions: (Nov 24, 2020) The DateSerial function is easy to write, but it's not as easy to read. Let's make our code easier to read with some "convenience functions."

Federal Holidays in VBA: (Feb 1, 2021) How do you calculate US federal holiday observances in VBA? One option is to use brute force and skip the calculation altogether.

VBA IsBusinessDay() Function: (Feb 2, 2021) A simple function that returns True except for weekends and US federal holidays.

IsLastBusinessDayOfMonth() Function: (Feb 3, 2021) I bet you can't guess what this function does.

Counting Weekdays in VBA: (Feb 4, 2021) This otherwise mundane function is made a bit more interesting through its use of the little-known VBA backslash operator, which performs integer division.

Counting Business Days in VBA: (Feb 5, 2021) Counting business days is easy with the WeekDayCount() and FederalHolidays() functions. What's interesting is how we test the WorkingDayCount() function.

Using TDD to Calculate Holidays in VBA: (Feb 6, 2021) This article takes you step-by-step through the Test Driven Design process, unencumbered by the complexity of any sort of testing framework.

Sticky Wicket: Looping by Month: (Feb 20, 2021) Some programming problems are difficult to solve. Others are difficult to solve clearly.

Looping by Month: DateSerial: (Feb 21, 2021) The first approach to looping by month uses only the builtin VBA functions DateSerial() and DateDiff().

Looping by Month: Custom Functions: (Feb 22, 2021) By using a couple of custom functions, we can write very readable loops that iterate one month at a time.

Looping by Month: Lookup Table: (Feb 24, 2021) Have I ever used this method? No. Can I imagine a situation where it could be useful? Maybe. Should I write an article about it? Sure, why not.

Creating a Monthly Lookup Table: (Feb 23, 2021) Having a table where each record represents a single month can come in quite handy. Let's create such a table.

Looping by Month: Loop Until with DateAdd: (Feb 25, 2021) Reader Francesco Foti writes in with his own solution to the Looping by Month challenge.

A Safer Alternative to BETWEEN When Filtering Dates: (Dec 11, 2021) This convenient function generates defensive SQL statements to help you avoid the subtle dangers of the BETWEEN statement when filtering dates.

Displaying Human Readable Time Spans: (Apr 13, 2022) The HumanizedSeconds() and ConvertToSeconds() functions work hand-in-hand to provide a general solution for storage and display of time spans.

#DesignProcedures Routines

Python-inspired Doc Tests in VBA: (Sep 21, 2020) Doc tests are not a replacement for unit or integration testing. But they do provide the best return on investment (ROI) of any type of test, mostly because the effort to write them is near zero.

Writing Code with Code in VBA: (Dec 10, 2020) Can you write code with code in VBA? Yes. Should you write code wit--OH, BE QUIET YOU!!! Where's your sense of adventure? ;-)

"Complex" DTOs in VBA: (Dec 14, 2020) Can you use the OpenArgs parameter to pass multiple values to forms and reports with compile-time checking? You can if you use DTOs.

How to Set a Breakpoint Inside of an Access Query: (Mar 4, 2021) Did you ever want to set a breakpoint inside of an executing query? How about inside a form event property? This trick lets you do it.

Quickly List the Properties of an Object in Access: (Aug 23, 2021) Here's a quick and dirty procedure to iterate through an Access object's Properties collection.

Mystery of the Ancients: (Sep 1, 2021) The Ultimate Guide to Recovering Password-Protected, Access 97 Format .MDB Files

Converting the DataTypeEnum Values to Equivalent VBA Types: (Oct 5, 2021) Two simple functions to convert DataTypeEnum values to descriptive strings and their VBA data type counterparts.

Listing External Table Sources: (Nov 9, 2020) Using the Parse() function to list linked tables by source. Use this when you want to refer to Linked Table Manager info while writing code.

Set Report Properties in Bulk: (Jun 27, 2022) Looking to set a report property, such as an event handler, to the same value for multiple reports? Use this routine to automate the whole process.

Modern On/Off Button in Access: (Jul 26, 2022) Leave those boring checkboxes behind and move into the 21st century with these modern on-off switches for Microsoft Access. Very little code required!

Triple-State Modern On/Off Switch in Access: (Aug 19, 2022) Yes, No, or I Don't Know (or Don't Care). With this updated design, you can offer your users a third option with your modern on/off switches.

tblTempVar: Creating and Populating a Table of TempVars: (Sep 28, 2022) These three simple procedures: (1) create, (2) populate, and (3) create/delete + populate a local table with the contents of the TempVars collection.

ListFields() Part 1: Listing Table Fields in the Immediate Window: (Dec 6, 2022) An easy way to reduce context switching between the VBA IDE and the Access application window is to list table field names in the immediate window.

Converting VBA Long Color Values to RGB: (Dec 29, 2022) The VBA RGB() function converts red, green, and blue values into a VBA long integer color value. This simple function works in the other direction.

#FileFunctions Routines

Recursion Demystified: Creating Subfolders: (Nov 29, 2020) Recursion: it's not just for calculating factorials any more! A practical example of using recursion to create multiple missing subfolders.

The Subtle Dangers of Dir(): (Dec 1, 2020) If Len(Dir(FullPath)) > 0 Then 'the file exists. This potential bug patiently waits to pounce and ruin another poor programmer's day.

Kill Failed? Let User Try Again: (Dec 2, 2020) My Access applications regularly need to overwrite existing files. A common example is exporting data to a text file, such as a .csv. Sometimes the user chooses to overwrite the existing file, and sometimes the program is hard-coded to save to a specific filename. Oftentimes, the user wants to immediately

Text Files: Read, Write, Append: (Dec 4, 2020) There is beauty in simplicity. These three functions for reading, writing, and appending text files are simple, but effective.

Unicode-Safe Filtered Filename Lookups: (Dec 23, 2020) Need a Unicode-safe way to retrieve a list of filenames from a folder? And its subfolders? And filtered by filename? It's all here!

Joining Paths in VBA: (Feb 11, 2021) How many times have you gotten a runtime error because you had duplicate or missing backslashes in your file paths? Never again!

Finding the Temporary Folder with VBA: (Mar 23, 2021) Be a good steward of your users' file system. If you are creating temporary files, be sure to create them in the designated temporary folder.

Getting a Temporary File Name with VBA: (Mar 24, 2021) Here is a simple function to generate an unused temporary file name in VBA.

Preventing File-Writing Race Conditions in VBA: (Dec 7, 2021) If you're waiting on an external process to write a file to disk, how can you be sure it's really done? This routine helps avoid (or at least rule out) some race conditions in VBA.

LogToTempFile() Function: (Jul 4, 2022) A quick and dirty method for debugging large strings in VBA.

GetTempPath: Using the Windows API for Maximum Performance: (Aug 8, 2022) If you are looking to maximize performance in VBA, using the Windows API will almost always be your best bet.

3 Ways to Create Missing Subfolders in VBA: (Aug 16, 2022) If you need to generate multiple levels of subfolders using VBA, these three functions will each get the job done.

MakeSurePathExists: Using the Windows API to Create Missing Subfolders in VBA: (Aug 12, 2022) A Windows API function makes verifying (and creating, if necessary) a full folder hierarchy dead simple.

EnsurePathExists: A Unicode-Safe Way to Create Missing Subfolders in VBA: (Aug 15, 2022) The EnsurePathExists function--based on the API function SHCreateDirectoryExW--is the ultimate tool for verifying and creating folder structures.

A GUID-Based Temporary File Name Generator: (Oct 3, 2022) If you need a reliable and fast way to create lots of temporary file paths, a GUID-based filename generator has some compelling benefits.

#FormFunctions Routines

My Dirty Little Secret: (Nov 2, 2020) I don't use Me.Dirty = False in my #msaccess code. I wrap it up and call it from a "guard clause" instead.

Filling in the Blanks: (Nov 3, 2020) One of the keys to being a good developer is to make the *easy way* also be the *right way*. Exhibit A: the ControlIsBlank() function.

Why so lazy, Access?: (Nov 4, 2020) Introducing FormIsOpen() and ReportIsOpen(), two simple functions to help deal with Access's laziness.

Clearing a List Box in Access: (Dec 21, 2020) Sometimes you need to wipe the slate clean and start fresh. Here's a simple function to do just that for an Access list box.

Unicode-Friendly MsgBox: (Dec 24, 2020) There's a great big world outside of the ANSI bubble. Make every MsgBox in your program Unicode-safe in no time at all with this drop-in replacement.

Setting MultiSelect At Runtime: (Jan 14, 2021) You can't actually set a list box's MultiSelect property at runtime. But there are a few ways to work around the limitation. Let's explore them.

PreviewReport Function: (Mar 16, 2021) This custom function is the simplest and safest way to preview reports in any Microsoft Access application.

The ArrowKeyNav Routine: (May 1, 2021) Enable Excel-like navigation in your continuous forms by overriding the default behavior of the up and down arrow keys.

ComboBox Dropped Down State: (May 10, 2021) Overriding the up/down arrow key behavior improves the user experience on a continuous Access form. But what if the user drops down a combo box?

Get Form By Control: (May 14, 2021) A VBA function that returns the first form parent of the control you pass to it.

Microsoft Access: Check if Form Object is a Subform: (May 15, 2021) How do you check if the form object is a subform in Access without triggering a runtime error? Hint: this is a trick question.

Get Top Form By Control: (May 17, 2021) A VBA function that returns the topmost form parent of any control you pass to it.

Combo Boxes and Target Sizes: (Jun 7, 2021) Just like in darts, it's easier to hit a larger target when using the mouse. With this in mind, let's make our combo boxes more user-friendly.

Avoiding the Error "invalid reference to the property CurrentRecord": (Jul 1, 2021) Error number 2455, "You entered an expression that has an invalid reference to the property CurrentRecord," is annoying, but there is an easy fix.

How to Highlight the Current Record in a Continuous Form: (Jul 2, 2021) Step-by-step instructions for applying a custom highlight to the currently selected record in a continuous form in Microsoft Access.

#Internet Routines

Convert CIDR Notation to IP Address Range in VBA: (Jan 22, 2022) A VBA function to convert from CIDR notation (192.168.1.1/24) to the corresponding IP range (192.168.1.0 - 192.168.1.255).

#ReportFunctions Routines

Nothing To See Here: (Mar 22, 2021) Introducing InformNoData(), a simple function to improve the user experience when there is no report data to show.

Show a Friendly Message When a Report Has No Data: (May 10, 2022) A handy function you can call from an Access report's Property Sheet to gracefully inform the user when their report has no data to display.

#SqlServer Routines

Pushing Field Comments to SQL Server: (Jun 1, 2021) A VBA routine that takes field comments from a front-end linked table and pushes them to the corresponding back-end table in SQL Server.

How to Return the Precision and Scale of a Decimal Field in Access: (Feb 22, 2022) There's no way to use DAO to return the scale and precision of a Decimal field in Access. Luckily, there's an easy workaround using ADO.

SQL Server "Codify" Function: (May 20, 2022) This function will jump-start the process of converting long descriptions into meaningful abbreviations. It's great for creating "Code" columns in lookup tables.

GetLatestOdbcDriver(): Use VBA to get the Latest SQL Server ODBC Driver: (Dec 12, 2022) This simple VBA function will check the user's registry and return the latest ODBC driver from a list of supported drivers that you can customize.

GetLatestOledbProvider(): Use VBA to get the Latest SQL Server OLE DB Provider: (Dec 14, 2022) This simple VBA function will check the user's registry and return the latest OLE DB provider from a list of supported providers that you can customize.

ptReplaceDeclaredValues(): A VBA Function to Update SQL in Pass-Through Queries: (Jun 1, 2023) Use this function to reliably replace declared values in a pass-through query.

#StringFunctions Routines

Quoth thy SQL? Evermore!: (Sep 29, 2020) Solving the "O'Malley problem" with dedicated functions to sanitize our strings. Little Bobby Tables would be proud.

Come Together: (Sep 30, 2020) Do you build strings in loops? Stop trimming the delimiter at the end of the loop. There's a better way.

Part and Parse()-l: (Nov 8, 2020) Take the Tag property and OpenArgs argument to the next level. This function makes it easy to assign and extract multiple values from a single string.

Now you have two problems: (Nov 16, 2020) Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems. --Jamie Zawinski

Converting Numbers to Text: (Nov 28, 2020) Need to print business checks directly from Microsoft Access? You'll need a way to convert numbers into text. The NumToString function does just that.

A Function to Quote Literal Dates When Building SQL in VBA: (Dec 13, 2021) This improved version of my date-quoting convenience function eliminates a common source of date-handling ambiguity via the ISO date format.

How to Remove Special Characters from a VBA String: (Dec 23, 2021) A simple function to remove special characters from a string in VBA. Useful for sanitizing file names, sheet names in Excel, and lots of other stuff.

Reading Text Aloud in Microsoft Access: (Jan 7, 2022) Want a quick way to get started with Text-to-Speech in your VBA application? This 4-line method gets the job done and requires no references!

The Pluralize Function: (Apr 1, 2022) You don't have to settle for messages like, "1 record(s) inserted." This simple function is an easy way to format strings for pluralization in VBA.

Printf Function: (Apr 19, 2022) User @wqweto's string interpolation VBA function has some neat tricks, like using Unicode's Private Use Area to get safe temporary placeholder characters.

TagWrap() Function: (Jun 20, 2022) This convenience function helps you write more readable code when building strings with HTML-style opening and closing tags.

XmlWrap() Function: (Jun 21, 2022) This convenience function helps you write more readable code when building Office Ribbon-flavored XML strings.

CreateGuid: A Reliable Way to Generate GUIDs in VBA: (Aug 5, 2022) A well-tested method to generate GUIDs in both 32-bit and 64-bit VBA.

Dt() Function v2: Handling Time-Only Date Values: (Dec 19, 2022) An important bug fix for my previously published date-wrapping VBA function that addresses an issue integrating time-only values with SQL Server.

Dt() Function v3: Refactoring with Automated Tests: (Dec 20, 2022) Reader Brenda Bachtold provides a more readable version of my date-wrapping function. Automated doc tests prove that her version functions the same as mine.

#todo Routines

Subs vs. Functions in VBA: (Feb 7, 2023) What is the difference between a Sub and a Function and why would you use one or the other? I'll give you the short answer...and then we can explore the long answer.

Five Problems Working with Global Constants in VBA: (Mar 24, 2023) Working with global constants in VBA is unnecessarily difficult, resulting in code that's harder to read and maintain than it needs to be.

#we Classes Routines

weArrowKeyNav Class: (May 18, 2021) Do you want the up and down arrow keys to move to the previous and next records in a continuous Access form? Here's how to do that with only two lines of code.

The One Function Every VBA Developer Needs in a WithEvents Class: (Jun 15, 2021) Your WithEvents event handlers will only run if you set the event property to "[Event Procedure]." Here's a safe and easy way to do that.

A Wonderful, Magical Class Module: (Jul 30, 2021) Imagine a single class module you can use for progressive combo box filtering, lazy loading combo boxes, AND multi-column filtering!

#WindowFunctions Routines

Fun with Form Windows: (Nov 26, 2020) Using my FillAccessWindow function to resize Form and Report objects relative to the amount of available space on the Access canvas.

Get Back Here, Form!: (Nov 27, 2020) What happens if the user resizes their Access window so that our form can't open in its entirety? KeepFormOnCanvas() to the rescue!

Get a Handle on Window State: (Dec 28, 2020) Using the IsZoomed and IsIconic API calls to return the window state (minimized, maximized, or restored) of a form, report, or Access itself.

UseHand(): Changing the Mouse Cursor in Microsoft Access: (Aug 31, 2022) A classic Microsoft Access mouse cursor trick gets simplified and updated for 64-bit VBA compatibility.

#WizHook Routines

Wiz(): Never Forget to Set the WizHook Magic Key: (Oct 26, 2022) This convenience function handles setting the WizHook Key property with the required magic value once per session.

WizMsg(): A Simple Way to Create a MsgBox with Bold Text: (Oct 27, 2022) Bold text in a Microsoft Access message box? It's true. And it requires no API calls, no third-party libraries, and no arcane string syntax. Check out WizMsgBox!

Sorting Arrays of Strings in Access with WizHook: (Nov 2, 2022) No need to write your own array-sorting code from scratch. Use the SortStringArray method of Access's hidden WizHook object instead!