Code Library 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 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.