The Power of SQL Applied to Excel Tables for Fast Results

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

Continue Reading...

Understanding Relational Databases: The Basics

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

Continue Reading...

Excel VBA ParamArray: A Function That Handles Unknown Number of Arguments

excel excel-vba vba May 20, 2020
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...

Continue Reading...

The Excel Dashboards Guide: The Visual

How should a dashboard strategy be conceptualized? How do you translate the accumulated data into an effective business compass for the decision maker? Start your journey here

We planned our Dashboards and prepared the data. We’re now ready for the artistic magic: the visual effect and user experience. This is the focus of today’s Blog post, the last in this “all Dashboards” 4-articles series. If you missed the previous parts, start here.

Start with Observation

Now is the time to take the blueprint of our Dashboard we sketched when we planned our Dashboards layout,...

Continue Reading...

The Excel Dashboards Guide: Data Staging

How should a dashboard strategy be conceptualized? How do you translate the accumulated data into an effective business compass for the decision maker? Start your journey here

After we have finished all the preparations for our Dashboard in the previous Blog post of this Dashboards’ Guide series, we’re now ready to start implementing our Dashboard in Excel.

Preparing the Data for the Dashboards

A good data staging serving our Dashboards must be very efficient, to quickly refresh the Dashboards. This implies that we need to carefully strike a balance across all resources required...

Continue Reading...

The Excel Dashboards Guide: Planning Considerations

How should a dashboard strategy be conceptualized? How do you translate the accumulated data into an effective business compass for the decision maker? Start your journey here

In the previous Blog post we understood the main aspects and types of Dashboards. In this second of a 4-part Blog post, we start preparing ourselves for the much-anticipated Dashboard.

The Big Picture

The place to start is the business requirements analysis document, we prepared (or received) before we planned and implemented the application. The Dashboards’ elements should already be described right there.

...

Continue Reading...

The Excel Dashboards Guide: Orientation

How should a dashboard strategy be conceptualized? How do you translate the accumulated data into an effective business compass for the decision maker? Start your journey here

The importance of Dashboards

Dividing information systems’ role into two main focus areas, we have the operational processes on one hand (OLTP) and the analytics on the other (OLAP).

Considering the ultimate goal of supporting and driving the business, OLTP systems streamline the daily work, govern the processes and control the data as it is being accumulated in the database. OLAP solutions are tasked with...

Continue Reading...

Excel VBA User Forms – A Game Changer

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

Continue Reading...

The Full Dev to QA to Prod Cycle with Excel VBA Projects

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

Continue Reading...

105 Excel VBA Functions Explained: My New Course Launched Today

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

Continue Reading...
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!