Getting a date from a spreadsheet cell in a Basic macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Fiable.biz
Posts: 3
Joined: Sat Feb 19, 2011 5:15 am

Getting a date from a spreadsheet cell in a Basic macro

Post by Fiable.biz »

Hello.

I've a very simple function:

Code: Select all

Function END_WAIT(a) As Date
  If IsDate(a) Then
     END_WAIT = a
  Else
     END_WAIT = Date()
  End if
End Function
When I use it, firstly I don't get a date as expected, but a string. I would have expected an implicit conversion, since END_WAIT is declared "As Date". And I don't know how to make an explicit conversion.
Secondly, when I use my function like this
=END_WAIT(F20)
in OpenOffice.org Spreadsheet, and the F20 cell does contain a date, I nevertheless get today's date, i.e. the result of Date(), rather than the date present in the said cell.

What is wrong?
http://Fiable.biz Ubuntu 10.10 OpenOffice.org 3.2.1
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Getting a date from a spreadsheet cell in a Basic macro

Post by FJCC »

Dates in Calc are just numbers formatted to look a certain way. The underlying value, however, is just a number. Day zero is Dec 30, 1899, day one is Dec 31, 1899 and so on. The value of Feb, 18 2011 is 40592. Also, I think only numeric values and text can be passed back and forth by Basic functions. So, if cell F20 contains a date, the parameter received by your function is just a number. The IsDate function returns False and your cell always gets today's date. A modification of your code that might work closer to what you want is

Code: Select all

Function END_WAIT(a) As Date
  If IsNumeric(a) Then
     END_WAIT = a
  Else
     END_WAIT = Date()
  End if
End Function
Of course, this function cannot distinguish between F20 containing a date (i. e. a number formatted to look like a date) and it containing a number formatted in a more typically numeric way like 1.45. I can't think of any way to have a function work with the format of the cell that provides the parameter value.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Fiable.biz
Posts: 3
Joined: Sat Feb 19, 2011 5:15 am

Re: Getting a date from a spreadsheet cell in a Basic macro

Post by Fiable.biz »

Thank you for this clear explanation. But the function still does not work well, because Date() returns a string. How to convert it into a number?
http://Fiable.biz Ubuntu 10.10 OpenOffice.org 3.2.1
TinManTom
Posts: 2
Joined: Mon Mar 07, 2011 11:43 pm

Re: Getting a date from a spreadsheet cell in a Basic macro

Post by TinManTom »

Well I would think the line END_WAIT = Date()
should read END_WAIT = Datevalue(a)
At least that is how I would do it in VBA.
OpenOffice 3.3
Windows Vista
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Getting a date from a spreadsheet cell in a Basic macro

Post by rudolfo »

We have a classic constellation here:
"I've a very simple function:"

Code: Select all

Function END_WAIT(a) As Date
  If IsDate(a) Then    ' or If IsNumeric(a) Then
     END_WAIT = a
  Else
     END_WAIT = Date()
  End if
End Function
But obviously though it is very simple it would require a line or a comment about what it is supposed to do.
My understanding is: "Return the passed parameter if that is (or looks like a date) otherwise return the current date". And for the current date you must not pass a parameter to Date(), nor can you use DateSerial() or DateValue() which are both conversion-like functions.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Getting a date from a spreadsheet cell in a Basic macro

Post by B Marcelly »

Hi,
You have to understand this :
A Calc cell contains
  • either a value of type Double
  • or a String.
Even if a Calc cell displays a date value, it contains a Double.
Even if a Calc cell displays 1 it contains a Double.

So if you want to return a number, it should be declared as returning a Double. Then Basic can convert the type Date to the type Double

Code: Select all

Function END_WAIT(a) As Double
Then you will see in your cell a number like 40610.
Format the cell with a date format, it will display the date.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
Post Reply