VBA - No Longer Set (Page 3)

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.

VBA

3 Ways to Generate Temporary File Names in VBA

Here are three options for generating temporary file names in VBA. Which is best? As with many programming questions, the answer is, "It depends."

TempVars

A Safer Way to Use TempVars

The TempVars object is an intriguing alternative to traditional global variables, but it has some shortcomings. Here's one way to work around them.

VBA

Namespaces in VBA

VBA may not have the same level of formal support for namespaces as VB.NET, but with a little creativity, we can realize the same benefits in other ways.

Quick Tip

Quick Tip: Set Next Statement with Mouse

Try this tip the next time you use the Edit & Continue feature when debugging. After more than 15 years of developing VBA, I'm still learning new things.

VBA

Static Functions and Subs

After fifteen years of writing VBA, I'm still learning new features of the language. Even if they're not particularly useful features...

Hidden Features

How to Show Hidden Items in the VBA Object Browser

One possible fix for the "Cannot jump to '|' because it is hidden" error is to show hidden members in the VBA object browser.

Memory Management

Memory Management in VBA: 3 Keys to Avoiding Memory Leaks

Managing memory in VBA is a piece of cake, especially if you follow three simple guidelines.

VBA

Philipp Stiefel on VBA Compilation

Is VBA an interpreted language? Or a compiled language? Or something in between. Philipp Stiefel shines light on the topic for us.

VBA

Avoiding Overflow Errors When Defining Calculated Constants

Overflow errors are usually straightforward. But what about when you get an overflow assigning a value of 1 to a Long integer?