QDE (Quick Date Entry)

Hi Community of Excel Users!

This is a joint posting from 4 of the "regulars" to these newsgroups:
Bob Phillips, Frank Kabel, Ron de Bruin, and Norman Harker.

QDE.xla Version 1 is now available *free* from:

Bob Phillips
http://www.xldynamic.com/source/xld.QDEDownload.html
and
Ron de Bruin
http://www.rondebruin.nl/qde.htm

A long outstanding feature request in these newsgroups has been for a
robust method by which dates can be entered as numbers only. By
"robust" we mean a method that will stand up to the fullest possible
range of Operating System settings.

Although this is Version 1, it has been tested as much as possible
within the constraints we faced. However, we believe that it is now
ready for release and we hope that the community of Excel users will
appreciate what we have tried to achieve and will have no problems
with using it.

It comes with the usual full money back if not delighted guarantee;
"You pay nothing for it and you'll get nothing back!"

Edited thrilling highlights of what QDE is all about are provided
below our sign-off with further details in the Help File and at the
link above.

Regards


Norman Harker, Sydney, Australia
Bob Phillips, Poole, England
Frank Kabel, Frankfurt, Germany
Ron de Bruin, Netherlands


***** Edited Highlights of QDE

*** Background:
This is a joint effort of 4 of the "regulars" Bob Phillips, Frank
Kabel, Ron de Bruin and Norman Harker.

*** Why QDE?
Entry of dates into Excel is not too difficult to master for any given
set of Operating System Regional Settings although users have to
appreciate the inbuilt assumptions that Excel makes regarding various
date entry forms.

However, where large numbers of dates need to be entered, Excel's date
serial number approach imposes obligations to use date separators (eg.
03-05-2004).

Where many dates are required to be entered, this imposes a
requirement for more key presses than might be achieved. For example,
we might prefer just to enter
0305 and have this interpreted "correctly" as 03-05-04. If we can do
this, we save 4 key presses. Big deal? Not really! Unless, that is,
you have many hundreds or even thousands of such dates to enter.

This is why we need QDE. We need a means by which we can economize on
the number of keystrokes required to enter dates.

*** What Is QDE?
QDE is an Excel Addin that provides quick input of dates, in all
international formats. With QDE you now have the option to enter dates
using numbers only.
These are interpreted by QDE as dates based upon settings which you
will rarely, if ever, need to change.

QDE is a stand-alone utility, that once installed is available to all
of your workbooks and worksheets. It does not change anything on the
worksheet until directed to by you the user.
Once dates are entered they are treated by Excel as dates entered
using non-QDE methods. Workbooks constructed with the assistance of
QDE do not require that QDE should remain installed or even be present
on whatever computer the workbook is open on.

*** Where Did It Come From?
Date entry has been a perennial problem in Excel. Many people want to
input the date components (day. month, year) without having to bother
with the date delimiters. The problem here is that Excel then
interprets this as a normal number, not a date. Chip Pearson made the
first serious attempt at a solution that we know of, which you can see
at Chip's site on his Date and Time Entry page, Whilst it does show
some of the issues that need to be addressed, it is US date centric,
and there are a couple of problems with the input.
Norman Harker decided that a full international version of this
routine was needed. Norman is an Excel MVP based in Sydney, Australia
and is a regular contributor to the Excel newsgroups. He has a special
interest in Excel's date handling capabilities. Norman posted a
tentative solution which was hitting problems.

There were many follow up responses to this query, mainly from Frank
Kabel, based in Frankfurt, Germany and Bob Phillips, based in Poole,
England. Both are regular contributors to the Excel newsgroups. The
discussions moved the problem on somewhat, but as usual, further
issues were revealed as the "regulars"
educated themselves on the complexities involved.

At this point, Norman, Frank and Bob decided informally that the
newsgroups were not the best forum for development work and informally
agreed to set up a team to tackle the problem. Ron de Bruin, another
Excel MVP, was recruited to provide the extra skills and experience
needed, and QDE was conceived.
There were many problems along the way to what we have here. These
were mainly those of ensuring that all international issues, operating
system settings and Excel versions were covered. This is the result of
that development, and we hope that you enjoy it and find it useful.

If you are interested, you can see the original newsgroup discussion
that gave rise to QDE at this Google thread

http://tinyurl.com/3luwe

*** How It Works
This is a classic case of, "Easier said than done!"

Excel has limited date interpretation capabilities that are inherent
in Excel not using a dedicated data type (as is common for database
programs).
Excel uses the date serial number concept whereby dates are nothing
more than a format of a number with 1 representing a base date.
However, users, want to enter dates quickly with as few numbers and
key presses as is possible, and to have Excel interpret those numbers
as the dates that the user intends.

The trouble is that there are three interacting issues:

1. There are two date serial number bases in Excel; the 1900 Date
System where 1 represents 1-Jan-1900 and the 1904 Date System where 1
represents 1-Jan-1904.
The 1900 Date System is the default setting for Windows and the 1904
Date System is the default setting for Mac. But Excel for Windows also
allows you to change the default setting to the 1904 Date System.

2. Ordering the three parameters of day month and year varies
according to the user's country custom. In the US (predominantly)
entry is Month Day Year. In some Asian countries and in countries
that have adopted the International Standard ISO8601:2000 the order is
Year Month Day. The rest of the world (when using the Gregorian
Calendar), including most of Europe, UK, South America and former
British Colonies all use Day Month Year.

3. The quick date entry of a user might be from 3 to 8 digits long
depending upon whether or not it was a single or double digit day or
month number and whether or not they were entering a double digit or
four digit year (or, indeed, wanted the current year to be assumed).
QDE handles quick date entry interpretation and reflects these three
interacting issues. In most situations those issues are handled only
once for a given range or worksheet and most often the user will not
even have to change the parameters that QDE takes from current Excel
and Operating System settings.

End of Post.

very many thanks. I shall download the addin. meanwhile my gratitude to
you guys for making our lives easier. thanks again;.




Norman Harker wrote in message
...
Hi Community of Excel Users!

This is a joint posting from 4 of the "regulars" to these newsgroups:
Bob Phillips, Frank Kabel, Ron de Bruin, and Norman Harker.

QDE.xla Version 1 is now available *free* from:

Bob Phillips
http://www.xldynamic.com/source/xld.QDEDownload.html
and
Ron de Bruin
http://www.rondebruin.nl/qde.htm

A long outstanding feature request in these newsgroups has been for a
robust method by which dates can be entered as numbers only. By
"robust" we mean a method that will stand up to the fullest possible
range of Operating System settings.

Although this is Version 1, it has been tested as much as possible
within the constraints we faced. However, we believe that it is now
ready for release and we hope that the community of Excel users will
appreciate what we have tried to achieve and will have no problems
with using it.

It comes with the usual full money back if not delighted guarantee;
"You pay nothing for it and you'll get nothing back!"

Edited thrilling highlights of what QDE is all about are provided
below our sign-off with further details in the Help File and at the
link above.

Regards


Norman Harker, Sydney, Australia
Bob Phillips, Poole, England
Frank Kabel, Frankfurt, Germany
Ron de Bruin, Netherlands


***** Edited Highlights of QDE

*** Background:
This is a joint effort of 4 of the "regulars" Bob Phillips, Frank
Kabel, Ron de Bruin and Norman Harker.

*** Why QDE?
Entry of dates into Excel is not too difficult to master for any given
set of Operating System Regional Settings although users have to
appreciate the inbuilt assumptions that Excel makes regarding various
date entry forms.

However, where large numbers of dates need to be entered, Excel's date
serial number approach imposes obligations to use date separators (eg.
03-05-2004).

Where many dates are required to be entered, this imposes a
requirement for more key presses than might be achieved. For example,
we might prefer just to enter
0305 and have this interpreted "correctly" as 03-05-04. If we can do
this, we save 4 key presses. Big deal? Not really! Unless, that is,
you have many hundreds or even thousands of such dates to enter.

This is why we need QDE. We need a means by which we can economize on
the number of keystrokes required to enter dates.

*** What Is QDE?
QDE is an Excel Addin that provides quick input of dates, in all
international formats. With QDE you now have the option to enter dates
using numbers only.
These are interpreted by QDE as dates based upon settings which you
will rarely, if ever, need to change.

QDE is a stand-alone utility, that once installed is available to all
of your workbooks and worksheets. It does not change anything on the
worksheet until directed to by you the user.
Once dates are entered they are treated by Excel as dates entered
using non-QDE methods. Workbooks constructed with the assistance of
QDE do not require that QDE should remain installed or even be present
on whatever computer the workbook is open on.

*** Where Did It Come From?
Date entry has been a perennial problem in Excel. Many people want to
input the date components (day. month, year) without having to bother
with the date delimiters. The problem here is that Excel then
interprets this as a normal number, not a date. Chip Pearson made the
first serious attempt at a solution that we know of, which you can see
at Chip's site on his Date and Time Entry page, Whilst it does show
some of the issues that need to be addressed, it is US date centric,
and there are a couple of problems with the input.
Norman Harker decided that a full international version of this
routine was needed. Norman is an Excel MVP based in Sydney, Australia
and is a regular contributor to the Excel newsgroups. He has a special
interest in Excel's date handling capabilities. Norman posted a
tentative solution which was hitting problems.

There were many follow up responses to this query, mainly from Frank
Kabel, based in Frankfurt, Germany and Bob Phillips, based in Poole,
England. Both are regular contributors to the Excel newsgroups. The
discussions moved the problem on somewhat, but as usual, further
issues were revealed as the "regulars"
educated themselves on the complexities involved.

At this point, Norman, Frank and Bob decided informally that the
newsgroups were not the best forum for development work and informally
agreed to set up a team to tackle the problem. Ron de Bruin, another
Excel MVP, was recruited to provide the extra skills and experience
needed, and QDE was conceived.
There were many problems along the way to what we have here. These
were mainly those of ensuring that all international issues, operating
system settings and Excel versions were covered. This is the result of
that development, and we hope that you enjoy it and find it useful.

If you are interested, you can see the original newsgroup discussion
that gave rise to QDE at this Google thread

http://tinyurl.com/3luwe

*** How It Works
This is a classic case of, "Easier said than done!"

Excel has limited date interpretation capabilities that are inherent
in Excel not using a dedicated data type (as is common for database
programs).
Excel uses the date serial number concept whereby dates are nothing
more than a format of a number with 1 representing a base date.
However, users, want to enter dates quickly with as few numbers and
key presses as is possible, and to have Excel interpret those numbers
as the dates that the user intends.

The trouble is that there are three interacting issues:

1. There are two date serial number bases in Excel; the 1900 Date
System where 1 represents 1-Jan-1900 and the 1904 Date System where 1
represents 1-Jan-1904.
The 1900 Date System is the default setting for Windows and the 1904
Date System is the default setting for Mac. But Excel for Windows also
allows you to change the default setting to the 1904 Date System.

2. Ordering the three parameters of day month and year varies
according to the user's country custom. In the US (predominantly)
entry is Month Day Year. In some Asian countries and in countries
that have adopted the International Standard ISO8601:2000 the order is
Year Month Day. The rest of the world (when using the Gregorian
Calendar), including most of Europe, UK, South America and former
British Colonies all use Day Month Year.

3. The quick date entry of a user might be from 3 to 8 digits long
depending upon whether or not it was a single or double digit day or
month number and whether or not they were entering a double digit or
four digit year (or, indeed, wanted the current year to be assumed).
QDE handles quick date entry interpretation and reflects these three
interacting issues. In most situations those issues are handled only
once for a given range or worksheet and most often the user will not
even have to change the parameters that QDE takes from current Excel
and Operating System settings.

End of Post.

Having read the help file, qde.chm, it says your add-in adds code to
worksheet class modules apparently by replacing Change and
SelectionChange event handlers. Uh, guys, you need to refresh your
understanding of systems programming. Since your add-in is manipulating
VBE objects, you *could* add *additional* procedures to worksheet class
modules and *add* a statement to existing event handlers calling the
added procedures.

That said, it's buggy (or the documentation is inadequate).

Following your installation and usage instructions, I opened a new
workbook, and activated QDE in Sheet1 of that workbook, set Priority to
Day Priority checked Current Year, and specified A1:A10 as the date
entry range. With A1:A10 selected, I entered various numbers in these
cells. As I entered them, nothing changed (possibly because the
Selection didn't change though the ActiveCell moved through the
selected range). Once I finished entering them and moved though the
entry range with cursor keys, the entries changed to dates when each
cell was activated.

Here are my entries and the resulting dates (standard US).

Entry___Not Activated____Activated

__1____01/01/1900____12/31/1899
_10____01/10/1900____01/09/1900
_11____01/11/1900____01/10/1900
100____04/09/1900____04/09/1900
101____04/10/1900____04/10/1900
110____04/19/1900____04/19/1900
111____04/20/1900____04/20/1900
122____05/01/1900____05/01/1900
131____05/10/1900____05/10/1900
222____08/09/1900____08/09/1900

Doesn't match up with claimed functionality. Also, the fact that the
dates in A1:A3 display as one day before when those cells are the
active cell would be an indication there's an off-by-one bug somewhere
in the code.

I'm running XL97 SR-2 under Windows NT4 SP-6. 1900 date system.
mm/dd/yyyy default date format.

How many besides you 4 have *tested* this? If no others, then you've
got *BETA* stage software at best. You should include standard
beta-level warnings somewhere in your package.

Personally I don't find this sort of thing useful mostly because I
don't enter many dates. The vast bulk of what I work with comes from
network and online sources. For anyone with Access, they'd be better
off entering lots of dates in Access using date input masks. There may
even be freeware or cheap shareware lightweight databases with date
input mask features. What I'm getting at is that there may be little
need for a product such as this. After all, if the date entry cells
were formatted as Text, then it really is painless to convert entries
to dates with formulas only.

1 - day of month in current month and year
11 - ambiguous, either md, dm or dd in current month and year
111 - ambiguous, either mmd, mdd, dmm or ddm
1111 - ambiguous, either mmdd or ddmm (dmmy etc possible*)

anything longer would include years.

One point y'all may not have considered is that Excel most users would
enter dates in one and only one format. For the most part individual
users would never change their settings. Only resolution of ambiguous 2
and 3 digit partial dates would possibly differ between uses, and those
could also be handled purely by formula.

1 - 1-Sep-2004
11 - 11-Sep-2004
111 - 11-Jan-2004
1111 - 11-Nov-2004
=--IF(LEN(x)=2,MONTH(NOW())&"-"&x,LEFT(x,
ROUNDDOWN(LEN(x)/2,0))&"-"&RIGHT(x,ROUNDUP(LEN(x)/2,0)))

1 - 1-Sep-2004
11 - 1-Jan-2004
111 - 1-Nov-2004
1111 - 11-Nov-2004
=--IF(LEN(x)=1,MONTH(TODAY())&"-"&x,LEFT(x,
ROUNDUP(LEN(x)/2,0))&"-"&RIGHT(x,ROUNDDOWN(LEN(x)/2,0)))

5 through 8 digit entries would also best be handled by inserting
dashes between groups of 1 or 2 numerals then using -- on the resulting
string to convert to date numbers.

While I appreciate you all spent some time & effort on this, it just
doesn't seem to merit a +500KB add-in that can't coexist with
preexisting Change and SheetChange event handlers.


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

Hi Harlan

Why it is always so long your reply'sg
Remember I must translate it all

VBE objects, you *could* add *additional* procedures to worksheet class
modules and *add* a statement to existing event handlers calling the
added procedures.

You are right, but we start with this and are open for all comments.


Following your installation and usage instructions, I opened a new
workbook, and activated QDE in Sheet1 of that workbook, set Priority to
Day Priority checked Current Year, and specified A1:A10 as the date
entry range. With A1:A10 selected, I entered various numbers in these
cells. As I entered them, nothing changed (possibly because the
Selection didn't change though the ActiveCell moved through the
selected range). Once I finished entering them and moved though the
entry range with cursor keys, the entries changed to dates when each
cell was activated.

Good Catch in 97
I never notice this when testing it in 97 on a Virtual PC
in 2000-2003 this is working correct

If you enter 1 for example in a cell it will not accept it in all the other Excel versions(we have a bug here)


What I'm getting at is that there may be little
need for a product such as this

No problem, we do this for fun Harlan


I am sure that Norman will respond also with good English.
Thanks for your reply


--
Regards Ron de Bruin
http://www.rondebruin.nl


"hgrove " wrote in message ...
Having read the help file, qde.chm, it says your add-in adds code to
worksheet class modules apparently by replacing Change and
SelectionChange event handlers. Uh, guys, you need to refresh your
understanding of systems programming. Since your add-in is manipulating
VBE objects, you *could* add *additional* procedures to worksheet class
modules and *add* a statement to existing event handlers calling the
added procedures.

That said, it's buggy (or the documentation is inadequate).

Following your installation and usage instructions, I opened a new
workbook, and activated QDE in Sheet1 of that workbook, set Priority to
Day Priority checked Current Year, and specified A1:A10 as the date
entry range. With A1:A10 selected, I entered various numbers in these
cells. As I entered them, nothing changed (possibly because the
Selection didn't change though the ActiveCell moved through the
selected range). Once I finished entering them and moved though the
entry range with cursor keys, the entries changed to dates when each
cell was activated.

Here are my entries and the resulting dates (standard US).

Entry___Not Activated____Activated

__1____01/01/1900____12/31/1899
_10____01/10/1900____01/09/1900
_11____01/11/1900____01/10/1900
100____04/09/1900____04/09/1900
101____04/10/1900____04/10/1900
110____04/19/1900____04/19/1900
111____04/20/1900____04/20/1900
122____05/01/1900____05/01/1900
131____05/10/1900____05/10/1900
222____08/09/1900____08/09/1900

Doesn't match up with claimed functionality. Also, the fact that the
dates in A1:A3 display as one day before when those cells are the
active cell would be an indication there's an off-by-one bug somewhere
in the code.

I'm running XL97 SR-2 under Windows NT4 SP-6. 1900 date system.
mm/dd/yyyy default date format.

How many besides you 4 have *tested* this? If no others, then you've
got *BETA* stage software at best. You should include standard
beta-level warnings somewhere in your package.

Personally I don't find this sort of thing useful mostly because I
don't enter many dates. The vast bulk of what I work with comes from
network and online sources. For anyone with Access, they'd be better
off entering lots of dates in Access using date input masks. There may
even be freeware or cheap shareware lightweight databases with date
input mask features. What I'm getting at is that there may be little
need for a product such as this. After all, if the date entry cells
were formatted as Text, then it really is painless to convert entries
to dates with formulas only.

1 - day of month in current month and year
11 - ambiguous, either md, dm or dd in current month and year
111 - ambiguous, either mmd, mdd, dmm or ddm
1111 - ambiguous, either mmdd or ddmm (dmmy etc possible*)

anything longer would include years.

One point y'all may not have considered is that Excel most users would
enter dates in one and only one format. For the most part individual
users would never change their settings. Only resolution of ambiguous 2
and 3 digit partial dates would possibly differ between uses, and those
could also be handled purely by formula.

1 - 1-Sep-2004
11 - 11-Sep-2004
111 - 11-Jan-2004
1111 - 11-Nov-2004
=--IF(LEN(x)=2,MONTH(NOW())&"-"&x,LEFT(x,
ROUNDDOWN(LEN(x)/2,0))&"-"&RIGHT(x,ROUNDUP(LEN(x)/2,0)))

1 - 1-Sep-2004
11 - 1-Jan-2004
111 - 1-Nov-2004
1111 - 11-Nov-2004
=--IF(LEN(x)=1,MONTH(TODAY())&"-"&x,LEFT(x,
ROUNDUP(LEN(x)/2,0))&"-"&RIGHT(x,ROUNDDOWN(LEN(x)/2,0)))

5 through 8 digit entries would also best be handled by inserting
dashes between groups of 1 or 2 numerals then using -- on the resulting
string to convert to date numbers.

While I appreciate you all spent some time & effort on this, it just
doesn't seem to merit a +500KB add-in that can't coexist with
preexisting Change and SheetChange event handlers.


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

Hi Harlan
just in addition to Ron

"hgrove " schrieb im
Newsbeitrag ...
Having read the help file, qde.chm, it says your add-in adds code to
worksheet class modules apparently by replacing Change and
SelectionChange event handlers. Uh, guys, you need to refresh your
understanding of systems programming. Since your add-in is
manipulating
VBE objects, you *could* add *additional* procedures to worksheet
class
modules and *add* a statement to existing event handlers calling the
added procedures.
That said, it's buggy (or the documentation is inadequate).

First thanks for reading + testing it (honestly!). As Ron said this was
our go-in position. As The event code is a little bit more complex it
would be kind of difficult only to 'add' the code to existing event
handlers and removing the code would be even more complicated. So yes
we chose the easier way. I would totally agree with you if it were just
a simple statement but it is a little bit more code involved.
BUT we should (and will) add this in the documentation to clarify this
possible issue!


Following your installation and usage instructions, I opened a new
workbook, and activated QDE in Sheet1 of that workbook, set Priority
to
Day Priority checked Current Year, and specified A1:A10 as the date
entry range. With A1:A10 selected, I entered various numbers in these
cells. As I entered them, nothing changed (possibly because the
Selection didn't change though the ActiveCell moved through the
selected range). Once I finished entering them and moved though the
entry range with cursor keys, the entries changed to dates when each
cell was activated.

Excel 97 is really different. Though tested with various versions and
different languages we didn't catch thus bug. Thanks for spotting it.
Have you tried entering the dates then only a single cell is selected
(hopefully this works for you?)

[....]

Doesn't match up with claimed functionality. Also, the fact that the
dates in A1:A3 display as one day before when those cells are the
active cell would be an indication there's an off-by-one bug
somewhere
in the code.

See above for the bug reason

[...]
How many besides you 4 have *tested* this? If no others, then you've
got *BETA* stage software at best. You should include standard
beta-level warnings somewhere in your package.

Some more but also as stated in Norman's email we 'hoped' that no bug
is there anymore. Maybe next time we should invite you to out beta test
vbg


Personally I don't find this sort of thing useful mostly because I
don't enter many dates. The vast bulk of what I work with comes from
network and online sources. For anyone with Access, they'd be better
off entering lots of dates in Access using date input masks. There
may
even be freeware or cheap shareware lightweight databases with date
input mask features. What I'm getting at is that there may be little
need for a product such as this. After all, if the date entry cells
were formatted as Text, then it really is painless to convert entries
to dates with formulas only.

Personally I don't need this either but many questions in this NG
suggested this requirement. Personally I prefer using a database for
mass data but Excel is often used differently and it seems to me that
many users use it for this kind of data processing. In addition to Ron:
- pure fun for us (therefore totally free addin)
- also a learning excercise (international date settings, manipulationg
event handlers, mutli-language support, etc.)
- So for me if at least one user uses it my personal goal is achieved
:-)))


1 - day of month in current month and year
11 - ambiguous, either md, dm or dd in current month and year
111 - ambiguous, either mmd, mdd, dmm or ddm
1111 - ambiguous, either mmdd or ddmm (dmmy etc possible*)
or even dmyy


One point y'all may not have considered is that Excel most users
would
enter dates in one and only one format. For the most part individual
users would never change their settings. Only resolution of ambiguous
2
and 3 digit partial dates would possibly differ between uses, and
those
could also be handled purely by formula.
Totally agree with you that this could handled also by formulas BUT
this would require a helper column. This is saved by the addin

[...]
While I appreciate you all spent some time & effort on this, it just
doesn't seem to merit a +500KB add-in that can't coexist with
preexisting Change and SheetChange event handlers.

As we won't want to earn money with it we at least had our fun
developing it. And thanks again for your valid comments + suggestions +
bug reports.

Regards
Frank

Thanks Harlan!

Just the sort of comments we need and will take on board.

Frank and Ron have covered the technical issues you raised. I'll stick
to utility.

Same as you, I don't often need this type of tool as I don't often
have to enter that many dates.

But there are researchers out there plus other mass date entry users
who will benefit significantly from QDE *if* we've got it right.

There is, without a shadow of doubt, a pain before you gain! And you
will not gain enough to warrant the pain unless you have a significant
major date entry requirement.

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!

Pretty extensive, but nonetheless imperfect, testing indicated that
QDE works OK. We don't think that many users will hit problems but
obviously we keep our fingers crossed. 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.

What we are attempting to redress is a fundamental defect / inadequacy
in Excel. It doesn't have a dedicated data type for dates and times
but uses special formatting of numbers plus interpretation of date
type entries. That attempt must have limitations and the pain gain
trade-off that you've hit on is the biggest one for most users.

Now if we could have the sort of Spreadsheet program that we could
design from base up without the accumulated detritus and historically
imposed limits. Plus avoid the need for backwards compatibility...
Nirvana! I hope to see you there.

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

Frank Kabel wrote...
...
. . . As The event code is a little bit more complex it would be
kind of difficult only to 'add' the code to existing event handlers
and removing the code would be even more complicated. So yes
we chose the easier way. I would totally agree with you if it
were just a simple statement but it is a little bit more code
involved.
...

I'll repeat - y'all need to revisit your systems programming.

Something else I just thought of. If there were date entry ranges in 2
or more worksheets in a workbook, would each worksheet's class module
get its very own copy of the QDE Change and SelectionChange event
handlers? Seems rather wasteful. Wouldn't it make more sense to add the
procedures to be called to a separate general module and add single
lines to preexisting Change and SelectionChange event handlers or
create single statement event handlers if there were none to begin
with?

Just make the macros parametrized, and they won't appear in the Macro
Run dialog. Users would have to go out of their way to call them, and
if they perceived a need to do so, why not?

Excel 97 is really different. Though tested with various versions
and different languages we didn't catch thus bug. Thanks for
spotting it. Have you tried entering the dates then only a single
cell is selected (hopefully this works for you?)
...

So you all hadn't even tested this under XL97? I'll revise my earlier
assessment - it's an *ALPHA* release (at least for XL97). Did any of
you test this under XL2K?

WHERE ARE THE WARNINGS?!

It works if only one cell is selected when a date entry is made. IMO, a
requirement that only a single cell should be selected for this add-in
to work would obviate much of its claimed purpose of reducing user
keystrokes. Do any of the authors believe most users would expect it to
work when entire data entry ranges were selected? Or do you all believe
that users must set their Edit options to move selection after enter?

Some more but also as stated in Norman's email we 'hoped'
that no bug is there anymore. Maybe next time we should invite
you to out beta test
...

How naive.

There are ALWAYS bugs in any code more than a few dozen lines. All that
one may hope is that the bugs manifest themselves rarely.

Perhaps you all may learn from this what TESTING really means. For me
to have caught a bug arising from entry into multiple selected cells
means it wasn't EFFECTIVELY tested at all no matter how many people
tried it out on single cell ranges.

Why do you need to use the SelectionChange event in addition to the
Change event? That just doesn't make sense to me. I'd also note the
check for the selection being a single cell in the Change event handler
is ill-considered. If you check that the selection does span multiple
cells, then you only need to check the
Application.MoveAfterReturnDirection property and use that information
sensibly in an Offset range property call to figure out which cell must
have changed. Granted there are wrap-around issues, but perhaps you all
can have fun and gain enlightenment figuring that out.

Personally I don't need this either but many questions in this NG
suggested this requirement. Personally I prefer using a
database for mass data but Excel is often used differently and it
seems to me that many users use it for this kind of data
processing. . . .

You have one individual who's asked about this several times in the
last few months, and others who each ask once infrequently. What's
wrong with the standard response of using a macro, which users could
run when they want to, to convert entries to dates in place in batch?

In addition to Ron:
- pure fun for us (therefore totally free addin)
- also a learning excercise (international date settings,
manipulationg event handlers, mutli-language support, etc.)
- So for me if at least one user uses it my personal goal is
achieved

Personal discovery is fine in it's place, but responsible people
shouldn't distribute lightly tested, problematically useful software
without warnings about its state of development. If you all didn't
realize what state of development it was at, you shouldn't have
released it at all.

Totally agree with you that this could handled also by formulas
BUT
this would require a helper column. This is saved by the addin

Wrap the formulas' logic in a macro.


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

Sub foo()
Dim c As Range, f As String, df As String

If Not TypeOf Selection Is Range Then Exit Sub

For Each c In Selection.Cells
If Not (c.HasFormula Or c.Formula Like "*[!0-9 ]*") Then
f = c.Formula

If Not IsError(Evaluate("." & f)) Then
df = CStr(CDate("1-Feb-2003"))
df = Application.Substitute(df, "2003", "yyyy")
df = Application.Substitute(df, "03", "yyyy")
df = Application.Substitute(df, "02", "mm")
df = Application.Substitute(df, "2", "m")
df = Application.Substitute(df, "01", "dd")
df = Application.Substitute(df, "1", "d")

Select Case Len(f)
Case 1
f = Format(Now, "yyyy-mm-0" & f)
Case 2
f = Format(Now, "yyyy-mm-" & f)
Case 3
f = Format(Now, "yyyy-0") & Left(f, 1) & "-" & Right(f, 2)
Case 4
f = Format(Now, "yyyy-") & Left(f, 2) & "-" & Right(f, 2)
Case 6
f = "20" & Right(f, 2) & "-" & Left(f, 2) & "-" & Mid(f, 3, 2)
Case 8
f = Right(f, 4) & "-" & Left(f, 2) & "-" & Mid(f, 3, 2)
Case Else
Debug.Print c.Address(0, 0, xlA1, 1) & " not a valid date"
End Select

If c.NumberFormat "General" Then c.NumberFormat = df

c.Formula = f

End If

End If

Next c

End Sub

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


As we won't want to earn money with it we at least had our fun
developing it. And thanks again for your valid comments +
suggestions + bug reports.

So if you don't want to make $$ from it, why not open source it? Then
others could point out the bugs in the code for you.


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

In article ,
hgrove wrote:

If you check that the selection does span multiple cells, then you
only need to check the Application.MoveAfterReturnDirection property
and use that information sensibly in an Offset range property call to
figure out which cell must have changed.

FWIW, I've always found using Change event while depending on the
..MoveAfterReturnDirection property problematic. Within a multicell
selection, the Change event can be fired by the Enter key, shift-Enter,
the tab key, shift-tab, del, Delete, Edit/ClearContents, and ctrl-click,
a change in sheet or workbook, at least. Not to mention that Undo and
Redo seem to fire _Change() twice. In addition, I routinely set up my
MacXL version to have the .MoveAfterReturnDirection set to xlDown, but
not to move after the keypad Enter key. Pasting into a multicell
selection returns a union of the range and the activecell.

It's even worse when dealing with a multiple area range.

"hgrove " wrote...
....
I'll repeat - y'all need to revisit your systems programming.
....

For example,


Sub foo()
Dim cm As CodeModule, k As Long, n As Long

Set cm = ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule

With cm
k = .ProcStartLine("Worksheet_SelectionChange", vbext_pk_Proc)
n = .ProcCountLines("Worksheet_SelectionChange", vbext_pk_Proc)

If Not .Find("Call foobar", k + n - 2, 1, k + n, 80, -1, 0, 0) Then
.InsertLines k + n - 1, Chr(9) & "Call foobar"
End If

End With

End Sub


I'm not going to say it's easy to modify preexisting event handlers, but it
*IS* possible. A similar approach could be used to delete added statements.

As for the macro I gave in my preceding response, drop the df statements. If
a cell would be changed to a date with system default date format if
originally formatted as General and an ISO date (yyyy-mm-dd) date were
entered into it, then all that's needed is changing the number format to
General and entering an ISO date string.

Note that I'm now bypassing cells already containing dates (so formatted as
dates).


Sub foo()
Dim c As Range, f As String

If Not TypeOf Selection Is Range Then Exit Sub

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

If Not IsError(Evaluate("." & f)) Then

Select Case Len(f)

Case 1
f = Format(Now, "yyyy-mm-0" & f)

Case 2
f = Format(Now, "yyyy-mm-" & f)

Case 3
f = Format(Now, "yyyy-0") & Left(f, 1) & "-" & Right(f, 2)

Case 4
f = Format(Now, "yyyy-") & Left(f, 2) & "-" & Right(f, 2)

Case 6
f = "20" & Right(f, 2) & "-" & Left(f, 2) & "-" & Mid(f, 3, 2)

Case 8
f = Right(f, 4) & "-" & Left(f, 2) & "-" & Mid(f, 3, 2)

Case Else
Debug.Print c.Address(0, 0, xlA1, 1) & " not a valid date"

End Select

If Not IsError(Evaluate("--" & f)) Then
c.NumberFormat = "General"
c.Formula = f
End If

End If

End If

Next c

End Sub


I'm not claiming a deep understanding of internationalization issues, but
from what I gleen from VBA online help, the .NumberFormat property is always
in English while the .NumberFormatLocal may not be, and VBA's Format
function takes only English date formatting metacharacters. If so, the code
above should work for interpretting date entries so that 2 digits are always
just the day in the current month, and 3 digits are necessary to provide
month as well as day of month. Easy enough to adapt to d[d[m[m]]] short date
entries or other individual user needs.


. . . I'd also note the check for the selection being a single cell in
the Change event handler is ill-considered. If you check that the
selection does span multiple cells, then you only need to check the
Application.MoveAfterReturnDirection property and use that information
sensibly in an Offset range property call to figure out which cell must
have changed. Granted there are wrap-around issues, but perhaps you all
can have fun and gain enlightenment figuring that out.

That was stupid of me. The user could have moved to the current ActiveCell
either by [Enter], [Shift]+[Enter], [Tab], [Shift]+[Tab] or not have moved
at all but clicked on the check mark in the formula bar to enter the
ActiveCell.

This points out a design flaw in QDE. If users do select entire date entry
ranges and enter dates just pressing [Enter] or [Tab], shifted or not, your
Change event handler won't process the entries since the selected range's
cell count is 1, but the SelectionChange event handler won't fire at all
as long as the entire entry range remains selected. The only way to convert
dates entered in such manner is to select some other range then reselect the
entry range. Clumsy and undocumented would be a charitable assessment.

Better to change cells as entered rather than rely on the SelectionChange
event handler cleaning up such entries when & if users *re*select entry
ranges. I understand that you don't want to have to process all cells in the
intersection of the selected range and the QDE date entry range, but you
don't have to. All you need to process are the up to 5 cells that could have
changed: the active cell itself (user clicked on the check mark in the
formula bar) and the preceding cells if the user had pressed [Enter],
[Shift]+[Enter], [Tab] or [Shift]+[Tab].

Here's a brute force approach to determining those cells.

Dim a As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

Set a = ActiveCell
SendKeys "~", -1
Set a = Union(a, ActiveCell)
SendKeys "+~+~", -1
Set a = Union(a, ActiveCell)
SendKeys "~{TAB}", -1
Set a = Union(a, ActiveCell)
SendKeys "+{TAB}+{TAB}", -1
Set a = Union(a, ActiveCell)
SendKeys "{TAB}", -1

Application.EnableEvents = True
Application.ScreenUpdating = True

Elegant alternatives are left to you for fun & enlightenment.

"JE McGimpsey" wrote...
....
FWIW, I've always found using Change event while depending on the
.MoveAfterReturnDirection property problematic. . . .
....

Yeah, I figured that out belatedly myself.

However, for an add-in that supposed to support fewer keystrike date entry,
it's not unreasonable to assume that the actual changed cell when the Change
event is fired is either the ActiveCell itself or the cell activated by
[Enter], [Shift]+[Enter], [Tab] or [Shift]+[Tab]. Redundant to process all 5
on each Change event, but MUCH better than processing a selected range
spanning hundreds or thousands of cells.