Working with Colors in Excel VBA

In this Blog post I cover all ways and tips for using color codes in your Excel VBA program. Discover how to store colors as constants, identify the code of any color and apply colors to any Excel object.

Understanding Color Codes

Every color is identified by a unique number. For example, 255 represents Red. 16774980 represent a specific cyan.

You can also convert any decimal number to its Hexadecimal representation (or Hex for short). For example, that cyan color number equals FFF744 in Hex representation (use any decimal to hex converter you find on the web to see how it works).

You can...

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

Is Your Excel VBA Running SLOW? Here are 6 Tips to Improve Your Excel VBA Performance

A prospect asked me once if I can improve his VBA code performance. After 30 minutes analysis of his code, I committed to improve performance in 95% * How can your Excel VBA code run faster, much faster?

Last week I offered some advice on improving the performance of your Excel Workbook. This week, we’ll improve your VBA code!

A story of frustration with slow VBA

Most of the VBA code out there is a result of advanced Excel users who learned some VBA statements. They know how to reference Worksheet cells, how to run a loop and maybe manage multiple Workbooks and Worksheets.


Continue Reading...

Excel is slow? Here are possible causes and ways to make Excel run faster

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.

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

How to Create Your Own Web Service with Google Apps Script and Call it from Excel VBA

Ever needed to perform some Web task in service of your Excel VBA application? You can write your own web-service to do whatever you need * Popular use cases: access Google Sheets, Docs, Presentations, email and calendar from your Excel application!

Calling Web-services from Excel VBA

As I thoroughly explain and demonstrate in my online course: Beyond Excel Boundaries with VBA: Office, Files and Internet, you can send any HTTP request over an Internet connection from VBA using the WinHttp service library. Here’s a code snippet to send a request to some URL:


Continue Reading...

Excel VBA: How to Check if Printer is Ready

Your VBA program needs to send out an original document to the printer. What if the printer is offline, or disconnected? Verify the printer is ready before printing or notify the user of a problem * Here’s the VBA function for that

Why check if the printer is ready?

The first situation in which I encountered the need to verify the printer is ready, was in an application I developed for small business management, in Excel.

In my home country, Israel, tax regulations dictate that the tax invoice confirming the receipt of a payment is printed exactly ONCE. This would be the ORIGINAL...

Continue Reading...

Excel VBA: Restoring the State of the Application Before Returning Control to the User

When your VBA code finished its task and control is returned to the user – be sure to restore protection, hide Worksheets, enable events, reset the mouse cursor and turn on screen updating * here’s a Sub that will take care of that

A controlled environment for the user

The flexibility, diversity and power of Excel makes it a generic tool for almost any task. However, this richness of options and openness can put your data and business at risk.

Consider a large table you maintain in Excel. Mistakenly, you mis-typed a date to be: 05/10/20020. You didn’t notice. That row is...

Continue Reading...

50% Complete

Two Step

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