Archive for the ‘Excel’ Category

Office 2016 Migration issues

Monday, 16th April, 2018

I have been doing some Office migration stuff recently and came across a couple of issues I wondered if anyone else has seen. and perhaps has a solution?

Word 2016

From time to time gets very upset at docs with form fields in and scatters the text all over the screen, like toys from a pram. Seems to be related to printing, possibly quick print, but printed docs come out fine. So it looks like the printer driver is upsetting the screen driver somehow. Swapping printers ‘fixes’ the issue. Anyone seen this?

Excel 2016

Application.getfilename manages to get a filename Excel can’t use? I have had to go and add a file type to the file saveas stuff, fair enough. But the (non null) file name is not acceptable??

I thinks this might be related to server paths or something?

(this is a remote project so troubleshooting is a bit of a challenge – any ideas?)

cheers

 

Advertisements

Spreadsheets ahead of the game

Monday, 19th March, 2018

I am fairly regularly bombarded with negative news about spreadsheets. Excel in multimillion pound error, Excel causes wrong contract choice, etc etc.

I get it, spreadsheets have errors…like most things.

Anyway, on my quest to understand why something as ridiculous as Object Oriented programming has pervaded our world I came across this, which paints spreadsheets in a much more positive light (IMO) .

A video where Simon P-J, (a Microsoft boffin), points outs something I have been bleating on about for a while – that Excel is the most widely used Functional programming language in the world.

If you don’t know what functional programming is, it’s the future. OO was vaguely useful for one or two specific use cases in the 80’s, but in the modern world, apart from those selling books or consulting on how to make OO less completely shit, most people are recognising Functional approaches as more useful.

Functional programming is what we do in a spreadsheet grid, a key tenant of which is no side effects, which is why UDFs have such limitations on what they can do. Although I notice Ms has opened these up a little in recent times.

So anyway, when all the propeller heads from IT are moaning about spreadsheets, its probably because they are locked into a 1980s paradigm and don’t understand our functional approach.

Bless…

2018 UK Excel meet up/conference

Sunday, 25th February, 2018

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

Tuesday, 6th February, 2018

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!

 

Cutting Excel out of your process

Thursday, 23rd November, 2017

Some CFOs keen to see the end of Excel.

Why don’t these people understand? Excessive Excel is a symptom of crap systems. Normally caused by decades of underinvestment in IT and piss poor IT McMissManagement.

They made a cynical strategic decision to cut investment in IT and let end user ad-hoc analysis take up the slack. Now they want to eradicate (well hide) the symptom (well evidence).

And should the systems eventually catch up with requirements (even a little bit), those users have so little confidence in IT after decades of under delivery, and unreliability that they will continue to prefer their existing proven approach. surprisingly! (Thats assuming they are ever allowed sufficient access to do their job of course).

Anyone who thinks outsourcing and offshoring your IT will help this Excel eradication process, is a dumbarse. Not quite as dumb as the people who think the solution is a crack team of overpriced under-experienced fresh graduates posing as consultants, but nearly.

The obvious solution is a big integrated system, like say SAP. A couple of spotty yoofs should be able to get that up and running in a few weeks and it will solve everything. Even cheaper – get someone in a different timezone to do it. I would do it for them but I am tidying my sock drawer that day.

Its not a technology thing, putting your shitty inadequate systems in the cloud just make them less secure, and more available. And who needs more availability of crap data and irrelevant analysis?

The actual solution is a minimum of 10 years of high quality IT management to implement fit for purpose systems. So start today and in 2027 you will be sorted, call it 2028 to give you a few months to recruit that high quality management.

Is VBA a dead end ?

Thursday, 28th September, 2017

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

 

10 year birthday

Sunday, 12th February, 2017

I missed it obviously but one of the kids pointed out I have been blogging here for 10 years! ken ell!

(as of January just gone)

I can’t imagine dragging it out for another 10, unless some spreadsheet miracle happens and I am sucked back in. I still think spreadsheets are brilliant at a great many things, I have just given up waiting for numb nuts management to realise.

I built a random mental maths test generator for the kids the other day. I looked at doing it as an app, as a web thing,  but in the end I did it in a spreadsheet in about 10 minutes. The kids hate it!

I love it!

(it even has a bit of VBA in it – woo hoo)

cheers

simon

Excel Conference in Amsterdam

Tuesday, 5th January, 2016

I just saw this (on linkedin of all places – I only go there twice a year).

Dunno much about it, except its being run by a gang of well known Excel experts so should be very excellent.

I won’t be going as Excel is basically dead to me these days.

If you are going, have fun.

cheers

simon

 

 

Excel Modeling World Championships 2014

Wednesday, 27th August, 2014

I saw this last year and thought it was a great idea, and have just been prompted to mention it.

Here is all the info

Big wedge for the winner…

And the fame of course…

ciao, peeps

Excel Dev book

Monday, 6th January, 2014

Thanks for all the input folks.

I had a good think, and although it feels somewhat like a waste not to share some of my most useful experiences, I don’t think now is the time for the book I have in mind.

There are several factors depressing the Excel development market at the moment, some are permanent trends, some I think will reverse in a year or two. In particular there is a heavy regulatory downer on Excel just now, once people get caught messing up in the replacement technologies that focus will move off, allowing people to get back to the benefits of Excel.

Also IT departments currently have a big veto power, I think once people notice that that doesn’t actually move the business forward their influence will decline, allowing people to get back to the benefits of Excel.

maybe.

On the other hand the time may really be up for professional Excel development.

Either way I’ll still chip in here with points of interest.

cheers

simon