Manipulating the incoming numbers

Discussion regarding the spreadsheet functionality of Bet Angel.
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”