Self Spreadsheet Saboteur risks 10 years porridge

I used to put a little support contact details messagebox  in some of my more complex spreadsheets in the hope I may get a lucrative support gig.

This guy went a bit further.

He time bombed his VBA and locked the projects. Now its in court and it could be 10 years in prison and/or 250kUSD fine. Oops!

I wouldn’t accept a contractor keeping passwords, a proper external supplier maintaining their IP ok, but a contractor working on company files? no. And I personally wouldn’t lock my VBA either, if the client wants to do that fine, until they lose the password and I have to hack it…

 

 

Eusprig 2019

If you are interested in Excel, and therefore spreadsheets you could do a lot worse then spend a day immersed in the risks and challenges associated with them.

That’s where the European Spreadsheet Risk Group comes in with their annual conference, this year in that London.
—————————————–

The European Spreadsheet Risk Interest Group Conference (EuSpRIG 2019)
will be held at Browns Courtrooms, Browns Covent Garden, London, on
Thursday 11th and Friday 12th July 2019.

http://www.eusprig.org

Real life case studies of EUC control, deep dive into data analysis,
experience with skills transfer, and two tutorials on new features.

—————————————–

I can’t be there this year, but I’ve been a few times and would heartily recommend it to anyone with more than a passing interest in spreadsheets.

Even if the content does not seem immediately, directly relevant, a day in that environment will raise your awareness of the risks and dangers and have you thinking more cautiously next time you are building or changing a spreadsheet. Which is a very good thing.

cheers

simon

Office 2019 released

Does anyone care?

It sounds like it is pretty much just a freeze of the current Office 365.

Which as we know contains xlSlow, so I assume that’s not getting its performance fixed, ever.

Has anyone got it?

Is it any good?

I only just picked up a cheap 2016, so I can’t see me ‘upgrading’ to 2019 any time soon, if ever.

I’m doing a lot of SQL Server at the moment which is a nice change, and SSIS, which is less nice. But still better than the Access I was doing. I like the Access visual designer (a bit), but the rubbish SQL editor is frustrating. In the end I had all the SQL in notepad++ (colour coded key words, indenting and new lines (MS Access team take note – how hard can it be?). And looking at the visual designer for the joins. Not a bad way of working actually…

And I have been messing with RubberduckVBA, it seems that’s currently the best way to indent other peoples shitty VBA, to make it at least semi-readable. (Smart Indenter seems to be pining for the fjords with a combo of lack of VB6 love at MS and increased ‘security’.). (I am sure I used to have a VBA version of that somewhere).

Anyway, another shout out for the developExcel conf in a few weeks. I have been busy messing with VSTO and Addin Express in preparation. It looks like there are going to be quite a few of us (100 ish I believe).

Rach from the 2013 conf

said she would come if her sock drawer was in order.

cheers

simon

 

Excel 2016 performance – even interestinger

I’m on a crusade to understand what is going on with Excel 2016 performance.

In summary its bad, like more than 10 times slower than Excel 2010 bad.

ie BAD.

I am only just scratching the surface, so I am sure I’ll find areas where 2016 is better … at least I hope I will…

My little cell selection test gave Excel 2016 2.6secs, Excel 2010 0.14secs.

Which I appreciate is indeed more than 10x, I could calc it exactly but it would take a couple of life times in Excel 2016,

But of course these are different machines so I need to understand how much is due to the machine. so I wrote a C++ console app (so limited windows, or .net influence).

It just adds some (vaguely) random numbers, so a bit dependent on that library, but in principle I don’t see a glaring reason why it can’t give me an approximation of the difference in speed between two machines.

This is it:

for (int n = 0; n < 1000 * 1000 * 1000; ++n) {
result += -70 + (std::rand() % (60 – 25 + 1));
}

It runs 14 seconds on my XP box and 15 on Win 10, so I think you could say the machines are comparable, roughly.

So the 10x slowness is pure Win10/E2016 then?

Well its a bit early to say, I think there is a video/graphics element so I’m going to develop some more tests.

I mean as it stands Excel is so so bad you could develop in VSTO without noticing a performance drop (unless its worse too).

I’ll keep you posted, ping me if you want this console exe to benchmark your machine.

cheers

simon

Excel 2016 perf what the effing eff?

Well, I tried to resist, but shocked at the rank day to day performance of my shiny Win10E2016 toy I had to compare performance.

Obviously I haven’t done it properly, what would be the point in that?

But equally obviously I’m going to write about it as if it is indisputable fact.

I wanted a test that mimicked the slow UI response I was seeing so I selected the cells. I am in the throes of writing a more rounded benchmarking tool, but this was my first area of focus. Of course it ignores MTA etc etc, those tests are coming.

I’ll stick the code at the bottom. I would be delighted if you copied it into a new workbook module and ran it on your machine(s) and replied with the results and a hint about machine specs and Excel versions. Preferably in a clean Excel, not one shared with a workbook polluted with a quadzillion volatile VBA UDFs, or a million addins trapping the selectionChange event!

4 core i7 w 16GB RAM – W10E2016 – average of ten results 2.4 seconds

dual core i7 VM (with 500GB ram?(it thinks!)??) WXPE2010 av 0.14 secs

dual core i7 VM (with 500GB ram???) WXPE2003 av 0.12 secs

I ran it on a single screen in all cases, with just the VBA IDE visible. If the Excel grid is visible the performance is much worse.

I expected 2016 to be slower in this single threaded test, but not 10 times slower. What are they doing with all those cycles? (cryptomining???)

cheers

simon

Here is that code, please please take a minute and let me know your results

(if you are using a lapper be sure it does not have some work dodging power/performance reducing profile set)

Public Sub multiLoop()
Dim x As Integer
Dim t As Single

For x = 1 To 10
t = Timer

CellEntryTest 3.1

Debug.Print Timer – t
Next x
End Sub

Public Sub CellEntryTest(seed As Double)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

ws.Activate

ws.Cells.Clear

Dim x As Long

For x = 1 To 1000
ws.Cells(x, 1).Select
ActiveCell.Value = (seed + x) * 2 * Rnd(2)
Next x

End Sub

Feel free to chip in if you are convinced this is an invalid test.

If you are seeing sub 1 second response you could bump up the loop to 10k, that is what I had it on until I came to test 2016! (mention that in you reply, or just divide by 10)

 

Excel 2016 Performance

What’s your view on Excel 2016 performance and stability?

In particular Excel 2016 32bit on Win10 63 bit?

I’m talking about 365, so bang up to date, and what I am seeing is:

• Excel or other Office apps freeze for around 4 minutes, once or twice most days
• Too many styles will cause a workbook that would have dropped all formatting in 2010 will be unopenable and unrecoverable in 2016
• Too many conditional format will crash 2016 completely, where 2010 would be bearable, but stable.
• General calc speed seems slower
• UI is laggy, even with all Win10 bullshit turned off
• Excel VBA can completely freeze every running office application
• Opening everything in the same Excel instance is a monumental ball ache
  • Opening everything in its own one is not much better
  • Why can’t we have the 2003 behaviour of opening in last activate instance??
  • debuggering from VS is pants, I don’t want all my dodgy xlls loading into my working Excel.  So back to run excel /e
• Did YOU ask for cell selection change to be animated????????????
• Can’t paste charts as live links any more?

So the intermittent freezes and the instability are my main issues, I’m back to saving my work every 2 to 3 minutes like in Excel 2000. And spending a fair chunk of my life waiting for stuff to calculate.

Overall I’m thoroughly underwhelmed, 2003 is still the best ever, but 2010 is starting to feel like a not too shabby vintage too (I never tried 2013, was it any good?). I reckon 2003 would utterly fly on my 16gb i7, well apart from the single threadedness.

(the 63 bit was a typo, but somehow seems appropriate as the odd bit seems to get dropped here and there on this combo.) Sadly I see Office gradually choking Excel, or as its new name Word(tables edition).

What are your experiences?

Which is your favourite Excel?

Is 64 bit more stable?

cheers

simon

lets discuss it at DevelopExcel in October!

Excel Developer Conference 18th October 2018 London

Woo Hoo

There is going to be a major, major Excel extensibility conference in that London on 18th October 2018. This is THE big one. All the big guns from the main Excel extensibility tools and frameworks are going to be there speaking.

There will no better time in your lifetime to hear from the horses mouth the how and why of ExcelDNA, PyXLL, XLL + and the new Excel javascript APIs. Also sessions covering Power Query and VSTO and Add-in Express.

This is a community driven event, driven by a desire to get the top minds in the Excel extensibility space together.

The Date? Thursday 18th October 2018

The Location? Microsoft Reactor, 70 Wilson St, London EC2A 2DB

More info here

Bookings here

See you there…

(please reblog… and if you do, and you are coming, let us know we will link to you on the sponsors page for helping to spread the word.)

cheers

simon

Eusprig Conference

Its nearly time for the Eusprig conference on spreadsheet risk etc. (Thursday 5th July, glad you asked)

Here is the link, if you can make it and are involved in spreadsheet development, modelling or management its well worth a few hours of your time. Its at Imperial this year.

I’m a bit out of that world these days, I am waiting for the Excel extensibility conf in October.

But I would recommend Eusprig to anyone, lots of valuable info and contacts.

cheers

simon

Office Headshaker

I’m working on Win10/Office 2016 these days, well I spend most of my time waiting for it. And shaking my head…

I was on the beta programs for Office 2007 and 2010 and those early betas were more reliable and performant than this ‘production’ 2016.

I have a feeling its a lot to do with Windows rather than Office, but my god its slow.

Alt tf to get options – 3 second wait before the dialog come up. Alt f11 for VBA, 5 seconds before the editor lethargically appears.

I know I am not on a super computer, but its not a total lemon either, a perfectly serviceable corporate machine. Or it would be without this combo.

10 seconds to open Excel, but its all forgiven because they have animated the activecell movement when you change selections.

10 seconds to open Word but its all ok, they animate the carriage return…

for real…

In ye olden days I had a dumb terminal onto a mainframe across a phone line that was more responsive.

All the animations made me sick, and not just because of the wasted cycles, so I turned it off in Windoze settings. Does some Microsoft UX expertidiot really fucking think people want to watch the computer slooowly respond to basic commands, slooowly???

Its like back in the day when we didn’t turn screenupdating off so it looked like we were doing the work manually instead of tossing it off in a little netscape window to geocities and alta vista.

Today alone I had five 10 minute plus application freezes, Excel, SQL Server Management studio, notepad++, and some other stuff. And Windoze Explorer crashed. This is what makes me think its at least as much a Windows problem as Office.

I was thinking I might consider a wondows lapper next now macbooks are shit, but it looks like it would be unusably slow or uncarriably heavy and bulky (and probably too powerful for a battery so coming with a 50m power cable on a reel). Never mind their ridiculous intrusive prove you’re not a pirate, frequently, features.

So maybe it will be a linux Dell finally, as system 76 only offer US keyboards (and sticker sets for johnny foreigners (like me)).

Unless you have a better suggestion for a (non wonkdoze) laptop?

How is your win10 Office 2016 experience?

cheers

simon

Excel Javascript UDFs

“Office developers have been wanting to write JavaScript custom functions for many reasons,” Microsoft says,

My Arse!

I remain sceptical of the whole webbification of Excel. It started with the Toytown UI in 2007 and just keeps going downhill.

MS have been trying to tempt mainstream developers to Excel forever, and have completely consistently completely and utterly failed. I doubt chucking a bit of javascript at it will have web devs thronging in either.

Here is a fact:

Office development is just not ‘sexy’.

‘sexy’ is pointless navel gazing arguing about ‘is a’ v ‘has a’ (inheritance v composition)

‘sexy’ is arguing whether your new pointless feature should be a property or a method, and then arguing about whether to put it in a concrete class or create a new abstract base class to inherit from so you can also tick the polymorphic box in your 60 line sort routine. (don’t even think about range.sort – are you mad??)

getting the work done quickly with no drama is just not, not now, not ever again seemingly, ‘sexy’.

It used to be appealing, now? not so much.

It might be that some Excel devs do see value in js, I think a genuine focus on a more mainstream language (than VBA) could be useful. I think friction free access to the whole web infrastructure could be good (web queries are excellent, as far as they go). Python would be more use than javascript I think.

So, No I do not believe for one second that ‘Office developers have been wanting to write JavaScript’.

But Microsoft consistently misses the mark  – they need to target existing Office devs and give them what they actually want, not web devs and not Visual Studio devs. Whatever happened to VSTA? does VSTO still exist?

I’m not totally ranting here, when I was on the Office Developer Advisory Panel for Office 2010 we said exactly this – give us a decent IDE. what did we get? the Toytown UI! They eventually disbanded the panel, probably cos they were sick of all the greybeards refusing to sing from the koolaid hymn sheet.

If there was a decent IDE for Office development every aspect would improve, regardless of the language (even brainfuck or fetlang!).

But Microsoft don’t want to give Office developers what they want, they want Office developers to want whatever shallow gimmick Microsoft wants to play with this week. Which hasn’t been pragmatic, getting the job done stuff, or respect for old/working stuff since the MSDN generation took over from the Raymond Chen school of thought over a decade ago.

Probably… I do most of my spreadsheeting in LibreOffice these days, and developing in IntelliJ, so what do I know!