When preparing a dynamic report in Excel, you need to arrange the data in the report structure. In this Blog post I demonstrate how to clear the report range and re-populate it with data.

Types of reports

Reports can take many forms, as far as presenting data is concerned.

A report is any artifact created from data for end use, or as required by another system as input.

A report can be a single page summarizing information about something (see the certificate example of last week’s Blog post), it can be an order for an employee to do something (such as in a production order) and it...

Take your Excel model one step further beyond calculations. Prepare and publish the final report as a slick PDF file with a click of a button. Here is how…

Why PDF

PDF has become to be the standard file format for immutable documents.

Immutable, for those not computer programmers yet, means it cannot be changed once it is created.

PDF is highly popular for its quality, compactness, and the ability to sign electronically to render a formal document accepted by private and public institutions.

Preparing the report

What I always do, is dedicate a Worksheet for staging the report. This...

You are good with Excel. You love it. It gets the job done. You even heard about Macros, or VBA, but pushed it aside. It is not for you, you said to yourself. It is too complicated, you thought. I am here to tell you: it is exactly what you need to do next. Here is why.

The plain motivation at sight

Would you skip learning how to drive a car had I told you it can get you to your destination hundreds and thousand times faster than your bicycle?! I don’t think so.

Operating a vehicle may sound difficult, if not frightening, if all you’re used to is riding your bike. Still –...

I received many questions on The Excel VBA Inner Circle program since it was launched. I summarize here the most frequent questions and my answers to them.

Last week I published a Blog post I thought would be all you may need in order to choose the right Inner Circle plan for you.

However, I am still getting some questions from some Excel enthusiasts about the program.

Today’s post is dedicated to those still having questions.

Frequently Asked Questions

Q:           I already learned Excel VBA and get along pretty well. Why should I...

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

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

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

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