The Bang! (Exclamation Operator) in VBA

April 11, 2013 in VBA

One of the most peculiar aspects of Visual Basic for Applications in the ! operator, called the “bang”. It is particularly common in code for Microsoft Access, where you may see things like this: strCriteria = “EmployeeID = ” & Me!txtEmplID Set rst = CurrentDb.OpenRecordSet(“SELECT * FROM Employees WHERE ” & strCriteria) If Not rst.EOF Then Me!txtName = rst!Name Me!txtTitle = rst!Title Me!txtHireDate = rst!HireDate End If rst.Close Set rst = Nothing 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! Definition: The Bang
…read more

Collections in VBA – Overview

March 27, 2013 in VBA

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 Arrays are a language feature and use value-type semantics (they do not use the Set keyword for assignments, for example). Collections and Dictionaries are object types provided by the Visual Basic for Applications library
…read more

Copy and Paste in VBA

March 4, 2013 in VBA

It can be really handy 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. Using Microsoft Forms DataObject The most common way to implement Copy and Paste is to add a reference to Microsoft Forms 2.0 and use the DataObject class. Microsoft Forms often does not show up in the
…read more

Organizing Code With Namespaces in VBA

February 19, 2013 in VBA

Many programming languages provide ways of logically grouping code by name at multiple hierarchy levels, called namespaces. Java, JavaScript, 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. Identifiers in VBA Visual Basic for Applications does not provide a mechanism for this. Reflecting its origins more in procedural than object-oriented programming, all entity definitions in VBA exist in the global scope. In practical terms: Any function or sub defined in a standard module
…read more

Increasing Performance of Regular Expressions in VBA

February 12, 2013 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. Caching Compiled Regular Expressions What we’ll use is the fact that, behind the scenes, every regular expression pattern gets compiled into a tiny little program that executes in order to actually apply the regular expression.
…read more

Regular Expressions in VBA

February 8, 2013 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. Oracle supports regular expressions natively as does Teradata 14 (first released in February 2012), but most other database platforms require some sort of extensibility mechanism. For SQL Server you have to build CLR wrappers, in DB2 and
…read more