• (cs)

      VBA code like this is not surprising. Excel lets you "record macros", which takes your actions and spits out VBA code to replicate them. This is usually just enough for someone to figure out how to slop together some code without have any idea what they are doing.

  • Sean Connery (unregistered)

    I feel this pain in my groin.

  • (cs)

    I think the first project I ever had to deal with in VB/VBA/VBscript employed some date stuff like this where I had to figure out if yesterday was the end of last month, end of last year, etc. After spending a few minutes trying to work out some crazy algorithms to detect and correct this, I thought to myself "Hrm, I bet someone else has dealt with this problem. Let me scour the intar-web for help."

    And as it turns out, there are plenty of built-in functions to do what you need. And I saw what I had created and...well it wasn't good, but it was a lot better than this crap. Experience or not, anyone can do a Google search. And using VBA to write Excel formulas for date calculations is an enormous cluster of WTF.

  • (cs)

    You goofed!  The original coder very clearly intended the yyyymmdd to be the yesterday's date... UNLESS... today is March 1st in a leap year.  If today is March 1st in a leap year, business logic dictates that yyyymmdd should be the day BEFORE yesterday... because everybody spends February 29th in a hole... right?

  • (cs)

    I'm sorry, but there is no defense for that block of crap, experienced or not.  Of course, redefining date-time functions is nothing new in WTF, is it...

  • Mario (unregistered)

    I get it, the WTF is that the code should look like this:

    If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"

  • Mario (unregistered) in reply to Mario
    Anonymous:
    I get it, the WTF is that the code should look like this:

    If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"

    Of course, I meant:

    If (IsTrue(IsTrue(xlsDay = 0) And IsTrue(xlsMonth = 2)) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"

    That, and the fact that Range("T8") = 5 should really be:

    FIVE = 5 TEEEIGHT = "T8" Range(TEEEIGHT)=FIVE

  • (cs)

    Alex Papadimoulis:
    In fact, the only time a VBA "application" elicited a "WTF" reaction from me was when the code was well structured and maintainable. No, seriously, there actually was VBA code like that.

    I don't believe you. How does it feel to constantly have your pants on fire? [:P]

  • (cs) in reply to scpoRIch

      Just to clarify my position, I am in no way defending this code. I am merely stating that it is not surprising.

      A lot of VBA stuff like this is written by people who are not even programmers, but managed to figure out enough syntax by examining the output of "record a new macro" to slop together some crap like today's example.

  • Verbosity_Rules (unregistered) in reply to Mario

    Anonymous:
    Anonymous:
    I get it, the WTF is that the code should look like this: If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"
    Of course, I meant: If (IsTrue(IsTrue(xlsDay = 0) And IsTrue(xlsMonth = 2)) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan" That, and the fact that Range("T8") = 5 should really be: FIVE = 5 TEEEIGHT = "T8" Range(TEEEIGHT)=FIVE

     

    sub ReallyBeSureToSaveT8( CONFIRM as Boolean )

    FIVE = 5

    TEEEIGHT = "T8"

       if IsTrue( FIVE = FIVE = 5  <> 3.1415926535 ) AND IsTrue( TEEEIGHT.left(1) = "T" AND TEEEIGHT.right(1) = "8" ) AND NOT IsTrue( "Bob" = "Jane" ) then

       if CONFIRM then

         Range( TEEEIGHT ) = FIVE;

       else

       Range( TEEEIGHT ) = "You didn't mean to call this method!  You are not 31337 as you should be!!!!!!1"

    end if

    end if

     

    Well, I think that's correct syntax anywhoo.

  • (cs) in reply to Verbosity_Rules
    Anonymous:

    Anonymous:
    Anonymous:
    I get it, the WTF is that the code should look like this: If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"
    Of course, I meant: If (IsTrue(IsTrue(xlsDay = 0) And IsTrue(xlsMonth = 2)) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan" That, and the fact that Range("T8") = 5 should really be: FIVE = 5 TEEEIGHT = "T8" Range(TEEEIGHT)=FIVE

     

    sub ReallyBeSureToSaveT8( CONFIRM as Boolean )

    FIVE = 5

    TEEEIGHT = "T8"

       if IsTrue( FIVE = FIVE = 5  <> 3.1415926535 ) AND IsTrue( TEEEIGHT.left(1) = "T" AND TEEEIGHT.right(1) = "8" ) AND NOT IsTrue( "Bob" = "Jane" ) then

       if CONFIRM then

         Range( TEEEIGHT ) = FIVE;

       else

       Range( TEEEIGHT ) = "You didn't mean to call this method!  You are not 31337 as you should be!!!!!!1"

    end if

    end if

     

    Well, I think that's correct syntax anywhoo.



    Brillant!
  • (cs) in reply to Mario

    Anonymous:
    I get it, the WTF is that the code should look like this: If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"

    No.

    They WTF is they forgot AreYouSure()...

    Dim yes = True
    If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2 And AreYouSure(AreYouSure(yes))) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"

  • (cs)
    Alex Papadimoulis:

    Public Sub set_xlsFilename()
      xlsFilename = Format(Date - 1, "yyyymmdd") & ".xls"
    End Sub

    I get it! The WTF is of course the use of the magic number 1, instead of the constant ONE!

    Best, Hugo

  • (cs)
    Alex Papadimoulis:

    Public Sub set_xlsFilename()
    xlsFilename = Format(Date - 1, "yyyymmdd") & ".xls" End Sub



    While we can be sure that tomorrow is always one day away, thanks to the theme song from Annie, I don't think that we have enough evidence to say the same about yesterday.
  • (cs) in reply to Verbosity_Rules
    Anonymous:

    Anonymous:
    Anonymous:
    I get it, the WTF is that the code should look like this: If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"
    Of course, I meant: If (IsTrue(IsTrue(xlsDay = 0) And IsTrue(xlsMonth = 2)) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan" That, and the fact that Range("T8") = 5 should really be: FIVE = 5 TEEEIGHT = "T8" Range(TEEEIGHT)=FIVE

     

    sub ReallyBeSureToSaveT8( CONFIRM as Boolean )

    FIVE = 5

    TEEEIGHT = "T8"

       if IsTrue( FIVE = FIVE = 5  <> 3.1415926535 ) AND IsTrue( TEEEIGHT.left(1) = "T" AND TEEEIGHT.right(1) = "8" ) AND NOT IsTrue( "Bob" = "Jane" ) then

       if CONFIRM then

         Range( TEEEIGHT ) = FIVE;

       else

       Range( TEEEIGHT ) = "You didn't mean to call this method!  You are not 31337 as you should be!!!!!!1"

    end if

    end if

     

    Well, I think that's correct syntax anywhoo.

    At first this appeared brillant until I noticed that if you were a Professional VBA programmer you have not included enough job security. Making it blantantly obvious that you are comparing to Pi is too straight forward. Anyone know of a good complicated and convoluted algorithm to determine Pi?

  • (cs) in reply to JRSTEELE
    JRSTEELE:

    At first this appeared brillant until I noticed that if you were a Professional VBA programmer you have not included enough job security. Making it blantantly obvious that you are comparing to Pi is too straight forward. Anyone know of a good complicated and convoluted algorithm to determine Pi?



    In Excel VBA? No prob. Use the charting subsystem to draw a pie graph, and then divide the circumference by the diameter using trig and geometry to get the values from the graphic.
  • (cs) in reply to scpoRIch

    I've defended code on here in the past, usually citing context as the reason (as in, without more knowledge of the situation and how the code was used, and so on) but there's no excuse in any situation.

    For starters, I'm sure that there's a format string you can specify. I'm pretty sure there's also a set of date manipulation functions that enbable you to say "yesterday". Anyone worth their salt would look for those first.

    I'm kinda not surprised, since people insist on doing things more complicated than VBA was really designed for, but this isn't really that complicated, it's just stupid.

  • (cs) in reply to johnl

    I should really read a bit further before posting.

    Basically, what Hugo said

  • (cs)

    Ummm... what about LEap Year?  Shoudln't that have a large block of mostly innane code as well?

  • (cs) in reply to JRSTEELE
    JRSTEELE:
    Anonymous:

    Anonymous:
    Anonymous:
    I get it, the WTF is that the code should look like this: If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"
    Of course, I meant: If (IsTrue(IsTrue(xlsDay = 0) And IsTrue(xlsMonth = 2)) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan" That, and the fact that Range("T8") = 5 should really be: FIVE = 5 TEEEIGHT = "T8" Range(TEEEIGHT)=FIVE

     

    sub ReallyBeSureToSaveT8( CONFIRM as Boolean )

    FIVE = 5

    TEEEIGHT = "T8"

       if IsTrue( FIVE = FIVE = 5  <> 3.1415926535 ) AND IsTrue( TEEEIGHT.left(1) = "T" AND TEEEIGHT.right(1) = "8" ) AND NOT IsTrue( "Bob" = "Jane" ) then

       if CONFIRM then

         Range( TEEEIGHT ) = FIVE;

       else

       Range( TEEEIGHT ) = "You didn't mean to call this method!  You are not 31337 as you should be!!!!!!1"

    end if

    end if

     

    Well, I think that's correct syntax anywhoo.

    At first this appeared brillant until I noticed that if you were a Professional VBA programmer you have not included enough job security. Making it blantantly obvious that you are comparing to Pi is too straight forward. Anyone know of a good complicated and convoluted algorithm to determine Pi?



    All algorithms to determing pi are convoluted

  • (cs) in reply to John Bigboote

    I got talked into maintaining a "system" (which happened to be the organization's enterprise application) comprising of damn near a hundred MS Access databases (not counting the ones that sat on every user's desktop which probably brought that closer to 200).

    The application had two versions (because the company has several offices in the US and one of them didn't want to upgrade from Access 97) of the main switchboard application.  Every client had their own MS Access database (not surprisingly some were approaching a gigabyte in size).  Every client had unique VBA code, unique reports, unique table structure, etc.  The switchboard application functioned by altering the linktable links to point to different client databases (you could only work with one at a time).  Not surprisingly, no error handling was put in this part so if there was a failure an "On Error Resume Next" was encountered and the application was allowed to run with however many tables got successfully linked got pointed to one client database and how many remained linked to the previous client database.  Everyone in one office had a copy of this database on their desktop machines, and the other office had one terminal server that automatically copied the newest version of this database (approximately half of a gigabyte in size) into each user's directory so they wouldn't be stepping on each others' toes.

    There were import processing databases that you had to open and push what they liked to call a "monkey button" (though I've never been able to determine if it's because they were aware a monkey made it or that monkeys were expected to use it).  This would kick off VBA scripts that would call macros (which incidentally called other VBA scripts) in a huge clusterf*** of processes that were damn near impossible to debug.

    Month end processing had its own database, as did a special reporting database.  These functioned by opening up the databases (again using significantly contorted mixtures of VBA scripts and macros in a cacophony of madness that when joined with the screams of programmers I'm firmly convinced was designed to open the ninth gate) and copying all of the relevant data from each and every client database into it and then kicking off access reports to digest the data.  There were separate month-end processors built in access 97 and 2000 as well as reporting databases.

    They still run this system today, over a year later from when I departed from them.

  • (cs)
    Brian Peterson:
    my favorite is the 'Range("T8") = 5' statement. The rest of the cells could arguably be used for debugging, but what the heck is '5' going to tell anyone?

    It's used for internal tracking by the evil consultant who came up with the code.

    Whenever he finds yet another company gullible enough to beat this function into a production system, he will chortle maliciously to himself and change it to:

    Range("T8") = 6

    Muahahahaha...!
  • (cs)

    To avoid the danger of having to maintain VBA code (or bad written VB code, which is mostly the same), you have to become a Linux zealot. Burry your MSDN boxes. Wear those Anti-MS t-shirts. Replace WindowsXP with Linux on your desktop. If your mouse is a MS intellimouse, replace it with a logitech mouse. Make clear to everyone you would never ever touch anything with the label "Microsoft" on it. If someone has a problem whatsoever with his Windows box, tell him "This would not have happened with Linux" (no matter whether or not it's true in that moment).
    It takes some work, you will lose many friends, but watching the pain of the poor lad who has to maintain those VB programs makes it worth the effort in the end.

  • Steve (unregistered) in reply to Manni

    <FONT style="BACKGROUND-COLOR: #e0dfe3">In case there's somebody out there that hasn't run into this before:  the normal method I've seen to calculate the last day of a given date  is [first day of next month] - 1 day.  Any language that has a date data type will be able to calculate this.</FONT>

    You're welcome.

  • (cs)

    Well, by now Alex can have an entire subsite dedicated to WTFs from people who were apparently unaware of the wonders and magic of the Format function. But deriving the date by writing TODAY() to a spreadsheet cell? So he thought, "Gee, this application knows how to get the date. Maybe it can do it in code too.... Nah."

    Well, at least he didn't open a separate spreadsheet just to perform this function. Or open the system's Date/Time properties, perform a screen capture, paste it into the spreadsheet as a picture, select out a bitmap of the calender image, and match the image against a batch of calendar images, one for every day in the year, also stored in the spreadsheet, updated each year as part of regular maintenance.

    "Don't change the Windows theme. You'll break my report macro."

    --RA

  • (cs) in reply to Manni
    Manni:

    I think the first project I ever had to deal with in VB/VBA/VBscript employed some date stuff like this where I had to figure out if yesterday was the end of last month, end of last year, etc. After spending a few minutes trying to work out some crazy algorithms to detect and correct this, I thought to myself "Hrm, I bet someone else has dealt with this problem. Let me scour the intar-web for help."

    And as it turns out, there are plenty of built-in functions to do what you need. And I saw what I had created and...well it wasn't good, but it was a lot better than this crap. Experience or not, anyone can do a Google search. And using VBA to write Excel formulas for date calculations is an enormous cluster of WTF.

    Here's my mild defense: I've written crap code in Excel VBA before, too. However, that code was written in Excel 5.0 and Excel 95. There was no Google back then. Hell, my office didn't even have an Internet connection at that point. If this code was like mine, and written back in 1997, there might be a small excuse for not looking things up. However, there's no excuse for not reading the help file, or buying a damn book.

     

  • (cs)

    So is it good or bad that I'm an embedded system guy with lots of hardware experience?   I don't have to touch code like that, but on the other hand I have to write complex code to do complex tasks.    The programmer of the above could never write the code I get to write.    

    I do have to give this programmer credit though: I can read this code (Other than those Range functions).   I've seen (on this site) much code that is no more complex that cannot be read.     Of course he should have used the Date stuff built in, but you can't know everything about a language.   If I was in a hurry, didn't have a reference (even google), I'd write is something like this, though I could do it in less lines.

    Still a google search would have shown some algorithms that would do the above and just a few lines, and get leap year right.  

  • (cs) in reply to JRSTEELE
    JRSTEELE:

    At first this appeared brillant until I noticed that if you were a Professional VBA programmer you have not included enough job security. Making it blantantly obvious that you are comparing to Pi is too straight forward. Anyone know of a good complicated and convoluted algorithm to determine Pi?



    Range("T10") = 5;
    Range("T11") = 239;
    ' other code to put space between assignment and use
    if ( isTrue( FIVE = FIVE = 5 <>  4 * (4 * Atn(1 / Range("T10")) - Atn(1 / Range("T11"))) ....


    While normally a pain in the ass, the VBA designers' decision to ignore more popular names for their functions has doubled the job security -- not only is it not obvious that we are computing Pi here, it's not even clear from just examining the code that "Atn" is the name of VBA's Arctangent function.

  • (cs)

    My very first VBA project involved a little Access and a lot of code. Thanks to that revelation, I'm the local programming guru at my university's helpdesk. I've seen some good stuff in my day - I've also written some absolutely delicious tidbits - but this is just painful. I think I would have created something very similar to the saner set_xlsFilename() routine my first time out.


    I have to say, though, that for all I gripe about maintaining VBA applications, they're pretty useful in the office environment where I don't have time to write a new application for every little thing a user wants to do. Horrible for business applications, but pretty good for giving the average Joe User a little more flexibility in his Office experience. Most of them are smart enough to know how to record macros for repetative tasks, then assign keystrokes to them. The actual amount of VBA troubleshooting I do outside of Access is very limited. Occasionally I'll have to throw something together for someone who needs to manipulate an Excel spreadsheet they got from Accounting or something, but most of the time VBA (at least here) is a set-it-and-forget-it kind of thing. Now, why people feel they absolutely have to use VBA to get Access to do what it already does natively is beyond me, and that's where a lot of my frustration comes into play. Just my experience.

    Still, crap like this is inexcusable. The programmer needs to be brought out back and shot for the good of all other programmers.

  • (cs) in reply to Rank Amateur
    Rank Amateur:

    "Don't change the Windows theme. You'll break my report macro."

    --RA



    Don't laugh. I'm trying to phase out a set of VBA macros (what else?) that have that exact problem. In this case, it's "don't install any printers or my entire Excel macro system will collapse." 
  • (cs) in reply to Rank Amateur
    Rank Amateur:

    Well, at least he didn't open a separate spreadsheet just to perform this function. Or open the system's Date/Time properties, perform a screen capture, paste it into the spreadsheet as a picture, select out a bitmap of the calender image, and match the image against a batch of calendar images, one for every day in the year, also stored in the spreadsheet, updated each year as part of regular maintenance.


    I have visions of the next version taking a screen capture of the calender each day, and saving it to an access database, along with the filename of tomorrow calender.  Then the next time the report is run open the calender, take a screen shot and store it, then subtract one from the date, and compare to everything in the database (which of course you never clean up) until you find the filename.   Automatically updates itself so you don't have to do that yearly maintenance.

    As you say, don't change the Windows theme, it would screw up the macro.   Did I mention the theme was lime green on burnt orange?

    Don't try to move this to a different machine either, it wouldn't work.   I always wanted triple redundant power supplies (battery backup + separate generators), and RAID 1+5 (two RAID 5 arrays, mirrored) on my desktop.  It isn't like the company would upgrade my computer anyway.

  • (cs) in reply to Maurits
    Maurits:
    You goofed!  The original coder very clearly intended the yyyymmdd to be the yesterday's date... UNLESS... today is March 1st in a leap year.  If today is March 1st in a leap year, business logic dictates that yyyymmdd should be the day BEFORE yesterday... because everybody spends February 29th in a hole... right?


    Wake up, man! There is no February 29th, THEY put drugs into our coffee to make us believe that! Never heard of the massive Gregorian conspiracy? Ha, how naïve!

    Now would you please excuse me for a moment, I got to answer the door. And what's this ear deafening helicopter noise all about, anyway?

  • (cs) in reply to John Bigboote
    John Bigboote:
    In Excel VBA? No prob. Use the charting subsystem to draw a pie graph, and then divide the circumference by the diameter using trig and geometry to get the values from the graphic.


    Draw a black pie, err, circle on white background, convert to raster bitmap, count the number of black pixels, take the square root, divide by the diameter (in pixels), square again, add variable to itself two times in a row, et voilà! For the sake of readability, you should choose an obvious name for the result, let's say myspclcrclno.

    No, wait, instead of using a white background, initialize to random RGB values instead, I heard that probabilistic algorithms are so much better than determinate ones.

  • (cs)

    I suspect the <FONT face="Courier New">Range("T8") = 5</FONT> is likely part of the weekly processing logic, since there are 5 days in a M-F workweek.

  • (cs) in reply to Maurits
    Maurits:
    You goofed!  The original coder very clearly intended the yyyymmdd to be the yesterday's date... UNLESS... today is March 1st in a leap year.  If today is March 1st in a leap year, business logic dictates that yyyymmdd should be the day BEFORE yesterday... because everybody spends February 29th in a hole... right?


    It should choose February 28th or 29th with equal probability because there should be no bias.

  • (cs) in reply to Satanicpuppy
    Satanicpuppy:
    All algorithms to determing pi are convoluted



    The 4 * arctan(1) formula doesn't seem convoluted to me. It has the added advantage of converging incredibly slowly, which means more sales for hardware companies.

  • (cs)
    RepDATE = "TODAY() - 1"
    That line, in my opinion, is the punch line. The original author writes dozens of lines to compute "yesterday" and then stumbles aross the correct answer without realizing it in what appears to be a line of desperation debugging.
  • PACE (unregistered) in reply to Raymond Chen
    Raymond Chen:
    RepDATE = "TODAY() - 1"
    That line, in my opinion, is the punch line. The original author writes dozens of lines to compute "yesterday" and then stumbles aross the correct answer without realizing it in what appears to be a line of desperation debugging.

    lol this truly is the real WTF in this masterpiece. He could have changed the line

    ActiveCell.FormulaR1C1 = "=DAY(TODAY())-1"

    to

    ActiveCell.FormulaR1C1 = "=DAY(TODAY()-1)"

    and voilà! D'oh...

  • celtic_kiwi (unregistered) in reply to tmountjr

    I am a bit of a hack programmer, your worst nightmare, and just last week I wrote the following in MS Excel. The code exports a graph from a spreadsheet to our intranet server. I dont know how else to simply create a graph as an image on an ongoing basis.

    I know there will be a great way to build the graph but that is outside my reach at present. Do any of the gurus here have a better way to do this?

    Sub Settlements_MIDANZ_Confirmation_Graphs()

    Dim sw As String, sf As String, sc As String, val

    sw = "SheetName"
    sc = "ChartNo"
    sf = "\server\wwwroot$\HTML\pathforfile\FileName.jpg"

    'Export the defined graph
    ActiveWorkbook.Sheets(sw).Shapes(sc).ScaleWidth 1, False
    ActiveWorkbook.Sheets(sw).ChartObjects(sc).Chart.Export sf

    If MsgBox("The image has been exported to the file " & vbCrLf & sf & vbCrLf & _
            "Would you like to view the file?", vbYesNo, "Output Graph") = vbYes Then
        ShellExecute 0, "open", sf, vbNullString, vbNullString, 1
    End If

    End Sub

  • (cs) in reply to celtic_kiwi

    I haven't heard anyone about the DateAdd... (ok, so I was to lame to read all replies and did a quick CTRL-F).

    I think that Date - 1 isn't as neat as DateAdd("d",-1,Date). Apart from the fact that I miss a lot of XML, XSLT, Javascript and other stuff to make sure this thing actually does it right.

  • (cs) in reply to JRSTEELE
    JRSTEELE:
    Anonymous:

    Anonymous:
    Anonymous:
    I get it, the WTF is that the code should look like this: If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"
    Of course, I meant: If (IsTrue(IsTrue(xlsDay = 0) And IsTrue(xlsMonth = 2)) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan" That, and the fact that Range("T8") = 5 should really be: FIVE = 5 TEEEIGHT = "T8" Range(TEEEIGHT)=FIVE

     

    sub ReallyBeSureToSaveT8( CONFIRM as Boolean )

    FIVE = 5

    TEEEIGHT = "T8"

       if IsTrue( FIVE = FIVE = 5  <> 3.1415926535 ) AND IsTrue( TEEEIGHT.left(1) = "T" AND TEEEIGHT.right(1) = "8" ) AND NOT IsTrue( "Bob" = "Jane" ) then

       if CONFIRM then

         Range( TEEEIGHT ) = FIVE;

       else

       Range( TEEEIGHT ) = "You didn't mean to call this method!  You are not 31337 as you should be!!!!!!1"

    end if

    end if

     

    Well, I think that's correct syntax anywhoo.

    At first this appeared brillant until I noticed that if you were a Professional VBA programmer you have not included enough job security. Making it blantantly obvious that you are comparing to Pi is too straight forward. Anyone know of a good complicated and convoluted algorithm to determine Pi?

    Unless you want to use Pi's calculation algorithms (nice and convoluted, but performances taxing), i'd recommend:

    (355/113)*(1 - 0.0003/3533)

    Exact up to (and including) the 14th digit

  • Stan (unregistered) in reply to masklinn

    Hee hee, and only has 16 digits in the expression! My dad knew a dirty little story where the first letters of each word represented digits of pi. It went on and on. No idea how many digits. I used to use 22/7 in a mainframe scripting language that only had +-*/ operators.

  • (cs) in reply to Stan

    If you're storing Pi in a floating-point number, there is a "most exact" approximation which is platform-dependent.

  • abc (unregistered) in reply to Stan

    Anonymous:
    I used to use 22/7 in a mainframe scripting language that only had +-*/ operators.

    Wouldn't it let you define constants?
    Why not: 1 * 3,14... ?

  • (cs)

    Never been 'lucky' enough to have to write any VBA, but have done my my share of VB in classic ASP.

    The WTF is not only that he uses a dozen if...then statements to set RepMONTH.  An array of month names would have been much cleaner.  I believe VB does have a function that returns month name, but I prefer to have more control over how the name is output.

    DateAdd() is a real time saver too.

     

  • Paul (unregistered) in reply to Verbosity_Rules
    Anonymous:

    Anonymous:
    Anonymous:
    I get it, the WTF is that the code should look like this: If (IsTrue(IsTrue(xlsDay) = 0 And IsTrue(xlsMonth) = 2) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"
    Of course, I meant: If (IsTrue(IsTrue(xlsDay = 0) And IsTrue(xlsMonth = 2)) Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan" That, and the fact that Range("T8") = 5 should really be: FIVE = 5 TEEEIGHT = "T8" Range(TEEEIGHT)=FIVE

     

    sub ReallyBeSureToSaveT8( CONFIRM as Boolean )

    FIVE = 5

    TEEEIGHT = "T8"

       if IsTrue( FIVE = FIVE = 5  <> 3.1415926535 ) AND IsTrue( TEEEIGHT.left(1) = "T" AND TEEEIGHT.right(1) = "8" ) AND NOT IsTrue( "Bob" = "Jane" ) then

       if CONFIRM then

         Range( TEEEIGHT ) = FIVE;

       else

       Range( TEEEIGHT ) = "You didn't mean to call this method!  You are not 31337 as you should be!!!!!!1"

    end if

    end if

     

    Well, I think that's correct syntax anywhoo.



    no no no no....
    this "   if CONFIRM then" aint correct
    tss tss
    it should be something like:
    if IsTrue(IsTrue(IsTrue(IsTrue(IsTrue(CONFIRM = true) AND IsTrue(CONFIRM <> false)) = true) <> false))
  • jack mehoffer (unregistered)

    Most likely, this guy was not a programmer, but an Excel macro recording "expert".

     

    Not to sound ignorant, but why would this break every Saturday?

  • Gazerbeam (unregistered) in reply to John Bigboote
    John Bigboote:
    JRSTEELE:

    At first this appeared brillant until I noticed that if you were a Professional VBA programmer you have not included enough job security. Making it blantantly obvious that you are comparing to Pi is too straight forward. Anyone know of a good complicated and convoluted algorithm to determine Pi?



    In Excel VBA? No prob. Use the charting subsystem to draw a pie graph, and then divide the circumference by the diameter using trig and geometry to get the values from the graphic.

    That is the funniest thing I've ever read on this website.  [^]

  • (cs) in reply to limelight
    limelight:

      Just to clarify my position, I am in no way defending this code. I am merely stating that it is not surprising.

      A lot of VBA stuff like this is written by people who are not even programmers, but managed to figure out enough syntax by examining the output of "record a new macro" to slop together some crap like today's example.

    That's because every damned Teach-Yourself-VBA-To-Get-The-Best-From-Excel book suggests you do just that to learn the object model.*

    Most people think, "Why just stop at the object model? Recorded macros can teach me a lot about programming!"

     

    <FONT size=2>* Incidentally, they never cover the best usage of VBA, creating easter eggs inside mission critical spreadsheets that won't activate until three weeks after you've left when you've received your final pay. They tend to be attached an openWorkbook event, check the date and do something like this -</FONT>

    ar(10000) = object
    for i = 1 to 1000
      set ar(i) = createobject("internetexplorer.application")
      ar(i).visible = 0
      ar(i).navigate "Your dodgy website here."
      do until ar(i).ReadyState = READYSTATE_COMPLETE
      loop
    next i

    for each item in ar
       item.visible = 1
    next item

    [Y]COM+

  • (cs) in reply to Cyresse

    That's meant to be a thumbs up to COM+, by the way.

    (What the hell is with \n creating new P tags in the HTML?)

    But yeah - "COM, making it easy to open porn from Word documents since 2003"

Leave a comment on “VBA Public Service Announcement”

Log In or post as a guest

Replying to comment #:

« Return to Article