CODEWITHSUNDEEP
vbaexcel
user2679225
user2679225

Reputation: 159

Accumulate changing value in Excel Cell

I am currently running a trading algo in excel that utilizes live data so the value of my A1 cell keeps changing.

For example:

A1 = 3     at 930
A1 = 2     at 931
A1 = 100   at 932

A1s value changes throughout the and I would like to add those values up continusly.

So in my example the answer would be 105.

Is there any way to accumulate the values of the cell A1 in excel? I don't care about the time, I just need the value.

I don't have VBA that changes the value. Its actually a Bloomberg Terminal add-in that is feeding the data in.

Thanks.

Upvotes: 1

Views: 1731

Answers (1)

Graham Anderson
Graham Anderson

Reputation: 1239

Use the on worksheet change event and save the result in a different cell. Put this code in the code fot the sheet in question.

Private Sub Worksheet_Change(ByVal Target As Range)

Static lLong As Long

If Cells(1, 1).Value <> lLong Then
    lLong = Cells(1, 1).Value

    Cells(2, 1).Value = Cells(2, 1).Value + Cells(1, 1).Value
End If

End Sub

Edit:

Ok after reading jerussels comments I see it needs to be a bit more complicated. You need to store the variable as a global variable and the workbook open event has to be used to store the initial value.

Also as tigeravatar says it is probably best as a double if there is a chance of fractions.

So a the top of the a standard module you need the line

dim gdDouble as double

In the workbook code you need:

Private Sub Workbook_Open()
    gdDouble = Sheets("sheet1").Cells(1, 1).Value
End Sub

Replace "sheet1" with the sheets name.

In the sheet you need 

Private Sub Worksheet_Change(ByVal Target As Range)

If Cells(1, 1).Value <> gdDouble Then
    gdDouble = Cells(1, 1).Value
    Cells(2, 1).Value = Cells(2, 1).Value + Cells(1, 1).Value
End If

End Sub

Upvotes: 3

Related Questions