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

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

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

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

Dashboards typically come from the Business Intelligence world. Updating the operational DB come from the transactional world. Why not connect the two for a full insight-to-action experience?

In a project I delivered this week to a new customer in Australia, it hit me that I have combined dashboards with updating data records for long time. Intuitively, as I envision the customer’s line of thought, I usually allow the user to cross the lines and open an update screen straight from a dashboard.

What’s the Big Deal?

In the traditional data-centric approach to business information...

Why protect your Excel Worksheets? How to do it right? How to employ a protection strategy in a tight business application based on Excel? Can you protect your IP vested in your VBA code? Read this before you deliver your next Excel project to your customer

All Protection Options Available for You

Before we explore the business motivation and possible strategies for protecting your work, let’s recap all of the protection options we have in Excel:

• Password-protect your Workbook file. This option will require a password you set, when the Workbook is opened. The Workbook will not even...

Making an attractive Excel product that runs on every Windows computer is challenging * What were my main considerations, tricks and compromises? Read on…

Most of you are probably already familiar with the recent addition to my Excel mastery offerings: The Ultimate Excel Date Picker. If you’re not, now is the time to pause and have a look: https://morsagmon.com/datepicker.

Product vs Project

Unlike a customer-specific project, the Date Picker is a standard product. What’s the difference, you’re asking? A project is designed to meet specific needs of a particular...

SUM, AVERAGE, COUNTIF, VLOOKUP – these are a few of the most popular Excel functions. If you’re advanced, or in a specific profession, you may also use DSUM, NPV, REPLACE – to name a few. Even if you heard of, or used, SUMPRODUCT – be prepared for a surprise.

As I was updating myself with Excel questions in Quora this morning, considering what to post in my Quora Excel VBA Expert Space, I came across this question: What is the most useful Excel function that no one ever uses? I needed less than a second to shout out: SUMPRODUCT!

The purpose of SUMPRODUCT

The...