• (cs) 

    You're close, but to replicate the original behaviour you need to account for the bizarre behaviour around leap years.

    Here's my solution, which I think you'll agree is a WTF of its own.

    Public     Sub set_xlsFilename()
      Dim DateIn2003 as DateTime
      DateIn2003 = CDate("2003-" + Month(Date) + "-" + Day(Date)) - 1
      xlsFilename = Format(CDate(CStr(DateDiff("yyyy",DateIn2003,Date) + 2003) + "-" + Month(DateIn2003) + "-" + Day(DateIn2003)),"yyyymmdd") + ".xls"

    End     Sub

  • (cs) in reply to Maurits
    Maurits:
    If you're storing Pi in a floating-point number, there is a "most exact" approximation which is platform-dependent.

    Yeah. Usually you can get it (or get very close) by doing something like

    double pi = 4 * atan(1);

    or whatever the equivalent is in your language of choice.

  • (cs) in reply to JRSTEELE
    JRSTEELE:
    Anyone know of a good complicated and convoluted algorithm to determine Pi?


    Just use (HOURS_IN_DAY - 1 - 1)/DAYS_IN_WEEK

    Rik


  • (cs) in reply to rikkus

    rikkus:
    JRSTEELE:
    Anyone know of a good complicated and convoluted algorithm to determine Pi?


    Just use (HOURS_IN_DAY - 1 - 1)/DAYS_IN_WEEK

    Rik


     

    GOOD ONE!!! HAHA!!

  • (cs) in reply to Cyresse
    Cyresse:
    (What the hell is with \n creating new P tags in the HTML?)

    Pressing <enter> in the editor creates a new paragraph (thus the P -tag). <shift+enter> creates a line break (br -tag).

    Paragraph1
    line1
    line2

    Paragraph2

     

  • Nand (unregistered) 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?


    You can use the monte carlo method to approximate pi: Imagine a quarter of a circle, with the center at (0,0) and radius 1. Randomly pick points between (0,0) and (1,1). Count how many of the points are inside and how many outside.

    After a few thousand random samples, you know how many landed inside and therefore the 'area' of the quarter circle. multiply the whole thing times 4 and use the formula for circle-area to extract PI. done.

    I'm sure if you google you can find lots of sample implementations. Shouldn't be more than a few lines, but on a quick look and without PI in the variable names it just looks like lots of random numbers :P

  • (cs) in reply to Nand

    Anonymous:
    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?


    You can use the monte carlo method 
    .
    .
    .
    Shouldn't be more than a few lines, but on a quick look and without PI in the variable names it just looks like lots of random numbers :P

    Not to mention cpu-time needed for meaningful approximation. Hardware vendors are happy again. And you as the programmer can sleep you night well knowing you have utilised you precious cpu which otherwise would just sit useless.

  • Dave (unregistered) in reply to hank miller

    hank miller:
    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.

    Me me me!  No one could ever be as smart as me!  I'd like to take this opportunity to tell everyone how smart I am.

     

  • Anonomoose (unregistered) in reply to Dave

    you didnt read the rest of his comment then, dickhead?

    anyway... VB, VBA... all much of a muchness aren't they?

  • (cs) in reply to Verbosity_Rules
    Anonymous:
    sub ReallyBeSureToSaveT8( CONFIRM as Boolean )


    <font size="2">that was excellent, but incomplete.  what we need is a function that will incorporate the last 50 daily wtfs.

    (gears start turning inside brain)
    </font>
  • barrel of worms (unregistered) in reply to celtic_kiwi
    Anonymous:
    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?

    ...

    End Sub


    Gnuplot?

  • christoofar (unregistered) in reply to tmountjr

    There's a project management anti-pattern I like to call the DisAccess Antipattern.  Through most of the 90s I dealt with it at a 20,000 user site in San Antonio.  It goes something like this:

    Access is a useful tool for less educated users, for people who want to get things done in a small environment.  In a large organization given enough time, eventually someone will write something useful (maybe 1 in 40 database projects).  Thanks to email and Lan Manager shares, use of database starts to spread and grow like a worm.

    Before too long, you have a lot of users trying to manage the same Access database file concurrently over a network--and you know what that is like.

    I've worked in large organizations where the IT dept was stuck in analysis paralysis for so long, these little Access nightmares started to grow and fester by the dozens. After a long period of time, when IT has no control over HR, the business ultimately put out job postings for "Access Experts" behind everyone's back and mini programming shops dotted around the organization start to appear.

    Ultimately. once the Access app suffers a massive crash, gets severely corrupted or users accidentally wipe out sections of the database, the blame goes directly to the in-house IT people for all the chaos and destruction--and not listening to user's requirements and implementing or buying a solution in the first place, not to mention causing  embarrasement to the CIO who is then seen by his/her peers as incompetent or inept at being able to satisfy the business... so much so that departments have taken programming work away from IT and are doing it themselves.

    C'est la vie.

  • (cs)

    I recently had to OOP'ify in .NET / SQL Server 2000, some Access stuff.  It kind of went like this:

    Some button invokes an "Update Query".  The Update Query refers to a VBA function, passing it a "Mode" parameter, which is a "smart string", where the first n characters represent one meaning, and the next m characters represent another meaning.  This way, the VBA function called by the SQL can work in the number of n*m different modes.

    Then inside the VBA function there is a Select Case block with a Case block for each possible combination.  Each Case block was about two pages long dynamically building Strings with Left$(), Right$() Mid$(), Replace() and so forth, such that the resulting string was the name of another Update Query, which, when executed called other VBA functions that did something similar.

    I spent over a week in the debugger with lots of breakpoints and Debug.Print statements, just to figure out that all it was doing was updating different blocks of recods in a table based on their status column.  SHEESH!

    I can definately identify with this Brian Peterson's suffering.

     

    Peace & happy computing,

    ~~ vbSensei ~~

  • (cs) in reply to 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?

    4 * ATN(1)

     

    Peace, & happy computing,

    ~~ vbSensei ~~

  • (cs) in reply to Oliver Klozoff
    Oliver Klozoff:


    
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.


    ATN() has been used as the name of the arctangent function in various BASICs for, oh, thirty years or more.

    Sincerely,

    Gene Wirchenko

  • (cs) in reply to Gene Wirchenko
    Gene Wirchenko:

    ATN() has been used as the name of the arctangent function in various BASICs for, oh, thirty years or more.


    Has anyone EVER used the arctangent function for any purpose other than calculating pi?
  • (cs) in reply to Maurits
    Maurits:
    Has anyone EVER used the arctangent function for any purpose other than calculating pi?
    Yes - I have. For calculating a rotation. Honest. (Well, you did ask.)
  • (cs) in reply to tmountjr
    tmountjr:
    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."

    My shame is that I once wrote a VBA mini-app where the flakiest part was the print/fax/pdf output. (Well, upgraded from a small wtf to a bigger wtf; the vb half was much worse when I got it and much nicer when  I was done. I need to find and submit that someday.) It was actually the most useful part. But once day I fixed a printing problem on the guy's computer by upgrading to a PCL6 driver.... aaaaand it stopped working. It was using the name of the printer directly, of course. ;_;

    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?


    Would the monte carlo method work? It has the added advantage of requiring a "Wait, Calculating..." screen every update, as well as occasionally mysteriously failing. (Check whether it's within 2% of the PI constant in T22.) "Server failure. Please retype."

    Damn, just noticed someone else mentioned it.
  • (cs)

    I once worked for a company that had an API that clients would call through Excel (though the smart clients would use the API in a real language).  They'd create some scenario where their Excel macros would get kicked off by a Scheduled Task every few minutes and download a TON of data.  They wouldn't put a single line of error handling in the damn things and then they had the nerve to complain that the API was the problem when it choked occasionally on the sheer amount of data that was being downloaded.  It never ceases to amaze me what people try to do with Office applications.  Scheduled Task + Excel + VBA != Production Application

    The trick to escaping the VBA trap that Alex talks about is to be a competent programmer.  You can write solid code in VBA if you know what you're doing.  Get out of VB altogether by learning another language (Perl, C#, C++, whatever).  Learning VB.NET doesn't count.  Once that's done, never, EVER mention that you know anything about VBA, Excel or Office.  "Excel?  What's that?  Is that one of those newfangled scheduling suites?"

    btw, if you think Excel's object model is bad, try Word's!  At least Excel is essentially coordinate based.  Word is based on paragraphs, sentences, lines, words, etc.  Ug.

  • (cs) in reply to Maurits

    Maurits:

    Has anyone EVER used the arctangent function for any purpose other than calculating pi?

    Yeah. When screwing around with various graphics programming you use those a bit. Not just the obvious 3d stuff, but it comes in handy for image processing. Other sorts of signal processing use it a lot as well, like audio processing and such.

    So don't be maligning arctan! arctan makes your life better! [:P]

    arccos, on the other, is about useless. [;)]

     

  • (cs) in reply to Maurits
    Maurits:
    Has anyone EVER used the arctangent function for any purpose other than calculating pi?


    I haven't used atan a lot, but only because atan2 is in general much preferable.

  • svartrev (unregistered)

    As far as I'm concerned, saying "In the original coder's defense, he was not very experienced writing VBA" is wrong. I would be more forgiving if he HAD been writing VBA for a long time... Longer for the brain-rot to set in!

  • (cs) in reply to Otto

    <FONT style="BACKGROUND-COLOR: #d3d3d3">arccos, on the other, is about useless.</FONT>

    Never had to calculate an inverse dot product?  

    n \dot l = |n| * |l| * arccos( \theta ), and that theta can be mighty useful!

  • (cs) in reply to iAmNotACantalope

    iAmNotACantalope:
    Never had to calculate an inverse dot product?

    <FONT face="Courier New" size=2>are you a fan of 'vlad! from molvania'?</FONT>

    <FONT face="Courier New" size=2>like a lion kills an antelope /
    like a hammer hits a catalope /
    i am the anti-pope</FONT> 

  • (cs) in reply to emptyset

    I bet the T8 = 5 bit is actually the year; if you look he saves the day and the month and then 5. On the cell, it is likely that T8 is formatted with a leading 0, i.e. "05" and viola, you have the day, month and year - but only for 2005!

  • (cs) in reply to emptyset
    emptyset:

    <font face="Courier New" size="2">are you a fan of 'vlad! from molvania'?</font>

    <font face="Courier New" size="2">like a lion kills an antelope /
    like a hammer hits a catalope /
    i am the anti-pope</font> 



    Now I know! You are the Pushme-Pullyu from Ultima VI. "East lie beasts, so go quest west."

  • (cs) in reply to Magic Duck
    Magic Duck:
    Cyresse:
    (What the hell is with \n creating new P tags in the HTML?)

    Pressing <ENTER>in the editor creates a new paragraph (thus the P -tag). <SHIFT+ENTER>creates a line break (br -tag).

    Paragraph1
    line1
    line2

    Paragraph2

     

    Cheers for that. I did try ctrl+Enter...
    Obligatory comment about wtffery of forum software

  • (cs) in reply to Alexis de Torquemada
    Alexis de Torquemada:
    emptyset:

    <FONT face="Courier New" size=2>are you a fan of 'vlad! from molvania'?</FONT>

    <FONT face="Courier New" size=2>like a lion kills an antelope /
    like a hammer hits a catalope /
    i am the anti-pope</FONT> 



    Now I know! You are the Pushme-Pullyu from Ultima VI. "East lie beasts, so go quest west."

    <FONT face="Courier New" size=2>no.  my friends call me "non-sedentary" and never find me playing some MMORPG online.</FONT>

  • Davey (unregistered) in reply to emptyset
    emptyset:
    <font face="Courier New" size="2">my friends call me "non-sedentary" and never find me playing some MMORPG online.</font>


    Are you sure your friends don't call you smokey?

    I'm referring to the bong and the drugs dude.
  • (cs) in reply to Davey

    Anonymous:
    emptyset:
    <FONT face="Courier New" size=2>my friends call me "non-sedentary" and never find me playing some MMORPG online.</FONT>


    Are you sure your friends don't call you smokey?

    I'm referring to the bong and the drugs dude.

    <FONT face="Courier New" size=2>"smokey's a pacifist, walter!"</FONT>

    <FONT face="Courier New" size=2>let's put the matter of drug use aside for a minute here.  it's always really bothered me here in america that people have a culture of anti-imagination.  if a kid has a hyper-active imagination, they're either exorcised or put on ritalin, depending on the parent's persuasion.  when a teenager has an active imagination, they're stuck rolling 20-sided die or arrested as a vandal for trying to spraypaint a mural.  when an adult has an over-active imagination, that's the drugs talking - or she's the unmarried fruity lady with the 12 cats.</FONT>

    <FONT face="Courier New" size=2>by and far, the most popular drug is alcohol: the complete anti-thesis to imagination.  art classes in school?  baby, please.</FONT>

    <FONT face="Courier New" size=2>have you ever asked a guy in his 40s if any zany thoughts were going through his head?  i get many interesting responses.  people have the innate ability to use their own imagination, but they're blind to it.  most of the time, they just let the tv or movies decide imagination for them.</FONT>

    <FONT face="Courier New" size=2>america has me depressed in so many ways.  :(</FONT>

  • Rob (unregistered) in reply to Davey

    I have little to add except that this WTF was featured the same week I got pressganged into doing work on an enormous VBA app in Word for my company. If I reread the first two paragraphcs enough times, maybe it will turn into a block of words praising the VBA app and saying what a joy it is to work on one.


    ... :cry:

  • hoodaticus (unregistered) 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. "Don't change the Windows theme. You'll break my report macro." --RA

    It's moments like these, when I can't stop laughing, that I live for =D

Leave a comment on “VBA Public Service Announcement”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article