+ Reply to Thread
Results 1 to 12 of 12

Tips for understanding someone else's VBA macros

  1. #1
    Forum Contributor
    Join Date
    04-14-2008
    Location
    Fort Collins, Co
    MS-Off Ver
    2016
    Posts
    107

    Question Tips for understanding someone else's VBA macros

    I am a new employee taking over for someone who is already gone. I can program my own macros in VBA, but I'm not the best at understanding someone else's code and I'm not sure what approach to take.Especially since this code violates many programming best practices (contains lots of GOTOs, is inefficient, not well-organized). I don't even know what the code is doing as there's no documentation. My job is going to be to make this code better, maybe even rewrite it. But I can't rewrite it unless I understand what it's doing. The person who uses the spreadsheet and wants me to look it over says it takes 4 hours to run, so it's not really possible to use the debugger to step through.

    So, should I make flow charts? Notes in the code? Is there a tool for annotating code to better understand it?

    Any tips are appreciated. It looks like this is going to be my job for some time to come, and I will be looking over many spreadsheets like this and trying to make them maintainable and well-documented.
    Last edited by 4am; 03-08-2018 at 07:58 PM.
    Is That Your Spreadsheet Or Did Your Database Just Throw Up?


  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,110

    Re: Tips for understanding someone else's VBA macros

    How is the code organised? Is it a single module with many subs or one big fat? You should approach it as you do in many modular based programming. Try to split the code in chunks based on what each line is supposed to accomplish. 4 hours is over the top. The longest code I have run so far took about 56 minutes. I am surprised that the code does not crash.

  3. #3
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    2,128

    Re: Tips for understanding someone else's VBA macros

    With something like that, I'll often put line numbers on the code & then I can write up documentation elsewhere referring to the line numbers.
    Otherwise you can end up with more comments than code!

  4. #4
    Valued Forum Contributor
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    1,013

    Re: Tips for understanding someone else's VBA macros

    Maybe you can run it then escape out of the macro a few times and check to see where it spends most of it's time (like 90% of the time you stop it hopefully).
    Then try to make that faster (maybe you can take the data into an array as opposed to working on a sheet or something like that, or comment it out entirely or just force it to output a given known valid result) then it might become plausible to spend the time stepping through it.
    Last edited by scottiex; 03-08-2018 at 04:32 PM.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  5. #5
    Valued Forum Contributor
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    1,013

    Re: Tips for understanding someone else's VBA macros

    Of course sometimes its easier to just collect the available inputs and the desired outputs and draw up some sort of process as to how it should have been written in the first place, and just code that. Retaining parts of other people's badly written code can be an ongoing pain every time you have to make an adjustment.

    Maybe you would need to spend the time convincing the user that they don't need exactly the same output as long as it serves their basic need, and that maybe you can provide something in some sense better then get a bit of flexibility for how it is created. (you can probably offer them a massive improvement in speed at the very least!)
    Last edited by scottiex; 03-08-2018 at 05:34 PM.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    11,734

    Re: Tips for understanding someone else's VBA macros

    I'd put in a bunch of stops and try to make a flow of each segment - you can time the interval between stops to find where it's gagging. How many pages does the code fill?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,570

    Re: Tips for understanding someone else's VBA macros

    Why not ask the person who uses the code what it does for them?
    If posting code please use code tags, see here.

  8. #8
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    2,783

    Re: Tips for understanding someone else's VBA macros

    If the existing code is poorly written (typically evidenced by lots of unnecessary Select and Activate statements), as your reference to 'lots of GOTOs' suggests, you'd do better to find out what its supposed to do, then write an efficient version from scratch. Depending on what the code is doing, 4 hours may or may not be reasonable; I've written efficient code that took that long to run because of the nature of the task (interrogating & analysing 12 million records in 150 text files) and the hardware & software (Excel 2000) in use.
    Cheers,
    Paul Edstein
    [MS MVP - Word]

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,110

    Re: Tips for understanding someone else's VBA macros

    So, if I were to run the code everyday, I have to wait for 4 hours. The software world is gone made. I can not wait for AI to kick in.

  10. #10
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    2007
    Posts
    3,882

    Re: Tips for understanding someone else's VBA macros

    .
    I'll go with Norie and macropod. First understand what the code does - overall - what is the final product ? Then from your experience, would it be better to rewrite the existing code (balance how much time/effort it would take) vs. starting from scratch and create a new product that is leaner and more efficient. If going the 'from scratch' route makes more sense ... maybe ask the user what improvements they would like to see in the existing product. You could use that as leverage to start from scratch and provide a 'new look' for the GUI. That might assist you with selling the new product. Everyone (hopefully) likes new and shiny.

    Wonder if the original programmer worked years ago where they were paid by the number of lines of code written vs. efficiency.
    DEPLORABLE : A hard working, tax paying, patriotic American. That's me !

    Cats. Because People Suck.

    If someone has assisted you in any way, please be respectful to acknowledge it.
    Simply saying "Thank you" will suffice. If you are so moved, adding to their reputation by clicking
    <------ Add Reputation is a very nice gesture.
    When your question has been answered, Please mark the thread as SOLVED.

  11. #11
    Forum Contributor
    Join Date
    04-14-2008
    Location
    Fort Collins, Co
    MS-Off Ver
    2016
    Posts
    107

    Re: Tips for understanding someone else's VBA macros

    Thanks, all of you. The guy who wrote the code is long gone. The situation is so bad that people don't even know what the code is supposed to do or how it gets to the end result. Just that it outputs a report by compiling from many, many other reports.
    There are 9 modules called "module 1", "module 2"... and no rhyme or reason to the modules at all. It's bad. Even if I'm going to rewrite it, I still have to understand what it does because like I said the person who runs it only knows that it puts out a report in a certain format.
    This is going to be so fun. /s

    Thanks again. Marking solved.

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    11,734

    Re: Tips for understanding someone else's VBA macros

    You're welcome and thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1