CODEWITHSUNDEEP
excelvba
user9440272
user9440272

Reputation:

How to fix a cell that is not automatically updating due to a macro

I have cell that updates a balance with any expenses. Because there is no subtract function in excel, I wrote this simple macro:

Private Function SubtractChecking(cell As Range)
    Dim currentBalance As Single
    currentBalance = cell.Value

    SubtractChecking = currentBalance - WorksheetFunction.Sum(Range("B7:M23"))
End Function

The function works but the cell that holds this function does not update the calculations automatically; I checked that the Excel calculation options are set to automatic. I either have to double click on the cell to update it or use Ctrl-Shift-Alt-F9 to update. I figured it had to do with the macro but I have no clue.

Upvotes: 2

Views: 274

Answers (2)

user4039065
user4039065

Reputation:

You are passing a range reference into the function as a parameter for the currentBalance but not the range to modify the balance.

Private Function SubtractChecking(cell As Range, vals as range)
    Dim currentBalance As Single

    currentBalance = cell.Value2
    SubtractChecking = currentBalance - WorksheetFunction.Sum(vals)

End Function

Usage: =SubtractChecking(A1, B7:M23)

Using native worksheet function SUM: =sum(a1, -sum(B7:M23))

Upvotes: 3

Storax
Storax

Reputation: 12207

The general answer to the question "update the calculations automatically" is that you have to use Application.Volatile In this case

Private Function SubtractChecking(cell As Range)
    Dim currentBalance As Single
    Application.Volatile
    currentBalance = cell.Value

    SubtractChecking = currentBalance - WorksheetFunction.Sum(Range("B7:M23"))

End Function

But, of course, Michal's comment is also right: Why don't you use -?

Upvotes: 3

Related Questions