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:
VBA.IsNumeric("123.45-")
returns True, andVBA.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:

- Development platform: Excel 2016 (Office 365 ProPlus) 64 bit
- First published: 9 May 2018
- Revised: Saturday 25th of February 2023 - 09:39 AM, [Australian Eastern Time (AET)]