# 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

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: