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!
Manipulating the incoming numbers
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.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
I encountered the same problem , Wolf,that the speed of my sheets slowed down drastically the more it was saved even though those saves were generally just a small tinker with the code and nothing saved to the worksheets. The workbook size was also increasing and I just couldn't figure out what was causing the increase in size or slowdown of the sheet. In the end I just figured whatever was happening was above my paygrade and simply exported the vba into a blank new sheet and that got things back to normal.
I'll take that as a vote for dont save the workbook unnecessarily.spreadbetting wrote: ↑Fri Sep 28, 2018 4:39 pmI encountered the same problem , Wolf,that the speed of my sheets slowed down drastically the more it was saved even though those saves were generally just a small tinker with the code and nothing saved to the worksheets. The workbook size was also increasing and I just couldn't figure out what was causing the increase in size or slowdown of the sheet. In the end I just figured whatever was happening was above my paygrade and simply exported the vba into a blank new sheet and that got things back to normal.
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
Ditto.Wolf1877 wrote: ↑Fri Sep 28, 2018 4:49 pmI'll take that as a vote for dont save the workbook unnecessarily.spreadbetting wrote: ↑Fri Sep 28, 2018 4:39 pmI encountered the same problem , Wolf,that the speed of my sheets slowed down drastically the more it was saved even though those saves were generally just a small tinker with the code and nothing saved to the worksheets. The workbook size was also increasing and I just couldn't figure out what was causing the increase in size or slowdown of the sheet. In the end I just figured whatever was happening was above my paygrade and simply exported the vba into a blank new sheet and that got things back to normal.
I'm doing less and less in vba these days, i've been doing my processing elsewhere and just using Excel to analyse the CSV's I'm creating.
But I've found a couple of sheets I was using (pretty big, 40k rows x 1500 cols) stared giving out of memory errors when I tried to loading them. Click ok & they still loaded thankfully. I've got 16Gb...and TaskManger was showing Excel getting up to about 3.5Gb before it bugged out, so it's not a total RAM issue and 64bit excel should use way more than that, 32 bit was 2Gb max.
The solution, copy and paste the content to a new sheet, just as you've found.
The slowing macros are something I've had too, the data collection blazes away...you go back and look at at teatime, and it's logging 1 every 3 or 4 seconds. There doesn't appear to be any serious memory leaks going on, but I do wonder how efficiently it manages the call stack. VBA was always a bit of a hacked VB3/4(?) that they squeezed into Excel and Word, I've never really had much confidence in it. It's a good tool, I'm not knocking it and doing things differently can be a pita sometimes unless there's a real need to, but it has limitations and 'funnies'.
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
It is, and I often wonder was could have been achieved with the combined develepment hours people have invested. I wouldn't want new people to think that tech was essential though, it's just an option that suits me because I don't really have a massive interest in sport, but I do love a good puzzle and making things.