Excel VBA: User Defined Data Types

Choosing the right data structures as we plan our program is especially important. Molding our data to be structured to our specific requirements makes our program more readable, maintainable, and efficient. What can we do beyond integer, string, and date?

In my last week’s Blog post I elaborated on data types and their use in VBA.

Beyond using the readily available native data types we’re all familiar with, such as Integer, Date, String and Boolean, we can define our own, user-defined data type.

What is a User-Defined Data Type?

Simply put, a user-defined data type is a group...

Continue Reading...

Excel VBA: Data Types and Option Explicit

Why do we need to declare data types for our variables, what purpose does it serve? * Why do some programming languages not care about it too much and what data type are available for us in VBA?

What are data types?

A data type is a structure to store data with given attributes and constraints.

You are probably familiar with some popular data types defined for us in VBA, such as an Integer, String and Date.

An Integer in VBA, for example, is allocated with 2 bytes to store numbers in the range -32,768 to 32,767.

A Date in VBA is allocated with 8 bytes to store numbers representing date and...

Continue Reading...

Excel VBA: Collections

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

Continue Reading...

Excel VBA: Enumerations

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:

...

xlCalculationManual

-4135

xlCalculationAutomatic

-4105

Continue Reading...

Object Oriented Programming with Excel VBA – Part 2

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...
Continue Reading...

Object Oriented Programming with Excel VBA – Part 1

Ever wanted a concise, clear with code examples guide to implement OOP in Excel VBA? It is your lucky day. Read on…

After we covered in detail what OOP is in last week’s Blog post, we’re ready to see how this works in Excel VBA.

Keep your eyes on the Object

The key concept to keep in mind as we implement objects in Excel VBA, is that we are creating our own custom objects, on top of the available objects at our disposal out of the box.

The image here shows a list of the available properties and methods of the Workbook object.

This list is the exposed interface of the...

Continue Reading...

Understanding Object Oriented Programming

If we live in a world of objects and our computer programs help us manage our world, how about representing the world’s objects in our computer programs! * Object Oriented Programming explained

In order for you to understand this article, you need to know what an Object in computer programming is. Lucky for you, I just wrote about it last week.

Between Two Programming Paradigms

There are two dominating programming paradigms out there.

Procedural Programming

The basic concept is that of statements organized in modules and functions, executed in order.

Typically, there is a main...

Continue Reading...

Understanding Objects in Computer Programming

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

Continue Reading...

Scheduling Automatic Excel VBA Programs for Uninterrupted Execution

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

Continue Reading...

Using Pre-Compiler Directives for Cross-Platform Compatibility of Your Excel VBA Programs

Need your single Excel program to run on Windows and Mac? With Office 32-bit and Office 64-bit? You can do that using pre-compiler directives * Here’s what you need to know (and code!)

In my Blog post from last week In which I elaborated on writing VBA code that runs on both Office 32-bit and Office 64-bit, we ended up with a challenge. We need to include the PtrSafe and LongPtr qualifiers in external libraries declarations for Office 64-bit, but ignore these qualifiers in Office 32-bit environments (where they are unknown).

Today I’ll explain all about VBA’s pre-compiler...

Continue Reading...
Close

50% Complete

Two Step

Once you submit your details, you'll receive an email with a confirmation link. That's it! you're subscribed!