xlf QandA series

Using trailing minus numbers in VBA

QUESTION

A USERFORM TEXTBOX INCLUDES THE USUAL NUMBER VALIDATION TECHNIQUES, YET ACCEPTS A NUMBER WITH A TRAILING MINUS SIGN. HOW CAN THIS BE?


Answer icon Answer

UserForm TextBox control coding usually assigns the TextBox value to a variable of type String. Suppose that the value is "123.45", then:

  1. VBA.IsNumeric("123.45-") returns True, and
  2. VBA.Val("123.45-") returns 123.45 (ie. Val drops the trailing minus)

Both functions classify the trailing minus string expression as a numeric value. These, and other examples are described in code 1.


1. Trailing minus numbers (VBA)



Code 1: The TrailingMinusNumbers macro: demonstration and TrailingMinus function
' =======================================
Sub TrailingMinusNumbers()
Dim Num(1 To 12, 1 To 2) As Variant

        Num(1, 1) = "123.45-": Num(1, 2) = TypeName(Num(1, 1))                      ' Returns: 123.45-: "String"
        Num(11, 1) = VBA.IsNumeric("123.45-"): Num(11, 2) = TypeName(Num(11, 1))    ' Returns: True: "Boolean"
        Num(12, 1) = VBA.Val("123.45-"): Num(12, 2) = TypeName(Num(12, 1))          ' Returns: 123.45: "Double"

    ' 1. Type conversion with mathematical operators ===
        Num(2, 1) = "123.45-" / 1: Num(2, 2) = TypeName(Num(2, 1))  ' Returns: -123.45: "Double"
        Num(3, 1) = "123.45-" * 1: Num(3, 2) = TypeName(Num(3, 1))  ' Returns: -123.45: "Double"
        Num(4, 1) = --"123.45-": Num(4, 2) = TypeName(Num(4, 1))    ' Returns: -123.45: "Double"

    ' 2. Type conversion and format functions ===
        Num(5, 1) = VBA.CDbl("123.45-"): Num(5, 2) = TypeName(Num(5, 1))    ' Returns: -123.45: "Double"
        Num(6, 1) = VBA.CSng("123.45-"): Num(6, 2) = TypeName(Num(6, 1))    ' Returns: -123.45: "Single"
        Num(7, 1) = VBA.CDec("123.45-"): Num(7, 2) = TypeName(Num(7, 1))    ' Returns: -123.45: "Decimal"
        Num(8, 1) = Application.Sum("123.45-"): Num(8, 2) = TypeName(Num(8, 1))                 ' Returns: Error 2015: "Error"
        Num(9, 1) = VBA.Format(-123.45, "#,##0.00;#,##0.00-"): Num(9, 2) = TypeName(Num(9, 1))  ' Returns: 123.45-: "String"
                                                                                                ' The semicolon (;) distinguishes the positive and negative formats
        Num(10, 1) = DemoTM.TrailingMinus(-123.45): Num(10, 2) = TypeName(Num(10, 1))           ' Returns: 123.45-: "String"
    Stop

End Sub

' =======================================
Private Function TrailingMinus(ByVal Num As Double) As String

    If Num < 0 Then
        TrailingMinus = Format(Abs(Num), "#,###.####-")     ' Maximum number of decimal places (4 in this example) is set by trailing hash (#) characters
    Else                                                    ' Better to use the format function - see Num(9) - with argument "#,###.####;#,###.####-"
        TrailingMinus = Num
    End If

End Function

Details of the properties used in this module.


VBA Functions Description
IsNumeric(expression)Returns a Boolean if the entire expression evaluates as a number. Expression is a Variant containing a number or number string. Date expressions return False
Val(string)Returns the number found in a string expression. The function stops at the first character that isn't recognised as part of a number'

2. Trailing minus format - WS


Worksheet number formats:

xlf-trailing-minus
Fig 1: Format Cells dialog box - custom type: #,##0.00;[Red]#,##0.00-