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

Do you need to generate Excel reports based on a template on a regular basis? Do you need to copy the template Worksheet to another Workbook, or from another Workbook? Here are some approaches with VBA code

Possible use cases for Excel templates

• Your monthly report is based on an Excel Worksheet you prepared, and each month needs to be stored as a separate Workbook with its specific calculations for the month.
• You are preparing and sending your customers a standard letter with calculations / charts that are changing every time. Maybe a quote, or a model calculation. You want to send them...

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

Sometimes writing a short, simple function, can hide in-depth processing that resolves quickly – by calling… itself * What is a recursive function, how does it work, what to be aware of and how it is implemented in Excel VBA – read on…

What is a Recursive Function?

Technically speaking, a recursive function calls itself as part of its program flow.

Yes, you read it correct. This is the general idea:

Function CalcSomething(…) as Long

     …

     X = CalcSomething(…)

     …

End...

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

Arrays, one of the most flexible, performant and popular data structure in almost any programming language becomes especially useful in Excel. All you need to know about arrays in VBA is right here

In case you missed it, the last month in my Blog was dedicated to data structures in computer programming and their use in Excel VBA. After we covered Enumerations, Collections and User-Defined Data Types, we close this week with a grand finale: Arrays.

What is an Array?

An array is a memory storage structure for storing an indexed series of elements of the same data type.

This is how we would...

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

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

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