BasicMaker and PlanMaker > PlanMaker's object model

Access paths:

▪Application à Workbooks à Item à Sheets à Item à Range à NumberFormatting

▪Application à Workbooks à ActiveSheet à Range à NumberFormatting

▪Application à ActiveWorkbook à ActiveSheet à Range à NumberFormatting

▪Application à ActiveSheet à Range à NumberFormatting

Instead of "Range", you can also use other objects and properties that return a Range object: ActiveCell, Selection, Rows(n), Columns(n) and Cells(x, y). You can find examples of these access paths in the Range-Object.

1 Description

You can use the NumberFormatting object to read and change the number format of a range (corresponding to the options on the Number format tab in the dialog box for the cell properties).

2 Access to the object

NumberFormatting is a child object of the Range object – for each Range object there is exactly one NumberFormatting object.

3 Properties, objects, collections and methods

Properties:

▪Type (default property)

▪DateFormat

▪CustomFormat

▪Currency

▪Accounting

▪Digits

▪NegativeRed

▪SuppressMinus

▪SuppressZeros

▪ThousandsSeparator

Objects:

▪Application → Application

▪Parent → Range

Type (property)

Data type: Long (PmNumberFormatting)

Gets or sets the number format for the cells in the range. The possible values are:

pmNumberGeneral = 0 ' Standard

pmNumberDecimal = 1 ' Number

pmNumberScientific = 2 ' Scientific

pmNumberFraction = 3 ' Fraction (see also Digits property)

pmNumberDate = 4 ' Date/Time (see note)

pmNumberPercentage = 5 ' Percentage

pmNumberCurrency = 6 ' Currency (see note)

pmNumberBoolean = 7 ' Boolean

pmNumberCustom = 8 ' Custom (see note)

pmNumberText = 9 ' Text

pmNumberAccounting = 10 ' Accounting (see note)

Note: The formats pmNumberDate, pmNumberCurrency, pmNumberAccounting and pmNumberCustom can only be read, but not set. To apply one of these formats, use the properties DateFormat, Currency, Accounting and CustomFormat (see below).

DateFormat (property)

Data type: String

Gets or sets the date/time format for the cells in the range.

Example:

' Format cell A1 as a date

pm.ActiveSheet.Range("A1").NumberFormatting.DateFormat = "YYYY-MM-DD"

For details on the format codes supported, see the online help for PlanMaker, keyword "User-defined number formats".

Note: The letter codes for the components of a date format are language-specific. If PlanMaker is running with its English user interface, e.g. DD/MM/YYYY is a valid date format. If the German user interface is used, TT.MM.JJJJ has to be used, with the French user interface it has to be JJ.MM.AAAA, etc.

If you would like to retrieve the date string used in a cell, you must first check if the cell is formatted as a date at all – otherwise this property fails:

' Display the date string of cell A1

With pm.ActiveSheet.Range("A1")

If .NumberFormatting.Type = pmNumberDate Then

MsgBox .NumberFormatting.DateFormat

Else

MsgBox "Cell A1 is not formatted as a date."

End If

End With

CustomFormat (property)

Data type: String

Gets or sets the user-defined formatting for the cells in the range.

Example:

' Format cell A1 with a used-defined format

pm.ActiveSheet.Range("A1").NumberFormatting.CustomFormat = "000000"

Currency (property)

Data type: String

Gets or sets the currency format for the cells in the range.

Use an ISO code to specify the desired currency. When you read this property, it will return an ISO code as well. Some popular ISO codes:

EUR Euro

USD US dollar

CAD Canadian dollar

AUD Australian dollar

JPY Japanese yen

RUB Russian ruble

CHF Swiss franc

You can find a complete list of ISO codes (PlanMaker supports many of them, but not all) in the following Wikipedia article: http://en.wikipedia.org/wiki/ISO_4217

Example:

' Format cell A1 as euro

pm.ActiveSheet.Range("A1").NumberFormatting.Currency = "EUR"

To retrieve the currency string used in a cell, you must first check if the cell is formatted as a currency at all – otherwise this property fails:

' Display the currency string of cell A1

With pm.ActiveSheet.Range("A1")

If .NumberFormatting.Type = pmNumberCurrency Then

MsgBox .NumberFormatting.Currency

Else

MsgBox "Cell A1 is not formatted as a currency."

End If

End With

Accounting (property)

Data type: String

Gets or sets the accounting format of the cells in the range.

Exactly like for the property Currency (see there), you pass the ISO code of the desired currency to this property. When you read this property, it will return an ISO code as well.

Example:

' Format cell A1 in the accounting format with the currency "euro"

pm.ActiveSheet.Range("A1").NumberFormatting.Accounting = "EUR"

To retrieve the currency string used in a cell, you must first check if the cell is formatted in Accounting number format at all – otherwise this property fails:

' Display the currency string of cell A1 (formatted in Accounting format)

With pm.ActiveSheet.Range("A1")

If .NumberFormatting.Type = pmNumberAccounting Then

MsgBox .NumberFormatting.Accounting

Else

MsgBox "Cell A1 is not formatted in Accounting format."

End If

End With

Digits (property)

Data type: Long

Gets or sets the number of the digits right of the decimal separator for the cells in the range.

This property can be used with the following number formats:

▪Number (pmNumberDecimal)

▪Scientific (pmNumberScientific)

▪Percent (pmNumberPercentage)

▪Currency (pmNumberCurrency)

▪Accounting (pmNumberAccounting)

Example:

' Set cell A1 to 4 decimal places

pm.ActiveSheet.Range("A1").NumberFormatting.Digits = 4

You can also use this property with the number format "Fraction" (pmNumberFraction), but in this case it sets the denominator of the fraction:

' Format the cell A1 as a fraction with the denominator 8

With pm.ActiveSheet.Range("A1")

.NumberFormatting.Type = pmNumberFraction

.NumberFormatting.Digits = 8

End With

For the number format "fraction" Digits may be between 0 and 1000, for all other number formats between 0 and 15.

NegativeRed (property)

Data type: Boolean

Gets or sets the setting "Negative numbers in red" for the cells of the range, corresponding to the option of the same name on the Number format tab in the dialog box for the cell properties.

SuppressMinus (property)

Data type: Boolean

Gets or sets the setting "Suppress minus sign" for the cells of the range, corresponding to the option of the same name on the Number format tab in the dialog box for the cell properties.

SuppressZeros (property)

Data type: Boolean

Gets or sets the setting "Don't show zero" for the cells of the range, corresponding to the option of the same name on the Number format tab in the dialog box for the cell properties.

ThousandsSeparator (property)

Data type: Boolean

Gets or sets the setting "Thousands separator" for the cells of the range, corresponding to the option of the same name on the Number format tab in the dialog box for the cell properties.

Application (pointer to object)

Returns the Application object.

Parent (pointer to object)

Returns the parent object, i.e. an object of the type Range.

An example for the NumberFormatting object

In the following example, the range from A1 to C3 will be formatted as percentage values with two decimal places:

Sub Main

Dim pm as Object

Set pm = CreateObject("PlanMaker.Application")

pm.Visible = True

With pm.ActiveSheet.Range("A1:C3")

.NumberFormatting.Type = pmNumberPercentage

.NumberFormatting.Digits = 2

End With

Set pm = Nothing

End Sub

NumberFormatting (object)

NumberFormatting (object)