Formatting, Rounding, and Truncating Numbers

VBA has its own Format function, which is described here for MicroStation VBA developers. VBA's numeric handling provides a number of arithmetic operations, including the conversion operators CLng, CInt, CDgl etc., Int & Fix, and Round. You can find out about those functions in the Microsoft VBA documentation.

VBA's Format function is a string conversion function. It takes a numeric input and converts it to a string, applying your formatting en route.

MicroStation VBA developers have asked for an approximation function, which goes beyond anything provided with VBA. An approximation function doesn't just trim your decimal numbers, it also adjust the integer part of your number to the nearest number divisible exactly by 10, 100, 1000 …

• Approximation Function
• Format Function

Formatting Function

Q VBA's Format() function provides an answer to several common questions along these lines …

• How do I format a number in VBA?
• How do I specify a fixed number of digits after the decimal separator?
• How do I include a thousands separator in a large number?
• How do I increment text?
• How do I increment MicroStation text elements?

A Suppose you have a floating-point number (e.g. 3.74952) that you want to round and present with three decimal digits (i.e. 3.750). You can use VBA's Round() function (e.g. Round (3.74952, 2) is 3.75). However, Round does not guarantee a fixed number of decimal places.

It's because, numerically, 3.750 is identical to 3.75; you're asking for a cosmetic formatting with no numerical meaning. However, the VBA Format() function provides that cosmetic make-over. Format converts numeric, date or time data to a string. The string is formatted the way you want.

Format() takes two parameters: a number to format, and a format expression. The format expression is text and uses some special characters to indicate the required formatting. You can find plenty of information about Format in Microsoft VBA help. In this case …

Dim oText  As TextElement
Dim number As Double
   ...
number = 3.74952
oText.Text = Format (number, "#,##0.000")

Where #,##0.000 means:
       ^  ^   ^
       |  |   |
       |  |   +- Display three digits after the decimal point
       |  |
       |  +- For numbers less than one, keep a zero before the decimal point
       |
       |
       +- Format thousands with a comma separator

---

Approximation Function

Q This article provides an answer to a common question along these lines …

• How do I round a number to the nearest multiple of 10 (or 100 or 1000)?

A In other words, you want to convert a number like 12345.67 to 12350 (to the nearest 10) or to 12400 (to the nearest 100). The answer is a function similar to this …

' ---------------------------------------------------------------------
'  Approximate
'  number is the value you want to approximate
'  factor is the approximation multiplier
'  Example:
'  Given 13827.3754
'  setting the accuracy to 0 will give me
'  13827
'  however I want to round up/down to nearest for example
'  13830 - nearest 10   (factor = 10)
'  13800 - nearest 100  (factor = 100)
'  14000 - nearest 1000 (factor = 1000)
' ---------------------------------------------------------------------
Function Approximate(ByVal number As Double, ByVal factor As Long) As Long
  If factor = 0 Then
    Approximate = CLng(number)
  Else
    Approximate = factor * CLng(number / factor)
  End If
End Function
' ---------------------------------------------------------------------

Thanks to Dušan Paulovič for suggesting a simpler and more accurate Approximate function. Dušan is an enthusiastic support of the MicroStation Programming Forum.

Here's a subroutine to test the Approximate function …

' ---------------------------------------------------------------------
Sub TestApproximate()
  Dim result As Long
  Const factor As Long = 1000         '  Change as you like: 100, 10, etc.
  Const number As Double = 13827.3754 '  Any number you like
  result = Approximate(number, factor)
  Debug.Print "Number=" & CStr(number) & " Factor=" & CStr(factor) & " Result=" & CStr(result)
End Sub

Increment Text

Q How do I increment text that contains a number? For example, my document numbers look something like this: ABC123. I want to be able to increment them like this: ABC124.

A You'll have do to some work, because clearly the string ABC123 is not a number. VBA can't understand what you mean if you write something like ABC123 + 1. We've written a VBA project that contains several functions that deal with the numeric content of an alpha-numeric string. The function attempts to preserve leading zeros (e.g. ABC001 increments to ABC002). The primary IncrementText function takes a string of alpha-numeric text and returns the string with its numeric content incremented …

Function IncrementText (ByVal s As String, ByVal increment As Integer) As String
  …
End Function

The increment parameter lets you specify a positive or negative value. That is, you can both increment and decrement an alpha-numeric string using this function.

Example use …

Dim alpha As String
alpha = "ABC123"
Dim result As String
Dim increment As Integer
increment = 1
result = IncrementText (alpha, increment)
 ' result = ABC124
alpha = "Item 001"
result = IncrementText (alpha, increment)
 ' result = Item 002
alpha = "Item 001"
increment = 3
result = IncrementText (alpha, increment)
 ' result = Item 004
alpha = "Item 010"
increment = -1
result = IncrementText (alpha, increment)
 ' result = Item 09

Questions

Post questions about MicroStation programming to the MicroStation Programming Forum.