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?
- 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 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 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
Be Communities.
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 of 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
Increment MicroStation Text
Q How do I increment a MicroStation text element that contains a number?
A Read the text element, pass its content to the described above, set the content to the result, and rewrite the element …
- Locate a
TextElement - Read the
TextElementinto memory - Pass its text content to the IncrementText function described above
- Assign the result of the
IncrementTextfunction to theTextElement - Rewrite the
TextElement
The TextIncrementor VBA project is a working project that implements a text locator along with the text increment funcion.
Download the
TextIncrementor ZIP archive, and unpack it to a suitable location such as
\Workspace\Standards\vba.
Start the macro using the key-in …
vba run [TextIncrementor]modMain.Main
