Coffeehouse Thread

11 posts

VBA Horror Stories! Tell us about the sin ...

Back to Forum: Coffeehouse
  • User profile image
    Sabot

    What is the worse bit of VBA code you have ever seen?

    You know the story, some 'user' or 'script kiddie' has "Tool-maxed" an Excel spreadsheet or Access database and made the spreadsheet or database of nightmares, (which so happens to be business critical so your Boss is doing his Nut!) full of spaghetti code, scary functions or just plain school grade stupidity ... and it's now your job to fix it !!!

    2 years ago, I was give a set of spreadsheets that were written by someone in the business who was 'gifted' at this sort of thing and had left the company to pursue a career in I.T. The spreadsheets were a complete nightmare, I do wake up in a cold sweat to this day thinking about them.

    1) The spreadsheets VBA used labels to create thousands of line numbers, with hundreds of Goto's. it was a nightmare to debug!

    2) Not one declared variable! NOT ONE!!!!! Hundreds of variants.

    3) One function that had over 10,000 lines of code.

    4) As for comments? Do you think there was any? Nah!!! not surprised are you????

    Anyway, this is the thread to get it off your chest, tell channel 9 about that well remembered coding sin!

    (think of it as pay back to Microsoft for putting VBA in Excel and Access in the first place Tongue Out )

  • User profile image
    Jeremy W

    And here was me thinking ALL VBA was a horror story Wink

    Seriously though, I haven't encountered any, mostly because the companies I've worked for were either IT shops or had stupid users (I was impressed if they knew how to USE Excel).

    I guess I'm spoiled that way.

  • User profile image
    Knute

    Sabot,
    I feel your pain. My first paying VB job was on a project very similar. I had inherited a previous tool from another developer and was tasked to turn it into a new version.

    Some of the nightmarish features were:

    1. Useless comments. He commented trivial things that were totally obvious and for the more heinous methods, not one comment or clue.

    2. Names that made no sense. He named some methods things like "Finished Veggies" WTF is that? This program has nothing to do with veggies!!!

    But it was a tremendous learning experience as I had to go through the code piece by piece and figure out what was going on. I am sure he was somewhere laughing his a$$ off.

    ~ Knute

    ps - I know that this is not VBA, but thought I would vent over this pain...

  • User profile image
    miies

    Not exactly VBA, but the stupidest thing I've ever seen is

    Sub Print(string)
       Response.Write string
    End Sub

    Saving space is one thing, but this..

  • User profile image
    Manip

    miies wrote:
    Not exactly VBA, but the stupidest thing I've ever seen is

    Sub Print(string)
       Response.Write string
    End Sub

    Saving space is one thing, but this..


    What are you talking about? You can't have a sub called 'Print' in VBA, it is reserved.

  • User profile image
    miies

    Manip wrote:
    miies wrote: Not exactly VBA, but the stupidest thing I've ever seen is

    Sub Print(string)
       Response.Write string
    End Sub

    Saving space is one thing, but this..


    What are you talking about? You can't have a sub called 'Print' in VBA, it is reserved.


    Have you read the first line of my post? it's ASP, actually.

  • User profile image
    spod

    Well it's not a specific example, but i've seen quite a few apps that seemed to use these guidelines as their coding standards...

    http://www.afralisp.com/vbaa/unmain.htm

  • User profile image
    qwert231

    Sadly, having no formal training, I've succumbed to the spaghetti way of coding. But no more... I've had to suffer supporting my own work. (Which I am sure will please many of you pros...)

    I even comment for myself.

    I did a whole site in VBScript (ASP) and I look back wondering how I ever got data in and out of my database.

  • User profile image
    Sabot

    Microsoft do print the 'bible' of all 'how to produce good code' books and his name is "Code Complete"

    http://www.amazon.co.uk/exec/obidos/ASIN/0735619670/ref=sr_aps_books_1_1/026-9521030-8063644

    I have sworn by this book for 10 years! Mmmm, time to buy the latest edition. I wonder if the new edition covers C#? Could anyone in Cyberland tell me?


  • User profile image
    reilly

    Horror story or work of genius?  This is a complete pacman game implemented using VBA in Excel (using spreadsheet cells as pixels!):
    http://www1.plala.or.jp/chikada/vba/pac/pacelle.zip

    The home page (in Japanese) is here:
    http://www1.plala.or.jp/chikada/vba/vba.htm

  • User profile image
    Manip

    If you want to produce good quality code in VBA do the following:

    - Option Explicit (From the start!)
    - NO ERROR CONTROL! (Do not use error control to avoid making things correctly..)
    - Goto is banned.

    If you follow these simple instructions you've already got a fairly good standard of code.. then just write.. optimize... test... write... optimize...

    Look at this:

    Open sz For Input As #7
    ERRLINS = 0
    Do
     THISLIN$ = "**END**"
     On Error Resume Next
     Line Input #7, THISLIN$
     If THISLIN$ = "**END**" Then Exit Do
     If THISLIN$ <> "" Then
      'Code.....
      '......
     Else
      ERRLINS = ERRLINS + 1
     End If
    Loop

    If ERRLINS > 0 Then IMPERR$ = " (Import errors: " & ERRLINS & ")"
    Label1 = "Status: IDLE  (" & ListView1.ListItems.Count & " ranges)" & IMPERR$


    Some of the variables where defined.. but worse is the use of error control to handle the EOF! Here is how it SHOULD have been done:

    Dim FreeStream%, THISLIN$
    FreeStream = FreeFile
    Open sz For Input As FreeStream

    Do while NOT EOF(FreeStream)
     Line Input #FreeStream, THISLIN$
     If NOT THISLIN = "" Then
      'Code.....
      '......
     End If
    Loop

    Label1.caption = "Status: IDLE  (" & ListView1.ListItems.Count & " ranges)"

    This is before any error control (non-release). The code fragment is from a semi-popular application that a couple of Channel 9 users are probably running.. that is how poor some VBA coders program.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.