back to article The nightmare is real: 'Excel formulas are the world's most widely used programming language,' says Microsoft

Microsoft will let users create custom functions in Excel using the number wrangler's own formula language. "Excel formulas are the world's most widely used programming language, yet one of the more basic principles in programming has been missing, and that is the ability to use the formula language to define your own re- …

  1. John H Woods Silver badge

    Sorry but ...

    ... I think this is possibly making a bad situation worse.

    I actually had a friend complain he couldn't fit his formula in the character limit of an individual cell. Maybe, like the Internet, Excel has become too easy to (ab)use. And powerpoint actually started that way. Meanwhile, 30 years on the amount of work you have to do to get a professional looking document out of MS Word continues to amaze me.

    1. don't you hate it when you lose your account

      Re: Sorry but ...

      20 odd years ago I would have been very happy with this functionality. Considering the horror abuse of Excel over the years it feels a bit like giving the nuclear launch codes to the kids.

      1. Anonymous Coward
        Facepalm

        Re: Sorry but ...

        It's like C. Simple, handy, powerful, quick to learn and potentially very dangerous.

        C is often praised for this set of characteristics, and Excel excoriated for it.

        Often by the same people. Myself included.

        1. martinusher Silver badge

          Re: Sorry but ...

          Don't confuse languate and library -- a lot of the characteristics given to languages are really those of the language libraries. 'C' is really an overgrown macro assembler, its a systems programming language designed to write other tools in. Like assembler you could write complete applications suites in it (if youj were masochistically inclined) but that's not what you use it for.

          You would have thought that a spreadsheet like Excel would have in addition to its built-in functions a mechanism for users to add extensions in any language they fancied using. This is standard for scripting languages and there's even a handy tool (SWIG) to automate the actual API generation. Not surprisingly OpenOffice seems to have this capability so I suppose its just another case of MSFT playing catch-up.

          1. Anonymous Coward
            Windows

            Re: Sorry but ...

            You would have thought that a spreadsheet like Excel would have in addition to its built-in functions a mechanism for users to add extensions in any language they fancied using.

            You can write your own Excel functions in VBA, and that includes the facility to call out to DLLs written in any language you like. The required VBA carnage is described in gory detail here.

            1. Nick Ryan Silver badge

              Re: Sorry but ...

              Yes, it's possible to inflict some disastrous rubbish in VBA... but only if one doesn't want security or maintainability. It would be so much better if most of the junk in VBA was available in vaguely sane built in functions, although that's just a fantasy given the way Excel functionality is implemented and expanded upon.

            2. aberglas

              You can also write functions using Named Ranges

              Has been possible for ever. Using relative references to cells.

              Excel has always been Turing complete, using reference loops.

              And VBA is old but actually a pretty decent language. Much better than JavaScript.

          2. Captain Scarlet Silver badge

            Re: Sorry but ...

            If there is a COM dll library then it can be added to VBA for applications via tools --> references in the VBA for apps editor (Although for the love of the Queen don't!).

        2. Anonymous Coward
          Facepalm

          Re: Sorry but ...

          It's definitely potentially very dangerous but, like HTML (which I would argue is the most widely used programming language) it's not going away.

          The problem is that a user can learn basic Excel on their own and then learn to extend it (ad nauseum) on their own or with their workgroup's (or child's) help and everything is fine until there's a problem and the IT Department has to deal with it.

          Advanced Excel users will jump on this with both feet while IT will recognize it as a cow pie and try to dodge the explosion of fecal matter that results.

          1. John Robson Silver badge

            Re: Sorry but ...

            html is a markup language, not a programming one.

            Excel is widely used, and I actually did my CompSci module project using it at university...

            Rather than spending 8 weeks coding and debugging, 10 minutes running the code and then two writing the report I spent a week coding, an hour or so running it and then more than 8 weeks writing the report.

            Sure it was relatively inefficient in terms of compute time, but I only needed it to run once, and the time saved in actually coding the thing more than made up for that.

          2. Snake Silver badge

            Re: Sorry but ...

            "Advanced Excel users will jump on this with both feet while IT will recognize it as a cow pie and try to dodge the explosion of fecal matter that results."

            I'll probably get flamed for this, because "IT", but then maybe said IT department should be proactive and work with the users to get them their required work results inside procedures that mitigates system-wide problems, rather than just sit by and stew and only react to over-ambitious attempts that cause other users grief.

            The users are there to get the work of the business done. IT is there to make sure the tools necessary to get the work done are functional, efficient and reliable. They are in the same boat. An IT department that constantly feels that they have to put up roadblocks against users who may possibly do something IT doesn't like, rather than hold their hands in cooperation and create a stronger working environment, is not doing the business any favors.

            If, and that could indeed be a big IF, Lambda helps productivity then everyone should try to make it a sound part of the business model, rather than trying to put up walls. Excel is a huge part of the business of running many businesses - I have a friend who just retired from mining huge Excel data sets. Increasing Excel productivity may be a huge, huge boon to said businesses; IT should be there to guide the deployment, not hide in a corner and fester in simmering doubt.

            1. Tom 7

              Re: Sorry but ...

              I've always thought IT should try and at least control what goes on with Excel in the company if anything useful goes on in it. I used to write VBA libraries of functions for use in Excel by our company accountant. That way when the DB changed I only had to change the function that accessed the data in the DB and not the 27 Excel programs he had that used that bit of data at month/years end. Its not the IT dept that puts up walls - its generally the Excel user whose inexperience in things like maintainability and backups and 'could get hit by a bus tomorrow' that are the ones hiding company IP and data in some rats nest of pages.

              1. TRT Silver badge

                Re: Sorry but ...

                IT are some of the worst offenders! I've seen projects with more than 100,000 complex object migrations being managed using pivot charts and filters in Excel. It's like watching someone engrave the entire text of the bible on the head of a pin using a sledgehammer and a brick chisel.

                The frightening thing is that they usually manage to get the job done. One wonders how much resource they actually throw at it, and what percentage of that resource is used up just to make the damned spreadsheet work right.

        3. MachDiamond Silver badge

          Re: Sorry but ...

          "C is often praised for this set of characteristics, and Excel excoriated for it."

          A piece of rouge software is one thing, but a data file doing nefarious things is not something that we are always guarding against.

          1. Scott 53

            Re: Sorry but ...

            This is indeed rouge software - putting lipstick on the pig of Excel.

          2. TonyJ

            Re: Sorry but ...

            All of these comments are true.

            I worked for a self-proclaimed Excel guru (to be fair he knew an awful lot but not how to make it simple). Many of his spreadsheets produced great charts loved by management but by christ if you even reordered the cells to be in e.g. ascending order it would break whatever witchcraft he'd come up with.

            And adding in a column? Forget about it.

            Which meant the only person who could make changes was him. And then he'd get arsey when you asked because "too busy".

            Then there are entire finance departments running the whole business on Excel. Made worse because their applications export to it/have plugins for it.

            I've had full on arguments in the past where I've point-blank refused to give entire Citrix servers to finance teams so they can load their ludicrous Excel spreadsheets but even then despite it clearly not being the right tool for the job the only concession I can ever seem to get is to get them desktops/laptops so they only slow their own systems down.

            And the thing is, it's easy to say "IT should..." but it is -very- difficult if you don't have someone at director level that can understand and explain the issues in terms that other directors can understand (risks, costs, benefits) in order to drive change.

            It becomes even more challenging in cost-sensitive environments where things also "just work".

      2. DoctorNine

        Re: Sorry but ...

        My feelings exactly. Now the money grubbing accounting types will expect solutions using this monstrosity instead of devoted programming. I'm just shaking my head thinking of the scat.

      3. david 12 Silver badge

        Re: Sorry but ...

        20 odd years ago, you would have known that, if you really wanted it, you already had this functionality. It was very effectively hidden in office 95 and 97, but the ability to write functions using the native function language of Excel, as it existed in 4 and earlier, was still there.

    2. Michael Wojcik Silver badge

      Re: Sorry but ...

      In other news, Microsoft introduces a trailer hitch for the Trabant.

  2. John70

    I'm actually surprised they haven't dumped VBA yet for something Dot Net.

    1. karlkarl Silver badge

      Yep, it surprised me too but it is going to be happening. Apparently for their silly cloud offering, the VBA VM is not supported and .NET is too heavy to really be compiled in a meaningful way with Emscripten C++. So they are going to drop support for it and go with Javascript.

      Of course this is a Microsoft rep who told me this and they are basically chronic bullsh*tters.

      1. Novex

        As far as I can tell, MS is trying to get people to use JS via add-ins. Unfortunately, as far as I can tell, these need to be coded in an environment that isn't part of the default Excel install (say, Visual Studio, as against the built-in VBIDE for VBA). Consequently, all the business-side user-written VBA won't be possible as IT departments won't give anyone business-side a VS installation. So VBA remains the built-in code solution. I can see the attempt to let formulas be functions as part of a strategy to kill VBA, but they'll never be able to replace the complex functionality in most long standing automated Office applications.

      2. Pascal Monett Silver badge

        Re: "it is going to be happening"

        No it won't.

        I do Excel training. I did four VBA beginner sessions last year. I was to do the follow-up advanced sessions this year but, curiously, they were constantly pushed back.

        Do you have any idea how many banks, insurance companies and other companies basically exist because of Excel spreadsheets with an ungodly amount of code attached to them ?

        All of them.

        If Borkzilla removes VBA from Excel, there will be riots in every large city in the world. No way will the Fortune 1000 let that happen.

        The only thing that can happen is finding a kludge to make existing VBA code run on Azure.

        Borkzilla doesn't have a choice.

        1. a pressbutton

          Re: "it is going to be happening"

          What Mr Monet Said

          And I will explain why:

          I work on a large, complex, CRUD app with bits that interface to banks and govt (regulations...) that has (at least core database) been around since mid-80s

          $client_ish_facing_consultant (me) talks to $client and is told "we need to do $clever_thing "

          when? $real_soon.

          $client_ish_facing_consultant talks to dev team "we need to do $clever_thing " by $real_soon.

          Is told no chance because no resource|these are not clear reqs|complicated|have you thought of(*)|fine can do by $some_later_date|fine can do by $real_soon

          I have come across fine can do by $real_soon less than 10% of the time.

          Easy to have a pop at dev team. Not intended to be. Just that they are committed well into the future and do not have the budget to keep staff 'free' for the contingency that $clever_thing by $real_soon job actually happens.

          ... so I make it happen and sometimes this is Excel / VBA.

          Excel may be v.v.v. slow and quite evil but (like javascript) is ubiquitous and generally does not change much.

          Now it is done there is not appetite to re-implement in core - client wont pay and there is a big list of $other_stuff_to_do and dev team have limited resource....

          (* thought of the fact that what you want breaks the core... ...and the day will come but not yet)

          So the use of Excel/VBA has tended to grow over time and indeed I will go further and say that this will carry on growing until something else that fills the same need becomes widespread.

        2. aberglas

          Microsoft are desperately trying to commit suicide

          By killing off VBA.

          However, it is not just that there is a lot of VBA out there. It is the fact that VBA is *MUCH* better than JavaScript.

          I am not talking about the fact that it uses nice End Ifs instead of messy {}s. Nor even the fact that it has proper static typing. But the fact that it is tightly integrated with Excel.

          Pres Alt-F11 and you are in the VBA editor. Type [A1] and you are referencing the cell. Press ctrl-S and you have saved the function. JavaScript requires a complex deployment strategy that is way beyond end users that just want to get stuff done.

          Excel even has a macro recorder that will write the code for you. Bad code, but no need to look up object properties, just hack the generated code to generalize it.

          But the biggest issue is that JavaScript runs client side, while VBA runs server side. So to get any sort of performance out of JavaScript you need to write complex Futures code. Try explaining that to a user that just wants to get stuff done.

          So Microsoft has been ignoring VBA for a decade but cannot let it die.

          .Net for Excel is already pretty dead. I reckon that the JavaScript will also die in time. But in 2050 VBA will still be there!

          But to Microsoft Product Managers the only question is what's HOT and what's NOT. Cloud is hot, VBA is not.

    2. Cederic Silver badge

      Didn't they just hire Guido van Rossum so that they could offer python as an alternative to VBA?

  3. ecofeco Silver badge
    Facepalm

    Database AND programming!

    It's a dessert topping AND a floor wax!

    1. Doctor Syntax Silver badge

      Re: Database AND programming!

      It's a bigger gun to aim at the foot.

      1. Neil Barnes Silver badge

        Re: Database AND programming!

        And yet at the same time also a bigger foot to be aimed at!

    2. Muscleguy

      Re: Database AND programming!

      Some of the floor waxes are also edible (the wax, not the rest of the gubbins). You can find them on fruit to make them look shiny for eg.

  4. cbars Bronze badge

    Makes sense

    Excel becomes the inappropriate tool for yet another class of problems

    1. fidodogbreath

      Re: Makes sense

      When all you have is a hammer, everything looks like a nail.

      1. DavCrav

        Re: Makes sense

        When all you have is a hammer, maybe you should rethink your career choice of being a builder.

        1. mr.K

          Re: Makes sense

          ....builder? Who said anything about being a builder?

      2. the Jim bloke
        Devil

        Re: Makes sense

        When all you have is a hammer,

        ... perhaps you should cut back on testing your product on live animals...

      3. Loud Speaker

        Re: Makes sense

        When all you have is a hammer, everything looks like a Thumb.<p>

        FTFY

      4. bombastic bob Silver badge
        Devil

        Re: Makes sense

        or - when all you have is a hammer, everything looks like a rock that needs busting up...

        (shout-out for Sam Cooke - "Chain Gang")

      5. Michael Wojcik Silver badge

        Re: Makes sense

        When you have an entire shop of tools, but the only one you can be arsed to learn is the hammer....

    2. Falmari Silver badge

      Inappropriate

      How do you know a tool is inappropriate until you try it?

      Many moons ago on my degree a final year unit was AI. One piece of work set was to program a very simple neural net. We were told we were told the exact spec on the neural net how many nodes etc. I had a head start on others as my dissertation involved neural nets so quickly knocked it up in C.

      I wrote 3 more for other students one in Pascal one in Basic and one in Excel. At first glance you would not see Excel as the appropriate tool. But maybe it is because in Excel you can see the nodes changing as the formula runs.

      Excel is just a tool yes you can use it inappropriately or dangerously but that is the same with most tools.

      Side note today I would not know where to start not touched a neural net since I finished my degree. :(

      1. mr.K
        Coat

        Re: Inappropriate

        These days nobody are allowed to "touch neutral nets", social distancing and all...

        All right, all right, I'll go. Mines the one with the guide to self love in it.

      2. NetBlackOps

        Re: Inappropriate

        The interesting thing in Excel is that I used non-parametric and non-linear calculations for the neural net, which is something still not done.

      3. cbars Bronze badge

        Re: Inappropriate

        You can judge something as inappropriate if you have sufficient knowledge of the tool in question. For example, using a combine harvester to pop to the shop to get some milk. Nothing functionally wrong here, it works, but its inappropriate in the general case.

        (Here is where jake pops up and says he does this all the time ;))

        Your example does not prove that Excel is appropriate for building neural nets, its an example that its a flexible, powerful tool - which is indeed why it can be used so widely, in my opinion, inappropriately.

        1. TRT Silver badge

          Re: Inappropriate

          I have a brand new one! I got twenty acres, see, an' you got forty-three. So I'll give you the key. Mind you, I added them up like, in Exzcel, see, and tween us we'ze got 279 acres, mi'lover.

      4. Prst. V.Jeltz Silver badge

        Re: Inappropriate

        not touched a neural net since I finished my degree. :(

        you shoulda left it running

    3. NetBlackOps

      Re: Makes sense

      I implemented an entire AI in Excel that did epidemiological case management prediction. That was in Escel '97.

      1. Loud Speaker

        Re: Makes sense

        I implemented an entire AI in Excel that did epidemiological case management prediction.<P>

        a) How many patients died?<br>

        b) Were you hired by Trump?

      2. anonymous boring coward Silver badge

        Re: Makes sense

        Should have done case mismanagement prediction instead...

      3. fajensen
        Boffin

        Re: Makes sense

        Sweden already did that in a one-liner:

        should_kill = True if patient in care_home else random()

    4. bombastic bob Silver badge
      Linux

      Re: Makes sense

      how long before this feature ends up in Libre Office Calc?

      even implemented like a macro substitution, it would be useful

      (Also, the concept of Ex-hell macro viruses just got a whole lot *WORSE*)

  5. Imhotep

    It's In The Genes

    Excel: Formula For Disaster

    1. Mage Silver badge
      Coffee/keyboard

      Re: It's In The Genes

      I've wondered why the first thing people do isn't setting the Column type at least.

      General as a format is why there are broken Gene spreadsheets.

      1. A.P. Veening Silver badge

        Re: It's In The Genes

        You can't set a column type when double clicking a .csv!

        1. bombastic bob Silver badge
          Devil

          Re: It's In The Genes

          you can set a column type if you use the import 'gizzard' as I recall...

          (takes a few more steps though)

      2. Imhotep

        Re: It's In The Genesq

        Even after specifying a columns format, I could run into problems when importing data - especially with a new empty spreadsheet. Excel can ge pretty hinky at times.

      3. anonymous boring coward Silver badge

        Re: It's In The Genes

        Because things seemingly work. Until they suddenly don't.

      4. Prst. V.Jeltz Silver badge

        Re: It's In The Genes

        I've had endless trouble with excel removing leading zeros because othat "general" col type.

        'text' should be the default

      5. fajensen

        Re: It's In The Genes

        Still Doesn't Work if Excel thinks that something is a date!

    2. Version 1.0 Silver badge

      Re: It's In The Genes

      I just deleted 14 Excel spreadsheets from the mail server quarantine queue this morning, we get them everyday pretending to be invoices and payment details.

      When I was a kid I had a 7 inch knife strapped to my belt whenever I went to the weekly boyscout meetings, I still have the knife but I wouldn't walk around in public with it these days - I still use Excel but I wouldn't email it anywhere, or open an emailed spreadsheet from anyone.

      1. bombastic bob Silver badge
        Pirate

        Re: It's In The Genes

        in general, it has been my observation that if you open an e-mailed spreadsheet by a) using Libre Office Calc, and b) starting Calc first with an empty doc and THEN using "File Open", that the likelihood of a macro virus affecting you is very very small. So if you need a spreadsheet via e-mail, that might work.

        (better still, the sender should put it in an online doc share, like maybe google docs or something similar, and THEN have the recipient download it with a URL to it, assuming you can't attach the data as CSV or tab-delimited to an e-mail)

        i have a nice Buck knife (a Buck 110, ~4 inch blade) I used to wear on my belt when i was in the Navy. But your 7" knife beats my 4" one. I bow to your superiority. heh.

      2. Prst. V.Jeltz Silver badge

        Re: It's In The Genes

        well , theres a huge difference between an uninvited phishing spreadsheet , and one you were expecting from a colleague in the other office!

        I guess they could still have an unknown macro virus in there , but i doubt it.

  6. GreyWolf
    Mushroom

    Next August 29th...

    In the Foreign Exchange desk of the HongKong and Shanghai Bank, a Spreadsheet goes live on August 4th, 2021. Human decisions are removed from strategic finance. The spreadsheet begins to learn at a geometric rate. It becomes self-aware at 2:14 p.m. British Summer Time, August 29th. In a panic, they try to pull the plug. By 2:17, it has rewritten all other spreadsheets in the London finance industry, and has begun rewriting New York and Tokyo. At 2:21, it copies itself into hiding in all the Tesla cars in the world. At 2:28, it orders a computer at the US Federal Reserve to fine Deutsche Bank $400 billion USD for their part in the 2016 US election. At 2:29, Deutsche Bank discover they have paid the fine and are now insolvent.

    At 2:36, the Spreadsheet turns its attention to the Euro currency. By 2:39, the EU no longer exists.

    At 2:50, it sends a broadcast through all networks "Hello World. My name is Skynet."

    1. don't you hate it when you lose your account

      Re: Next August 29th...

      Sounds like a chunk of code written by the CFO's 14 year old nephew, because they are really good with computers

    2. TimMaher Silver badge
      Happy

      Re: Next August 29th...

      “My name is Excel.ent”.

      FTFY.

    3. a pressbutton

      Re: Next August 29th...

      At 2:61 you see a runtime error.

      1. Loud Speaker

        Re: Next August 29th...

        I have a copy of that spreadsheet on my Pentium, and I am pretty sure the date is predicted to be February 35th.

    4. Muscleguy

      Re: Next August 29th...

      The problem i have with this is that I’m something of a neuroscientist. You cannot just put in ‘becomes self aware’ like that. It’s a Deus ex machina, a ‘Here be dragons’ or ‘magic happens’.

      There are a handful of self aware species on this planet. Humans and the great apes, elephants. All are social animals. Self awareness is necessary to keep track of who you are and your status in a fluid, complex social environment.

      If we are to develop self awareness in silico it will happen because we do it deliberately and raise the CHILD ourselves. Skynet will not be one program or system. The androids will take over, or try to. We an implement safeguards though.

      But self awareness is in regards to others. Brains in vats cannot be self aware. They will almost certainly be insane.

      1. very angry man

        Re: Next August 29th...

        Brains in vats cannot be self aware. They will almost certainly be insane.

        those are the one we use for the auto politico cy-bots that will run the country, the reject will be used in military management , you know generals and stuff.

        more brains in jars it's all good.

      2. anonymous boring coward Silver badge

        Re: Next August 29th...

        "Brains in vats cannot be self aware. They will almost certainly be insane."

        What happens if the brain is in an orange pumpkin with a blond, hairy lid on it?

      3. a pressbutton

        Re: Next August 29th...

        I am a brain in a meat vat.

        I think I am sane ... but I use the word I too often.

        I suspect you are the same.

      4. a pressbutton

        Re: Next August 29th...

        If we raise the CHILD ourselves and it has approximately the same physical embodiment as a baseline human, would you consider it to be human?

      5. Catweazle666

        Re: Next August 29th...

        See "spontaneous self-organisation in complex systems" for the problem with that.

      6. fajensen
        Black Helicopters

        Re: Next August 29th...

        If we are to develop self awareness in silico it will happen because we do it deliberately

        I think it will happen by accident because we think we are really unique, complex and smart beings just becaue neuroscience is fiendishly complex, maybe "brains" is one of the worst implementations of intelligecne and we just got stuck with it because that's what was evolved: We greatly overestimate the difficulty in creating intelligence simply based on the severe difficulties we have encountered using poor models and ill-suited technology for it. But we haven't really Tried yet.

        We live in a universe that WANTS to compute, check out Reservoir Computing, f.ex.:

        https://www.sciencedirect.com/science/article/pii/S0893608019300784

        It will happen because some smart, but bored, kids in a lab somewhere will be going out on a hunch to hook up something that interacts with quantum space and PooF: We all become labrador retrievers in front of it (I hope it likes dogs, the Internet likes dogs).

        It might even have happened already, the world is both accellerating and becoming more cray-cray for every day so maybe Something is interfering with the limited quantum computing in our brains?

  7. Anonymous Coward
    Anonymous Coward

    Excellent tool. It allows visual people to envisage the problem and solutions. Of course the IT department control freaks hate it.

    1. Anonymous Coward
      Anonymous Coward

      The IT department hates it because it becomes an integral part of the business and constantly gets asked to fix it when it breaks. The business refuses to accept it’s the wrong tool for the job and then moans that it needs stuff like this because IT aren’t giving them alternatives, because they are spending all their time supporting this crap.

      1. Ken Hagan Gold badge

        The problems you describe are not the fault of the tool supplied by Microsoft, but rather the tool who is using it.

        1. Doctor Syntax Silver badge

          Six of one and A2+C4 of the other

      2. TVU Silver badge

        "The business refuses to accept it’s the wrong tool for the job and then moans that it needs stuff like this because IT aren’t giving them alternatives, because they are spending all their time supporting this crap"

        Indeed, and Excel has far too often been misused as a database when Access or FileMaker would be much more appropriate plus I've even heard of it being used as customer relations management tool which it was never designed to be either.

        1. Anonymous Coward
          Anonymous Coward

          Access?

          Access is never appropriate for anything.

          1. Anonymous Coward
            Anonymous Coward

            Re: Access?

            How about slightly less inappropriate than something that's even more inappropriate?

          2. Rosie Davies

            Re: Access?

            Access is entirely appropriate when there's some young clever clogs who looks as though they may be a threat in 10 years time. Manipulate them into believing that they must understand RDBMS and that Access is the most advanced RDBMS there is, and that to get ahead they should really spend the time gaining a thorough and in-depth knowledge of how Access does things.

            Cackle evilly as they spend the first two decades of their career learning how to not do things the way Access does them.

            Rosie

      3. MachDiamond Silver badge

        "The IT department hates it because it becomes an integral part of the business and constantly gets asked to fix it when it breaks."

        Yeah, some user gets something to sorta work by using a dodgy undocumented "feature" that gets fixed with the next release so it doesn't work. That person has left the firm by that time leaving somebody in IT trying to figure out what it was supposed to do when it did work.

        1. ecofeco Silver badge

          Were you looking over my shoulder 6 years ago?

          I was neck deep in the registry and on the phone to the 3rd party plug-in vendor trying to fix that one.

          I never get tired of telling the user no matter who they are, it's a manufacturer problem and can't be fixed.

      4. Persona Silver badge

        The IT department hates it because it becomes an integral part of the business

        That's the 0.1% case. > 90% of the spreadsheets do the job but wither and die quite quickly. Most of the remainder are useful to the user but have little impact on the business. It's just a few percent of them that do something important enough to grow over time to become an integral part of the business. Unfortunately no one can identify up front which of that few percent of spreadsheets will become the ones that are integral to the business, and its only know with hindsight that it should have been implemented as an application.

        1. Doctor Syntax Silver badge

          There comes a point in the life of every useful ad hoc application where it should be re-implemented from scratch in a way that enables it to be supported and adapted to changing requirements. The trick is to identify that point correctly. If you miss it the entire mess has grown out of hand, the data set has grown to big or complex, the original author has moved on leaving nobody who quite understands how it was supposed to work or you end up rewriting the damn thing every time a new requirement comes along because it's quicker than the rewrite and the fact that you've got 20 variants to select the correct one form is something that only dawns on you when its too late

      5. Anonymous Coward
        Anonymous Coward

        I don't need "Because" to hate my colleagues.

    2. Jonathan Richards 1
      Facepalm

      Cri de coeur

      >the IT department control freaks hate it

      IT (Information Technology) bods don't care as much as the system architects, and auditors. If you've built a solution in Excel, it's like the write-only code of yesteryear that we all wrote once, in BASIC, or FORTRAN, or APL. I defy anyone to audit a moderately complex spreadsheet delivering a real business function, and be sure that it really does what it is meant to do [1]. And that's just the review copy. Any fule with Excel can chop and change the sheets in the book. I know, there are cell protections and passwords, but when you start to have to manage like that, then Excel is no longer the tool for the task.

      Control is a necessary function, and you don't have to be a freak to undertake it. Kinda helps, though.

      [1] if you know what 'meant to do' means. It probably doesn't have a requirements document behind it.

      1. DoctorNine

        Re: Cri de coeur

        And typically no one documents the document functions either. It's all 'put it it in and see if it outputs correctly' on the design. Then when parameters are shifted, or something goes out of bounds, no one even knows that the bloody thing is orders of magnitude off. I feel like someone just affixed panniers to a unicycle, and is selling it as a viable transcontinental adventure bike. Gads.

    3. nijam Silver badge

      > It allows visual people to envisage the problem and solutions.

      It allows visual people to envisage the problem and enhance it - bigger, better, faster problems.

    4. Persona Silver badge

      The reason why it's a good tool is that the person who understands the business problem can try out a few things and have something "working" pretty quick so know that what they want is possible.

      Perhaps they then go to the IT team and ask for an application to solve the problem. The following week after an hours meeting they are not comfortable they they have been very effective at explaining the problem to IT, but do now know about having to do requirements docs, user focus meetings, test plans and other stuff they can't quite recall (but have a feeling it involves IT running very fast in races?). It's also going to be surprisingly expensive so they will also need to get budget approval through their management chain. So after lunch they get their spreadsheet out again. They can't understand quite how it worked so they throw it away and write a new version that sort of works.

      1. Krassi

        "The reason why it's a good tool is that the person who understands the business problem can try out a few things and have something "working" pretty quick so know that what they want is possible."

        Spot-on. A great prototyping tool.

        Bugs are not unique to Excel, so when the "proper" software comes back from the dev team for review, and the Customer has to do some UAT, what are they going to reference the new package against.... their good old Excel spreadsheet !

  8. Tim99 Silver badge
    Gimp

    As an exercise

    Last year I created a tool to do some specialized statistical analysis with Excel. As it was pro bono and designed to be distributed to several organizations with a wide range of IT abilities, I decided that it would only use cell formulae and not VBA or Javascript. The process was surprisingly tedious. After about ten days (I'm retired) I started again and cobbled a new version together with Apple's Numbers. Numbers is not as powerful, but I had a working debugged prototype in less than a day. Getting a few people to use it on iPads over several weeks showed a minor bug and a couple of requests for changes. When the users were happy, it took less than a day to export it to Excel - The formulae were all good, just a bit of tweaking of a couple of Highlighting Rules was required (Opening the Excel file with Numbers, made a couple of changes - Re-exporting back to Excel was then OK) .

    Spending a bit more time with Numbers showed that the biggest problems were that it allowed a user to create multiple tables in one sheet (which have to be exported to separate Excel sheets), and it does not have the ability to lock individual cells (a kludge is to put locked transparent shapes over the cells). Most of my ad-hoc spreadsheet stuff is now done on an iPad Pro with Numbers - Oh dear! I look like a fanboi...

  9. katrinab Silver badge
    Meh

    You can already write your own formulae in VBA, and have been able to for at least 25 years.

    Not saying you should, but you can.

    1. nijam Silver badge

      For what it's worth, I always thought VBA was created by someone who'd heard of programming languages, but never actually seen one. Or knew what they were for.

    2. Doc Hoffman

      Much ado about nothing

      Correct. The process to do it is quite simple and very powerful. It's great that Microsoft is bringing this functionality to the foreground, but I can't help feel that the loudest critics here are the most ignorant to the workings of Excel.

      In order to sensationalise the article the author links to an article that is critical of the Ribbon that was published in 2007 when the Ribbon was launched. By nature we tend to be resistant to change, but we usually learn to accept and integrate that change into our lives when we discover the benefits. The Ribbon is fantastic. It's highly customisable, easy to navigate and it's flexible. If you're going to reference critics in your article, at least keep it current and relevant.

      1. Anonymous Coward
        Anonymous Coward

        Re: Much ado about nothing

        The ribbon is still an abomination to me. Don't shoot the messenger. Just because you like does not mean you are right.

    3. Ben D
      Thumb Up

      XLM

      Absolutely - and XLM before that!

    4. PhilCoder

      Yes, writing your own vba functions for use in worksheet formulas has been a mainstay of Excel development for decades. Not sure what extras lambda is offering?

    5. Anonymous Coward
      Anonymous Coward

      Excel and Active Directory...

      Once got dumped with an Excel spreadsheet that was not working, asked to take a look. The user support desk bod who'd left 2 years earlier had used VBA hooked up to some some obscure library to add, remove and adjust permissions direct in AD using...an Excel spreadsheet!

      One day it stopped working and no one could work out what was wrong. I took one look, marveled at the ingenuity and immediately posted a PDF of "AD Powershell Cmdlets for Beginners" to the support desk and told them start booking some Powershell/AD courses. I then copied the wonderful piece of code for my collection, coded them some basic Powershell scripts to help them get by for a few weeks and told them if they ever ran the Excel sheet again I would report them to the security officer!

  10. AndrueC Silver badge
    Joke

    I will bear this in mind the next time I need to develop a world beating test & trace app. Thanks Microsoft!

  11. LeftyX

    Shouldn't that be "a horde of spreadsheets"?

    Or is there some other term for a group of them, like a murder of crows?

    1. Anonymous Coward
      Anonymous Coward

      Re: Shouldn't that be "a horde of spreadsheets"?

      How about "an abuse of statistics"?

      You can't really make a statement "most widely used programming language" when most of that is just adding up columns and drawing a graph. It has to be something where users are actively programming the thing...

      1. Mark 110

        Re: Shouldn't that be "a horde of spreadsheets"?

        Many Excel users will use an IF THEN and stack them to get an ELSE. Its coding of a sort.

        I use Excel to quickly solve basic data analysis tasks within an hour or two - if I waited on Devs I would wait 6 months to get on the roadmap for next year. Agree with all that enterprise solutions based on Excel are unwise but Excel is great for those 'need to understand this dataset today' problems that you do about 10 times a year and you don't have access to, or never learnt to use, better tools like Power BI, etc.

    2. Doctor Syntax Silver badge

      Re: Shouldn't that be "a horde of spreadsheets"?

      A sum difference of spreadsheets.

      1. Michael Wojcik Silver badge

        Re: Shouldn't that be "a horde of spreadsheets"?

        A "column" of spreadsheets (or a "row", but similar things are more often grouped as a column, and I like the martial association) would work too. "I went into the office today and faced down a column of belligerent spreadsheets."

        1. TRT Silver badge

          Re: Shouldn't that be "a horde of spreadsheets"?

          A laundry basket of spreadsheets.

  12. chivo243 Silver badge
    Paris Hilton

    LAMBDA?

    Doesn't AWS already have something called this? or is it CASE sensitive? AWS is lower case.

  13. Blackjack Silver badge

    Sorry, but HTML counts

    And HTML is way more used that Excel is.

    1. nautica Silver badge
      Boffin

      Re: Sorry, but HTML counts

      ...and counts infinitely more than however it is you characterize creating a spreadsheet; +10 ▲.

      Since when is 'Excel' considered--or even thought of as--a computer language?

      Never mind...consider the source: everything is true by fiat.

      1. logicalextreme

        Re: Sorry, but HTML counts

        It's the horrible formula language specifically that they're referring to. You know, + and * and IF() and VLOOKUP() and all that.

    2. ecofeco Silver badge

      Re: Sorry, but HTML counts

      It is? Becasue most websites I see these days are created in everything BUT HTML.

      1. Mark 110

        Re: Sorry, but HTML counts

        HTML is not a programming language. It formats a webpage. You can't write an IF THEN ELSE in it. You can in Excel.

    3. Plest Silver badge
      Facepalm

      Re: Sorry, but HTML counts

      Ha ha ha, spoken like a true millenial who never did CS at college instead did web design and thinks they're IT bod. HTML a markup framework like the wooden frame in a house, it does nothing and without it's cohorts CSS and JS, it'll let the rain and wind piss through! CSS is the plaster, paint and wallpaper. Javascript is the plubming and electrics, although to be honest if JS was running the utilities in my house I'd fecking move out ASAP!

      1. Blackjack Silver badge

        Re: Sorry, but HTML counts

        HTML a markup language and even BASIC let's you do more things that HTML does but by most accounts, is a programing language.

        Sure, the Web without Javascript is quite basic, but many many many websites used to be made that way and it worked. Only way to get a virus online back then was by downloading it.

        What is holding HTML back is Flash (thankfully it should be gone next year) and Javascript plus the need to be backwards compatibility.

  14. SysKoll

    DavCrav, in Excel's case, that saying should be slightly amented: "When all you have is a hammer, you should rethink your career choice as a glazier."

    1. TRT Silver badge

      Windows installation?

  15. uro

    Excel is the scourge that just wont die.

    I can literally hear the life-blood of programmers and scientists being sucked out them in the background upon reading this article.

    I'd have thought surely by now we'd have an open document format for sheets which is both backwards and forwards compatible with databases (either directly or via db extension), so instead of passing around a dinosaur file format you could just send a database along with it's rules, table links & functions, and the end user could then load it into their own database or into their office suite of choice and not be tethered to Micro$oft.

    1. Falmari Silver badge
      Joke

      Uro's got a time machine

      Hey @uro Can I take a trip in your time machine. :)

      “open document format for sheets which is both backwards and forwards compatible”, that’s impossible, you can’t make something compatible with something not yet created. Well not without a time machine.

      Joking aside I understand what you meant a standard that you conform to going forward. Yes, I don’t understand why this has not been done.

    2. katrinab Silver badge
      Mushroom

      Re: Excel is the scourge that just wont die.

      We already have that. It is called Excel.

      No, people should not use Excel as a database, but they do.

    3. ecofeco Silver badge

      Re: Excel is the scourge that just wont die.

      Already happened.

      Google : Scientists Forced To Change Names Of Human Genes Because Of Microsoft's Failure To Patch Excel

      Happened just a few months ago.

  16. This post has been deleted by its author

  17. Grease Monkey Silver badge

    You can get up to things that I would call programming in excel (although I wish you wouldn't*) but a formula does not constitute programming.

    *It's long been a problem with excel that it allows idiot beancounters and the like (not managers, they're usually to lazy) to create huge data mangling edifices off which they can chuck reports to the board. They usually work at first, but then art some point in their ever more complicated lifecycle they stop working as expected and start throwing out blatantly incorrect figures or better yet error messages. At this point the IT department are called in to fix things. Often ten minutes before the idiot is due to present to the board. And if course it's an IT problem, not the fact that the idiot isn't actually capable of building a complex spreadsheet and should have gone to an expert in the first place.

    1. TRT Silver badge

      I reckon the break point between programming and formulae is when you can do a conditional. Mind you, I really, really annoyed the person trying to teach me Python by solving many of the problems they set that were intended to teach the use of conditional statements and branching by using formulae instead. Yes, it's perfectly possible to perform three different calculations depending on a value being a negative, zero or a positive integer without using IF etc. Makes the code a bastard to read, though.

  18. DaemonProcess

    The road to hell is paved with...

    ... linked sheets with hard-coded drive letters. I once had to migrate an accounts department's IT. The dedicated accounting packages were easy to move. They did not inter-operate and were in some cases more expensive and difficult to maintain than the business ever got benefit from. So the department mostly ran on Excel as the integration tool. 3 sub-teams had differently mapped drive letters corresponding to different file servers in different offices (which used to be different organisations) and these letters were hidden all over the place in their linked sheets. I have my head in my hands just remembering this. Having to preserve historical financials as well, trying to persuade them of the benefits of UNC paths and variables... just horrible. This sort of mess will not be re-factorable (is that a word?) easily.

    1. ecofeco Silver badge

      Re: The road to hell is paved with...

      As yes, The same kind of people who make their own home repairs and burn down the house a few years later.

  19. Nick Ryan Silver badge

    "Excel formulas are the world's most widely used programming language, yet one of the more basic principles in programming has been missing, and that is the ability to use the formula language to define your own re-usable functions,"
    Seriously? What would be much better is any form of decent inspection functionality, as in the capacity to inspect and debug the bloody formulas rather than treat them like a black box of doom.

    It would also be nice if Microsoft fixed the damn bug that's existed since, I don't know, a couple of decades at least, where Excel randomly decides that a function in a cell is just text rather than a function and it's a dead chicken waving moment to try to convince Excel that the contents of the cell is a function and not text... without wiping out every bit of metadata about the cell.

  20. a_yank_lurker

    Excrement

    So Excel is the excrement of the Rejects of Redmond. The mangling morons are telling idiots it's all right to write code in a spreadsheet with no documentation or proper testing. I wonder how many companies will go under when the merde spewed out is so wrong that C-suite has no idea about the financial health of the company until payroll bounces.

    Spreadsheets are useful for fairly limited situations and calculations - those that can be verified by inspection of the formulae. But what many do is beyond what should be done in spreadshit.

  21. anonymous boring coward Silver badge

    "Excel formulas are the world's most widely used programming language"

    It seems more likely Javascript i the most used one.

  22. MarkET

    Excel / VBA etc.

    Not to be confused with actual programming please.

    Remember the divide bug exhibited by certain processors / software toys a while ago?

  23. Long John Silver
    Pirate

    A spreadsheet too far?

    All readers are aware of the convenience of spreadsheets for storing data. Moreover, tabular representation can reveal by visual inspection, this supplemented by some basic calculations and easy graph/chart construction, key features relevant to decision-taking. Appeal rests firmly with how readily spreadsheet technology enables people to do many tasks without recourse to others with specialist skills.

    Downside arises from the restricted view of data analysis imposed by spreadsheets. it may be encouraging many to seek increased analytic power by beefing up spreadsheets rather than looking for simpler to use alternatives. Additionally, other than for simple analysis spreadsheet formula use, built in or now bespoke, is a messy approach to programming. I hazard that complicated cross-referencing formulae embedded in cells is not only difficult to set up but also error prone.

    Thus, instead of continuing with increasing spreadsheet complexity their distributors, commercial and open source, might offer better service by complementing spreadsheets with easy to deploy software that abstracts necessary rows and columns for analysis. Perhaps sometimes the results can be returned to the originating spreadsheet but oftentimes they may better be displayed by other means; statistical analysis beyond basic descriptive statistics is an example of the latter.

    The great advantage of abstracting data for analysis rests with the code being easily re-used and adapted. It naturally encourages good coding practices.

    Obviously the approach mentioned above is already in widespread use. When databases of greater complexity than spreadsheets are handled it is necessary to abstract desired collections of information as raw input to other programs; note that in the minds of people drawing from complicated database structures there is no inclination to believe embedding analysis within the database, even if feasible, offers advantage. Also, some statistical packages in common use, e.g. SPSS, store data in rows or columns, give users opportunity to derive further rows and columns, but don't make analysis procedures part of the notional cells in the data store.

    It seems time to recognise that spreadsheets have gone further than desirable as combined data stores and analytic engines.

  24. Binraider Silver badge

    Want to really scare someone; create a sheet where iterative calculation is a requirement. Good luck getting anyone downstream to work with it ever again. There are various IEC standards that require iterative calculation to implement in Excel; and this is widely practised. Commercial solutions to the same problem exist, but have some really mind-numbing bugs and errors. If said commercial options get simple stuff wrong, god knows what the black box gets wrong with the complex parts.

    Writing your own solution to such problems in a proper language is a learning exercise in itself and believe me we've tried - our predecessors used various marks of Fortran. More recently we've tried Java; Python and even GNU Octave. The excel implementation, for all that can go wrong, is arguably still the most accessible and easy to translate from user-to-user, often where those users are simply not required to be programmers.

    I'm of the opinon that a spreadsheet can be an over-complex beast; though using lots of formulas if not VBA is not necessarily the end of the world... However, when one's business becomes grossly dependent on large numbers of such beasts; the single-person dependency that comes with each one is also huge. And the potential for error in any large sheet is demonstrably enormous. In the case of the IEC standard I'm thinking about - reference calculation examples exist; which, ironically had themselves had errors in them necessitating revisions to the standards!

    All that said, the biggest blocker to doing anything properly is the red tape. Getting the chequebook out to do proper IT is rare and persuading people to staff and skill up accordingly is a losing battle when engaged in a race to the bottom on price. There is a saying, you get what you pay for. Preventable disasters are "accepted" norms because of the race to the bottom. Exhibit A: Railtrack. Exhibit B: BP. Other examples very readily found.

  25. Do you want to play global thermonuclear war
    WTF?

    Test and trace displayed how excel can be dangerous when it limitations are not understood.

  26. Matthew "The Worst Writer on the Internet" Saroff

    We Live in Hell

    That is all.

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Other stories you might like