excel efficiency

[Yorkie]

Hi guys

Is there someone out there with a good understanding of how excel works and what makes it efficient or not?

Currently I have 1 workbook that contains the worksheets for 5 systems.
Each system has:

A Control sheet with the system settings and parameters
A Trigger sheet linked to BA
A status sheet showing the P/l of the system and a few other bits of info.
A Results sheet. ( required to be seperate)
A temp results sheet where the days results are copied to at the end of the day ready for the next day.

Each Trigger sheet has it's own vba attached for copying the results at the end of the day and reloading the market etc.

Is this the most efficient way?

Is it more efficient for excel to have 1 control sheet with the setting for each system contained on it.
Is it more efficient to have all systems on 1 trigger sheet with the start cells staggered of course. This would mean combining the vba which would make it a fairly lengthy bit of code. (probably more code than is needed but that's not my strong point)
Is it more effiecient to put all the system status details on a single sheet.

Or is it simply more effiecient to have 5 seperate workbooks and so 5 instances of excel running.

Basic question i suppose is small number of large sheets vs large number of smaller sheets, or multiple instances of excel.

Also how much impact does conditional formatting have?

Cheers for any answers

Yorkie

[2020vision]

Does this help yorkie?

Basic question i suppose is small number of large sheets vs large number of smaller sheets, or multiple instances of excel.

http://www.gruss-software.co.uk/forum/viewtopic.php?t=4484

Hope so - Michael

[Yorkie]

Hi Michael

Yes it partly helps and it's something i've seen before but it only seems to answer the question of which is the most efficient way for ba to interact with excel. What i'm also trying to determine is which is the best way to use the computers resources. The pc that ba is on is running at a constant 100% cpu and i suppose my question is more to do with the how excel runs efficiently rather than how ba interacts with it.


Gary's Quote
"If you're using the latest Beta then I recommend using the Excel quick link feature. This will open the 4 workbooks in 4 separate instances of Excel. This is definitely more efficient as Betting Assistant can only update one worksheet per Excel instance at a time. The updates aren't ignored they are just delayed by a fraction of a second.

So the rule is the fewer worksheets in each Excel instance the better."

This is fine but if the multiple instances of excel cause more stress on the pc cpu/ram, then the benefits on the ba side could be outweighed by the impact on the pc, making the computer the bottleneck.

I also think that many of us would benefit from being able to optimize our spreadsheets so if anyone has knowledge of the best do's and don't's then speak up.lol

Yorkie

[osknows]

It's not easy to answer as there are many factors which could affect performance including processor (single core/multi core), RAM, version of excel being used, volatile calcs in Excel, and refresh rates in BA to name a few.

If you are using multi core processor and Excel 2007 or above I don't think you will experience many problems with multiple instances of Excel running.

This link may help and has some code to measure speed of code http://msdn.microsoft.com/en-us/library/aa730921.aspx

[GaryRussell]

From a VBA perspective you need to take a look at any loops in your code, eg. For...Next, Repeat...Until, etc. Think about how you can reduce the number of iterations required or do it without using a loop.

If you are writing to multiple cells from VBA see if you could write them in one operation by using a range instead of one at a time as this is quite slow and CPU intensive.

[MatGreenaway]

Hi, How can I use a range in VBA - I have this as part of my VBA:

Cells(5, 26).Value = Cells(5, 6).Value
Cells(6, 26).Value = Cells(6, 6).Value
Cells(7, 26).Value = Cells(7, 6).Value
Cells(8, 26).Value = Cells(8, 6).Value
Cells(9, 26).Value = Cells(9, 6).Value
Cells(10, 26).Value = Cells(10, 6).Value
Cells(11, 26).Value = Cells(11, 6).Value
Cells(12, 26).Value = Cells(12, 6).Value... etc

I'm hoping you tell me its simple - I'm still learning on VBA!

Also do you know if Excel 2007 would be any more efficient than Excel 2003?

Thanks,
Mat

[Spike]

You can use a simple loop- eg:
For i = 5 to 12 (or whatever)
cells (i, 26) = cells (i, 6)
Next i


However there is probably a better way which doesn't use loops, hopefully someone more proficient will enlighten us.

If you're running excel 2007 then the quickest way to make your sheets run better it to switch versions to 2003, when I tested this I found VBA running 5 times faster in 2003 than on 2007 on fairly quick machines.

[doris_day]

If you're running excel 2007 then the quickest way to make your sheets run better it to switch versions to 2003, when I tested this I found VBA running 5 times faster in 2003 than on 2007 on fairly quick machines.

That's interesting and possibly could be something to do with my underperformances. Seems they may have come along when I upgraded to 2007. Have you any further info on relative performances of Excel versions (or other similar data) ?

[Spike]

Basically when I upgraded to 2007 I noticed that complex macros that I was using to sort through large sets of results were far slower running than they had been, so I tested how long macros were taking to run using a bit of VBA I found somewhere on t'internet that times how long it takes your code to execute. Like I said, the upshot was that a macro that took 0.2 secs (say) in 2003 would take 1 whole second in 2007. I ran the same test on two servers and a couple of laptops- all of which are a decent spec and found that although some were faster than others in all cases 2003 was about 4 or 5 times faster. Needless to say I immediately moved back to 2003 (call me fickle but I like having money). Try it for yourself, I'm sure you'll see a difference.

[MatGreenaway]

Thanks for the quick reply. I had considered a for/next but Gary had talked about a range to copy the cell data I was after.

And much appreciate the comments on Excel 2007 vs 2003 - I think I'll stop on 2003 for the moment. I run Excel 2003 SP3.

Thanks,

[throwmeadisc]

Hi, How can I use a range in VBA - I have this as part of my VBA:

Cells(5, 26).Value = Cells(5, 6).Value
Cells(6, 26).Value = Cells(6, 6).Value
Cells(7, 26).Value = Cells(7, 6).Value
Cells(8, 26).Value = Cells(8, 6).Value
Cells(9, 26).Value = Cells(9, 6).Value
Cells(10, 26).Value = Cells(10, 6).Value
Cells(11, 26).Value = Cells(11, 6).Value
Cells(12, 26).Value = Cells(12, 6).Value... etc

I'm hoping you tell me its simple - I'm still learning on VBA!

Also do you know if Excel 2007 would be any more efficient than Excel 2003?

Thanks,
Mat

I would use something like this instead:

Code: Select all

Cells(5, 26).Resize(1, 6).Value = Cells(5, 6).Resize(1, 6).Value


Cheers,

Al

[osknows]

Hi, How can I use a range in VBA - I have this as part of my VBA:

Cells(5, 26).Value = Cells(5, 6).Value
Cells(6, 26).Value = Cells(6, 6).Value
Cells(7, 26).Value = Cells(7, 6).Value
Cells(8, 26).Value = Cells(8, 6).Value
Cells(9, 26).Value = Cells(9, 6).Value
Cells(10, 26).Value = Cells(10, 6).Value
Cells(11, 26).Value = Cells(11, 6).Value
Cells(12, 26).Value = Cells(12, 6).Value... etc

I'm hoping you tell me its simple - I'm still learning on VBA!

Also do you know if Excel 2007 would be any more efficient than Excel 2003?

Thanks,
Mat

there are several ways to do this

Range("AA5:AA50").Value = Range("F5:F50").Value

or

Range("F5:F50").Offset(0, 20).Value = Range("F5:F50").Value

or if you intend to do some calcs on the range before writing back to excel you can use this which reads in a range once, performs calcs in array and writes back whole array to excel once.

Set rng = Range("F5:F50").Value
load_array = rng
'do calcs in load_array
Range("AA5:AA50").Value = load_array

[doris_day]

Thanks for that Spike. I'll change back and see what happens. None of my macros datamine as such but I think if there's any doubt about speed in any respect then its probably better working with a version that's known to be faster. Like most software, over the years they often get fatter (like me) and slower (like me).
Thanks for the tip.

[Spike]

The VBA I use on my bots is fairly simple too- it's just when I was running long loops that I noticed the difference. I really agree that speed is crucial in running, so my reasoning was that if it saves me 0.1 of a sec every time BA refreshes that's got to be worth a lot of extra matches. Also, seeing the macros run so slowly made me wonder whether formulae might also be compromised. I don't know for sure but I'd rather not take the chance.

It's not just me that's noticed this- if you do a web search you'll see loads about excel 2007 being deadly slow.

Anyhow, I hope it helps you out of your flat patch.

[alrodopial]

It looks that there is a performance issue at same specific cases:
Read/write in sheet
Charting
worksheetfunction inside VBA

http://excelicious.wordpress.com/2009/0 ... peed-test/


and for few performance coding tips:

http://blogs.msdn.com/excel/archive/200 ... tices.aspx

No problem if calculations are done inside VBA