XLL, C++ and dates

[witek]

Hi guys.

Does anybody of you write XLLs in C++?

How do you deal with dates?
XLOPER12 still does not recognize dates.
Where can I find some useful functions to convert numeric value into date?
Is there a way to recognize if value in cell is formated as date or
regular numeric value?

 

[joel]

I is easy to convert a number into a date. Excel stores the date as a
number with Jan 1, 1900 equal 1 and ech day afterwards is equalk to 1.
One hour equal 1/24, 1 minute equals 1/(24*60). You could write your
own function to convert a number to a date and a date to a number.

 

[witek]

I is easy to convert a number into a date. Excel stores the date as a
number with Jan 1, 1900 equal 1 and ech day afterwards is equalk to 1.
One hour equal 1/24, 1 minute equals 1/(24*60). You could write your
own function to convert a number to a date and a date to a number.

Theory is beautiful, practice is not.
Leap years, lotus bug make it more difficult.


Also I have numbers like 40000 and dates like 1/1/2009, and both are
passed the same way to XLL function. I need to know if there is a way to
distinguish them. If it is not, I have to rewrite other piece of code,
which I do not want to.

 

[joel]

A date is just a number and is passed between functions as a singl
variable type. Microsoft treat numbers and dates exactly the sam
except at the user interface they are displayed differently. There arr
a few quirks in excel the microsoft screws up the number by trying to b
too smart and looses accuracy of the number. I sometimes havve t
declare a variable in VBA as a single instead of a date so the built i
VBA functions don't do things I don't want them to do.


You can sue the Wind32 functions to convert your string dates t
number. See this URL for a list of functions.


'National Language Support Functions (Windows)
(http://msdn.microsoft.com/en-us/library/dd319081(VS.85).aspx)


I can write VBA functions that can call any of these functions. In C+
there are usually header funtions in the C++ libraries that can als
call these Win32 DLLs. I haven't written a lot of C++ code to kno
exactly how to use these functions but if figure out which function(s
you want to use then do a search on the web for sample code

 

[witek]

A date is just a number and is passed between functions as a single
variable type. Microsoft treat numbers and dates exactly the same
except at the user interface they are displayed differently. There arre
a few quirks in excel the microsoft screws up the number by trying to be
too smart and looses accuracy of the number. I sometimes havve to
declare a variable in VBA as a single instead of a date so the built in
VBA functions don't do things I don't want them to do.


You can sue the Wind32 functions to convert your string dates to
number. See this URL for a list of functions.


'National Language Support Functions (Windows)'
(http://msdn.microsoft.com/en-us/library/dd319081(VS.85).aspx)


I can write VBA functions that can call any of these functions. In C++
there are usually header funtions in the C++ libraries that can also
call these Win32 DLLs. I haven't written a lot of C++ code to know
exactly how to use these functions but if figure out which function(s)
you want to use then do a search on the web for sample code.

1.Excel does not follow standard which is used in VBA, so VBA date and
Excel date are not the same.

2. I do not have strings. I have a number: 42345. And I need to know
year, month and day.

3. I also need to know if 42345 was entered by user as 42345 or as a
date which is represented by that number.

#2 is easy, but requires a little bit of thinking
I expected, that maybe Microsoft did that thinking for me.

#3 is not easy and I do not know an answer.

 

[joel]

#1) The storage of the date in a wroksheet and VBA are identical. Some
of the mathematics are a little different. VBA performs some strange
trunicating and rounding of dates that on't happen in the worksheet. I
sometimes declares dates in VBA as Single so unexpected reults don't
occur.

#2) I agree and that is what I said earlier. You can easily get the
Year month and date of the number by using the Year(), Month(), and
Day(), functions.


MyYear = Year(42345)
MyMonth = Month(42345)
MyDay = Day(42345)

or
Dim MyDate as single
MyDate = 42345
MyYear = Year(MyDate)
MyMonth = Month(MyDate)
MyDay = Day(MyDate)


or a string
DateStr = Format(42435,"MM/DD/YY")




#3) Why do you care if a number was entered as a date or a number? A
date is stored exactly like a single precision number. The only
different between the two is the way they are displayed.

 

[witek]

#2) I agree and that is what I said earlier. You can easily get the
Year month and date of the number by using the Year(), Month(), and
Day(), functions.

In c++?

#3) Why do you care if a number was entered as a date or a number?

It is needed in my program.

User can enter a number or a date and based on that different
calculation is done.

 

[joel]

I would think that C++ in windows would use the same libraries as VBA
and store date the same way. All you shold have todo is over-ride the
varible type and pass the number into a C++ function like MyDay =
day(Date 42345)

If this doesn't work then you can use the VBA function like this


obj = excel.application 'not usr eof the exact syntax
obj.application.evaluate("Day(42345)")


or
obj = excel.application 'not usr eof the exact syntax
obj.worksheetfuncttion.Day(42345)



YOu are going to have to give the user an option box to choose either a
number of a date. Or have the user enter the date in mm/dd/yy format
and tthen search for the forward slash in the string. If you havve the
forward slash it would be a date otherwise a number.

 

[witek]

I would think that C++ in windows would use the same libraries as VBA
and store date the same way.
All you shold have todo is over-ride the
varible type and pass the number into a C++ function like MyDay =
day(Date 42345)

C++ does not have Date type.

If this doesn't work then you can use the VBA function like this


obj = excel.application 'not usr eof the exact syntax
obj.application.evaluate("Day(42345)")


or
obj = excel.application 'not usr eof the exact syntax
obj.worksheetfuncttion.Day(42345)

Too slow, and I do not want to link C++ back to Excel. It must be
independent. The interaction is done using XLOPER

YOu are going to have to give the user an option box to choose either a
number of a date.

It does not work.
User can paste data in any way or mixing them.


Or have the user enter the date in mm/dd/yy format

Date can be entered in any local format used. It used globally around
the World. So I can't assume any date format. Excel does that
interpretation form based on local settings.

and tthen search for the forward slash in the string. If you havve the
forward slash it would be a date otherwise a number.

I don't have access to date entered as string.
The only way Excel passes information to XLL function is integer
representing date.

 

[joel]

if you use printf to output the date in XLOPER using a %i format don'
you get the same number that excel would produce.

You are asking the impossible for the XLOPER to distiquish between
date and a number where both are integers. Maybe you want to pass tw
parameters

 

[witek]

if you use printf to output the date in XLOPER using a %i format don't
you get the same number that excel would produce.

there is no date type in xloper. double(integer) only. For both: numbers
and dates (it means internal representation)

You are asking the impossible for the XLOPER to distiquish between a
date and a number where both are integers.

that what I am asking for. "Impossible" is also a valid answer but I
want to check if there is really no other solution.

Maybe you want to pass two
parameters.

that can be acceptable.
At Excel level I can add another range, checking if values entered into
first one are dates or not.
It gives me true/false values at XLL level so I can find if
corresponding number was date or number.
It requires one parameter more in XLL function and one range more in
Excel, but does not requires changes in other parts of program.

At VBA level it was easy

if isdate (param) then
else
end if

 

[joel]

Dates in excel are stored a a single precision number 24 bit IEEE
standard and uses 4 bytes (32bits) to store the data. Integers were 16
bits (2 byes) but I'm told they are being stored as long which is also
32 bit. Characters are 1 byte (8 bit) per character but I believe the
actual storage of the string has the byte count before the actual data
(I don't think you actually see the byte count when it is passed to
another function).

 

[witek]

Dates in excel are stored a a single precision number 24 bit IEEE
standard and uses 4 bytes (32bits) to store the data. Integers were 16
bits (2 byes) but I'm told they are being stored as long which is also
32 bit.

including time in it, date is stored as double.
and there is no way to distinguish if number passed to c++ procedure is
a real double or a date.
That what i was asking for. if there is really no way to distinguish
that or simply I don't know how to do that.

and procedure to convert number into date and date into number is simple
but requires a little of bit o work. That procedure is of course
included in Excel but not available as "source code", so I was asking if
that information is somewhere so I don't have to write what is already
written.

We are at the beginning.




Characters are 1 byte (8 bit) per character but I believe the

actual storage of the string has the byte count before the actual data
yes.

(I don't think you actually see the byte count when it is passed to
another function).

strings are passed as pointers and must be prepared the same way in
XLOPER. First byte represents string length.