From a Developer’s Workshop: Developing the Ultimate Excel Date Picker

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

Continue Reading...

Error Handling in Excel VBA: What, Why and How

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

Continue Reading...

The HUGE performance difference: Worksheet cells vs. Arrays

excel excel-vba vba Jan 15, 2020
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...

Continue Reading...

5 surprising facts you may not know about Excel VBA

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

Creating dynamic dashboards in Excel

Creating a live dashboard is a skill combining data sourcing and staging (arrangement of the data to serve the dashboard elements), queries (typically SQL, or smart Excel functions), visual design, user experience and layout, and business requirements to be fulfilled. My students learn how to create beautiful, business dashboards.

I recently offered an answer on Quora to the question: How can you create a live dashboard with Excel for your clients? After submitting my answer, I thought my Blog readers may benefit from this as well, so here it is :)

Consider the following dashboard:

This is...

Continue Reading...

Microsoft is Storming the Cloud. What’s the Future of VBA? Impressions and Thoughts from a Microsoft Meeting

Last Sunday was our monthly meeting at Microsoft's Offices in Raanana, Israel. We are Israel's Excel Masters - developers and trainers mostly. We meet once a month at Microsoft's office to learn new things and engage in discussions with Microsoft's managers * Below are my key takeaways and predictions from this meeting with Microsoft's Collaboration Customer Success Manager, Maor. Do we know what is the future of Excel VBA?

Cloud Cloud Cloud

Maor, who's leading the Collaboration Customer Success with the Large Enterprises in the local market, presented Microsoft's future (cloud, cloud,...

Continue Reading...

VBA As a Programming Language: Pros and Cons

Should you consider investing in learning Microsoft’s Visual Basic for Applications? It is so ubiquitous as it is supported by all MS-Office installations. However, it was not adopted as a general, all purpose programming language by the professional developers’ community. Who would benefit and what are the pros and cons of learning and becoming a professional VBA developer?

In my last blog post I offered a brief analysis of why VBA was not adopted by the professional developers’ community as an all-purpose business applications technology. You will not find too many...

Continue Reading...

Why isn’t VBA Popular Amongst Software Programmers?

A glimpse at any major open positions board will quickly reveal VBA is not among the popular programming languages out there, when it comes to business applications. This is surprising, considering the fact it is the language used in MS-Access, a database-oriented applications development environment that is part of the MS-Office 365 Business Suite, and the underlying language of the other, most popular MS-Office Suite of applications.

Brief VBA History

Ever since it was introduced in 1993 with Excel 5.0, VBA hesitantly played in between two fields: Microsoft’s Office applications...

Continue Reading...

Can Business Information Systems be Developed on Excel? YES!

Ask yourself: what can I do with the Excel I have on my computer? In most cases, the answer will be: maintain tables, compute with formulas, present data as charts, summarize tables by different variables. The advanced users among us will add: record a sequence of tasks and save as a “Macro” for automatic repetition. * Did you know that Excel offers a complete development environment allows for information systems for almost any purpose to be realized at a significant lower time and cost?

Every entrepreneur, business owner or a corporate manager knows that information systems...

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!