VBA - No Longer Set

ptReplaceDeclaredValues(): A VBA Function to Update SQL in Pass-Through Queries

Use this function to reliably replace declared values in a pass-through query.

Quick Tip: Code Comment Breadcrumbs

This quick tip is a great way to boost the signal-to-noise ratio of your code without sacrificing valuable context from your decision-making process.

Hidden Features

Understanding the Access ColumnWidth VBA Property

The ColumnWidth property of an Access datasheet has some strange behavior, especially when it comes to its special values: Default Width and Size to Fit.

Defensive Programming

Five Ways to Turn Runtime Errors into Compile Errors in VBA

Compile errors are cheaper and easier to fix than runtime errors. Here are five ways to turn potential runtime errors into compile errors.

Basic

Why You Should Always Use Option Explicit in VBA

Don't let typos and logic errors ruin your VBA code. Read our latest blog post to learn about the importance of Option Explicit and how to use it.

Defensive Programming

Five Ways to Turn Logic Errors into Runtime Errors in VBA

Runtime errors are cheaper and easier to fix than logic errors. Here are five ways to make that happen.

Backwards Compatibility

How to Use VBA to Set the ControlSource Property of an Access Image Control (and Other Deeply Hidden Object Properties)

Ever wonder why you can set some values in the form/report designer Property Sheet but not in VBA? You may just need this well-hidden workaround.

Class Modules

Better Global Constants in VBA with Dot-Driven Development

My global constant "C" class--with PredeclaredId = True--is a great alternative to traditionally-declared global Public Const's.

VBA

Five Problems Working with Global Constants in VBA

Working with global constants in VBA is unnecessarily difficult, resulting in code that's harder to read and maintain than it needs to be.

VBA

Why Do I Need to Set Objects to Nothing in VBA?

Everyone knows you need to set object variables to nothing in VBA, right? ... Right?

VBA

MS Access Windows API Viewer

Here are three good options for looking up proper Declare statements when calling Windows API functions from VBA.

Subs vs. Functions

Subs vs. Functions in VBA

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.

VBA

Enums in VBA: A Great 10-Minute Introductory Video

Excel MVP Paul Kelly packs a bunch of great information (including a well-hidden feature) into this ten-minute video introducing Enums in VBA.

VBA

Idempotence vs. Determinism

Idempotent functions can safely be run multiple times. Deterministic functions always return the same output given the same inputs. Let's explore further.

Defensive Programming

Guard Clauses

Guard clauses are one of my favorite low-friction defensive programming tools.

VBA

Block-Level Scope in VBA...Does Not Exist

VB.NET and VBA may look the same, but there are some important differences. This is a little-known feature of VB.NET that I really wish was available in VBA.

Basic

All About Indenting

Do you obsess over the smallest details of the code-writing process? If not, you might want to skip this article. Don't say I didn't warn you...

VBA

Choosing the Proper Lifetime of Variables in VBA

Some values should be calculated every time you need them. Others should be stored for performance reasons. But how do you decide which way to go?

VBA

Scope vs. Extent in VBA

Master the extent and scope of your VBA variables for code that's efficient, effective, and easy to maintain.

VBA

Avoiding the "Invalid in Immediate Pane" Error

Sometimes, using the colon character to combine statements in the immediate window results in an "Invalid in immediate pane" error. Here's why and how to fix it.

SQL Server

Epoch Differences Between VBA and SQL Server

Do you have unexplained dates of December 30, 1899 in your SQL Server database? VBA's epoch (i.e., zero date) could be the culprit.

Fluent API

Diving Deeper into the World of Fluent APIs: An Unusual Way of Constructing Class Modules

Martin Fowler first coined the term fluent interfaces back in 2005. What are they, what do you need to know about them, and what do they look like in VBA?

VBA

Dollar Signs at the End of VBA Functions

What's the difference between Left() and Left$()? Why would you use one or the other? And what do *I* do, personally?

Tools

Checking Version Compatibility with NetOffice

Take advantage of the open-source NetOffice project on GitHub to look up version compatibility for the Access/Office/Word/Excel object models.

Code Library

SetPredeclaredId(): Change the Hidden PredeclaredId Attribute of a VBA Class Module

This simple function overcomes the lack of a hidden attribute editor for PredeclaredId values in the VBA development environment.