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

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 )

And here was me thinking ALL VBA was a horror story

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.

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...

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..

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..

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.

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

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.

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?

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

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)"