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

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

Your Excel 2016 program runs perfect on your Excel 2016 32-bit, but fails on Excel 2007, or on Excel 2013 64-bit. Why that happens and how to write a fully compatible VBA program? Here are the answers (and some VBA code)

In last week’s Blog post, I explained and demonstrated how Windows functions libraries, or DLLs, can be called from VBA.

Leveraging external functions to Excel is great, especially considering the vast variety of functions the Windows operating system offers. However, breaking the tight Excel environment is also subject to some compatibility issues.

Why are there...

Windows offers tons of readily available functions that perform almost any task we need. Why not make use of it in our Excel VBA programs? * Here’s what you need to know about calling DLLs in Excel VBA

Modular Programming

As you recall from our computer programming lessons, one of the key concepts in good software design is modularity: breaking our code into functional subroutines or functions, each responsible for a specific task with a clear interface for calling it.

Once encapsulated in a self-contained function, this functionality can be re-used in many locations in your program....

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:

    Dim...

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

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

If you ever needed to query Excel tables (and who hasn’t) from your VBA programs that contained thousands of rows, or join multiple tables in the process – this is the Blog post you’ve been waiting for * Write complex SQL queries over Excel tables as if they were stored in a relational database

Do SQL and Excel go together?

YES!

SQL is the most popular Databases language. It is robust, proven and works with extremely large tables stored in any common relational Database software. If you want to learn more about Databases and SQL – read my last week’s Blog post...

What if you need to accommodate an unknown number of arguments to be handled by a single function? As with most functional languages, VBA supports this using a ParamArray type * Here’s all you need to know about ParamArray

Passing Arguments to a Function

As you probably know, a function (or subroutine) can accept arguments (or parameters) from the calling function (or sub). These arguments are part of the definition (or “stub”) of the function. For example:

Function AddTwoNumbers(_
ByVal FirstNumber as Single, _
ByVal SecondNumber as Single) as Single

The above function...

What separates an Excel model from a professional business application? Besides good programming practices it’s the user experience * Excel VBA User Forms allow you to deliver mission-critical business applications that will make your customer WOW.

Today I launched my third course in the Computer Programming and Databases with Excel VBA and SQL program.  This course, titled Beyond Excel Boundaries with User Forms: Deliver a Professional User Experience, covers User Forms implementation as the user interface for Excel-based business applications.

This launch today made it an easy...