CStr/String decimal place formatting

Hello. I’m working on a spreadsheet to help me at work. I have created a macro that scans a document and inserts a subtotal every time I leave a blank space in a list of parts. Everything is working as expected but I cannot find a way to make CStr give me an integer with 2 decimal places for the dollar. amount.

REM  *****  BASIC  *****

Sub caculate_subtotal()
	oCtrl = ThisComponent.CurrentController
	subTotal = 0 
	For i = 3 to 100
		rem Scann Document for blank line
		If oCtrl.ActiveSheet.GetCellRangeByName("E"+CStr(i)).Value = 0	Then 
			If subTotal > 0 Then
				rem Print, then clear subtotal on blank line.
				oCtrl.ActiveSheet.GetCellRangeByName("B"+CStr(i)).setString("Subtotal: $"+CStr(subTotal))
				subTotal = 0
			end If
		else rem if line is not blank, add value to subtotal
			subTotal = subTotal + oCtrl.ActiveSheet.GetCellRangeByName("E"+CStr(i)).Value
		end If	
	next
End Sub

Currently if subTotal = 4.5 it prints
Subtotal: $4.5
I would like for it to print
Subtotal: $4.50

CStr is just a simple function, without any advanced options.

Use Format function instead. There’s also FormatNumber VBA function.

Wow, thanks! Thats exactly what I was looking for. I’m having a hard time with the documentation. Is there an overview of all the available functions and object methods?

Indeed.
First of all, there is an expandable ToC in the help:

Basic ToC screencast

And then, there’s a ton of documentation at https://wiki.documentfoundation.org/Macros, including the BASIC guide, which is a nice introduction to the language, its syntax and functions.

But note that LibreOffice UNO API is separate from the BASIC functions. Most complexity when writing for LibreOffice is around the use of API, and there are many resources about that, e.g. the great Andrew Pitonyak’s great book (also linked from the wiki page).

3 Likes

Thank you!