MicroStation^® VBA Tips

Formatting, Rounding, and Truncating Numbers

VB/VBA has its own Format function, which is described here for MicroStation VBA developers. VB'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 VB/VBA documentation.

VB'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 VB/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 …

Formatting Function

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

• How do I format a number in VB?
• How do I specify a fixed number of digits after the decimal separator?
• How do I include a thousands separator in a large number?

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 the VB 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
  Dim intermediate As Double
  intermediate = CLng(number)
  Select Case factor
  Case 10, 100, 1000
    intermediate = factor * CLng((intermediate / factor))
  Case Else
  End Select
  Approximate = intermediate
End Function
' ---------------------------------------------------------------------

Sometimes I make life too easy for you. Here's a subroutine to test the Approximate function …

' ---------------------------------------------------------------------
Sub TestApproximate()
  Dim result As Long
  Const factor As Long = 1000
  Const number As Double = 13827.3754
  result = Approximate(number, factor)
  Debug.Print "Number=" & CStr(number) & " Factor=" & CStr(factor) & " Result=" & CStr(result)
End Sub