Manipulating the incoming numbers

Discussion regarding the spreadsheet functionality of Bet Angel.
CallumPerry
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!
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

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
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I'm with you callum on that method.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

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.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Wolf1877 wrote:
Fri Sep 28, 2018 1:46 pm
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.
User avatar
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. :cry:
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Wolf1877 wrote:
Fri Sep 28, 2018 1:46 pm
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.
User avatar
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.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Wolf1877 wrote:
Fri Sep 28, 2018 1:46 pm
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
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

ShaunWhite wrote:
Fri Sep 28, 2018 2:58 pm
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. :cry:
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.
spreadbetting
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.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

spreadbetting wrote:
Fri Sep 28, 2018 4:39 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. :)
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Wolf1877 wrote:
Fri Sep 28, 2018 4:49 pm
spreadbetting wrote:
Fri Sep 28, 2018 4:39 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. :)
Ditto.
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'.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Really interesting to see the different ways people approach the same problems.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

nigelk wrote:
Fri Sep 28, 2018 6:36 pm
Really interesting to see the different ways people approach the same problems.
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.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”