A collection of variables of the same data type is a very efficient data structure and easy to work with. Did you know that you can also define your own custom collections? Here is how!

What is a collection?

A collection is a stack of objects of the same type, “chained” one after the other.

As you add an object to the collection, it assumes the next index in sequence – added as the next “link” in the “chain”.

The collection is an easy to use data structure. It requires no declaration of the anticipated size of the “chain” in advance....

Leveraging the nature of numbers storage in memory, we can exploit enumerators to construct complex Boolean expressions in near-English language. This post will change the way you handle multiple-choice questions.

Last week we introduced Enumerations and how they make our lives easier when handling numeric sets of constants.

Today we’ll take it further by exploiting the Enumeration data structure to handle combinations of values.

What is Combination of Values?

The typical use case we’re addressing here, is when we have to handle multiple-choice variables that can hold any...

Instead of remembering numeric codes – label them with meaningful words. This way your code will flow from your fingers just as you speak, and your program will be clear and readable. Let us understand enumerations.

What are Enumerations?

Simply put, enumeration is about naming sets of related constant numbers.

You probably already worked with enumerations. For example, the Application.Calculation property is numeric. It holds a number representing the calculation mode of Excel. Possible values for this property are:

Prompt the user for required input, using an easy to interact with input box in your Excel VBA program. All you need to know about the InputBox function is right here. Oh, do not forget to validate!

What is an InputBox and When to Use It

In last week’s Blog post I covered in great detail the Excel VBA MsgBox function. I will not repeat how user interaction is incredibly important, but as you now know how to present informative messages to your user, sometimes you also need some input from the user to feed your program.

As you probably know, you can employ VBA User Forms for highly...

Show informative, easy to interact with messages to your Excel VBA program users. All you need to know about the MsgBox function is right here. Read on...

User interaction is a big deal

Why?

Because this is where your program meets the psyche, mindset, values, thoughts and habits of the user. Can you guess what those are?

If the user doesn’t feel “at home” with your application, he would not want to use it. The American science fiction writer, Larry Niven, captured the importance of the user experience beautifully:

“That's the thing about people who think they hate...

In this advanced chapter of OOP with Excel VBA, we implement objects composition with a dynamic collection in Excel VBA using Class Modules. Not for the faint of heart…

In Part 1 on this exercise of implementing OOP in Excel VBA, we left off with a half-baked order object. It had some properties and a couple of methods, but its line-items were missing.

Today we’re going to round up our order object to include its line-items.

Thinking about the right structure and arrangement of the line-items within the order object, two main characteristics should guide our thinking:

1. An order...

Objects are a key element in computer programming. While objects are at center stage in Object Oriented Programming, they are widely used in procedural languages and serve as the building blocks of complete applications * Let’s understand objects!

What is an Object?

Simply put, an object is any “thing” you can actually see, physically.

For example, a person, a car, a pencil, a circle – are all physical objects.

Extending our perception of object to understand how objects are used in computer programming, an object can also offer a well-defined service: A Government...

You’ve spent days in perfecting your Excel model. Sophisticated formulas spanning multiple Workbooks are generating the results you need. But it is now heavy with long delays and response times. What could be the reason and what can you do about it?

The magic of live formulas has its toll

No doubt one of Excel’s magic is the “live grid” in which formulas connect cells, charts and formatting for real-time calculations and rendering. It Is so appealing, that we tend to forget that maintaining the web of connected cells requires resources. Compute and memory resources.

Ever needed to run an Excel Macro every day automatically? I mean, without leaving Excel open or interacting with Excel at all. Here’s how you do this taking advantage of two technologies that comes with Windows

Why schedule a daily silent job?

In one of my customer’s project that manages trainings and certifications, employees receive email and SMS notifications on various events: upcoming training reminders, training result (with a certificate attached), change of instructor, training assessment reminders etc.

The technique for doing that is using a queue. All notifications...

Excel offers large Worksheets. Data can be arranged and stored flexibly, searched and acted upon. Do we have a Database here? * Let’s sort out Excel as a Database.

What is a Database?

Simply speaking, a database is any structure in which data is to be arranged along with tools to store, manipulate and retrieve the data.

Several such pre-configured “data arrangements” are available as products, each developed in its time to solve data-related challenges. Let’s mention a couple.

Relational Database: In a relational database data is arranged in tables with relations...