Re: QDE (Quick Date Entry)

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 09/03/04 

Date: Fri, 3 Sep 2004 21:30:02 +0200

> Frank Kabel wrote...
[...]
> My metaphorical point was that every *individual* users needs one
> 'tool' - some hammers, others screwdrivers, still others pliers. Your
> add-in is like giving each of them a 20kg toolbox with 100 tools in
> it. Yes, it does have their tool, but they have far more tools
> they'll never use but have to carry around. It's not convenient.

interesting methaphorical point :-) I think convenience is also a very
subjective issue. But I understood your point.

> [...]

> right together in the 'DEAR' range, so 111 would be 11-Jan, 412 would
> be 4-Dec, 520 would be 20-May, and 019 would be 9-Jan. So my approach
> would allow using MDY, DMY and YMD entries all at once, leaving it up
> to the user to specify the order in which the macro should try to
> interpret the dates.

No question about this. Also your approach using Evaluate to check for
a valid date seems to be more robust than IsDate or DateValue.

> Here we seem to be discussing the interpretation of date component
> metacharacters like ymd for English, jmt for German, amj for French,
> гмд (entered in Kyrilic) for Russian, etc.
>
> The question I'd ask is which is faster: querying the Registry or
> playing with the number format of a blank cell (or even the A1 cell,
> since it the initial format is reset, it doesn't matter which cell is
> used). I suspect the latter is quicker. Quick is good.

I would suspect the same but probably no user would recognize the speed
difference as this routine is only invoked once and not several times.
Also if you want to show the results in the default date setting format
your approach won't give you this information. so this seems to be for
me an advantage of querying the registry settings.

>> On the opposite your approach requires a little bit more effort on
>> the user side: he has to create this defined name range
>> somethere (or copy it manually from workbook to workbook).
>
> Now you've discussing storage and retrieval of an array of date
format
> specification strings.
>
> If the macro would available to work with any other workbook, then
> presumably the macro would be in something like PERSONAL.XLS. Would
> there be any reason the 'DEAR' range couldn't also be in that file
and
> the macro access it using ThisWorkbook rather than ActiveWorkbook?
> This isn't an issue.
>
> For that matter, the result of setting .NumberFormat to "ymd" then
> fetching the corresponding .NumberFormatLocal could also be stored in
> a defined name in PERSONAL.XLS.

Sure and if you go one step further you could also create an add-in :-)
Question is then how the user enters the array constants. Of course all
quite simple to implement but the more user friendly this should be the
more addition non-core code you will add. But again this is all a
personal taste of the individual users. Some7most would have no
problems to change your defined name range according to their needs,
etc.

>> No problem for a more experienced user who also has no
>> problem putting your code in a module, etc. We first thought
>> also about only providing the code without UI, etc. In the end
>> we thought that the user should do as little as possible. And
>> perople with your level of experience are probably not the
>> target audience for this add-in
>
> No! Really?!
>
> I agree that setting up an ordered date interpretation range would be
> more complicated than many users could handle. The cost for
> simplifying this for them is the loss of flexibility - only the
> options you provide them are available.

totally agree on this. This is always a decision between flexibility
and 'idiot proof usage'. We hope to have found a compromise between the
two (with the drawback of more code, and loss of flexibility). Your
approach is more flexible but it is not so idiot-proofed.

> This is *EXACTLY* the same issue as number formatting in 123 and in
> Excel. It's MUCH easier in 123 because there are rigid format
> categories with options limited to the number of decimal places, and
> negatives in red or not. Excel number format masks require users to
> learn a form of syntax for a very simple display language. One
> approach is definitely simpler, but the other approach is definitely
> more flexible and more powerful. How long will it take QDE users to
> perceive it's simplicity as a straightjacket?

This definetely depends on the user. I agree with you that some of them
will 'outgrow' QDE and some will not. So we are of course hoping for
user feedback to see where QDE is to restrictive, etc. This tool will
definetely not suit for 100% of all user's requirements. The
ease-to-use has its restrictions

[...]
>> 4 digit dates are also handled by QDE. . . .
> True to an extent, but not in such a way that a date entry range
> containing 1111 and 7799 could be converted, respectively, into
> 11-Nov-2004 (if 2004 were the current year) and 7-July-1999. If QDE
> were set up so that 7799 became 7-July-1999, then 1111 would become
> 1-Jan-2011. Flexibility?

Agreed. We are here more restrictive than your approach. We expect the
user to make a choice about his format and stick to it. And as you I
would expect he sticks to one single format choice.
also leave us some room for version 2.0 where we could add more
interpretation intelligence. e.g. as proposed in your approach an
interpretation order for 4 digit years, etc.

>> We're currently trying to reduce the size (still something around
>> 250K). And you're right. The core functionality is relatively
>> 'simple' and requires not that much code respectively. What
>> adds to the size:
>> - Dialogs
>> - language translations
>> - etc.
>
> Dialogs are problematic.
>
> Language translations raise the toolbox problem again: every user
gets
> to lug around all supported languages even though any individual user
> needs only one language.

Of course we could have build a laguange dependent version for each
supported language. Drawback: more files to maintain and risk of
differences between several language files (VBA does not really support
multi-language development). So we decided to go this way

> If you're going to go the Registry route for persistent storage,
there
> are a number of changes you could make.
>
> 1. XLA add-ins have worksheets, and their worksheets have class
> modules. Put the template Change and SelectionChange code into one of
> your XLA's work***'s class modules and thereby eliminate the "QDE
WS
> Events.txt" file from your distribution.

Already on our to-do list

> 2. Create a sectioned text file containing all dialog text with
> different language text in each section.
>
> [English]
> 001=What?
>
> [German]
> 001=Was?
[...]
>
> Determine the user's language using API calls, and store that
> language's dialig text in the Registry. Then update the .Text or
> Caption properties of your dialogs' controls from the Registry.

This we do already :-)

>
> Wouldn't that slim down your add-in?
I doubt that. You have to initially store the language information in
this addin (or a separate file). I have just checked what removing the
worksheets from the addin (containing the translations) would help and
it is not that much. From 260K down to somethere around 220K. And you
still have to distribute a separate file with all translations. So it
would only reduce the add-in file size.

Some more testing shows that Excel is really not reliable in respect to
use storage capacity. Somethimes only adding one or two lines of code
leads to an increase of >70KB

> [Credit where due: this is a crude adaptation of the standard
> Unix/Linux/BSD gettext approach to localized dialogs.]

Bob and I were also thinking about a separate 'resource file' and
reading this file for language codes (I don't like the idea of storing
it in the registry). But also this is something for release 2.0

Just curious: Do you have a good reference for the above.

Frank

P.S.: As Norman wrote already: I like this discussion with you and it
is definetely helpful for us. Some of your suggestions are already
implemented (1 digit year, size reduction, etc.). I still don't have a
clue though how to solve the Excel 97 issue :-(

Quantcast