Code Library

Code Library

Mike Wolfe

• 4 min read
Code Library
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.

Sign up for more like this.

Enter your email
Subscribe