Microsoft Access

The Bang! (Exclamation Operator) in VBA

One of the most peculiar aspects of Visual Basic for Applications in the ! operator, called the "bang". There's a lot of confusion about the bang operator, and nowhere have I seen the correct whole story about it. So, I present it here!Read more


Collections in VBA - Overview

In VBA programming collections of all types are indispensable. For both the new and experienced VBA programmer there is a lot to know about collection types in VBA, so I'm going to cover them one by one. In this post I'll provide a quick survey of the available collection types.

Types of Collections in VBA

There are three fundamental collection types in VBA

  • Array
  • Collection
  • Dictionary

Read more


Copy and Paste in VBA

It can be really useful to have simple string Copy and Paste functions in VBA. I use them frequently, as during development on MS Access applications I often use the Immediate window almost like an interactive command prompt. Sometimes I want to copy the output of a function directly to the clipboard, or fetch (Paste) data from the clipboard as input to a function. In this article I illustrate how to create simple Copy and Paste functions using the Microsoft Forms DataObject class.

Read more


Organizing Code With Namespaces in VBA

Many programming languages provide ways of logically grouping code by name at multiple hierarchy levels, called namespaces. Java, ActionScript and others call them packages, and they are tied to the file system organization of the source code. .NET programming languages just call them namespaces and they can be defined anywhere independent of file structure. In this article I show how to achieve a similar effect in VBA using a combination of standard and class modules.

Read more


Increasing Performance of Regular Expressions in VBA

In a previous post I presented some general-purpose wrappers for adding regular expressions functionality to VBA and SQL in Microsoft Access.

These wrappers work well, but we can make them much more efficient for situations where the same pattern is used many times, such as in a SQL query where the regular expression will be executed on each row of the input table or query. This article describes how to use a simple factory pattern to cache and reuse RegExp objects, dramatically increasing performance when the same pattern is used more than once during the lifetime of a program.

Read more


Regular Expressions in VBA

A frequent question for most database platforms is how to use regular expressions in SQL queries. Regular expressions are an awesome tool for working with text and are built in to just about every widely used programming language, but are not standardized in SQL. Sometimes relational databases are employed to work on chunks of text and having regular expressions available is mighty helpful. In this article I'll show how to build regex functions callable from both VBA and within MS Access SQL queries, using the VBScript Regular Expressions library. Also see Increasing Performance of Regular Expressions in VBA for how to turbo-charge the performance of these utility functions.

Read more