back to article Excel abuse hits new heights as dev uses VBA to code spreadsheet messaging app

We shouldn't encourage readers to waste their time like this, but it's the kind blend of wonderful insanity that springs from a sysadmin with time on his hands: an enterprise instant messaging platform that runs in Excel. Of course, nobody would want to deny Microsoft the chance to stub its toe properly by writing its own …

  1. Andrew Jones 2

    When I was at College, I made mine in Visual Basic, saving the chat unencrypted, in plain text to a dll file in the mapped network drive we all had access to. But I took a print screen of MS Word 97 and used that as the background of my form so when the lecturers walked by - they thought it was just Microsoft Word. It worked surprisingly well. People were less suspicious in the late 90s... they would run anything without a second thought (as evidenced when Newcastle University students sent the Win32CIH virus to someone in the College which was called "Porn2000.exe" and brought every computer in the College to it's knees in an incredibly impressive 3 and a half hours)

    1. David 132 Silver badge
      Thumb Up

      People were less suspicious in the late 90s.

      Some people are still not suspicious even now.

      Unable to believe that there was, really, an octopus glyph in the Segoe UI Symbol font (WHY, Microsoft?) I wrote a trivial Winforms app recently that just displayed the glyph, large, in the centre of the screen. Sent "octopus.exe" to my colleagues and they unthinkingly ran it.

      And then I had to explain that no, it wasn't supposed to do anything at all. Isn't a large octopus in the centre of your screen reward enough?

      1. Anon
        Black Helicopters

        Segoe UI Symbol WHY?

        Because nothing is beyond our reach:

        https://en.wikipedia.org/wiki/USA-247#/media/File:Nrol-39.jpg

      2. Rob Gr

        "WHY, Microsoft?"

        Because it is part of the Unicode character set perchance?

        1. Anonymous Coward
          Anonymous Coward

          flame on

          >Because it is part of the Unicode character set perchance?

          Unicode only exists because the US didn't have the balls to use its nukes to full effect right after WW2.

      3. John Brown (no body) Silver badge

        "Isn't a large octopus in the centre of your screen reward enough?"

        Bastard! I just *have* to use that in a conversation before the end of the week. And it's already Friday! Why could you not say that on Monday?

  2. Captain DaFt

    Yes, but...

    Can they use it to emulate Logo?

  3. Gene Cash Silver badge

    Aw yisss... as much point as the college friend that wrote an assembler... in COBOL.

    1. tony2heads

      @Gene Cash

      I know a guy who wrote a full screen text editor in FORTRAN for a bet.

      1. Sgt_Oddball Silver badge

        Re: @Gene Cash

        I came across an article today where someone wrote a workable OS for his CV... (apparently the bootloader was the hardest part)

      2. Alan Sharkey

        Re: @Gene Cash

        Pah - Easy stuff. I did mine in Pascal.

        1. Stevie Silver badge

          Re: @Gene Cash

          Pah, I did my full screen editor in line editor macros.

          Also wrote a Life machine in Cobol. I mapped the surface to a toroid.

          1. ben kendim

            Re: @Gene Cash

            Did mine in 6800 assembly...

            1. wikkity

              Re: @Gene Cash

              I started mine with C++/Delphi. Thought it would be cool to edit the source with the prototype only to discover a bug in the save and lost the code so moved on to my next project.

              1. Anonymous Coward
                Anonymous Coward

                Re: @Gene Cash

                When MicroFocus were best known as writers of commercial compilers they had a Space Invaders game that was written in Cobol - IIRC it ran on DEC hardware, maybe a PDP & VT120 ?

                That was a few years ago now.

            2. razorfishsl

              Re: @Gene Cash

              did mine in 6502 on the old Acorn Atom.....

        2. Doctor Syntax Silver badge

          Re: @Gene Cash

          "Pah - Easy stuff. I did mine in Pascal."

          UCSD?

      3. el_oscuro

        Re: @Gene Cash

        I once wrote a full assembler with the complete 6502 instruction set in Commodore 64 basic - because I didn't have the $30 for the official one from Commodore.

    2. Steve Evans

      And why not...

      I once wrote a 6502 assembler interpreter in BBC Basic...

      Come on, I can't be the only person who felt a programming urge, but couldn't think of anything worthwhile to write!

      Sometimes it's the journey, not the destination which is the entertainment.

      1. HighTension

        Re: And why not...

        I did a disassembler in 6502 assembly (BBC B) because the one I wrote in BASIC was too damn slow, and the curses-like hex editor I'd just done wasn't difficult enough. Fun days!

  4. Novex

    Oh mighty, invisible, non-gender specific, big entity in the sky...

    ...please save me from Excel absurdity such as this. While it's 'fun' to see how far to push a program with in-built coding capability (I've done it myself in Access) it's such torture to try and keep these things going because some PHB (and sometimes not so pointy) decides they actually like it and want to keep using it!

    As Mr. Scott once said (in Star Trek V, I believe): "The right tool for the right job". Excel is really pretty much only for data analysis. For everything else, there's 'proper' development tools.

    1. Naselus

      Re: Oh mighty, invisible, non-gender specific, big entity in the sky...

      ...you have no sense of joy.

  5. Anonymous Coward
    Anonymous Coward

    programmed a refund system for Jamster back in the day, in excel 97 !! NO WILDCARDS Nooooooooo

  6. Anonymous Coward
    Anonymous Coward

    "Excel is really pretty much only for data analysis."

    If it can change - then anything is data.

    An Excel spreadsheet is a useful way to store lists which can then be sorted and filtered on many different criteria.

    I use it to store details of international choirs and their video/events web pages: Facebook, Youtube, Vimeo, and custom sites.

    That information is then used by an automaton in VBA/Selenium to analyse any changes to the pages. That takes about 4 fours overnight with a VB.Net watchdog to overcome any unexpected Excel/browser processing hang-ups.

    Each choir has a spreadsheet that is the history of changes to the page. Only potentially relevant new items on a page are flagged for human decisions. It also remembers which videos have been found before even if the URL is different. Another spreadsheet remembers if Excel or the browser crashed while previewing a particular video.

    Other spreadsheets remember the details of: geographical locations and venues; performance titles; common city name translations in English; month and day of week translations.

    The HTML analysis parameters for each page are held in Notebook rather than Excel. They can then be edited easily while VBA is running should a page's HTML format change significantly.

    The human interaction is with a layered set of forms and dialog boxes that fill a 27" 16x10 screen plus a 19" 4x3 secondary screen.

    The Excel VBA finally produces and manipulates Word documents - the distillation being spewed out as plain text .json files for publishing on a Google Maps javascript page.

    Like Topsy - "it just growed". Moving it to Linux Mint will be a challenge.

    1. wikkity

      useful way to store lists which can then be sorted and filtered on many different criteria.

      There's this new fangled thing called a relational database ;-)

      > Moving it to Linux Mint will be a challenge.

      That is the crux of most peoples issues with excel, It's great for 10 mins of mucking about with data and throwing it away. Beyond that for anything useful it becomes a pit of man hours and only the person who lashed it together and hack every last suggestion in knows how it works. Every new feature means huge changes, often actually rewriting it. I've come across people who have survived redundancies because their spreadsheets have becomes so tangled up in processes.

  7. Doctor Syntax Silver badge

    The development manager at one of my clients wrote a sudoku solver in VBA in Outlook. If only he'd stuck to that and not tried to manage....

    1. Tony Haines

      I've seen someone write a sudoku solver in Befunge.

      Because I challenged them.

      1. wikkity

        I've seen someone solve a sudoku in tumbidge wells

        Does that count?

        1. John Brown (no body) Silver badge

          Re: I've seen someone solve a sudoku in tumbidge wells

          No, it's only an officially recognised achievement if it happens in Tunbrdge Wells.

          1. W.S.Gosset Bronze badge

            Re: I've seen someone solve a sudoku in tumbidge wells

            >>Re: I've seen someone solve a sudoku in tumbidge wells

            >No, it's only an officially recognised achievement if it happens in Tunbrdge Wells.

            shorely that should be Tunabridged Wells?

  8. Dan 55 Silver badge

    "Messages are delivered within 40 seconds"

    So it's giving Lync a run for its money.

    1. Bullseyed

      Re: "Messages are delivered within 40 seconds"

      I like that while Lync is dead, Skype for business still says "Lync has disconnected" if it messes up your business meeting.

      Also, sometimes I send an IM to someone and then leave for their desk and arrive at the same time my message does.

      1. tin 2

        Re: "Messages are delivered within 40 seconds"

        My current favourite is where s4b insists the message has not been delivered* and an hour later the recipient responds to said undelivered message.

        * due to some severe, insurmountable problem like a bit of packet loss or something.

  9. David Roberts Silver badge

    When all you have is a hammer......

    ......every task looks like a nail.

    Nobody can stop the use of any office product for scripting tasks because the person with the problem only knows one scripting language. Real coders are nowhere in sight and would anyway get sniffy about requirements, testing, budgets, ongoing support.

    If I had a pound for every middle manager who said "what's the problem? I knocked this up over the weekend and it works fine." I would have spent it all on beer long ago.

    1. Bullseyed

      Re: When all you have is a hammer......

      > ......every task looks like a nail.

      It's not like these business people making these apps have a copy of VS provided by their boss and refuse to use it... And chances are their IT department wouldn't allow them to run code they wrote anyway.

  10. phuzz Silver badge

    I can't be the only person that uses a spreadsheet (Libre office in my case) to analyse logs can I?

    Import as a CSV, and then you can filter and sort to your hearts content, and even graph stuff with a bit of pivot table action.

    Yeah, ok, I should probably find a way to feed it into logstash and graphana, but Libreoffice is right there...

    1. Sgt_Oddball Silver badge

      I'm allergic to that kind of thing... manhandling anything that beancounters can fiddle with is asking for problems.

    2. Holtsmark

      I have a spreadsheet that inputs CAN-bus log-files in hex, parses these in binary, and then outputs clear-text information. All using only Excel native commands, no VBA or similar.

      A very useful tool.

      However; if I had the chance, then I would have the dev-. team at Microsof Office undergo some light waterboarding, followed by a brief (by it's very nature) visit to the scorpion-pit.

      Who the hell thought it was a good idea to hard-code the default line with of a charted curve to 1.5?

      ..yes, it looks better for your example containing Bill and Bob, and the months January to March, but once you have 72 lines overlapping, you can not see anything, and you are forced to re format every single line one at a time!

      And, after all these years, why is there no xyz diagram option? It must be very EASY to code, and it would be a good reason to actually upgrade.

      1. Sgt_Oddball Silver badge

        Don't even get me started on numbers in Excel.... why oh why is so hard to understand that not every big number needs 'shortening' to an exponent (really fun if you have part numbers)

        1. wikkity

          RE: Don't even get me started on numbers in Excel

          At least you are working with genes

          http://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7

        2. Anonymous Coward
          Anonymous Coward

          @Sgt_Oddball ..... If it's a part number i.e. just a string of characters that just happen to all be numeric and you don't want to do arithmetic operations on it just set the cell format to text.

    3. JimRoyal

      No. I did it as well. Looking for contract jobs, downloading data from Jobserve, import into Excel and had a VBA filter which took out jobs I did not have the skills for, towns too hard to get to on a daily basis etc. It used to trim the list down from 750ish to about 50 in a minute or two.

    4. Trixr Bronze badge

      I use Logparser - one of those awesome MS apps like Robocopy. Shame they can't extend that kind of quality to their OSes or Office.

      Logparser will process a structured text file (doesn't have to be CSV - it's easy enough to create parsing rules) using SQL-type queries, and is lightning fast. Outputs to CSV, text, on-screen sortable grid and even graphs.

      If you've got big log files (or even not so big ones) or lots in a series, that's the tool to use.

  11. agurney

    Back in the 1980s I used Mentor Graphics' document scripting language on an Apollo workstation to calculate tidal gates and generate tide tables.

    [When I lost access to the Apollo I ended up converting the script to GW-basic .. it now gets fired up once a year in a Windows 98 virtual machine. It still works, so I don't see a need to bring it into the 21st century]

    1. Roger Kynaston
      Thumb Up

      Tide tables

      but does the laptop on your chart table have enough oomph to spin up the Win98VM?

      1. agurney

        Re: Tide tables

        The VM's run at home once every spring to generate these.

        No laptop on the boat, just a couple of Raspberry Pis; one for OpenCPN and t'other running a webcam and weather station .. but off topic as they're appropriate tools for the job.

  12. Jonathan 27 Bronze badge

    This sounds like another example of "wrong tool for the task". If they have Office, wouldn't it make sense to do this in Access, all the controls are in there already. You could have your "messaging app" written in about an hour and messages wouldn't take 40 seconds. Also, you won't hit the issue Excel does as your workbook expands. The solution in the article won't scale very well and will eventually corrupt the "server" worksheet.

    1. Anonymous Coward
      Anonymous Coward

      > This sounds like another example of "wrong tool for the task"

      Give him a break. He could have been doing his taxes on Skype.

      1. Loud Speaker

        He could have been doing his taxes on Skype.<p>

        Is that how Apple do theirs? if so, I would recommend it to anyone.

    2. Roland6 Silver badge

      Access isn't part of Office Starter, Home & Student and other entry level editions...

      The real question is does this work in ALL versions of Office (ie. every version since 4.0 and including 365)...

    3. Doctor Syntax Silver badge

      'This sounds like another example of "wrong tool for the task"'

      Doesn't that describe so much of management?

  13. Anonymous Coward
    Anonymous Coward

    So. Try it in mainframe FORTRAN.

    Annon because it's pretty obvious where I went if you've used this.

    1. H in The Hague Silver badge

      Re: So. Try it in mainframe FORTRAN.

      Actually, the Oxford Concordance Program, a text analysis program (and its PC DOS version, Micro OCP), was written in FORTRAN. Must see if I can find its box lurking in one of the dustier corners of my office and fire it up.

      1. wikkity

        Re: Oxford Concordance Program, ..., was written in FORTRAN.

        You've just reminded me of something I wrote years ago, I should try and dig that out too. A Concordance module to generate indexes that took SGML and TeX source and LatTeX* output. Implemented as a couple of awk 'modules' , an awk module being a script that piped input into other awk scripts.

        Thinking about it awk is a lot like excel, good for lashing stuff together but a maintainability nightmare.

        * I took my copy of the The LATEX Companion home from the office a log time ago after too many comments.

  14. Naselus

    Not excel, but...

    Last year, during a quiet period, I started building up my powershell profile - you know, just some basic functions and aliases for routine tasks, stuff like a short account unlock command so I don't need to go via ADU&C or a short command to load Exchangeshell and run a top-10 mailbox size report. Humdrum stuff to save me five minutes here or there.

    Well, the quiet period dragged on a longer than initially anticipated, and I got a little bit carried away. I ended up building a complete 1980s-style text-based adventure. It contains a complete 10x10 map of areas (stored in a hash table), each with an individual description, a short story line (which revolves around finding some cheese and defeating a Luminous Sprocket), several items you can pick up and use, and multiple NPCs you can interact with. I was just beginning to add another 10x10 level to the dungeon when some genuine work came up and the project had to be abandoned; there's still a downward staircase which just leads to a sign saying 'under construction'.

    But I didn't write it as a separate program - it's literally built into my profile on our production network. When I launch PS, it immediately greets me with "Welcome to Powershell Dungeons of the Endless! You are in a dark passage. Exits are to the North, South, East and West" along with some ASCII art. This has caused me to get some funny looks when someone wants their account unlocking and I respond by apparently launching a text-based adventure game.

    1. GrumpenKraut Silver badge
      Happy

      Re: Not excel, but...

      > I started building up my powershell profile ...

      Gasp! For a moment I thought the thing had some built-in social network.

      "/bin/cat wants to become you friend."

      1. Loud Speaker

        Re: Not excel, but...

        Rather that than /dev/op has poked you!

    2. John Brown (no body) Silver badge

      Re: Not excel, but...

      "This has caused me to get some funny looks when someone wants their account unlocking and I respond by apparently launching a text-based adventure game."

      Ah, a sort of steam punk version of what TV SciFi thought computer interfaces would look like in the future, like playing a first person shooter in a floaty psychedelic universe.

  15. Anonymous Coward
    Anonymous Coward

    A wrong assumption

    right at the start it says that everone has excel open in every cubicle farm

    Let me be the first to say that me and my collegaues only open any MS Office application when we have to and through gritted teeth.

    Even Lookout is accessed via the HTTP connection rather than the .exe

    And our company has sold itself to the devil and adopted Office 365.

    We are getting email from the IT thought police asking why none of us have installed Orifice 365 yet.

    Our next step is to create a Win 7 VM and install it on that VM and only use it when we absolutely have to.

    For us, Office 2010 is all we need.

    Posting A/C because it makes perfect sense.

    1. Gordon 10 Silver badge
      FAIL

      Re: A wrong assumption

      *sigh* if you don't get this it definitely isn't the droid you are looking for.

  16. AndrueC Silver badge
    Thumb Up

    I once wrote a Unix emulator in Amstrad BASIC. I even gave it a version of troff and email. The disk subsystem was derived from CP/M so I had to emulate folders using user areas. Luckily the CPC allowed you to select up to 255 areas so it was quite effective.

    It didn't really do multi-tasking but you could set up an alarm that 'rang' at a future time.

    1. John Brown (no body) Silver badge

      "emulate folders using user areas."

      Only WIndows has "folders". Unix has directories :-)

      (Oh, maybe GEM called them folders too. And AmigaOS. And...)

      1. Nolveys Silver badge

        "Directories" are what actually exist in file systems, be they real or virtual. "Folders" are representations of directories that may or may not be based on reality.

        1. AndrueC Silver badge
          Facepalm

          Yeah, fair comment about the nomenclature, guys. Guilty as charged. I used to develop data recovery software (I was the file system expert ironically) so I do know better. I tried to stick with the program but I'm a Windows developer and no longer in the data recovery field. Just run of the mill consumer stuff. So I've had to give in. Too many meetings where people are talking about folders. Too much documentation (and code) I have to write and share with people.

          Please forgive me - we all have to earn a living :)

  17. Anonymous Coward
    Anonymous Coward

    Cool

    Not Excel, but in the late 80s I used Lotus 123 to size refrigeration units using the tax table functionality.

    If BTU load is over this number, but under this number, then this is the refrigeration unit.

    Saved a lot of time for supermarkets with a couple of dozen units.

    Of course, then we started multiplexing units, so we had to write a real app.

  18. ben kendim

    Used Excel for PC board layout...

    OK, it was a small board, but Excel was the most convenient thing I had right then.

    Select all, set column heights and row widths to 0.05", and use borders/lines with 0.025" thickness, and voila PCB design tool... Can do multiple layers using different colors.

    Here is a sample I just did in 5 minutes...

    https://drive.google.com/open?id=0B6XOKbodr2nOVDV6a014X2JjRVk

  19. keithpeter
    Coat

    Not programming...

    http://www.spoon-tamago.com/2013/05/28/tatsuo-horiuchi-excel-spreadsheet-artist/

    ...but looks rather nice.

  20. Anonymous Coward
    Anonymous Coward

    I think I worked with this guy

    Oh, wait, he did it as a joke.

    No, the guy I worked with did shit like this and was serious about it. Years later I'm still dealing with his absolute *MESS* of spreadsheets littered around my network.

    He "developed" a few "automated" analysis tools, for instance. Previously they were compiled by hand taking half a day a week. Using his "automated" tools, it now takes 2 days AND has the added benefit of the numbers sometimes not adding up ( due to excel being excel and him not having any notion of what programming actually means ).

    I drink a lot.

    1. Anonymous Coward
      Anonymous Coward

      Re: I think I worked with this guy

      No mate you worked with me not him. I really was having a joke. Still laughing too.

      Have you spotted the database connection that occasionally replays the previous years data just to mess with your head? ...... nah just kidding.

  21. taxythingy

    Mixing compressed gases with Excel

    I have an Excel sheet at work controlling serial-connected equipment for mixing compressed gases. I chose Excel VBA for bodging it, as I could put the solution together quickly and didn't have to deal with IT to get a dev environment installed. It does the job I need.

    However... it is stupidly difficult to modify thanks to my shonky programming skills, as I found out when I wanted to blend different gases. I've tried twice to re-write it using C# to be more useful (as a learning exercise), but couldn't be bothered finishing it. Could be there is a different learning in that.

    1. Anonymous Coward
      Black Helicopters

      Re: Mixing compressed gases with Excel

      Great for controlling centrifuges if you copy/paste Stuxnet code into VBA

  22. Anonymous Coward
    Anonymous Coward

    excel abuse

    Used it in the 90's to write lease management system for over 200 assets, including reconciliation of bank payments and allocation etc

    Also wrote a cashbook style small business accounts for a friend that was retiring and going self-employed to use to start out with

    Sometimes when it's all you (or others) have to hand you have to make do best you can

  23. Oscar Pops
    Happy

    Badminton Game Picker

    I play badminton in a small club of 20 people. We use Excel to pick the games (all doubles matches), it picks teams based on giving everyone equal time on court or equal number of games, mixing up the teams as much as possible and keeping the strength of teams relatively balanced. It was fun getting it to work and more fun proving it worked by analysing the games played when disbelievers insisted it wasn't being fair :)

  24. This post has been deleted by its author

  25. Anonymous Coward
    Anonymous Coward

    If we're going to start mentioning non-Excel hammers

    Back in the 90s, over at comp.sys.hp48 we have used our calculators in a diversity of roles, such as:

    * Game consoles (though it doesn't really count as the calc itself came with a version of minesweeper)

    * Remote terminals (I recall doing emails and telnetting to a BBS from hotel rooms with my calculator and a dodgy 9600 baud modem)

    * Garage and car door opener (back when those had infrared remotes)

    * TV remote (or hifi, etc., as you could teach it more or less arbitrary commands via its IR receiver)

    * Thermometer (I never tried this myself, but various people froze and cooked their 48s in various calibration attempts. The thing to understand is that the calculator did not have any temperature sensor as such, but someone, can't remember who, figured that they could measure temperature differences by the shift in the CPUs clock rate. There is such thing as overgeeking it.)

  26. Barry Rueger Silver badge

    Fundraising Drive

    Managed a community radio station.

    One volunteer, yearly, came in with a floppy disk that generated a massive group of linked sheets.

    One sheet* for each radio show; probably twenty shows each day. added rows automatically so that each pledge phoned in could be entered with a name and amount.

    Plus daily summaries, and weekly, and grand totals.

    It was, for its time, (c 1999) a truly wonderful and impressive feat.

    Probably still have it buried somewhere on my hard drive.

    * tabs? We didn't need no stinkin' tabs in those days!

  27. Anonymous Coward
    Anonymous Coward

    It can get worse

    PowerPoint! 'scuse my language.

    One place I worked a sales team was introduced to PowerPoint back in the mid 90s and went crazy for it. To the sales way of thinking, 'if it looks good, it is good'. So they used it for anything they could think of. I've even seen someones annual staff appraisal done on PowerPoint. And it became an arms race with them trying to outdo each other with the flashiest presentation. I even had them complaining to me that someone else had a newer version of PowerPoint than them, when in fact all that the other person had was some extra clipart.

  28. Howard Hanek Bronze badge
    Happy

    Bah

    This project, by one Tristan Calderbank, is pretty special.

    Balderbunk I say!

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–2019