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

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

What can you do as an Excel developer? Can you make a living out of it? Is it worth anything in the job market? Here’s my take…

What Kind of an Excel VBA Developer are You?

You might be surprised this question is even raised. Intuitively, once you’re an “Excel VBA Developer”, then you’re an “Excel VBA Developer”. Right?! Well, let’s look closer.

Consider Joe. Joe is an accountant in a large corporation. He is an avid Excel user, really stretching the grid and built-in functions. Joe started recording some Macros to automate some tasks....

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

Would you embark an airplane without safety doors, slides and oxygen masks? I won’t * While not a matter of life and death, I won’t deliver an Excel application without proper Error Handling. Here’s why, and a hands-on video on how to do it right

What is error handling?

Ever received an error message popping up from an application you had no idea what it means, what to do and what’s going to happen to your data you just entered? Maybe something like this:

How does it make you feel? Do you know what caused this error? Do you know how to avoid it next time? Do you...

Just a small Excel fix. Just a couple of tables as a temporary solution. Just a little help with some formulas * How small Excel problems turn into a large-scale, business software. Happens to me all the time…

Just a small patch...

Couple of weeks ago, I was approached by a business owner, with this story: he’s offering an online service that matches home loaners with investors. For every mortgage need, he matches many small investors (any person, basically) to fund this loan. Loaners’ monthly receivables are distributed to the loan’s funders, short of the...

Need to update a large range of Worksheet cells using a Macro (VBA)?
Use an Array - don't loop the Worksheet cells!
The bottom line: 52 seconds or less than 1 second.

Arrays are a very important and popular data structures used in computer programming.

Manipulating arrays is extremely fast.

Updating Worksheet cells, one by one, is extremely slow.

Here’s an experiment that shows this in action

I set out to fill 500,000 cells with random numbers.

I did it in two ways:

Looping the cells, updating them one-by-one, took 52 seconds.

Looping an array, "throwing" the filled-up array to the...

Are you an Excel user?
Ever heard of Excel Macros, or VBA?
What comes to mind when you hear that?

Here are 5 surprising facts you may not know about Excel VBA.

1. Excel VBA is a complete software programming language. You can develop professional, top-notch business applications. Many of my customers work on Excel solutions for their mission-critical business needs, every day.
   
   Personal sales dashboard for increasing deals closure and meeting targets
   
   
2. Excel VBA can work with external data sources, including all major Databases in the market, such as MySQL, MS-SQL, Oracle, and others. You no...