Monitoring cell changes?

[danjuma]

Some thread back (can't find the thread now), someone posted a link to a spreadsheet that monitors the back odds as they change and record the difference between the current odds and previous odds.

There are two worksheets in the spreadsheeet. The VBA code (which I have pasted below) is in Sheet 1, and some of the cells referenced in the code are in Sheet2. The code is below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then

Application.EnableEvents = False

CurrentPRICE = Sheets("Sheet1").Range("F5").Value
PreviusPRICE = Sheets("Sheet2").Range("B10").Value

Sheets("Sheet2").Range("E10") = CurrentPRICE - PreviusPRICE

'Store the current price for next refresh - calculation in B10-Sheet2
Sheets("Sheet2").Range("B10") = Sheets("Sheet1").Range("F5")

Application.EnableEvents = True

End If

End Sub

The code basically compares the value in cell F5 in Sheet 1 with the new value in cell F5 at the next update and records it in cell E10 in Sheet 2. The code as it is monitors just one cell - F5 in Sheet1.

What I want is to monitor all cells in F column with a value. Basically I want to monitor the changes in the back odds for all the horses in a race.

Would somebody be so kind to amend the code for me please to apply to all the cells with odds in F column, or come up with a better solution?

Basically, I want to monitor the back odds as they increase or decrease and get my excel formular to trigger an action depending on whether the odds have increased or decreased.

Many thanks
Dan

[danjuma]

Looks like people are too busy these days to assist.

Anyway, riddle solved.

Cheers

[osknows]

The following code does what you need. Just change the ranges
accordingly




Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

CurrentPRICE = Sheets("Sheet1").Range("F5:F10")
PreviousPRICE = Sheets("Sheet1").Range("B5:B10")
Set CalcPRICE = Sheets("Sheet1").Range("E5")
Set StoredPRICE = Sheets("Sheet1").Range("b5")

r1 = 0
For Each c1 In CurrentPRICE
r1 = r1 + 1
r2 = 0
For Each c2 In PreviousPRICE
r2 = r2 + 1
If r1 = r2 Then

CalcPRICE.Offset(r1 - 1, 0).Value = c1 - c2
StoredPRICE.Offset(r1 - 1, 0).Value = c1

End If
Next c2
Next c1

Application.EnableEvents = True

End Sub

[danjuma]

Many thanks osknows.

Though, I have already sorted it out. But thanks for your response, greatly appreciated.

Cheers.

Dan

[osknows]

Ignore my code above. Although it works I've realised that all VBA code for BA and excel integration needs to be optimized for speed. Based on a refresh rate of 200ms my code above averages 400ms

A quicker solution seems to be of the form

Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For r = 5 To 55
Select Case .Cells(r, 51).Value
Case "", Is > .Cells(r, .Value
.Cells(r, 51).Value = .Cells(r, .Value
End Select
Next
End With
Application.ScreenUpdating = True
Application.EnableEvents = True

[danjuma]

osknows,

Many many thanks for going the extra mile to check your codes for optimum effect. You seem to know your onions when it comes to VBA, and I am sure you are going to be a valuable asset to this forum.

Welcome to the forum , and may you continue to have the willingness and motivation to assist we VBA newbies in future.

Cheers
Dan

[jokerjoe]

It's also faster to read a whole block of cells into an array and perform calculations on that array, and then write back an array to a range. There's a performance hit with every read to and from a worksheet and VBA, doing it that way cuts it down to two operations.

eg

Code: Select all

Dim TEMP as Variant
TEMP  = Range(...)
' calculations here, eg FOR NEXT loop generates TEMP2 array
range(...) = TEMP2


[danjuma]

It's also faster to read a whole block of cells into an array and perform calculations on that array, and then write back an array to a range. There's a performance hit with every read to and from a worksheet and VBA, doing it that way cuts it down to two operations.

eg

Code: Select all

Dim TEMP as Variant
TEMP  = Range(...)
' calculations here, eg FOR NEXT loop generates TEMP2 array
range(...) = TEMP2


Wow! Another 'VBAist' that definitely knows his onions.

[osknows]

It's also faster to read a whole block of cells into an array and perform calculations on that array, and then write back an array to a range. There's a performance hit with every read to and from a worksheet and VBA, doing it that way cuts it down to two operations.

eg

Code: Select all

Dim TEMP as Variant
TEMP  = Range(...)
' calculations here, eg FOR NEXT loop generates TEMP2 array
range(...) = TEMP2


Genius thinking there! Here is some code which is very quick:

Code: Select all

Application.EnableEvents = False
    Cells(2, 10).Value = "U" 'updates balance and exposure
           
    Set rng = Range("h5:az55")
    strArray = rng
    For i = 1 To UBound(strArray)
            Select Case strArray(i, 44)
            Case "", Is > strArray(i, 1)
            strArray(i, 45) = strArray(i, 1)
            Case Else
            strArray(i, 45) = strArray(i, 44)
            End Select
           
    Next i
    Range("ay5:ay55").Value = Application.WorksheetFunction.Index(strArray, 0, 45)
           
Application.EnableEvents = True


I don't know of a way to make an dimensional array using a non-contiguous range without looping each range seperately. Therefore, I have created a larger array Range("h5:az55"). The code loads in all data to the array where

col H is Lay Odds 1 [strArray(i, 1) ]
col AY is lowest Lay Odds 1 achieved [strArray(i, 44) ]
strArray(i, 45) is where I hold the calculation to pass out at end- if current lay odds is lower than lowest lay odds replace otherwise keep lowest lay odds

Also advisable to keep all calcs and ranges in same worksheet for this

[osknows]

You also need to add the following

Dim strArray, rng As Range
Dim i As Integer


I have mine wrapped up with others as:

Option Explicit

Dim dtStart As Long
Dim dtEnd As Long
Dim refreshTimes As Collection
Dim elapsedTime As Long
Dim totRefresh As Long
Dim refreshed As Boolean
Dim refreshCount As Long
Dim strArray, rng As Range
Dim i As Integer
Const avgCount As Integer = 10

[osknows]

I can't take credit for this as it's based on the 'Place bets on a selection of horses' template

but it does contain working code to track the lowest price of lay odds 1. Feel free to use and amend as required

http://www.mediafire.com/?sharekey=e2c0 ... b5aa27078d

[Norwegian Would]

I can't take credit for this as it's based on the 'Place bets on a selection of horses' template

but it does contain working code to track the lowest price of lay odds 1. Feel free to use and amend as required

http://www.mediafire.com/?sharekey=e2c0 ... b5aa27078d

This forum is an oracle for ideas!

I notice this template contains some extra fields:

SP
Saddle cloth
Form
Days since last run
Jockey
Trainer
Age/Weight
Jockey claim
Wearing (Headgear)
Stall draw

These can be viewed in a pop-up box when you hover over the nags name on the "Grid".

Two other fields
- Colour / Sex
- Official Rating

can be found in the pop-up. Doe's any good peeps know if / how these can be captured/ shown in a SS / workbook?

Cheers

[Ian]

Tick the box(es) for the field(s) you want adding on the pop-up box when you start to log the prices in Excel.

[Norwegian Would]

Tick the box(es) for the field(s) you want adding on the pop-up box when you start to log the prices in Excel.

Thanks Ian,
Sorry I did not explain properly. I DO have these displayed on the worksheet...
SP
Saddle cloth
Form
Days since last run
Jockey
Trainer
Age/Weight
Jockey claim
Wearing (Headgear)
Stall draw

No problem.

It's the OTHER two fields that I wondered about

- Colour / Sex
- Official Rating

These are found when you hover the mouse over the nags name. This is "static data" that is collected - but can they be shown on the Excel worksheet?

[GaryRussell]

Colour/sex and official rating cannot be displayed in Excel in the current version, it will be available in a future release.