Manipulating the incoming numbers

[sherman]

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

[sherman]

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

[ShaunWhite]

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)

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

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.

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

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.

[ShaunWhite]

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.

[sherman]

Thanks VERY much Shaun for your time and understanding .. this gives me plenty to chew over ..
Cheers
Paul

[CallumPerry]

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!

[nigelk]

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

[ShaunWhite]

I'm with you callum on that method.

[Wolf1877]

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.

[ShaunWhite]

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.

I was just keeping it simple.

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]

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.

[spreadbetting]

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 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 now

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]

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.

[ShaunWhite]

I was a programmer before excel was even invented

In my day you only got a command line, MBASIC embedded in DOS and 8k if you were lucky.
Kids today eh Wolf, they don't know how good things are.

[Wolf1877]

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.

Sorry about the coffee Shaun. If you've just got a fresh cup drink it before you read this!

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.