QDE (Quick Date Entry)

[hgrove]

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 i
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 wa
to go, and I'm dead certain no individual user ever needs multipl
formats. But if they did, they could completely define how they wante
ambiguous entries interpretted. Just enter them in another range lik
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 batc
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 locatin
a blank cell, changing it's .NumberFormat property to "ymd" and storin
its .NumberFormatLocal property in this new variable (then restorin
its original format). If I'm right about this, this macro and define
name combination provides the equivalent functionality of your entir
add-in. Actually, it'd provide more because it could handle singl
digit dates, 4-digit dates like 7799, 9977 and 1234, and bypass 5- an
7-digit numbers.

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

 

[Norman Harker]

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

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.

..

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)?

..

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.

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
(e-mail address removed)

 

[Norman Harker]

Hi Harlan!

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

 

[Harlan Grove]

. . . 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.aspx?scid=kb;en-us;812385&Product=xlw

 

[Harlan Grove]

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.

 

[Frank Kabel]

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]

Frank Kabel wrote...

..
..

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

 

[Norman Harker]

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.

 

[hgrove]

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 on
'tool' - some hammers, others screwdrivers, still others pliers. You
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 neve
use but have to carry around. It's not convenient.

[...]

very interesting code. We use a similar approach with array >constant

to process the entries

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

ddm
dmm
mdd
mmd

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

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 componen
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 o
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 i
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 forma
specification strings.

If the macro would available to work with any other workbook, the
presumably the macro would be in something like PERSONAL.XLS. Woul
there be any reason the 'DEAR' range couldn't also be in that file an
the macro access it using ThisWorkbook rather than ActiveWorkbook? Thi
isn't an issue.

For that matter, the result of setting .NumberFormat to "ymd" the
fetching the corresponding .NumberFormatLocal could also be stored in
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 b
more complicated than many users could handle. The cost for simplifyin
this for them is the loss of flexibility - only the options you provid
them are available.

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

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?

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

 

[Frank Kabel]

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.

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


[...]

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.

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

 

[hgrove]

Frank Kabel wrote...
...

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.

So far agreed.

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.

...

Unnecessary to know the date format!

Format a cell as General and enter an ISO yyyy-mm-dd date string. Exce
will *ALWAYS* convert such an entry into a date serial number *AND
display it in the system's default date format. Does this not work a
described on your system?

...
Sure and if you go one step further you could also create an
add-in

Big difference: macros in PERSONAL.XLS are shown in the Macro Ru
dialog while macros in add-ins require menu entries, command buttons o
hotkeys.

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.

...

A simple GUI could be used to select 'standard' short date conversio
masks in order from a single-selection list box. A text box could b
used to display the ones chosen so far. Add an edit box to ente
nonstandard masks not in the list box.

Then again, something tells me that the users who'd understan
speficying date conversion mask order wouldn't need a GUI.

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

...

There's always the hybrid approach - add an 'expert configuration
option to the GUI. For my macro, that'd mean adding a GUI to selec
from predefined ordered sets of date conversion masks. Exper
configuration would allow for editing the ordered masks as space or ta
separated strings. Probably should add a GIGO warning to such an exper
mode.

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

That would make the localized add-ins smaller.

This we do already

Yes, but all supported languages are built into the XLA.

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.

It's the add-in's file size that matters since it's loaded into RAM a
a whole. Disk storage was never the issue. You should want to move a
much seldom or never used stuff from the XLA into other disk files
They'd be there when needed, but won't soak up RAM.

One HUGE advantage of using a separate text file to store dialog text
(assuming you all really *want* others to find QDE useful) would be
that users could add any unsupported language to that text file
themselves. Or do you all want to retain as rigid control over the
dialog text as the VBA code?

As for vetting text received from users, would any of you be able to
say whether a particular Tagalog phrase meant 'current year' or 'up
your nose with a rubber hose'?

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

This is a sign of VBA memory leaks. There are several KB articles about
this. The standard fix is get the add-in working, then export all code
and dialog modules, copy any worksheet stuff into a new workbook, and
import the saved modules into that new workbook. Then save that new
workbook as the new version of the add-in.

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

For gettext internationalization? It's FSF/GNU software.

http://www.gnu.org/software/gettext/manual/html_chapter/gettext_10.html

Also search the GNU site for libintl and i18n.

 

[Frank Kabel]

[...]

..

Unnecessary to know the date format!

Format a cell as General and enter an ISO yyyy-mm-dd date string.
Excel will *ALWAYS* convert such an entry into a date serial number
*AND* display it in the system's default date format. Does this not
work as described on your system?

No, not in my German 2003 version. I'll see the date as 'YYYY-MM-DD'
and this is NOT my default windows setting. Seems my German version
recognizes this YYYY-MM-DD date as a valid date and adapts the date
format (strange). I also would have expected you described behaviour
(and this has nothing to do with 2003 as my english 2003 works the way
you described it)

[...]

A simple GUI could be used to select 'standard' short date conversion
masks in order from a single-selection list box. A text box could be
used to display the ones chosen so far. Add an edit box to enter
nonstandard masks not in the list box.

Then again, something tells me that the users who'd understand
speficying date conversion mask order wouldn't need a GUI.

Yes, agreed on that. This kind of user would be able to directly enter
the conversion in a range and specify the defined name. But I would
suspect that this kind of user is rare
At least looking at most of the excel users I know from my clients even
defining names could be too much )

..

There's always the hybrid approach - add an 'expert configuration'
option to the GUI. For my macro, that'd mean adding a GUI to select
from predefined ordered sets of date conversion masks. Expert
configuration would allow for editing the ordered masks as space or
tab separated strings. Probably should add a GIGO warning to such an
expert mode.

Yeah also agree on that. As mentioned in a previous post we may
consider allowing direct conversion after entry + maybe some more
flexibility.
Just curious: GIGO?

Of course we could have build a laguange dependent version

That would make the localized add-ins smaller.

agreed but not really that much (at least in my tests). But one could
argue even saving 20KB are a good thing

[...]

It's the add-in's file size that matters since it's loaded into RAM as
a whole. Disk storage was never the issue. You should want to move as
much seldom or never used stuff from the XLA into other disk files.
They'd be there when needed, but won't soak up RAM.

One HUGE advantage of using a separate text file to store dialog text
(assuming you all really *want* others to find QDE useful) would be
that users could add any unsupported language to that text file
themselves. Or do you all want to retain as rigid control over the
dialog text as the VBA code?

As for vetting text received from users, would any of you be able to
say whether a particular Tagalog phrase meant 'current year' or 'up
your nose with a rubber hose'?

We're looking for volunteers for translations. They would simply get
the Excel sheet (with the english names) and have to add thei local
equivalents (as you're right we couldn't verify it).
I agree with you on the add-in size. So a thing for next release moving
out the dialog text to something else (text file, DLL, etc.)

[...]

For gettext internationalization? It's FSF/GNU software.

http://www.gnu.org/software/gettext/manual/html_chapter/gettext_10.html
Also search the GNU site for libintl and i18n.

Thanks for the link

Frank

 

[Harlan Grove]

No, not in my German 2003 version. I'll see the date as 'YYYY-MM-DD'
and this is NOT my default windows setting. Seems my German version
recognizes this YYYY-MM-DD date as a valid date and adapts the date
format (strange). . . .

So cheat. Pick any cell, save it's initial formula and number format. Set
its (English) .NumberFormat property to "General" and its (English) .Formula
property to "=TODAY()". Record its new .NumberFormatLocal property, and
restore it's original formula and number format. Then again, maybe all you'd
need to do is enter the formula "=TODAY()" before entering the date value.

c.NumberFormat = "General"
c.Formula = "=TODAY()"
c.Value = Evaluate("=--""" & f1 & """")

Or maybe all you need to do is enter the date value as a Date type. Since
you'd already have established that f1 resolved to a valid ISO date,

c.NumberFormat = "General"
c.Value = CDate(Evaluate("=--""" & f1 & """"))

You need to exercise your hacking abilities.

Just curious: GIGO?

You need to learn to search for jargon on the web.

http://catb.org/~esr/jargon/html/G/GIGO.html

 

[Frank Kabel]

... [...]
c.NumberFormat = "General"
c.Value = CDate(Evaluate("=--""" & f1 & """"))

You need to exercise your hacking abilities.

Hi Harlan
no question that it is possible to cheat this way but then you can
argue why not simply read the windows settings. Performance is not an
issue here as both methods are fast enough. Why 'hack' then there's
another way to access the information directly. As often there're
several ways to achieve a goal and some are just a matter of personal
taste. I prefer reading the information directly from the registry as I
don't want to rely on Excel's sometime starnge behaviour.

FWIW: The hacks you described work. And it is sufficient to just enter
TODAY() prior to entering the date value (so easy to do)

You need to learn to search for jargon on the web.

was just too lazy. Thought it was easier to ask you then to start-up
Google

Frank

 

[Harlan Grove]

no question that it is possible to cheat this way but then you can
argue why not simply read the windows settings. Performance is not an
issue here as both methods are fast enough. Why 'hack' then there's
another way to access the information directly. As often there're
several ways to achieve a goal and some are just a matter of personal
taste. I prefer reading the information directly from the registry as I
don't want to rely on Excel's sometime starnge behaviour.

Because the hacking approach works on Macs, presumably, while reading the
*Windows* Registry certainly wouldn't. Also, for Excel 2003, isn't it
possible to specify different formats for Excel than the system's Control
Panel default number and date formats? If so, are you pulling the Windows
format or the Excel format?

That reminds me - QDE won't be useful/usable on Macs because you all made a
conscious decision to build in Windows-specific functionality, right?

FWIW: The hacks you described work. And it is sufficient to just enter
TODAY() prior to entering the date value (so easy to do)

Your 'sufficient' comment isn't correct for older Excel versions. Format a
cell as #,##0.00 and enter =TODAY(). Is the result formatted as a date?

 

[Frank Kabel]

Because the hacking approach works on Macs, presumably, while reading
the *Windows* Registry certainly wouldn't. Also, for Excel 2003,
isn't it possible to specify different formats for Excel than the
system's Control Panel default number and date formats? If so, are
you pulling the Windows format or the Excel format?

That reminds me - QDE won't be useful/usable on Macs because you all
made a conscious decision to build in Windows-specific functionality,
right?

Good point and you're right we made this decision. Currently the usage
is restricted to Windows user. Will add a comment about this
restriction in the web page and put this on the todo list for a later
version. This is of course also true for using the Windows registry for
storing some persistent information.
FWIW We're pulling the Windows settings. The only thing you can
specify in Excel 2003 is a different list separator but not a different
date setting.

Your 'sufficient' comment isn't correct for older Excel versions.
Format a cell as #,##0.00 and enter =TODAY(). Is the result formatted
as a date?

No of course not. Just tested it with a 'General' formated cell to
check if this is sufficient to overwrite the YYYY-MM-DD setting in my
German version. So of course using your approach you first have to
format the cell as 'General' but this was not my 'sufficient' point
(sorry for not beeing precise enough)

Frank

 

[Harlan Grove]

. . . This is of course also true for using the Windows registry for
storing some persistent information.

Good point. However, you could make the add-in generic by checking the
Application object's OperatingSystem property and using the result in If
blocks to make Windows API or their Mac equivalents. Another good reason for
open-sourcing: interested Mac users could do this for themselves and you.

However, this is all irrelevant. I may not have been explicit enough before.
I'll correct that. You don't need to know the system default date format at
all. Even though it appears you can't enter ISO yyyy-mm-dd date strings into
cells formatted General in all language versions and get date values in the
system's default date format, you could set such cells' .Value properties to
Date-type values and get dates in the system's default date format. So
instead of

c.Formula = f1 'where f1 is an ISO yyyy-mm-dd date string

use

c.Value = CDate(f1)

instead. There's no need to make Windows API calls to determine the system's
default date format. If there's no need, why do it?

 

[Frank Kabel]

Harlan Grove wrote:
[...]

However, this is all irrelevant. I may not have been explicit enough
before. I'll correct that. You don't need to know the system default
date format at all. Even though it appears you can't enter ISO
yyyy-mm-dd date strings into cells formatted General in all language
versions and get date values in the system's default date format, you
could set such cells' .Value properties to Date-type values and get
dates in the system's default date format. So instead of

c.Formula = f1 'where f1 is an ISO yyyy-mm-dd date string

use

c.Value = CDate(f1)

instead. There's no need to make Windows API calls to determine the
system's default date format. If there's no need, why do it?

Point taken
I agree with you that this would be a robust way to make the add-in OS
independent. So this is on the todo for version 2.0 (but first we'll do
the bug fixing and incorporating smaller requests) as this would be a
major rework in some parts (esp. storing the registry information at
some other place - e.g. a worksheet within the addin)

Frank