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…

 

 

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

2018 UK Excel meet up/conference

Would anyone be interested in an Excel related conference in the UK this year?

Probably summer time, probably that London, probably one day (with pissups before and after hopefully). Cost – unclear, probably between 50 and 200 gbp depending on numbers and the type of event. (the room I hired in 2012 has gone up in price 1,000%!, for real – 10x more expensive now!!!)

It could be user focused or developer focused (state your preference)

It could be classic Excel, on stuff that works on every version for the past 15/20 years, or it could be on the new stuff in 2013 and 2016 (again state your preference)

No promises, but if there is enough interest a few of us might try to get something organised.

At this stage we are just trying to gauge interest (do we need a phone box or the Albert Hall?)

So if you might be interested please leave a comment. (no commitment (either way))

Venue suggestions welcome too.

ta muchly

 

VBA test exercise

I have just been offered the opportunity to do a VBA test (“should take no more than 3 hours”!) for the chance to get an interview… for a 3 month contract!

I didn’t find out how many had been selected for the ‘opportunity’. I could have been 1 of 2 or 3, or 1 of 2-3,000. I would guess 10-20? that’s a lot of people wasting 3+ hours

I told them to do one obviously, but I couldn’t help taking a peek at the exercise and scoping it out (wasting a ton of time which I am annoyed at myself for doing, but there you go).

I could do a pretty half arsed job of it in 3 hours, but to do it properly and considering some of the ‘peripheral’ elements (date integrity, error handling etc) there is no way I could do it to what I would consider an acceptable standard in 3 hours. Add the fact that some of the specs were wrong (do I do as per spec or what I think is correct?) and it was at least a full days work.

Bearing in mind this is a test, that will be reviewed so needs to look the part not just functional.

Perhaps if I had ready to go VBA libraries and components I could cobble them together within the timescales. But who has loads of libraries they actually have the right to use? I have a lots of stuff from all the codematic and xlanalyst stuff, but the bulk of the code I have written belongs to clients. (and some of the more useful stuff appears to only be in C, C++ or C#)

The other red flag was advice to do it in a object oriented way. IN A NON OO LANGUAGE FFS!

This may be contentious, but Object Oriented in Excel VBA is utterly retarded. Fact!

(There may be some exceptions but if you are combining data and operations in VBA ‘objects’, what are you using the Excel grid for? Or are you duplicating all that data?)

So I have a few problems with this company’s recruitment process:

1. Its unduly burdensome on the potential candidates
2. The timescales are for an amateur standard hack
3. pre-built components may not be owned by candidates
4. OO approach smacks of boys playing at being men.
5. Spec was vague and incorrect
6. Required effort will put off many candidates
7. Some of whom might be ideal but too busy to risk 3+ hours
8. 3+ hours for a chance of an interview for a 3 month contract is a very poor risk return compared to the rest of the job market.
9. 3+ hours feels more like they want hobbyists rather than professionals.
10. lack of respect for candidates time doesn’t bode well for the future role

I mentioned it to one of my contractor mates: “for free? fuck that”.

precisely!

 

VBA third most hated language

amongst Stack Overflow devs anyway…

On SO devs can mention tech they would rather avoid. Perl was top at being avoid worthy, then Delphi (is that even still alive??) followed closely by our own VBA.

This is developers so its not really a surprise they prefer to avoid VBA. They want to be playing with new shit not adding business value.

Interestingly SO reported that VBA is still an actively tagged language so still popular in use, just not by devs.

In fact, of those languages disliked by over 3% of mentions, VBA is the only one not shrinking in tag mentions, in fact its growing slightly.

VBA course hosting

I think I might have mentioned that I am thinking of making an online video VBA course?

Well anyway, I haven’t exactly finished the content yet…

But I have been poking around for a ‘platform’.

I was initially thinking of Udemy, and I still might go that way initially, but I have heard a few not so positive things about them from an instructor POV. Although I am not sure if that is all old stuff or recent.

Cost wise I imagine the course being around 50 quid/dollars/francs.

I had a look at this place and they have that price as an example.

If the student pays 50 quid the instructor can get as little as 3.15!!!!!

That is the content creator, the author, gets just over 5% of the price the student pays!!

I just can’t quite believe it!

I think I might look into creating a ‘platform’ instead. Its only hosting with a few bells and whistles, most of which I won’t use.

Maybe I’ll have to do youtube with ads after all.

Is VBA a dead end ?

This topic came up on Excel-l, I got shouted down on there, so I thought I would elaborate on my views here. (its mainly an Excel view but I don’t think the other MS Office apps are much different.)

I see two sides to the VBA story – the technology and the career.

Technology wise a VBA solution is a dead end – it can’t be run on a server (officially), it can’t be run in a browser, it can’t be run in a high performance cluster, it can’t be run on mobile devices. This means it can’t be easily scaled if more users or geographies must be served. You can’t really even have multiple simultaneous users of an Excel VBA app. With the relentless march to cloud and mobile, VBA is left behind.

Of course MS won’t remove support for VBA, the world would fall apart, well the finance, pharma, and energy industries at a minimum. But the editor is pitiful by the standards of this century. It is testament to the work of those MS guys in the ’90’s that the debug Edit/continue is still better than nearly all mainstream IDEs. But its still out of date and hard to extend.

MS can’t invest in VBA because their strategy is .net/cloud/browser blah blah. They had the chance to fight on their strengths – rich client, but instead they tried to be google. And lost. Now we are all struggling with crap web sites in bloated browsers with slow Win3.11 style interactions from 1995.

VBA hasn’t suddenly become bad, its still very good at what it does, its just that we are often now looking for more from our solutions. More users, more device types, more threads, more whatever.

So its not dead but it is basically limited to single user apps on their individual workstations. But I am sure it will continue to work for a long time.

From a career point of view, what else is like VBA? nothing really, so expertise in VBA doesn’t easily lead onto anything else, hence its a bit of a dead end.

VBA is not really object oriented so it doesn’t lead to the sort of object based designs that C# and Java are suitable for. Its not functional so it doesn’t lead to F#/Scala.

Of course you can write VBA in classes, but lack of implementation inheritance hobbles your solution. By the same token you can write VB style ad-hoc procedural code in C#/VB.net, but you would be missing out on many of the elegant OO features of .net. You would struggle to implement most modern design patterns in VBA, and the latest CV must-have MVC is also all but impossible.

From a functional point of view (probably more useful than OO these days) VBA can’t treat functions as data smoothly (although CallByName does better than many languages). And a little toot of Application.Run can ease the friction too.

In Excel VBA especially we can get an awful long way without worrying about user interface/user experience or data structure. This is great for your current users (fast development/deployment) not so good for your career development – no other system is so well integrated as Excel/VBA. No worrying about Restful APIs, onClickListeners, Asynchronous callbacks, all very powerful, all generally avoided in VBA land.

If you love VBA, and why not, then continue on, you are probably adding untold business value with the work you are doing (and perhaps a little future maintenance pressure for someone). But I’m not sure it will take your development career too far, business career possibly but IMO it won’t help you much as a developer.

If you are doing VBA now and thinking of progressing to other development technologies then consider carefully how you will make your next steps. Adding Access and SQL to VBA is completely doable, adding modern scripting, web technologies or .net/Java is much tougher.

Getting into .net via VB.net is a possibility, but, I think it would be easier to go straight to C#, although the .net/Excel story is still, in 2017 very ugly.

ExcelDNA might be a good bet if you want to inject a bit of .net into your Excel life. I found this approach much more viable than VSTO infrastructure hell. Somebody somewhere is maintaining a lot of both that I developed. I bet the ExcelDNA maintainer is having more fun.

I also managed to blag my way into writing a load of custom Java on Essbase, that route probably isn’t open for many though.

Which led to my new focus on Android which is both challenging and rewarding. Although stay posted for news on that…

btw this is my 1,000th post on SOS, I assume WordPress will give me some superficial badge of honour, reward or ‘achievement unlocked’ bollocks, in the modern way.

cheers

simon

 

Job hunt

I’m back on the hunt for a contract.

Looking for easy work, high pay, and ability to work from home in my pyjamas.

I am just caught mid hop really, my Android mastery has not quite reached the level to dazzle, and my Excel/VBA/trading experience is starting to look a little distant.

That said I do have a few mini projects on the go both in VBA and Android, but a big juicy contract would be handy enough too

I am pimping myself out on all the usual job sites and I have noticed a pattern.

An agency posts a job, I apply with my cv, and then… tumbleweed.

This is not new of course, and perhaps my ‘varied’ cv is not what they are looking for but…

A couple of agents did get back to me either with a no thanks or a discussion or whatever. And you know what?

The next time a role comes on with multiple agencies, I apply to the ones that bothered their arse to get back to me. Those tumbleweed guys? I don’t bother.

So pimps of the world, if you can’t even be arsed to ping back a quick no thanks to your applicants, you may be losing out on candidates in the future. just sayin.