save the previous number and put in another cell

[crustycollins]

Ok, I have been working on this for a couple of days... I am new to doing this kind of thing in VBA so it may be a bit messy.

I get live data through DDE. I want to save the previous number and display is no matter what the current number is. This was easy when I only had one price ( only looking for the 2year bid) when I try and apply the same thing to multiple fields they step all over each other. It was obvious that I was overwriting the slot in the array with the new data... IF I could define the array with a variable in the function this would solve my whole problem... I CAN NOT FIGURE OUT HOW TO DO THAT!!!
Any suggestions?
Here is the code.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Public Static Function prevPrice(inputvalue As String)
Application.Volatile
Dim price(16) As String
Dim R As Integer
If inputvalue = "0" Or inputvalue = "" Then ' do not pub 0
Exit Function
End If

If price(R) <> inputvalue Then ' do not pub dups
price(R + 1) = price(R) ' move last one
Else
Exit Function
End If
prevPrice = price(R + 1) ' pub the prev price

price(R) = inputvalue ' reset for next iteration
End Function

 

[DarkSun]

Can you explain exactly what you are trying to achieve. Can you give an example.

When do you run this Function? Is it in a loop?

 

[crustycollins]

I get live data into a sheet. I want to make a copy of the current price and save it untill that price changes. When the current price does change, move the saved price to a specific cell.
I guess there is no real need to use an array but I can not figure out how to assign a specific cell to send the output to.

Example

2YEAR BID 101-12+ 2YEAR ASK 101-126
previous 101-10 101-12

I need to hold the current and previous price. The current is already displayed but I need to move that to the previous when there is a tic.

I hope this explained it better.
Cheers

 

[DarkSun]

Okay I have more of an idea what you are trying to do.

How is the data updated? Is it done through code?

The reason I am asking is there are a couple of ways of doing it.

1. If data is updated via some other code you can add a simple bit of code to copy the value before updating.

2. Have a loop which keeps checking every second or so to see if the value has changed.

I can give you some example code for the second solution if you like.

 

[crustycollins]

It updates through a dde connection. I can not change that code and the frequency of the updates are determined by the source.
How about returning the value to a specific cell?

 

[DarkSun]

Okay here is an example:

I hope this is clear.

The basic idea is to use the Ontime function to check the current value every second. I have done this by placing the Ontime function call inside the procedure it calls.

I have a sheet called &quot;BIDS&quot;. I have added two Modules to the VBA project. One called &quot;Globals&quot; and the other called &quot;Subs&quot;.

Place this code in the &quot;Globals&quot; module:

Global OldBid As Variant
Global MyWorkBookName As String

Place this code in the &quot;Subs&quot; module:

Private Sub CheckValue()
If Globals.OldBid <> Workbooks(Globals.MyWorkBookName).Sheets(&quot;BIDS&quot.Range(&quot;B2&quot.Value Then
Workbooks(Globals.MyWorkBookName).Sheets(&quot;BIDS&quot.Range(&quot;B3&quot.Value = Globals.OldBid
End If

Globals.OldBid = Workbooks(Globals.MyWorkBookName).Sheets(&quot;BIDS&quot.Range(&quot;B2&quot.Value
Application.OnTime Now + TimeValue(&quot;00:00:01&quot, &quot;CheckValue&quot;
End Sub

Public Sub StopChecking()
Application.OnTime Now + TimeValue(&quot;00:00:01&quot, &quot;CheckValue&quot;, schedule:=False
End Sub

Place this code in the &quot;ThisWorkbook&quot;:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Subs.StopChecking
End Sub

Private Sub Workbook_Open()
Globals.MyWorkBookName = ActiveWorkbook.Name
Globals.OldBid = Workbooks(Globals.MyWorkBookName).Sheets(&quot;BIDS&quot.Range(&quot;B2&quot.Value
Application.OnTime Now + TimeValue(&quot;00:00:01&quot, &quot;CheckValue&quot;
End Sub



This code will compare the value in cell B2 to the global variable &quot;OldBid&quot;. When they differ it will place the &quot;OldBid&quot; in cell B3.

If you have any trouble getting this to work I can send you the excel file. Just reply with your email address.

Hopefully the code is simple enough for you to adjust it for your spreadsheet.