Search code examples
excelvbaformulasubtraction

Preventing subtraction to appearing in formula bar (Excel 2010, VBA)

Seeking help! Hi, I am almost done on a project and there's a part I don't understand I have a + and a - button to add amounts from it's left cell to a total cell on the right (reads 54 in the example) (shown in the link down below) Also, the amount of times the + is pressed is also calculated on the far most cell (2 for this example)

From the following code I made:

Range("L8").FormulaLocal = "=" & Replace(Range("L8").FormulaLocal, "=", "") & "+" & Range("G8")

The problem is that with the same code, using subtraction, I don't want to see it in the formula bar as it ends up as: =29+29+29-29-29+29... (2nd image)

I only want to keep the positives. Is there something in the above mentioned code that I can change that will not show the subtraction though and not erase the whole formula that is there already

Thanks!

Part of my excel sheet for better understanding:

enter image description here

What I don't want to see in my formula bar:

enter image description here

Solution

  • First, shouldn't the value in your screenshot be 58, not 54?


    Next, does the cell really need to contain a formula? As @teylyn mentioned, just do the calculation in VBA and set the cell's value. E.g. for the + button:

    Range("L8").Value = Range("L8").Value + Range("G8").Value


    Finally, if you really need to keep the formula, and you know that the value in cell G8 (the value being added or subtracted) never changes between clicks of the + and - buttons, you could first increment the counter (which I assume is cell M8), and then use its value to build the formula.

    That might look something like the following, although you'd want to add some error trapping. Also, if you can ensure the counter will never be negative, you could eliminate the ElseIf portion:

    Public Sub MinusButton_Click()
    Range("M8").Value = Range("M8").Value - 1
    Range("L8").FormulaLocal = GetCellFormula
End Sub

Public Sub PlusButton_Click()
    Range("M8").Value = Range("M8").Value + 1
    Range("L8").FormulaLocal = GetCellFormula
End Sub

Private Function GetCellFormula()
    Dim strFormula As String
    Dim intNum As Integer

    strFormula = "="

    If Range("M8").Value > 0 Then
      For intNum = 1 To Range("M8").Value
        strFormula = strFormula & Range("G8").Value & "+"
      Next intNum
    ElseIf Range("M8").Value < 0 Then
      For intNum = Range("M8").Value To 1 Step -1
        strFormula = strFormula & Range("G8").Value & "-"
      Next intNum
    End If

    strFormula = Left(strFormula, Len(strFormula) - 1) 'strip trailing symbol

    GetCellFormula = strFormula
End Function

    Otherwise, if the value in cell G8 may change, your only option may be to do some (potentially) complicated parsing of the existing formula. If that's the case, please give it a try first, and post a new SO Question if you have any issues.