Macro 48: Convert Trailing Minus Signs
Legacy and mainframe systems are notorious for outputting trailing minus signs. In other words,
instead of a number like -142, some systems output 142-. This obviously wreaks havoc on your
spreadsheet — especially if you need to perform mathematic operations on the data. This nifty
macro goes through a target range and fixes all the negative minus signs so that they show up in
front of the number instead of at the end.
How it works
In this macro, we use two Range object variables to go through our target range, leveraging the
For Each statement to activate each cell in the target range. Every time a cell is activated, we con-
vert the value of the cell into a Double numeric data type by using the Cdbl function. The Double
data type forces any negative signs to show at the front of the number.
Sub Macro48()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells?
Select Case MsgBox(“Can’t Undo this action. ” & _
“Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
‘Step 3: Define the target Range.
Set MyRange = Selection
‘Step 4: Start looping through the range.
For Each MyCell In MyRange
‘Step 5: Convert the value to a Double.
If IsNumeric(MyCell) Then
MyCell = CDbl(MyCell)
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target
range, and the other called MyCell to hold each cell in the range as we enumerate through
them one by one.
2. When you run a macro, it destroys the undo stack. This means you can’t undo the changes a
macro makes. Because we are actually changing data, we need to give ourselves the option
of saving the workbook before running the macro. This is what Step 2 does. Here, we call up
a message box that asks if we want to save the workbook first. It then gives us three choices:
Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking
Cancel exits the procedure without running the macro. Clicking No runs the macro without
saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the
selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target
range is a named range, you can simply enter its name — Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell as it goes
through.
5. After a cell is activated, Step 5 uses the IsNumeric function to check to see if the value can
be evaluated as a number. This is to ensure we don’t affect textual fields. We then pass the
cell’s value through the Cdbl function. This effectively converts the value to the Double
numeric data type, forcing the minus sign to the front.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the
macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
