back to article Ever had a script you just can't scratch? Excel on the web now has just the thing

Microsoft has brought in Office Scripts for Excel on the web and pushed out the XLOOKUP function, a replacement for the well-used VLOOKUP, to both web and desktop versions of the spreadsheet program. Excel is a strategic application for Microsoft, being one of the elements that keep businesses hooked on Windows. Web versions …

  1. Binraider666

    I don't know about your experience, but the performance of O362 is so atrocious it is barely fit to be a doc viewer. In practise I download everything and work locally.

    That of course could be a symptom of a slow network and narrow internet pipe, but it's just as bad logging in from home.

    1. elDog

      Yeah. And O361 was even worse. Actually, I rather like O365

      for most viewing, limited sorting/filtering.

      I think it rather amazing that Microsoft after all of its foot-dragging and nay-saying has finally been able to make some decent web apps. Balmer must be writhing on his yacht.

    2. Anonymous Coward
      Anonymous Coward

      Office 2016/365 doesn't perform at all when you turn off its Spyware.

      I bought Office 2016 a few years back.

      Then one of the subsequent Office 2016 updates added and installed an always-on Windows Service in the background.

      When I tried disabling this always-on Service, Office 2016 applications then refused to start up.

      So I now no longer use Office 2016.

      1. Anonymous Coward
        Anonymous Coward

        Re: Office 2016/365 doesn't perform at all when you turn off its Spyware.

        I bought Office 2013 many years ago, upgraded to Office 2016 and have given up both last year. It was unusable because of its inability to remember it was already activated and nagging to activate again. Replaced by LibreOffice, which also means that my family members get the same experience whether they happen to be logged on a Windows or Linux box.

        Anonymous because I feel ashamed having paid good money for both Microsoft Office packages.

  2. P.B. Lecavalier
    Devil

    Excel is a strategic application for Microsoft, being one of the elements that keep businesses hooked on Windows.

    Excel fanbois have no choice but to keep it that way. Bring in someone with just a little bit of R or python know-how, and you realize that this one person can do the job of an army of "analysts" in no time. As a matter of fact, people are often scared of me, because they know if I looked into how they get work done, I may be able to put them out of their misery.

    You may now continue to reinvent the wheel with your puny VBA.

    1. bazza Silver badge
      Devil

      Sure, Excel may we’ll be a lowest common denominator but such things do play a useful role.

      R and Python? Sounds amateurish. I use C, possibly C++.

      1. Khaptain Silver badge
        Coat

        Top level nonsense

        Personally I will use assembler to fill in the bits where I feel that my the minions start to struggle with my machine code..

      2. Khaptain Silver badge

        Top level nonsense

        Personally I will use assembler to fill in the bits where I feel that my minions start to struggle with my machine code..

      3. Rich 11 Silver badge

        Sounds amateurish.

        Pfft. I use an abacus.

        1. Julz Silver badge

          Abucus! You don't know your born. I flip the loader switches with a soggy loofah then spin up a disc on a stick to load the executive. Only then can I throw darts to flip the bits to persuade the system to run my Turning complete code. And don't get me started on printing! The youth of today just don't know how lucky they are...

          1. Korev Silver badge
            Joke

            These people are just annoying show offs, they need a punch card in the mouth

    2. sum_of_squares Bronze badge
      Paris Hilton

      This is not about being a "fanboi". This is about Excel being the (de facto) nr 1 analytics tool.

      Having written a decent amount of VBA myself (I was young and desperate back then, please don't judge) I hate VBA with a passion. But try to convince your pointy-haired boss why you need tableau, pandas, R and whatnot for analytics, when you can do it in Excel.. ("No rocket science, OK?"). Excel and VBA are quirky and weird, but unfortunately they are "good enough" for a lot of things. I've seen things in Excel.. it's scary.

      Also unlike Access (the poor stupid little brother of SQL Server nobody cares for), Microshaft has poured a lot of money into excel over the years and made a lot of improvements. Excel is their cash cow and they know it.

    3. ArrZarr Silver badge
      Megaphone

      For analysing a chunk of data (and as long as it fits in Excel), you can get something out of Excel faster. Then you can display it faster.

      If the data doesn't fit in Excel or is already in a Database, then R or Python may well be better.

      Also, real Excel wizards use formulae for everything. Resorting to VBA is an act of surrender.

      1. Rol Silver badge

        When lesser mortals start drooling over your spreadsheet, seeking a copy for their own use, it is time to make it idiot proof, and only taking it to a level beyond their incompetence will work.

        While many have a passing knowledge of spreadshitting (the act of mucking about with things they don't really know enough about, but think they do) VBA is too many steps away to access, and even then, only a complete buffoon would play with something they haven't the first idea about.

        Once properly coded, the email from the boss wanting to know why everything has turned to shit all of a sudden, and it's your spreadsheet at the centre of it all, never gets sent.

        1. ArrZarr Silver badge

          I have usually had the luxury of working with people intelligent enough to know that they muck around in the spreadsheet at their own peril. Upon finishing a masterpiece, I always find that sitting down and talking them through the most important parts helps - either they understand what's going on and can take the sheet and run with it or they understand that they don't get it and leave it alone.

  3. Pascal Monett Silver badge

    XLookup, that's nice

    Yet another hour for an Excel Advanced training course. Thank you, Microsoft !

    1. veti Silver badge

      Re: XLookup, that's nice

      *Sigh*. Just when I'd learned to love MATCH...

  4. elDog

    As always, the big problem with Excel / VBA / formulas is the lack of transparency and auditability

    Echoing PBL's comment above, using an external set of scripts to drive the spreadsheet is much more performant, allows all sorts of technologies that are just bolt-ons to Excel, and are capable of being audited. For me storing scripts in a git/svn repository and seeing the version differences is immensely important.

    Spreadsheets should just be a display tool, not a full ETL suite.

    1. bazza Silver badge

      Re: As always...

      There clearly is an advantage to doing bespoke functionality in a proper software engineering way. But there simply aren’t that many software devs. Excel’s success (and Lotus 123 and Quattro Pro beforehand) has been its low barrier to entry for some low sophistication “programming”, and that’s allowed an awfully large number of people to get a whole lot of stuff done without troubling devs.

      Stopping spreadsheets getting out of hand is where the judgement comes. I know that bank compliance departments hate Excel because it allows the idiot traders to concoct and run lunatic investment schemes without control, getting the company into trouble before tea time. And then there’s the spreadsheet I suspect HP had when doing the due diligence for buying Autonomy...

      1. keith_w

        Re: As always...

        HP did due dalliance?

    2. Anonymous Coward
      Anonymous Coward

      Re: As always, the big problem with Excel...

      Yep, some control freaks hate Excel. Tough!

      We more creative people use it to develop our concepts and it allows us to see the numbers and patterns.

      Sure, productionise it properly later, but Excel is an amazing tool.

      1. Nevermind

        Re: As always, the big problem with Excel...

        Ah, even more "creatives" use Excel as a text template for all their memos, reports etc....in a well known big corporate..if it has numbers, then it's called a matrix, not spreadsheet.

      2. Rol Silver badge

        Re: As always, the big problem with Excel...

        Properly annotated, and mindfully written, a spreadsheet, with vba coding, could be the only immortal thing of use in an office, still working long after everything else has broken beyond economic repair.

        Vba savvy folk exist in every facet of a company's structure, and can readily tweak useful spreadsheets back to life in minutes, or at least by the end of the week. That cannot be said for any other automation tool that requires a very expensive and rare breed of expert to dawdle in from their last project to fiddle with someone else's code for weeks at £500/day, and present you with THEIR interpretation of what it is you wanted, but isn't quite.

        Our IT department regularly bangs out dysfunctional programs, and takes months to fix the smallest of bugs, while the users knock out spreadsheet solutions for themselves, that function exactly as the worker needs them to, and when the process changes, the spreadsheet gets tweaked immediately.

        Yes it's a dirty way of doing things, but it gets the job done, with the least hassle.

        1. Korev Silver badge
          Boffin

          Re: As always, the big problem with Excel...

          You could also be describing R Shiny applications which now seem to be spreading around our organisation virally...

    3. AOD
      Megaphone

      Re: As always, etc...

      Auditing Excel spreadsheets and their associated VBA code is a solved issue.

      Google "how to audit and version control your excel spreadsheets" for the lowdown.

  5. 2+2=5 Silver badge

    Close[1] but no cigar

    99.9% percent of my Excel macros only affect the current file. If MS produced a macro mode that was unable to access anything outside of the current spreadsheet file in any way, then perhaps we could go back to being able to embed macros again without worrying?

    This solution is nice for some things - and might drive solutions to use the scripting approach - but the fact that the scripts are local introduces a software distribution and installation problem that isn't there at the moment when a team of people need to use the same scripts. (Perhaps there's a 'group repository' option?)

    [1] Actually not very close at all. :-(

    1. BrownishMonstr Bronze badge

      Re: Close[1] but no cigar

      Except there are valid times when you may want to pull several files in using a macro, or perhaps just reading data from those files. So I think being unable to read files or perform some system-level tasks; such as renaming, deleting, moving, or executing files; would hinder some use valid cases.

      I hate using Excel for things it shouldn't be used for. Past a certain complexity, one must refrain from bastardising further and seek a custom implementation.

      That said, perhaps people are more used with VBA and like to use it to automate part of their (non-programmig/IT related) job.

      1. J. Cook Silver badge

        Re: Close[1] but no cigar

        Yep. some of our accounting staff have a pair of spreadsheets that pull data from each other. Their machines run out of memory frequently, probably due to recursion issues.

        1. Naselus

          Re: Close[1] but no cigar

          Or because both spreadsheets are now several hundred thousand lines past the point they should've been moved out of excel and into something more robust...

  6. Anonymous Coward
    Anonymous Coward

    Anyone who has written vba in excel that is data intensive will know this won't work. It's like trying to run them over the cloud. That will never be solved due to data limitations we currently have with internet connections. Access while not a good choice is even worse but used by many people. This is what happens when you rush to the cloud without thinking or testing first.

  7. a_yank_lurker Silver badge

    Security???

    Somehow these macros worry me because anytime you have a macro there is the possibility of it accessing system areas no macro should go. Also, many macros are not particularly well written nor audited for best practices or security.

  8. cb7

    The text under the 1st XLOOKUP example should say "the formula in cell D3" not E3.

    And the sum of the numbers is out by 1 cent.

    Should be $110.69 not $110.70.

    I'm hoping it's just a lazy writer and not a bug in Excel.

    1. Martin-73 Silver badge

      "Hoping" is an odd way of spelling 'omfg they did it again'

      1. veti Silver badge

        As I'm sure you know, that's a hilariously common error. Someone is displaying values as currency (with 2 d.p.), but has forgotten the important step of actually rounding them to that precision.

        1. Anonymous Coward
          Anonymous Coward

          Example

          1.5 + 1.5 = 3.0

          2.0 + 2.0 = 4.0

          2.4 + 2.4 = 4.8

          Now format all, Number 0dp.

  9. Martin-73 Silver badge

    Call me a luddite and downvote me baby

    But internet is NOT UBIQUITOUS, especially on mobile devices, which are the goto even for many professionals when using office type apps. (and now i see why the goto statement was deprecated in BASIC).

    It is possible, even in London, to find a coffee shop with no internet access. (the CLOUD wifi crashed and 5G? how about 1G... sorry we stopped that in '97)

    But all you wanted to do was look up something you KNOW you have seen on that device before.

    The CLOUD (which i still maintain is named because some idiot marketwat misinterpreted those networking diagrams that used a cloud symbol for the Internet) is a BACKUP for your LOCAL STORAGE/APPLICATIONS. geez

    1. quxinot Silver badge

      Re: Call me a luddite and downvote me baby

      All devs should be required to do testing on a computer at minimum specs, loaded with tons of resident applications trying to run in the background, over dialup (with significant packet loss).

      Not only would that be a vaild worst-case test, but their work would comparatively scream in average scenarios.

      1. Martin-73 Silver badge

        Re: Call me a luddite and downvote me baby

        I agree with this entirely. My boss even calls me 'worst case scenario' when doing demand calcs for electrical installations or the equations for surge protectors. Because it prevents callbacks and makes for happy customers (users in IT land)

  10. sum_of_squares Bronze badge
    Trollface

    "Hey, guys! I have a brilliant idea: let's mix Excel with JavaScript! What could possibly go wrong?!?"

  11. Roland6 Silver badge

    ODS Compatibility?

    This seems like MS introducing yet another proprietary scripting language to encourage application lock in, not just to Excel, but to a specific implementation of Excel.

    It would seem MS have no intention of maintaining the offline versions of the O365 applications beyond October 2020...

    1. bpfh Silver badge

      Re: ODS Compatibility?

      Stopping the desktop version completely is probably the only way they can kill VB6^M^M^M VBA.

      1. FrogsAndChips Silver badge

        Re: ODS Compatibility?

        If they kill VBA, they remove the lock-in they have on macro-loaded Excel sheets: if people or companies have to rewrite all their VBA code, they are much more likely to look for alternatives. Once you've removed Excel from your landscape, it doesn't take much to escape MS Office altogether.

    2. Philip Storry

      Re: ODS Compatibility?

      For a while now, I've been saying that they want to kill the offline/local binary versions of Office programs.

      These programs have issues. Their codebase is very old - parts date back to the mid-90s. This came out a decade ago during the OOXML specification debacle, when it became clear that Word has compatibility modes with names like "autoSpaceLikeWord95". No explanation was immediately forthcoming on what that meant though.

      Which means that somewhere in Word there's an entire code branch for spacing that can be activated, but only exists for compatibility purposes.

      Let's be honest, that's a problem. Ignoring the bloat, there's the security aspect. A bunch of code nobody is touching, fewer people understand as they leave/retire each year, but can still be activated by an OOXML document despite being 25+ year old code...

      Word, Excel and PowerPoint are overdue a rewrite. And if you're going to rewrite it, you may as well make it a web based application. Local "cached" versions can still be provided - look at Teams as an example.

      Whether we want it or not, this rewrite is happening. And the desktop apps will be matched by the web apps, then left behind. It's a stealth rewrite.

      I actually think it's probably a good thing in the long run. And long overdue. But I don't expect it to be popular...

  12. Mexo1

    So they can do all this when there isn't even a text orientation option in excel online.

    Literally been on the "user voice" (HA!) forum since 2016

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

Biting the hand that feeds IT © 1998–2020