• anon (unregistered) in reply to plg
    plg:
    I'm not sufficiently familiar with VB to come up with an interesting password. Does VB allow multiple statements on a line? And is the comment sign //?

    My password:

    "); somefunnycode(); //

    Close. Apostrophe instead of double slash, and colon instead of semi-colon with none trailing. 
    ") : someFunnyCode() '
  • (cs) in reply to justsomedude
    justsomedude:
    polymorphism in your spreadsheets...
    so this could be used to make: sum(a1:a40) implement: median(a1:a40)

    You Sir, are pure evil genius :)

  • (cs) in reply to Code Dependent
    Code Dependent:
    Synchronos:
    ...the incoming USS Bozeman.
    Either you've achieved new levels of geekness, or I need to turn in my geek ID card. I had to Google the reference to understand.

    Oh, come on. That's one of the more memorable episodes of ST:TNG. You don't need to be especially geeky to remember it.

    Now, linking the episode to this context... maybe that does require exceptional levels of geekness.

  • TheCPUWizard (unregistered) in reply to justsomedude

    While the posted sample IS an abuse (to put it mildly), I have actually made good use of the ability to create VBA code from within VBA.

  • IHazYourCheezburger (unregistered) in reply to TheCPUWizard
    TheCPUWizard:
    While the posted sample IS an abuse (to put it mildly), I have actually made good use of the ability to create VBA code from within VBA.
    I suppose that you don't use an enterprise version of McAfee as your antivirus. McAfee will happily wipe any code module that has the methods .InsertLines and/or .DeleteLines in it.

    Don't ask how I acquired that knowledge. I'm still ashamed.

  • Herby (unregistered)

    When it comes to self modification, sometimes it is a normal happening. There are machines built that do not have such niceities as index registers, or indirect addressing. I offer the following to those interested:

    <Instant Stop><Reset><Insert>

    160000900000

    <Release><Start>

    Which cures all in due time.

  • acid (unregistered) in reply to anonymous coward
    anonymous coward:
    TRWTF is that such a dangerous property like ".CodeModule.Lines(3, 1)" actually exists.

    True, but this is not the only dangerous object / property set in VBA. Most VBA Macros take advantage of a Macro.copy command or variation thereof, providing a propogation tool to coders who wouldn't otherwise have their own propogation capability within the interpreted environment.

    I actually passed on (through my work) an idea to Microsoft back in '96, telling them that the use of that single command should only be accessible by a command line switch, meaning that sys admins could still do their work but the average person could use and enjoy macros in relative safety. The answer came back that they had it covered, and that their solution would come out in office '97. That's when we got to turn the ENTIRE macro system on and off only, not enjoy the benefits macros without the risks.

    Thanks guys.

    CAPTCHA - damnum - I swear Alex as put some code in here which is partially pyschic, I've been getting far too many serendipitous Captchas for my liking lately.

  • acid (unregistered) in reply to Kharkov
    Kharkov:
    In the bank where I currently work, we (support level 3/4), have a simpler approach to macros: the official position of the IT deparments is that there is no support for it, unless given by Microsoft, at the rate of 1500 USD a day (at a minimum), to be paid by the branch using macro...

    Well done, you've just become part of the problem rather than the solution. People use Macros because they get what they want out of them when IT doesn't deliver. In my experience, places with a heavy reliance on things like VBA, COGNOS, SAS, Notes Databases etc. have it because...

    a) Business can't articulate their needs properly b) IT can't deliver tools which serve business properly c) The two arms of the business have developed a confrontational relationship

    Pick any or all that apply.

    If you REALLY want your users to stop using VBA, give them the tools they need. If they don't know what they need, get in Business Analysts who can figure it out for them, THEN give them what they need.

    Banning VBA (or refusing to support it which is tantamount to the same thing) just forces the business units underground. Instead of coming to you, they start hiring HPCs in VBA to fix their problems for a lot less than US$1500/day and we all know where the results end up because we all read this column.

    Bottom line. When you start banning things, users tend to go around you. Then years later you end up with an executive breathing down your neck who doesn't give a rat's arse about your official position on VBA, his business critical process is broken and you have to fix it for him and by then you've got a REAL problem on your hands because you now have code written by Lord knows who that does some really funky stuff...

    And the funny thing is that it IS your fault because you failed to help them when they first came to you.

    So, if you've got a VBA 'problem' in your environment, the first thing you need to do is ask yourself why? Sure, some people will write VBA because they think it's cool to act like a developer. But, if you're covering all the business critical processes, those people won't have anything serious to code and therefore can't generate a 'problem'. In other words, fix the problem at the source and make sure the users have the tools they really need in the first place. After all, it's our job.

  • pizzaguy (unregistered) in reply to Mayhem
    Mayhem:
    Code Dependent:
    An application can work flawlessly for years until some clueless n00b changes a dependency, and then it's "Your POS program doesn't work."
    Heh. I'm trying to fix a hand-me-down server at the moment where the services are running under domain admin accounts that we don't know the passwords for, and half of the passwords are hard coded into the app that we don't have the source for. God I love italians.

    Consider the fact that if the strings are in the code, unless they are encrypted for some reason, they are very easy to get out. There's even a UNIX tool for it, oh what's it called again?

    But why bother when you can take the opportunity to be a bigot instead? I think the sort of thing an Italian-Americans would call you a "mook" for, no?

  • tool police (unregistered) in reply to acid
    acid:
    Kharkov:
    In the bank where I currently work, we (support level 3/4), have a simpler approach to macros: the official position of the IT deparments is that there is no support for it, unless given by Microsoft, at the rate of 1500 USD a day (at a minimum), to be paid by the branch using macro...

    Well done, you've just become part of the problem rather than the solution.

    -- Snip --

    So, if you've got a VBA 'problem' in your environment, the first thing you need to do is ask yourself why? Sure, some people will write VBA because they think it's cool to act like a developer. But, if you're covering all the business critical processes, those people won't have anything serious to code and therefore can't generate a 'problem'. In other words, fix the problem at the source and make sure the users have the tools they really need in the first place. After all, it's our job.

    Settle down.

  • iNFiNiTyLoOp (unregistered) in reply to pizzaguy

    it's called compressible encryption. Which is really lame encryption but mostly defeats "strings", or MS Word's "recover text from any file". Of course, if you don't have the VBA source code at all.

    BTW, If you actually want to handle compilation errors in a VBA self-modifying code, you would generate code into a second module. When the first module notices that it can't execute the second module at all, it can assume some kind of compilation error and revert the second module's code less risk of damaging the 'driving' first module. Maybe you could have vba modules that make lossy copies and attack dissimilar modules, if you're truly bored.

  • jim steichen (unregistered) in reply to blindman
    blindman:
    galgorah:
    The issue is the script modifies its own code. Thus at some point becoming skynet or HAL 9000 except powered by excel.
    Whew! For a moment there I was worried about the future of the human race.
    Well you should...this is the start of the Idiocracy (TM)!
  • acid (unregistered) in reply to tool police

    [quote user="tool police]Settle down.[/quote]

    Nothing to settle down about, I'm completely calm and I stand by my comment. Sites like this would have less to print if those of us out there that understood IT did our jobs. This has come up several times now, and I've always said the same thing.

    Whether we like VBA or ridicule its existence, it's out there and people will use it if we don't give them viable options.

    The argument put forward by refusing support of a tool that's openly supplied by the IT infrastructure people is that the users shouldn't have a need to use it. My point is that the validity of that assumption is entirely based on our ability to provide the users with what they need.

    Trying to 'ban' VBA in the enterprise by officially expecting branches to pay $1500/day in support for it creates more problems than it solves, I've seen it first hand and I've seen the people the branches hire to save money. Those who don't want messes like that on their hands need to understand more than programming. They need to understand the users they support. That, as I'm forced to keep pointing out, is our job.

  • Marshall (unregistered)

    Back (many decades) when I was a student there was a machine called the IBM 360 and one of the subjects required us to to program it in Assembler.

    The only instruction for moving data around was MVC (Move Characters) .. which included, hard coded within itself, the one byte length of the number of characters to move.

    Since runtime speed was a key factor in the marking scheme amd the startup cost of an MVC was very high, the standard technique (within the student community) was to do an MVC of zero characters - preceded by an instruction to do a logical AND that overlaid the length. Of course moving more than 255 characters became quite interesting .. luckily we didn't know then not to use GOTO's :-).

  • Marshall (unregistered)

    Dynamically modifying the MVC instruction ...

    Whoops ... that was a logical OR to set (a logical AND to clear first if re-setting)

  • Sarails (unregistered) in reply to acid

    [quote user="acid]Well done, you've just become part of the problem rather than the solution. People use Macros because they get what they want out of them when IT doesn't deliver. In my experience, places with a heavy reliance on things like VBA, COGNOS, SAS, Notes Databases etc. have it because...

    a) Business can't articulate their needs properly b) IT can't deliver tools which serve business properly c) The two arms of the business have developed a confrontational relationship [/quote]

    you forgot d) "Management" have decided that there is "zero" budget for needed application development or support and "that guy/girl" who made that flash looking spreadsheet/powerpoint can do it cheaper than IT e) "Management" want the application "Yesterday" and IT are snowed under with existing "transformation" excercises, but we can go to "that guy/girl" who did that thing above. f) The FNG just didn't know who to ask and "just did it" "management" liked it, and like flopsy it "just Grewed and Grewed"

    I personally love "F" as this happened recently at business i know of, excpet the FNG left, changes needed to be made to allow outsourcing the divisions work OS, so they contracted the FNG's new company to get him to fix it, that's WTF!!

  • k1 (unregistered) in reply to Sarails

    [quote user="Sarails"][quote user="acid]

    a) Business can't articulate their needs properly b) IT can't deliver tools which serve business properly c) The two arms of the business have developed a confrontational relationship [/quote]

    you forgot d) "Management" have decided that there is "zero" budget for needed application development or support and "that guy/girl" who made that flash looking spreadsheet/powerpoint can do it cheaper than IT e) "Management" want the application "Yesterday" and IT are snowed under with existing "transformation" excercises, but we can go to "that guy/girl" who did that thing above. f) The FNG just didn't know who to ask and "just did it" "management" liked it, and like flopsy it "just Grewed and Grewed"

    [/quote] h) "that guy/girl" dislike or simply doesn't trust IT, so he/she create his/her own little fancy app; and from time to time he/she modify it; and one day...

    captcha: tation (ff suggest it as already typed in... I'm lucky?)

  • Mayhem (unregistered) in reply to pizzaguy
    pizzaguy:
    Mayhem:
    Heh. I'm trying to fix a hand-me-down server at the moment where the services are running under domain admin accounts that we don't know the passwords for, and half of the passwords are hard coded into the app that we don't have the source for. God I love italians.

    Consider the fact that if the strings are in the code, unless they are encrypted for some reason, they are very easy to get out. There's even a UNIX tool for it, oh what's it called again?

    But why bother when you can take the opportunity to be a bigot instead? I think the sort of thing an Italian-Americans would call you a "mook" for, no?

    Windows box. No information as to which accounts are hard coded, we only found out there might be a problem after disabling some accounts as part of a cleanup of our local domain. Trial and error on a domain controller is a bad idea. No information as to why the app even needs domain admin access in the first place. Half the system is localised in English half in Italian. No, its not Italians generally, its the hand-me-down mentality of my organisation which is an Italian company, but hey, sometimes you gotta vent. Plus I am not a programmer. How do you pull passwords out of precompiled code?

  • Anonymous (unregistered)

    That sure is cute. Why does CodeModule.Lines exist anyway? Does code really need to be self-aware?

  • (cs) in reply to Anonymous
    Anonymous:
    That sure is cute. Why does CodeModule.Lines exist anyway? Does code really need to be self-aware?
    actually, self aware code is the best kind of code.

    Imagine waking up in the morning to discover than you are a coding horror of epic proportions. Time to call the self-destruct routine, eventually leaving only the good code left in the world.

    So yes, not only do we need code to be self aware, we need it to be proud, and understand the concept of suicide.

  • natte (unregistered)

    why is this a wtf? 99% of the js/html soup does the same thing :D

  • (cs) in reply to natte
    natte:
    why is this a wtf? 99% of the js/html soup does the same thing :D

    Not quite. In the internet business, there's usually a script (in ASP, JSP, PHP, JS, etc...) that manipulates the HTML output of said process. The original script remains untouched.

    At least, that's my experience. Granted, I haven;t been around that long, but I've worked with some ... interesting code in the past 4 years.

  • Mr^B (unregistered) in reply to natte
    natte:
    why is this a wtf? 99% of the js/html soup does the same thing :D

    99% of your js/html might do that. Mine doesn't.

  • m (unregistered)

    what the hell is this site? this looks alot like spam to me

  • Thought, the hungry troll (unregistered) in reply to Mayhem
    Mayhem:
    pizzaguy:
    Mayhem:
    Heh. I'm trying to fix a hand-me-down server at the moment where the services are running under domain admin accounts that we don't know the passwords for, and half of the passwords are hard coded into the app that we don't have the source for. God I love italians.

    Consider the fact that if the strings are in the code, unless they are encrypted for some reason, they are very easy to get out. There's even a UNIX tool for it, oh what's it called again?

    But why bother when you can take the opportunity to be a bigot instead? I think the sort of thing an Italian-Americans would call you a "mook" for, no?

    Windows box. No information as to which accounts are hard coded, we only found out there might be a problem after disabling some accounts as part of a cleanup of our local domain. Trial and error on a domain controller is a bad idea. No information as to why the app even needs domain admin access in the first place. Half the system is localised in English half in Italian. No, its not Italians generally, its the hand-me-down mentality of my organisation which is an Italian company, but hey, sometimes you gotta vent. Plus I am not a programmer. How do you pull passwords out of precompiled code?

    http://en.wikipedia.org/wiki/Strings_%28Unix%29

    Its already been mentioned once by name in the thread. Also, according to the article there is also a windows port of the program.

  • ram (unregistered) in reply to Mayhem
    Mayhem:
    I'm trying to fix a hand-me-down server at the moment where the services are running under domain admin accounts that we don't know the passwords for, and half of the passwords are hard coded into the app that we don't have the source for.
    So, you're essentially saying that a script kiddie might actually be in a better position to fix it.
  • oreo (unregistered) in reply to m
    m:
    what the hell is wrong with me? I'll emit some comment spam
    FTFY
  • erica (unregistered) in reply to Marshall

    Sounds like they failed to teach you the EXecute instruction. That's exactly the situation it was design for.

    Later IBM machines came with the MVCL (move chars long) instruction which could move up 2 2**31 characters in one assembler instruction.

  • Mayhem (unregistered) in reply to Thought, the hungry troll
    Thought:
    http://en.wikipedia.org/wiki/Strings_%28Unix%29

    Its already been mentioned once by name in the thread. Also, according to the article there is also a windows port of the program.

    Many thanks. I'd never actually heard of this, I thought the "strings" reference earlier was suggesting we hadn't searched the config files for the app.

    As for the script kiddies, yeah, they might do better. this is the first time I've actually had to try and break into my own hardware, normally I have access to the admin passwords.

  • JohnB (unregistered) in reply to IHazYourCheezburger

    How did you acquire that knowledge? (I see no shame in asking!)

  • Synchronos (unregistered) in reply to Spike
    Spike:
    The penalty is doubled if you refer a version with a bald, french, pussy masquerading as a captain.

    You mean Kelsey Grammer is French?

  • Jay (unregistered) in reply to Marshall
    Marshall:
    Back (many decades) when I was a student there was a machine called the IBM 360 and one of the subjects required us to to program it in Assembler.

    The only instruction for moving data around was MVC (Move Characters) .. which included, hard coded within itself, the one byte length of the number of characters to move.

    Since runtime speed was a key factor in the marking scheme amd the startup cost of an MVC was very high, the standard technique (within the student community) was to do an MVC of zero characters - preceded by an instruction to do a logical AND that overlaid the length. Of course moving more than 255 characters became quite interesting .. luckily we didn't know then not to use GOTO's :-).

    Ah, for the good old days. When a programmer actually knew what was going on inside the computer.

    http://catb.org/jargon/html/story-of-mel.html

  • EngleBart (unregistered)

    I also like the recursion. If it fails, just call the same method again. Based on the user input, it will execute differently. I was too dizzy to figure out if it would correctly unwind the stack.

    On the kudos side, I like the separation of the UI layer from the backend layer from the error handling

    LogIn() vs. BatchLogin(...) vs. PassWordReset()

    Points off for inconsistent case though. It should have been Login() or else BatchLogIn(). (Or it could have been editorial misconduct?)

  • Corkscrew (unregistered)

    Right, I'm going to go out on a very thin limb and suggest that this is not actually necessarily daft.

    The basic problem with building any sort of VBA application that e.g. runs queries against a database is: where the hell do you store the password? If you put it in a spreadsheet cell then, no matter how you try to conceal it, anyone with a basic knowledge of VBA will be able to grab that password.

    This can be bad if the VBA code does things to the database that you don't want normal users being able to do. Users have a regrettable tendency to try to make their own lives easier, and finding out that the admin account on the database has a password of "overlord" could be used in a whole range of innovative labour-saving techniques. Right up until someone types DROP TABLE by mistake.

    Same problem if you try to stick the password in an external file. Probably you could do some funky encryption, but that's beyond most VBA coders (definitely beyond me). So really the only sensible thing to do is hardwire the password into the VBA code, and then lock the VBA project so no-one can view it.

    This is fine and dandy right up to the point that the password needs changing, at which point it becomes necessary for the code to self-modify. QED.

    Incidentally, there are non-evil uses for the CodeModule object's scarier methods. For example, I've used it to apply a bugfix to a couple hundred copies of the same spreadsheet.

  • Luis Espinal (unregistered) in reply to steenbergh
    steenbergh:
    I knew this would be fun when I read the title! Oh, the joys of self-altering scripts... That's what I call Organic Growth.

    Cancer also grows organically... just like this abomination of code :-/

  • PRG (unregistered) in reply to anon

    Username: John316 Password: M0053F15H

    Asuming the above are valid login details* the following password would succesfully log you in and display a message box:

    M0053F15H"): MsgBox "The MooseFish Has You." '

    After modification, line 3 in the code module would read:

    Result = BatchLogin("John316", "M0053F15H"): MsgBox "The MooseFish Has You" ' ")

    Only tested this in Excel 2003.

    *code modification only happens after valid details are entered.

  • (cs)

    I have used self-modifying code, but only in machine-code and in Forth, and not all the time (self-modifying codes are rarely useful, but it is sometimes good).

    However, I have once used self-modifying in GWBASIC, in a program where you enter an expression and it graphs it, it prepended a line number and wrote the expression to a file and then loaded it with CHAIN.

    But machine-codes and Forth codes does self-modifying codes best.

  • Shinobu (unregistered) in reply to RayS

    @Herby: I've stored values in x86 instructions when I was out of registers. Don't do that in DLL's though. And be aware of multithreaded environments.

    RayS:
    Imagine waking up in the morning to discover than you are a coding horror of epic proportions. Time to call the self-destruct routine, eventually leaving only the good code left in the world.

    So yes, not only do we need code to be self aware, we need it to be proud, and understand the concept of suicide.

    I feel like that all the time. Just can't find the epic self-destructor.

  • Ali Quam (unregistered) in reply to PRG
    PRG:
    What's your point? (The point that others haven't yet made, that is.)
  • Anonymous (unregistered) in reply to Corkscrew
    Corkscrew:
    Right, I'm going to go out on a very thin limb and suggest that this is not actually necessarily daft...
    Suggest all you like, it doesn't change anything.
  • Gup (unregistered)

    Its crazy and amazing

  • Mr^B (unregistered) in reply to Corkscrew
    Corkscrew:
    Right, I'm going to go out on a very thin limb and suggest that this is not actually necessarily daft.

    The basic problem with building any sort of VBA application that e.g. runs queries against a database is: where the hell do you store the password? If you put it in a spreadsheet cell then, no matter how you try to conceal it, anyone with a basic knowledge of VBA will be able to grab that password.

    This can be bad if the VBA code does things to the database that you don't want normal users being able to do. Users have a regrettable tendency to try to make their own lives easier, and finding out that the admin account on the database has a password of "overlord" could be used in a whole range of innovative labour-saving techniques. Right up until someone types DROP TABLE by mistake.

    Same problem if you try to stick the password in an external file. Probably you could do some funky encryption, but that's beyond most VBA coders (definitely beyond me). So really the only sensible thing to do is hardwire the password into the VBA code, and then lock the VBA project so no-one can view it.

    This is fine and dandy right up to the point that the password needs changing, at which point it becomes necessary for the code to self-modify. QED.

    Incidentally, there are non-evil uses for the CodeModule object's scarier methods. For example, I've used it to apply a bugfix to a couple hundred copies of the same spreadsheet.

    You need a password to access a database? Never heard of integrated security?

    I also store connection strings in Custom Properties, and use a property editing .dll (dsofile.dll) as part of a nant build script to substitute in the correct environment settings as required.

  • Steve Urkel (unregistered)

    Now THIS is truly worthy of a facepalm...

    What's worse is that this must surely have been done as a sort of sinister joke. It's unlikely that someone who is capable of writing self-modifying VBA code would be so incompetent as to not know how to make use of simple variables...

  • ThatsALotOfGravy (unregistered)

    Assuming there is no upper limit on password length...

    ") : x = 5: y = "ThisWorkbook.VBProject.VBComponents.Item(" & chr(34) & "DataBase" & chr(34) & ").CodeModule.InsertLines x, y: x=x+1": ThisWorkbook.VBProject.VBComponents.Item("DataBase").InsertLines 4,y '
  • (cs) in reply to anonymous coward
    anonymous coward:
    TRWTF is that such a dangerous property like ".CodeModule.Lines(3, 1)" actually exists.
    We are talking about VB. Anyone remember "option explicit"?
  • Fred (unregistered) in reply to Jumentum
    Jumentum:
    I haven't seen VBA code in years (thank god) so could someone enlighten me what these lines are supposed to do? Just looks a bit funny :p 
    AppSts = Application.ScreenUpdating
Application.ScreenUpdating = True
Application.ScreenUpdating = AppSts

    "Application" is Excel. These lines save the Excel state, change it, then restore it.

    Specifically, screen updating is expected to be off, which makes the code run faster and cleaner, and is turned on once, at a specific place. But if, for some reason, screen updating is already on, this doesn't mess it up.

  • Fred (unregistered) in reply to anonymous coward
    anonymous coward:
    TRWTF is that such a dangerous property like ".CodeModule.Lines(3, 1)" actually exists.

    As a relevant example, object oriented programming was not invented Ex Nihilo when object oriented programming languages were developed. In BASIC you implemented inheritance by deriving new objects from old objects. You do this by creating new objects which inherit the code of the old objects. To do this, you need to be able to write and load code.

    And why was a dangerous practice like object oriented programming allowed? Real applications. Real programs.

  • C (unregistered) in reply to anon
    anon:
    plg:
    I'm not sufficiently familiar with VB to come up with an interesting password. Does VB allow multiple statements on a line? And is the comment sign //?

    My password:

    "); somefunnycode(); //

    Close. Apostrophe instead of double slash, and colon instead of semi-colon with none trailing. 
    ") : someFunnyCode() '
    Very VERY close. But you can't call a procedure (subroutine) like you would a function. It'd be either: 
      ") : Call someFunnyCode '
    or: 
      ") : someFunnyCode("dummy", 42) '
  • Peter Schwarzman (unregistered)

    VBA for trading algorithms? No wonder we had a banking crisis. Do those banks have a Quality Assurance?

  • Stiggy (unregistered)

    Ah yes... The VBA Extensibility library, in all almost-reflective glory. For my sins, I have been guilty of referencing it.

    But not for anything like this. Damn, I wish someone would make better goggles.

Leave a comment on “Self Modifying VBA”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article