Removing brackets inside a formula-CodePudding

So I created a macro that muliplies a cell value with 100 (10^2). If the cell is simply a value it justs multiplies the cell value with 100, however if it's a formula it puts the formula in brackets and adds a 100* to the beginning As you can't undo a macro easily but I need it, I tried to create an "undo" macro. The Cell Value-Undo is quite easy, however for the formulas I struggle to delete the outer brackets. An example process would be: Original: =1 1 becomes =100*(1 1) and the undo should return to =1 1 (without brackets).

This is my code for the multiplication:

Private Sub MultiplySelectionBy100(Control As IRibbonControl)

Dim Cell As Range
For Each Cell In Selection
If Len(Cell.Value) > 0 And Application.IsNumber(Cell.Value) Then
If Cell.HasFormula Then
Cell.Formula = Replace(Cell.Formula, "=", "=100*(") & ")"
Else
Cell.Value = 100 * Cell.Value
End If
End If
Next
Application.OnUndo "Undo something", "UnDoSomething"
End Sub

For the UNDO, this is what I have so far:

Public Sub UnDoSomething()
Dim Cell As Range
Dim bet As String
For Each Cell In Selection
If Len(Cell.Value) > 0 And Application.IsNumber(Cell.Value) Then
If Cell.HasFormula Then
Cell.Formula = Replace(Cell.Formula, "=100*(", "=") 'This is not running
Cell.Formula = Replace(Cell.Formula, Chr(41), Chr(0))
Else
Cell.Value = 100 / Cell.Value
End If
End If
Next
End Sub

I don't know how to attach multiple replace functions but it gives me an error every time (mostly because I delete a opening bracket but the closing bracket remains). I also can't delete all brackets as there may be more brackets inside the formula.

Maybe you have an idea...

CodePudding user response：

Your main problem is that the first replace command will set the formula invalid because the closing ")" is still there, and Excel (not VBA) doesn't allow an invalid formula, so you get a runtime error (1004).

You should use an intermediate variable to handle the string manipulation of the formula, and only if that is done completely, assign the formula back to the cell. Using intermediate variables is always a good idea if you are dealing with formulas in VBA - you can check with the debugger if the formula looks really as expected before you write it back to the cell.

You need to be careful with your second replace-statement: This will remove all closing parenthesis characters, even if it has nothing to do with your "Undo". You should (a) check if you really want to remove it and (b) remove only the last closing parenthesis.

    Dim f As String
    f = cell.Formula
    If Left(f, 6) = "=100*(" Then
        f = Replace(cell.Formula, "=100*(", "=")
        f = Left(f, Len(f) - 1)
        cell.Formula = f
    End If

Or, maybe easier

    If Left(f, 6) = "=100*(" Then
        f = "=" & Mid(f, 7, Len(f) - 7)
        cell.Formula = f
    End If