Hidden Features - No Longer Set

Hidden Features

DefType Statements in VBA: The Dark Side of Backward Compatibility

Young VBA programmer, be not tempted by the dark side. DefType statements must be understood, but never used!

Intermediate

The IIf() Function vs. The IIf() Statement

They may look identical, but there is a very important difference in how they get evaluated.

Hidden Features

Hidden Feature: Drag and Drop Access Objects Between Files

Skip the "Import Access Objects" dialog screens and use your mouse to move Tables, Queries, Forms, Reports, and code modules between Access files.

Hidden Features

Expressions vs. Code: Implicit Type Conversion

In Microsoft Access, is a whole number literal implicitly treated as a Long or an Integer or a Double? As it turns out, it depends on where you ask.

Hidden Features

How to Check if a User Clicked [Cancel] on an InputBox in VBA

It's possible to distinguish between a user clicking [OK] on an empty box or clicking [Cancel]. You just need to use this simple trick.

Hidden Features

Debugging Private Procedures

After more than 14 years as a VBA developer, I recently discovered that you can debug private procedures without temporarily making them public!

Conventions

Beautiful Blocks of Boilerplate

Using the colon character to join multiple lines of code all willy-nilly can lead to messy code. But, used judiciously, it can create beauty from chaos.

Hidden Features

Careful What You Watch For

Can the simple act of creating a Watch change the behavior of your code while debugging? Why yes, yes it can.

Advanced

JetShowPlan: A Primer

You may be familiar with JetShowPlan, but I guarantee you've never read an article about it quite like this one.

Hidden Features

VBA's Case Changing "Feature"

VBA likes to change the casing of our code (upper/lower/mixed). It may seem random, but it's not. Read all the details here.

Hidden Features

Blast From the Past

Why use keyboard shortcuts from Access 2003 when developing in 2020? Because starting with Access 2007, the new shortcuts suck.

Hidden Features

VBA Alchemy: Turning Methods Into Properties

One can check the status of screen painting in Excel, but not in Access. This turns out to be an important shortcoming. Let's remedy it.

Code Library

Creating Optional Dependencies

Using conditional compilation to avoid the "Dependency Train" problem.

Error Handling

Graduate Level Error Handling

Once again, I borrowed an idea from Python. To smuggle this concept into VBA, I turned to a rarely-used language feature...

Hidden Features

Requery a Form in Place

Form.Recordset.Requery. That's it. That's the whole article.

Code Library

One Thing at a (Hundred) Times

How to use Run() and GetTickCount() for some quick and dirty performance profiling. Come for the code, stay for the scolding on premature optimization.

Hidden Features

A Rounding We Will Go

Two kinds of rounding, the VBA language spec vs. the Office VBA implementation, and a drop-in replacement for VBA.Round().

Hidden Features

Expressions vs. Code

When is code not code? When it's an expression. What's the difference and who really cares? Let's explore.

Hidden Features

Wherefore art thou, database properties?

In my previous post, I talked about adding custom properties to the database object. I covered how to do it, but did not go into any detail about why you might want to do it. Let's remedy that now.

Hidden Features

Database Properties for Thee

The DAO Database object has a Properties collection. You can read through the list of properties to extract saved database options. You can also add your own properties to the object.