Calc StarBasic Macro - change cell type for currency

[Arrowman]

Hello,
in the documentation there is written that cells has types:
Cell.type:
EMPTY - Empty cell
VALUE - Numerical value
TEXT - Text contents
FORMULA - Formula contents

How to make cell Currency type (like in right click->format cell->Cell type)?

I would like to do something like this:

oView = ThisComponent.getCurrentController()
oSheet = oView.activesheet
' oCell = oSheet.getCellByPosition( 0, 0 ).type = "Currency"

I commented line which is not correct but it shows what I would like to achieve.

[Zizi64]

Just some clarifications:

• The StarBasic+API is not MS VBA. A StarBasic+API type macro never will run in Excel. (However some MS VBA macros can run in LibreOffice)

• The EMPTY, TEXT, VALUE, FORMULA are types of the cell content.
  The data types and the formatting categories are two basicly different things.
  You can format a numeric VALUE (or a numerical result of a FORMULA) as a Date, as a Time, as a DateTime, as a Percent value, as a Currency, as a Scientific number, as a natural Fraction, as a Boolean value, or as a pure decimal number with less or more decimal places…

• I suggest you to use the Cell Styles instead of the direct formatting method even in the macros.

Zizi64

[Arrowman]

@Zizi64
I supposed that Libreoffice language is also VBA, I didn’t know about StarBasic.
Thanks! It will help me to find solutions in Google. Maybe it is why I couldn’t find some simple problems.

[Lupp]

There is no “currency type” but…

Create a New cell style, most likely derived from the Deafult style, and only choose the tab Numbers. There select the wanted format or create your own code.
Later apply this CellStyle to all the CalRange_s where you want numbers to be shown as currency amounts.

Also: Please note that the usage of the comma and of the point as well as a “Group Separator” (often called “thousands separator”) is explicitly deprecated by international standards.

Also: Always express the sign of a negative numeric value by an actual sign. Don’t use a color or parenthesing for the purpose.

LibreOffice doesn’t speak VBA, but it has a powerful API.

To apply the new CellStyle to all the cells of a SheetCellRange object simply assign:
myCellRange.CellStyle = “myCurrencyStyleName” placing your actual style name in the proper position.
A single cell also is accepted as a range object.

In rare case yo may want to avoid the usage of a named CellStyle. I would dissuade from such an approach, but it can be done. Come back if you need additional respective help.

[schiavinatto]

Use the macro recorder to generate the Macro, format a cell using the icon.

When creating Basic macro, do not save the file in Excel, the macro is deleted.

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[mauricio]

The correct property is NumberFormat

sub main()
	sheet = ThisComponent.CurrentController.ActiveSheet
	
	cell1 = sheet.getCellRangeByName("D7")
	MsgBox(cell1.NumberFormat)
	
	cell2 = sheet.getCellRangeByName("D8")
	cell2.NumberFormat = cell1.NumberFormat
end sub

[Lupp]

You can do it this way if cell1 (D7 of the sheet object in the example above) already has the NumberFormat you want to apply. The propery .NumberFormat then has a numeric value (key) accessing the respective format (a Currency in your case) within the given document. Due to unknown circumstances concerning the history of the documents, there is no guarantee that the mentioned number will access the same formatting in a different document.
If you want to get a NumberFormat key based on a format code, you need to query for it or -if not yet existing- to create it. The appropriate code you find in the famous texts by Andrew Pitonyak.

[Arrowman]

Thanks, however, it is not a solution for currency problem. Maybe I didn’t describe this with enough precision.
How to make macro to format this cell.NumberFormat as Currency with automate add currency shortcut to numbers? In your case, it is just Number not Currency formating.

[Lupp]

A currency format is a NumberFormat, and there are many currency formats.
There is no currency cell type!
If you want to set cells to a NumberFormat of the currency group using a macro, you need to accept that thinkable formats are so many that it not is possible to assiogn a fix identifyer to everyone of them. They are described by format codes, but the predefined and also the user defined formats are identified by a number each on a per-document basis. Therefore: If you want to make a new number format described by a well-formed code accessible via macros, you first need to create the format and its key.
Search for the function FindCreateNumberFormatStyle published by Andrew Pitonyak and used by millions if you want to create formats by macros.
There is no currency cell type!
“…with automate add currency shortcut to numbers…” Please descrbe to more detail what the shortcut is expected to do.