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 customer, needs to run in a given landscape and environment of that customer and personal support service is required over time.
A product, on the other hand, is designed based on an educated guess (or market analysis) of what its future customers will value and pay for, must be highly “tight†and robust to run in multiple settings (at times unforeseeable), offer flexibility in configuration and be visually attractive. In short, it needs to SELL.
As a side note, I’ll mention this is not my first Excel product. I developed and sell an Excel-based solution for small businesses management, however in Hebrew only. If you’re real curious, maybe this 3-minute clip will tell you something about it.
What’s in a Date Picker
Besides the obvious functionality of “picking†a date with the mouse instead of typing in 10 keystrokes, date pickers can become quite elaborate in what they offer. Here’s a list of desired features one may consider:
- A “Today†button
- Useful and clear navigation controls
- Setting the first day of the week
- Highlight today
- Smart placement within the application area, next to the date cell
- What would be the trigger to pop-up the Date Picker?
- Cursor behavior upon closing the Date Picker
- Visual appeal and ease of grasping the options at a glance
- Multi-language support
- Support also UserForms or just Worksheets cells
- Right-to-Left and Left-to-Right orientation
- Right-to-Left and Left-to-Right Worksheet/UserForm support
I’d say these are the main things, but you can get pretty crazy beyond that. I even saw some date pickers that offer a button to throw a monthly calendar into the Worksheet.
Technical and Marketing Considerations
While the above list of features directly addresses the user experience, other marketing and technical considerations must also be weighed in, such as:
- What technological platforms will be supported (you can imagine the haystack of possible combinations of computers, operating systems, Excel versions, regional settings, languages, etc.)
- What would the technical implementation approach be, and how will it affect the user’s Excel environment and files? should it be an Excel add-on, a VBA module or maybe developed in Java (to assist also with the multi-platform question).
- How inviting (or intimidating) will the installation process be?
- How can the Date Picker convince the customer that it meets his style, requirements and capabilities to adopt?
- How easy and attractive will the design, “feel†and visual flexibility be for different users?
My Plan and Product Solution
To me, being an avid VBA programmer, passionate about teaching others to program with VBA and deliver Excel VBA projects to customers, it was obvious that my Date Picker would run in both Worksheet cells, and UserForms text-box controls.
As I see an opportunity also to teach others about VBA techniques and programming, I opted to develop the Date Picker in VBA, using a single code Module and a single UserForm. I left the code open for anyone buying the Date Picker to see, change and learn from.
I also priced it very low, so the price is no barrier, and even give away the Date Picker for those opting to learn Excel VBA from me.
If you’ve seen some of my projects, you know the visual design and user experience is very important to me. That’s why I invested a lot of thought about the design of the Date Picker. I opted to include only the important elements most commonly used: Previous Month button, Next Month button, Year selector, Month selector and a “Today†button.
I didn’t add any “Setting†button on the Date Picker with a nice configuration form, for two reasons: a) A VBA developer using the Date Picker in his projects may not want to allow the end user to play around with the settings; and b) changing settings is a rare activity, not worth taking the space and attention on every use of the Date Picker. All settings are configured as text assignments within the Date Picker code Module. I know some will not like this, and maybe I’ll re-consider in the future.
I also invested in crafting 8 colors themes to select from, so the user can easily select the most appealing design and “feel†for his taste and brand. For maximum flexibility, I allow the user to configure the color of every element shown on the Date Picker, in addition to the out-of-the-box 8 themes.
Making sure the Date Picker pops up to always be fully visible on the screen is a must, so I made sure to locate the best possible placement, close to the target date cell (or control), depending on the Worksheet orientation (RTL/LTR) and considering the boundaries of the application window.
For Worksheets, I allow the user to choose if the Date Picker pops up automatically when the cursor lands in a date cell, or should a little button appear next to that cell for the user to click with the mouse when/if the Date Picker is to be used.
I decided not to support multi-languages at this time, just as an economical decision to be out with the first release of the product quickly. For any VBA developer, it should be a breeze to change the visible language to any other language, however the Date Picker is always oriented from left to right (that is, the week and days are laid out from left to right). Built-in support for other languages is another future-to-be feature I’ll consider, if the demand presents itself.
Some Development Challenges and Tricks
Microsoft ActiveX controls are not supported by Apple, so Mac’s had to be left out.
With Windows, I made sure all recent decade version of Excel will handle the Date Picker, that is, Office 2010 and above. I did aim to have earlier versions also comfortable with the Date Picker, therefore I made sure my declarations of Windows services calls are conditioned using pre-compiler directives, for example:
#If VBA7 Then
Public Declare PtrSafe Function GetWindowLongPtr Lib "user32" Alias "GetWindowLongA" ( _
ByVal hWnd As LongPtr, _
ByVal nIndex As Long) As LongPtr
#Else
Public Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" ( _
ByVal hWnd As Long, _
ByVal nIndex As Long) As Long
#End If
To implement the 8 colors themes bank, I used a 2-dimensional public array that is filled with all 15 colors settings for the 8 themes. I fill up that array only once (using a static Boolean flag), while the user-defined colors set is loaded every time the Date Picker is opened, to cater for any change of settings he may have introduced in-between. This works very fast, regardless.
Keep in mind that every time the Date Picker is opened, I need to populate all controls with values and repaint all elements. This is running very fast as well, practically unnoticed on any reasonable machine.
I did have a challenge with the little trigger-button I’m placing next to the target Date cell for the user to click. If the Worksheet is password-protected, I cannot unprotect the Worksheet in order to place and remove that little button. In that case, I revert to an auto-pop-up of the Date Picker as soon as the cursor lands in that cell (an option available in the settings for the user anyway).
Another challenge I confronted, is identifying a valid “Date†cell to offer the Date Picker for.
If the cell already contains a date value, it can be easily detected using the VBA IsDate function. But I wanted also to offer the Date Picker to empty cells formatted as dates, to support entry of initial dates. For that I query the formatting of the target cell, and try to find any hint of “Date†in the formatting string, such as a presence of “d†and/or “yâ€, or an “m†that does not format minutes, but rather months.
Identifying the target cell needed also special attention for cells within smart Tables. Their formatting is derived from the DataBodyRange.NumberFormat property, and not from the cell (Range.DisplayFormat.NumberFormat property). This is a little tricky as the event raised at the Worksheet level indicating a cell was selected is passing the cell Range object, and I need to locate the Table column and row indexes.
As you can see, it’s a whole party, but I love it and the feedbacks are TERRIFIC!
I hope you got some ideas and hints from this read, I’m always here to hear!
This article was first published on my Blog here: From a Developer’s Workshop: Developing the Ultimate Excel Date Picker