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

If you are in anyway related to software in the service of organizations – you MUST be familiar with Relational Databases * Let’s understand the basics of RDB: what, why, strengths and weaknesses

The Beginning

Relational Databases (RDB) drive nearly all of the information systems in the service of any organization today. Despite being a central and critical technology, it’s only 40 years old, 10 years younger than me.

It was Edgar Codd from IBM who first devised the RDB concept in the early 1970’s. Only in 1978 was the first commercial RDBMS (Relational Database...

When developing, delivering and maintaining software solutions, you need to manage the Dev-QA-Prod cycle. This is no different when developing an Excel based application * Here’s how I do it

The Basics of Development Lifecycle

After you finish developing a scoped application, you need to pass it over to QA – Quality Assurance. The QA folks are abusing the software and document their exceptional findings: logical errors, software errors, user experience issues, environment conflicts, and the like. Often, issues classified as important will bounce back to development for fixing....

What makes this course unique * What is this course about * What did it take to produce this course * Is this course right for you?

Not that I planned to launch this new course when everybody is glued to the computer screen waiting for the Corona scare to pass by, but today is the day, and it’s out!

Those of you who follow me for a while, already know the wide portfolio of services and products I offer, all about developing business software with Excel VBA and leading many others to become such consultants.

IF you’ve completed my flagship course: Computer Programming with Excel...

When using external function libraries in VBA, you need to choose between late biding and early biding * What is it, why chose one over the other and how is it done properly?

When the offered VBA functionality is not enough

With VBA you can flexibly write just the code you need for your program. It is a flexible, functional programming language that is also aware of the Excel Object Model. You can easily access and manipulate almost all of the Excel objects: Worksheets, Cells, Charts, Tables, Shapes, Printing settings and much more.

However, there are objects and tasks that are not readily...

What can you do as an Excel developer? Can you make a living out of it? Is it worth anything in the job market? Here’s my take…

What Kind of an Excel VBA Developer are You?

You might be surprised this question is even raised. Intuitively, once you’re an “Excel VBA Developer”, then you’re an “Excel VBA Developer”. Right?! Well, let’s look closer.

Consider Joe. Joe is an accountant in a large corporation. He is an avid Excel user, really stretching the grid and built-in functions. Joe started recording some Macros to automate some tasks....