Creating a PDF report from Excel Worksheet with VBA

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

Continue Reading...

Frequently Asked Questions on the Excel VBA Inner Circle Program

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

Continue Reading...

A story about a customer request and the delivered Excel VBA solution

A rare glimpse of the “behind the scenes” work on an Excel project. From the raw customer request through the business requirements analysis report up to the delivered solution. Impressions from the first Excel VBA Inner Circle workshop.

The first Excel VBA Inner Circle monthly workshop took place two days ago.

The monthly workshop is a deep dive into some aspect of Excel VBA and SQL programming and career. I expose advanced VBA programming, code, user experience considerations, data design and processes planning, and any professional aspect having to do with Excel VBA and SQL.

...
Continue Reading...

Excel VBA: Arrays

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

Continue Reading...

Excel VBA: User Defined Data Types

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

Continue Reading...

Excel VBA: Data Types and Option Explicit

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

Continue Reading...

Excel VBA: Collections

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

Continue Reading...

Excel VBA: Bit-Wise Enumerations

Leveraging the nature of numbers storage in memory, we can exploit enumerators to construct complex Boolean expressions in near-English language. This post will change the way you handle multiple-choice questions.

Last week we introduced Enumerations and how they make our lives easier when handling numeric sets of constants.

Today we’ll take it further by exploiting the Enumeration data structure to handle combinations of values.

What is Combination of Values?

The typical use case we’re addressing here, is when we have to handle multiple-choice variables that can hold any...

Continue Reading...

Excel VBA: Enumerations

Instead of remembering numeric codes – label them with meaningful words. This way your code will flow from your fingers just as you speak, and your program will be clear and readable. Let us understand enumerations.

What are Enumerations?

Simply put, enumeration is about naming sets of related constant numbers.

You probably already worked with enumerations. For example, the Application.Calculation property is numeric. It holds a number representing the calculation mode of Excel. Possible values for this property are:

...

xlCalculationManual

-4135

xlCalculationAutomatic

-4105

Continue Reading...

Excel VBA InputBox Explained

Prompt the user for required input, using an easy to interact with input box in your Excel VBA program. All you need to know about the InputBox function is right here. Oh, do not forget to validate!

What is an InputBox and When to Use It

In last week’s Blog post I covered in great detail the Excel VBA MsgBox function. I will not repeat how user interaction is incredibly important, but as you now know how to present informative messages to your user, sometimes you also need some input from the user to feed your program.

As you probably know, you can employ VBA User Forms for highly...

Continue Reading...
1 2 3 4
Close

50% Complete

Two Step

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