Macro / get display text instead of integer for dates

[Andras77]

Dear All,

Libreoffice 6.0 on Ubuntu 18.

I have a macro with custom function:

public function tt( myrange as variant )
    a = myrange(0,0)
    msgbox(a)
end function

I refer to my function from a cell like “=TT(A1)”

If cell “A1” being passed to the function contains a date value “2012-01-01”, then variable “a” in the function will not contain a string of “2012-01-01” but instead I get an integer “40909” representing the date in numeric form. However I need the former one because it cannot be decided from the integers if they are representing date values.

How could I get the displayed text of all cells in the range that are passed to my function instead their underlying numeric values? Is it a problem that I get an array as an argument that has not methods to get the .value or .formula, and not a range type? How could I achive that?

Thank you.

[mauricio]

“However I need the former one because it cannot be decided from the integers if they are representing date values.”

Why not?

What, exactly, you need make?

[Andras77]

Well, the input is a time series with at least 2 columns: dates and values. I need the date format.

[JohnSUN]

If you need a formatted value, then why aren’t you using the Format() function?

Like so a = Format(myrange(0,0),"YYYY-MM-DD")

[Andras77]

You cannot decide from the array’s values whether they are date types or other, because the date values are represented as a number too. I would like to find out the type of the cells in the first place and then I can decide what to do with their content. Thx.

[JohnSUN]

Unfortunately, any number can be interpreted as a date (or date - time). You can try comparing the number in myrange (0,0) with some acceptable date range, for example, between INT(DATE ())-365 and INT(DATE ())+180. This is rude and not reliable, but other methods are too complicated.

[Andras77]

I ended up doing that. Thx.

[samuel.chorrica]

Hello,

In plain VBA (MS version), this would be achieved by invoking the text property of the given range, in your code would be something like a.Text

In the basic mode of calc, something like the following:

Sub Main
	dim doc as object
	dim sheet as object
	dim cell as object
	
	doc = ThisComponent
	sheet = doc.Sheets(0)
	cell = Sheet.getCellRangeByName("C8")
	MsgBox(cell.Value)  'This shows 43971
	MsgBox(cell.String) 'This shows 20/05/2020
End Sub

[Andras77]

Thank you.