hi Shaun .. learning all the time here, thanks
Basically it sees OldVal as "empty"
So it's just not seeing the numbers from BA
cheers
paul
Manipulating the incoming numbers
thanks nigelk
What I am trying to achieve in this instance is to monitor a col ie "H" and when the numbers change to have the new updated value minus the old value put into col AN ..
so if H9 is 5.8, then it changes to 6.0, then AN9 should show 0.2, if it then changes to 5.5 then AN9 should show -0.5 and so on .. and of course during in-play this needs to happen fast .. and continously ..
thanks
Paul
What I am trying to achieve in this instance is to monitor a col ie "H" and when the numbers change to have the new updated value minus the old value put into col AN ..
so if H9 is 5.8, then it changes to 6.0, then AN9 should show 0.2, if it then changes to 5.5 then AN9 should show -0.5 and so on .. and of course during in-play this needs to happen fast .. and continously ..
thanks
Paul
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
There's always several ways to code a problem, but just to get something working you could do something like this code fragment. If you have trouble with code it's often best to strip it back to something super simple, if not expecially efficient, and work up from there adding sophistication.
The biggest potential problem I see is that the refresh rate is so fast that your figures in col AN (the price change?) will always appear to be zero. With that in mind the code below has an additional 'IF' which will mean it only updates if the price has changed, basically freezing the last change figure. Just remove the If & End If statements if you don't want it to work like that.
This code is inefficient as it will be called when anything changes anywhere, but as I said...let's get something working and build it up.
(I haven't tried this so E&OE)
Try that.
Next step would be to rename this proceedure eg replace "Worksheet_Change(ByVal Target As Range)" with something like 'UpdatePriceChanges()" and then insert some more code to call this only when your specific column changes. eg.
It's not pretty and it's not clever but it might get you moving. As I said it's untested so I hope it works.
The biggest potential problem I see is that the refresh rate is so fast that your figures in col AN (the price change?) will always appear to be zero. With that in mind the code below has an additional 'IF' which will mean it only updates if the price has changed, basically freezing the last change figure. Just remove the If & End If statements if you don't want it to work like that.
This code is inefficient as it will be called when anything changes anywhere, but as I said...let's get something working and build it up.
(I haven't tried this so E&OE)
Code: Select all
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Disable event monitoring so that changes don't retrigger Worksheet_Change
Application.EnableEvents = False
'Now update the price change figure if the price has changed, H = New prices, AN = price changes, AO = Old prices
If Range("AO9").Value <> Range("H9").Value then
'Store the change from the old saved price
Range("AN9").Value = Range("AO9").Value - Range("H9").Value
'Store current price as the old price
Range("AO9").Value = Range("H9").Value
End if
If Range("AO10").Value <> Range("H10").Value then
'Store the change from the old saved price
Range("AN10").Value = Range("AO10").Value - Range("H10").Value
'Store current price as the old price
Range("AO10").Value = Range("H10").Value
End if
...etc
'Enable event monitoring so that changes trigger Worksheet_Change
Application.EnableEvents = True
End Sub
Next step would be to rename this proceedure eg replace "Worksheet_Change(ByVal Target As Range)" with something like 'UpdatePriceChanges()" and then insert some more code to call this only when your specific column changes. eg.
Code: Select all
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H9:H16")) Is Nothing Then
Call UpdatePriceChanges
End If
End Sub
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
I think the flaw in your logic came down to this...
OldVal = Target.Value
code code code code code
Range("AN9") = Target.Value - OldVal
It would always be zero.
It's sometimes just a matter of writing down first how you'd do it on paper.
eg
Look at the new price
Is is different to the price I wrote down earlier?
If so, calc the change
Write down the 'old' price so I can remember it later
Repeat
...what was missing was writing down the old price to compare with the new one.
All coding is like that really, just re-doing steps that used to be done manually in ledgers. The problem people have though is that they discount steps which are done subconsciously such as remebering previous things, which of course a computer needs to be told to do. Basically you have to treat a computer like an idiot and tell it everything. Don't tell it what you do, tell it how you think, if that makes sense.
OldVal = Target.Value
code code code code code
Range("AN9") = Target.Value - OldVal
It would always be zero.
It's sometimes just a matter of writing down first how you'd do it on paper.
eg
Look at the new price
Is is different to the price I wrote down earlier?
If so, calc the change
Write down the 'old' price so I can remember it later
Repeat
...what was missing was writing down the old price to compare with the new one.
All coding is like that really, just re-doing steps that used to be done manually in ledgers. The problem people have though is that they discount steps which are done subconsciously such as remebering previous things, which of course a computer needs to be told to do. Basically you have to treat a computer like an idiot and tell it everything. Don't tell it what you do, tell it how you think, if that makes sense.
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
Not really adding much to this thread in terms of code but following on from what Shaun said, write it down first. Instead of paper though I've got a good sized whiteboard which I also use for tutoring. I find this makes me more productive as I'm not scribbling on 1,000 random pieces of paper and I can take pics on my phone of anything I draw which I find to be super useful. £3 from my local Wilkos.
Break things down one step at a time and buy different coloured pens. Really helps me break down problems and would highly recommend!
Break things down one step at a time and buy different coloured pens. Really helps me break down problems and would highly recommend!
How about comparing the current val in col H, comparing it with the old value in col AN, then storing the result in another column?
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("h9:h16")) Is Nothing Then
oldVal = Target.Offset(0, 32) 'refers to col AN (32 columns on from col H)
currval = Target.Value
diff = Target.Value - oldVal
Target.Offset(0, 32) = currval
Target.Offset(0, 33) = diff
End If
End Sub
Whenever I see peoples vba code snippets on this site I always notice that they are usually using Range extensively. I personally dont think Range is a particularly efficient way of manipulating cell values in VBA and I also noticed that some people have said that fetching/loading cell values into memory is slower than normal code which personally I dont find but I code in a different way.
I think people tend to start out as excel users, record macros manually and then modify/reuse the recorded code in their own VBA. I was a programmer before excel was even invented so I think I tend to approach things slightly differently though I do use macro recordings sometimes.
I tend to code by addressing cells using 3 dimensional co-ordinates (sheet number/row number/col number) instead of using Range.
This also has the additional advantage that you can save values from Bet Angel directly into a different sheet if you want. Also I can use numeric variables for looping.
e.g: I would tend to code
Range("AN9").Value = Range("AO9").Value - Range("H9").Value
as
Sheets(1).Cells(9,40).Value = Sheets(1).Cells(9,41).Value - Sheets(1).Cells(9,8).Value
.... or more likely I would tend to use variables for the sheetno, rowno, colno coordinates (useful for coding loops and procedures).
sheetno = 1
rowno = 9
Sheets(sheetno).Cells(r, 40).Value = Sheets(sheetno).Cells(rowno, 41).Value - Sheets(s).Cells(rowno, 8).Value
.... or even more likely I might for example calculate if race volume has changed by calculating the difference from the previous matched amount and then storing the race volume in a completely separate sheet for comparison on the next cycle. This is where 3 dimensional cell references really come into their own as values can be moved anywhere in the workbook. I think using numeric sheet numbers are probably slightly faster than using sheet names but that would be pretty trivial.
matched_diff = Sheets(s).Cells(2, 3).Value - Sheets("RaceSummary").Cells(r, 5).Value
Sheets("RaceSummary").Cells(r, 5).Value = Sheets(s).Cells(2, 3).Value
I basically think using Range slows things down because it performs a whole load of unnecessary excel functionality that mimics manual excel user activity but which is not needed for simply assigning values into or pulling values from cells. I havent bothered to put this to performance testing though.
So endeth the Excel coding sermon of St Wolf1877.
I think people tend to start out as excel users, record macros manually and then modify/reuse the recorded code in their own VBA. I was a programmer before excel was even invented so I think I tend to approach things slightly differently though I do use macro recordings sometimes.
I tend to code by addressing cells using 3 dimensional co-ordinates (sheet number/row number/col number) instead of using Range.
This also has the additional advantage that you can save values from Bet Angel directly into a different sheet if you want. Also I can use numeric variables for looping.
e.g: I would tend to code
Range("AN9").Value = Range("AO9").Value - Range("H9").Value
as
Sheets(1).Cells(9,40).Value = Sheets(1).Cells(9,41).Value - Sheets(1).Cells(9,8).Value
.... or more likely I would tend to use variables for the sheetno, rowno, colno coordinates (useful for coding loops and procedures).
sheetno = 1
rowno = 9
Sheets(sheetno).Cells(r, 40).Value = Sheets(sheetno).Cells(rowno, 41).Value - Sheets(s).Cells(rowno, 8).Value
.... or even more likely I might for example calculate if race volume has changed by calculating the difference from the previous matched amount and then storing the race volume in a completely separate sheet for comparison on the next cycle. This is where 3 dimensional cell references really come into their own as values can be moved anywhere in the workbook. I think using numeric sheet numbers are probably slightly faster than using sheet names but that would be pretty trivial.
matched_diff = Sheets(s).Cells(2, 3).Value - Sheets("RaceSummary").Cells(r, 5).Value
Sheets("RaceSummary").Cells(r, 5).Value = Sheets(s).Cells(2, 3).Value
I basically think using Range slows things down because it performs a whole load of unnecessary excel functionality that mimics manual excel user activity but which is not needed for simply assigning values into or pulling values from cells. I havent bothered to put this to performance testing though.
So endeth the Excel coding sermon of St Wolf1877.
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
I was just keeping it simple.Wolf1877 wrote: ↑Fri Sep 28, 2018 1:46 pmI tend to code by addressing cells using 3 dimensional co-ordinates (sheet number/row number/col number) instead of using Range.
This also has the additional advantage that you can save values from Bet Angel directly into a different sheet if you want. Also I can use numeric variables for looping.
I'd usually use it in a context something like this with a variable sheet, variable col & variable row. I have a function that turns numbers into column letters and one that tidies a numeric variable (if nec).
SourceSheet = "Bet Angel"
If CurrMktNum <> 1 Then SourceSheet = SourceSheet & " (" & Trim(Str(CurrMktNum)) & ")"
While Sheets(SourceSheet).Range(fnColTxt(DataRowNum) & fnLineTxt(DataLineNum)).Value <> 0
...etc
Or more usually
CellRange = fnColTxt(FromRowNum) & fnLineTxt(FromLineNum)
Sheets(SourceSheet).Range(CellRange)......
Or if I'm refering to a range not a single cell....
CellRange = fnColTxt(FromRowNum) & fnLineTxt(FromLineNum) & ":" & fnColTxt(ToRowNum) & fnLineTxt(ToLineNum)
Sheets(SourceSheet).Range(CellRange)......
....etc
It's not right, and it's not wrong either, you just get used to doing things one way I guess.
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
I haven't tried it but I don't thing the performance would be much different either.
tbh I keep all writing to excel sheets to an absolute minimum. Using cells to store variables is really slow.
If I must write to a sheet...eg data collection, I store everything in arrays and then write it to the sheet when it's flipping between markets
and time isn't so precious.
...I've got ANOTHER cold cup of coffee now Wolf thanks to you distracting me.
tbh I keep all writing to excel sheets to an absolute minimum. Using cells to store variables is really slow.
If I must write to a sheet...eg data collection, I store everything in arrays and then write it to the sheet when it's flipping between markets
and time isn't so precious.
...I've got ANOTHER cold cup of coffee now Wolf thanks to you distracting me.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
I only use Range because that was the way I was told when starting out and for the novice it's a lot easier to relate to P3 than using co-ordinates (3,19) whatever. Personally I wish I'd taken the time to start coding using co-ordinates as like you say the more coding you do the more you start using things like arrays and loops so co-ordinates make things simpler, but I do so little coding other than tweaking things it's just far too late for me to bother converting all my code nowWolf1877 wrote: ↑Fri Sep 28, 2018 1:46 pmI think people tend to start out as excel users, record macros manually and then modify/reuse the recorded code in their own VBA. I was a programmer before excel was even invented so I think I tend to approach things slightly differently though I do use macro recordings sometimes.
I'm still even using php in a web browser to run parts of some of my 'bots' that I wrote pre betting software , so I think the chances of me changing are pretty slim tbh.
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
Worth noting that the real performance killer is named ranges. Even if they just exist on the sheet and not used in your code, they cause Excel to go into deep recalc mode rather than the pretty good optimised one. I did the 'right' thing, named all my ranges to make the code nice and maintainable and 'defensive'...and it ran like absolute dog.
I reverted to what I always used to do, and delared some public variables at the top of the code for the important cell references. All visible in one nice tidy block and one never gets missed burried deep in a block of code somewhere if anything needs to change.
I reverted to what I always used to do, and delared some public variables at the top of the code for the important cell references. All visible in one nice tidy block and one never gets missed burried deep in a block of code somewhere if anything needs to change.
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
Sorry about the coffee Shaun. If you've just got a fresh cup drink it before you read this!ShaunWhite wrote: ↑Fri Sep 28, 2018 2:58 pmI haven't tried it but I don't thing the performance would be much different either.
tbh I keep all writing to excel sheets to an absolute minimum. Using cells to store variables is really slow.
If I must write to a sheet...eg data collection, I store everything in arrays and then write it to the sheet when it's flipping between markets
and time isn't so precious.
...I've got ANOTHER cold cup of coffee now Wolf thanks to you distracting me.
Regarding using cells to store variables being really slow. This is not my experience at all though I'd tend to agree that it is probably slower than in memory vba variables. There has to be a reason why you are experiencing slow cell value I/O in comparison to me. I've recenty upgraded my hardware running on a 20ms refresh in Excel and I'm running a shed load of code and storing some data in excel between frames and I havent found storing data in excel cells a delaying issue at all. I know you run your macro as a single continuous macro where mine is triggered by worksheet change so anything I want to save between frames has to either be in excel cells or externally to excel. I actually save some non essential stuff and I havent bothered stripping it out as the speed effect has not been an issue at all. Remember that an open excel workbook itself is in memory so writing to an excel cell should really not be massively different to storing in a vba variable other than the overhead of whatever excel is doing. I dont have any excel cell formula or computations whatsover, every single calculation I need is done in VBA. You may possibly get a problem if you are are short of RAM and your machine is forced to page the workbook into and out of memory from the hard drive.
Years ago I developed some excel vba code to visually map draw out some complex processes from some source code. One thing that quickly became apparent is it became slower and slower as the macro got repeatedly used. My code was redrawing diagrams by changing cell attributes like font colours, font sizes and cell borders. Repeatedly doing that and saving the workbook at the end of each session slowed things down massively. Solution I found to the continuous performance degradation was to copy the worksheet(s) to a new sheet within the workbook, delete the original worksheet then rename the new worksheet back to the original worksheet name. Its as though in the old worksheet, continuous layers of text font and border styles generated by my macro been applied to the cells and it was somehow slowing excel down. When the worksheet was copied to a new seemingly identical sheet the macro perfomance was 10 times faster. The moral of this is I would also avoid saving workbooks and continuously reopening and reusing the workbook over and over especially if you macros apply any styles to the cells. Create a new workbook how you want it then continuously reopen it but dont save the workbook at the end of each session so you start each session with the exact sane workbook. If you are getting performance degradation like I experienced years ago then maybe try copying the worksheet, deleting the original worksheet then renaming it and you may possibly find that things run faster. Other than that try avoiding using Range or at least develop some metrics to measure the speed difference between the 2 methods of assignment. And check your Ram usage.