QDE (Quick Date Entry)

Frank Kabel wrote...
...
Now we could argue what would be the better (better in this
case: easier to use for the end-user) approach. I like your idea
but dislike the idea of another hotkey.
...

I'll grant that all design decisions are in part subjective, but in
this case there's the issue about what would cause the least harm.
That'd have to be determined empirically.


I still think separating entry and conversion is the most workable way
to go, and I'm dead certain no individual user ever needs multiple
formats. But if they did, they could completely define how they wanted
ambiguous entries interpretted. Just enter them in another range like
the following.

d
dd
md
mdd
mmd
mdy
mmdd
mddy
mdyy
mmdy
mmddyy
mmddyyyy

You'd probably want to use

d
dd
dm
ddm
dmm
dmy
ddmm
ddmy
dmyy
dmmy
ddmmyy
ddmmyyyy

Name this range something long & complicated like

DateEntryAmbiguityResolution

(or define it with a constant array), and you could use a batch
conversion macro like the following.



Code:
--------------------

Sub csd()
Dim f0 As String, f1 As String
Dim i As Long, j As Long, n As Long
Dim ymd(1 To 3) As String, cm As String, cy As String
Dim c As Range, dear As Variant, v As Variant

If Not TypeOf Selection Is Range Then Exit Sub

dear = Evaluate("DateEntryAmbiguityResolution")
cm = Format(Now, "mm")
cy = Format(Now, "yyyy")

For Each c In Selection.Cells
If Not (c.HasFormula Or VarType(c.Value) = vbDate _
Or c.Text Like "*[!0-9 ]*") Then

f0 = Application.Substitute(c.Text, " ", "")
n = Len(f0)
f1 = ""

For Each v In dear
ymd(1) = ""
ymd(2) = ""
ymd(3) = ""

If Len(v) = n Then
For i = 1 To n
j = InStr(1, "ymd", Mid(v, i, 1))
ymd(j) = ymd(j) & Mid(f0, i, 1)
Next i

If ymd(1) = "" Then ymd(1) = cy

i = CLng(ymd(1))
If i 30 Then ymd(1) = Format(2000 + i, "0000")
If i 1900 Then ymd(1) = Format(1900 + i, "0000")

If ymd(2) = "" Then ymd(2) = cm

f1 = ymd(1) & "-" & ymd(2) & "-" & ymd(3)

If IsNumeric(Evaluate("--""" & f1 & """")) Then Exit For

f1 = ""

End If

Next v

If f1 "" Then
c.NumberFormat = "General"
c.Formula = f1
End If

End If

Next c

End Sub

--------------------



This isn't internationalized, but it could be by replacing the "ymd"
string constant with a variable. That variable would be set by locating
a blank cell, changing it's .NumberFormat property to "ymd" and storing
its .NumberFormatLocal property in this new variable (then restoring
its original format). If I'm right about this, this macro and defined
name combination provides the equivalent functionality of your entire
add-in. Actually, it'd provide more because it could handle single
digit dates, 4-digit dates like 7799, 9977 and 1234, and bypass 5- and
7-digit numbers.

I still don't see why this requires a +500KB add-in. The core
functionality just ain't that complicated.


---
Message posted from http://www.ExcelForum.com/

Hi Harlan!

In line comments. But I'll start as I finish by thanking you for the
effort put in to giving us high quality feedback.
..

I believe you're confusing and conflating the ng posters who ask
about
simple date entry with the link I posted about a month ago to the
article about genetic researchers having certain genetic markers
fried
upon import into Excel because some of those markers look like what
Excel considers partial dates.

YOUR ADD-IN DOES **NOTHING** TO ADDRESS THE LATTER ISSUE.

I was *not* thinking about that problem at all. There was *never* any
intention to address that issue, which came up much later. I don't
think that the introduction or any other material indicates anything
different. [The medical researchers' problem came from Excel
interpreting some of the genetic strings (such as DEC12) as dates when
they "should" have been treated as string text. Solution to that
problem was not one intended for QDE. I think that you and I agreed
that the real problem was in Research 1.01 vbg "Ensure validity of
data before you analyse!" was the "expletives deleted" comment I made
to myself when I looked at the issue and cut out all the crap in a
learned journal article]

The research I refer to is research from base records (such as hand
written medical records, completed forms, historic records or from
questionairres) where large numbers of dates are being inserted in
Excel.

Maybe, a later version might add a facility to interpret 12DEC type
entries as dates but it goes outside the brief of interpreting plain
numerics as dates. I wouldn't be recommending it now but (see later).

For now? Perhaps an addition to Help saying what it does *not* do or
what it does *not* accept or translate. I'll also suggest that we add
that it doesn't read your mind or make coffee. That comes later.

For people who want to *PREVENT* Excel interpretting data tokens as
dates,

Turning off Intellisense. You're talking revolution here! A different
project but a useful one because we all get a little miffed every time
it cuts in and gets it wrong. Microsoft regards it as a "feature" but
then they regard Clippy as a feature too. Maybe that will be
introduced in the next version of Excel. No harm in living in hope
whilst remaining in the real World.

One issues with dates that we keep coming back to is that base problem
of Excel not having a separate data type for dates. Whilst waiting for
Nirvana, it might be possible to develop a QDE type approach to
declare a date entry range that allows entry of a fully (Aaaargh!)
comprehensive range of date forms inclusive of numerics, ordinals, and
existing forms. This would certainly hit the same (and worse)
international issues. I'm under no illusions as to the difficulties
involved as I have done some work on the differences between returns
dependent upon dmy or mdy Regional settings although I haven't
extended it to ymd except for isolating the only unambiguous forms of
entry that are allowed). It wouldn't be easy but the QDE exercise has
revealed a lot of the issues. We've made life difficult for ourselves
by starting off with the principle that we are not just covering the
problems of one Regional setting or even one language.

there is NO ALTERNATIVE (wasn't that one of Margaret Thatcher's
favorite phrases?)

For some time Baroness Thatcher, as she is now called, was often
refered to (even / especially within her own party) as "Tina". Many
called her different names with the most famous being "the Iron lady".
Edward Heath, who she took over the leadership from, would never
mention her by name and always referred to her as, "*That* woman".
Newsgroup ettiquette prohibits reference to other names vbg.

to importing as text and specifying fields that
should be formatted as text. Your add-in doesn't and can't do that.
So
much for researchers.

I disagree! We don't and can't do everything for researchers. You've
taken a very narrow view of what researchers do. From that viewpoint,
you *are* correct. But in the university system I see many cases where
researchers are entering dates from thousands of questionaires. I'll
admit though that many of their problems would not arise if their
research techniques were better. The crucial pilot survey should be
used to address data entry problems.

But we also see cases where the researcher has no control over the
base data; old manually kept records are still in frequent use. UK for
example has one of the best continuous health record systems in
existence because of the structure of the health care system they
introduced in 1948. (Each person had their "own" General Practitioner
who kept a record which passed on to any new GP). 50 odd years of
continuous records to analyse (eg) to establish links between
childhood diseases and... And with a "free" system that set of records
is not corrupted by use or non-use of GPs. Those records were manual
and are a fantastic research resource.

That's just one example. There are many other cases of researchers
using manually recorded base records often from different sources with
different modes of representing dates. There has to be a faster way of
getting those dates into Excel if that is the (rightly or wrongly)
chosen program.

You need to install. You need to master what we hope is a fairly
simple user interface for setting up or accepting defaults. Then
it's a reasonably fast process of activation and range setting
and you are off!
..

And spinning your wheels.

A very good analogy of what happens if you don't ensure that what you
get is what you want before you proceed. That is addressed in Help for
QDE which recommends that step before you get too far.

As I've pointed out in my 'technical' responses, neither event
handler
does anything with partial date entries when users are entering
dates
into multiple cell selected ranges. That's the *most* *likely* usage
scenario for your add-in. So your add-in doesn't convert short date
entries upon entry in that most likely situation. The user would
have
to reselect all date entry cells after entry in order to convert
them.
What's the advantage of doing that vs using a simple macro to
convert
short date entries in batch?

I agree that it might be a useful additional facility to convert a
batch of pre- entered data applying the same principles that are used
for individually entered dates.

There will be a lot of issues here. I can see some users of that type
of facility using the combined power of the add-in and their computer
to corrupt their data at high speed and not realise it. It would
require "health warnings" but some of those issues might be addressed
by interposing a, "This is what you'll get" for a carefully selected
list of dates. Plus perhaps an undo. Perhaps even a programatic back
up of the file. We can try and make it "idiot proof" but making it
"bloody idiot proof" is where we have to call for those genetic
researchers to give us alternatives to the baseball bat or brick.

You've got a problematic concept that can't be implemented real-time
given the way Excel's event handlers actually work. What are the
benefits vs more traditional conversion methods (macros)?

I think that we can amend the Help file for the pain gain tradeoff
plus benefits v. more traditional conversion methods.

. . . We certainly don't have the
time or resources that are needed to test in a multitude of
environments. I think that you are right though in indicating that
a beta indication was warranted pending operation under fire.
..

Not only the beta warning, you also need to state EXPLICITLY in
which
environments you've tested or in which you haven't.


I'll agree that.
---
Message posted from http://www.ExcelForum.com/

Thanks very much for taking the time to give us vigorous feedback. It
really is appreciated. In a face to face situation would at least
warrant a few beers.

Regards

Norman Harker MVP (Excel)
Sydney, Australia

Hi Harlan!


Speaking of Gregorian vs Hijri, I take it QDE won't be much if any
use for much of the Muslim world?

Here you "take it" wrong!

In Indonesia (for certain) and in Pakistan (I believe) the Gregorian
calendar predominates as far as daily use is concerned. That covers
the two countries with the largest Muslim populations (231mil +
147mil). Middle East I can't speak for but I suspect that locally the
Muslim Calendar *is* used. I don't think that there are any special
Excel editions that use other than Gregorian dates.

Outside Muslim countries:

In India the Gregorian calendar prevails for daily use.

In Thailand where Buddhist based calendar is used, again, you'll find
that Gregorian predominates as far as daily use is concerned although
there are a lot of Government documents that use the Buddhist year
number where you simply need to deduct 543 from the year number to get
the Gregorian date.

In Israel, I don't recall having problems with the Gregorian calendar.

In China and Japan the Gregorian calendar prevails.

In terms of size of population, ymd predominates with dmy second and
mdy third.

US subscribes to the ISO but like the rest of us will have to be
dragged kicking and screaming before it will use yyyy-mm-dd as the
approved (separated) date form of ISO8601:2000. It's coming in for the
European Economic Community but for us old dogs...


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

"Norman Harker" wrote...
....
. . . Middle East I can't speak for but I suspect that locally the
Muslim Calendar *is* used. I don't think that there are any special
Excel editions that use other than Gregorian dates.

Perhaps I misinterpretted

http://support.microsoft.com/default...85&Product=xlw

Now XL2K SP-3.

I'll admit I'm torture testing now.

I select B2:C7 as QDE entry range in an activated worksheet. I select that
entire range, type 1 and press [Ctrl]+[Enter]. All cells contain 1. I have
to select individual cells to get them formatted as dates, but this doesn't
trigger invalid date entry. Also, every cell in this range displays
12/31/1899 when it's the ActiveCell, so there's definitely a bug in the
SelectionChange event handler - even in XL2K.

However, single cell entry in multiple cell selections does work.

Harlan Grove wrote:
Now XL2K SP-3.

I'll admit I'm torture testing now.
vbg
yes you're are but that's totally o.k.


I select B2:C7 as QDE entry range in an activated worksheet. I select
that entire range, type 1 and press [Ctrl]+[Enter]. All cells contain
1. I have to select individual cells to get them formatted as dates,
but this doesn't trigger invalid date entry. Also, every cell in this
range displays 12/31/1899 when it's the ActiveCell, so there's
definitely a bug in the SelectionChange event handler - even in XL2K.

I tracked this annoying display error down and this is again Excel's
one-day of error for dates prior to 01-March-1900. The VBA function
Format does interprete '1' as 31-Dec-1899. So VBA is again handling
some values differently than the worksheet function. (e.g. try
?Format(1,"MM/DD/YYYY") in the intermediate window.
Put this on our bug list to create a workaround for this behaviour.

We will also consider multi-cell entries (with CTRL+ENTER) as a
possible enhancement for the next version


However, single cell entry in multiple cell selections does work.
Relieved :-))

Frank

Frank Kabel wrote...
..
Now we could argue what would be the better (better in this
case: easier to use for the end-user) approach. I like your idea
but dislike the idea of another hotkey.
..

I'll grant that all design decisions are in part subjective, but in
this case there's the issue about what would cause the least harm.
That'd have to be determined empirically.

Agreed on that


I still think separating entry and conversion is the most workable
way
to go, and I'm dead certain no individual user ever needs multiple
formats. But if they did, they could completely define how they
wanted
ambiguous entries interpretted. Just enter them in another range like
the following.

I'm with you that an *individual* user has his format and sticks to it.
What we had to deal with is that each individual user may have a
different date format. So of course creating a specific conversion
routine would be far more simpler than our (how did you call it)
'hammer approach' to deal with as many formats as possible.

[...]
very interesting code. We use a similar approach with array constants
to process the entries


This isn't internationalized, but it could be by replacing the "ymd"
string constant with a variable. That variable would be set by
locating a blank cell, changing it's .NumberFormat property to "ymd"
and storing its .NumberFormatLocal property in this new variable
(then restoring its original format). If I'm right about this, this
macro and defined name combination provides the equivalent
functionality of your entire add-in.

This works (at least in my German version) but I still would go for the
registry settings (but this is more a personal taste). 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). 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 ;-)

Actually, it'd provide more
because it could handle single digit dates, 4-digit dates like 7799,
9977 and 1234, and bypass 5- and 7-digit numbers.

4 digit dates are also handled by QDE. You're right about one digit
dates. We just omitted these entries (though easy to add) as design
decision. But I put this on our list for the next release.


I still don't see why this requires a +500KB add-in. The core
functionality just ain't that complicated.
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.

But in my experience this is true for many programs that the core
functionality is relatively small. What one can argue about is if using
a real-time event handler is better than firstentering the short dates
and running a macro afterwards to convert all entries in one step.
We will discuss if we add a call to our processing routine which would
process all selected entries in one step without using event handlers
as second option

Frank

Hi Harlan!

Wow! Thanks for that. I wasn't aware of the Hijri capability although
now you mention it I have seen something on it somewhere.

I've seen the algorithm and basis for the calendar but never got round
to looking at implementation in Excel. Buried in all that code it
looks like it's already there.

It could be very useful for Muslims if we could get a side by side
conversion. Although Gregorian is used for secular purposes in the
majority of countries by population, there is still exclusive use of
Hijri for religious purposes for all Muslims wherever they are. But
don't hold your breath on potential calculation of some of the holy
days because they are complex, depend upon local sightings of the Moon
and are impacted upon by cloud cover. Some Muslims believe that the
calculation is prohibited.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Frank Kabel wrote...
...
I'm with you that an *individual* user has his format and sticks
to it. What we had to deal with is that each individual user may
have a different date format. So of course creating a specific
conversion routine would be far more simpler than our (how did
you call it) 'hammer approach' to deal with as many formats as
possible.

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.

[...]
very interesting code. We use a similar approach with array constants
to process the entries

Point being those array *constants* could be variables instead. It's
possible to have

ddm
dmm
mdd
mmd

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.

This isn't internationalized, but it could be by replacing the
"ymd" string constant with a variable. That variable would be
set by locating a blank cell, changing it's .NumberFormat
property to "ymd" and storing its .NumberFormatLocal
property in this new variable (then restoring its original
format). If I'm right about this, this macro and defined name
combination provides the equivalent functionality of your entire
add-in.

This works (at least in my German version) but I still would go
for the registry settings (but this is more a personal taste).

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.

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.

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.

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?


Actually, it'd provide more because it could handle single digit
dates, 4-digit dates like 7799, 9977 and 1234, and bypass 5-
and 7-digit numbers.

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?

I still don't see why this requires a +500KB add-in. The core
functionality just ain't that complicated.

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.

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 worksheet's class modules and thereby eliminate the "QDE WS
Events.txt" file from your distribution.

2. Create a sectioned text file containing all dialog text with
different language text in each section.

[English]
001=What?

[German]
001=Was?

[French]
001=Quoi?

[Spanish]
001=¿Que?

[Dutch]
001=Wat?

[Italian]
001=Che?

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.

Wouldn't that slim down your add-in?

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


---
Message posted from http://www.ExcelForum.com/

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 worksheet'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 :-(