none
Help Troubleshooting a VBA Application RRS feed

  • Question

  • I was hired as the sole IT person and director for a small metal building company. Stability was a big issue here and it took me a good year to get things running well, upgrading people to Windows 7 and establishing a good DR plan.

    Now, they want me to troubleshoot the inhouse application that is an "in between" program between their Metal Building Software (called MBS) and the machines in the shop that cut the panels, purlins and standing seam lines.

    The program is a massive Spreadsheet/VBA combination. The VBA is stored in an XLA file and it is imported into each sheet that is created. 

    Bottom line to the problem: There are standard punches that go into column I (in code shown as (i, 9). Columns K-P are columns if they want to include a special punch in addition to the punch they put in column I. So for instance, if the punch is PES but they need additional flange holes, they put that in column L, and then run two macros; one called Validate Holes and then one called Create Note. Create note takes the PUNCH that was in column I, moves it to Column Z and replaces it with a #_1, or #_2 and so on. Then it makes a note saying, for instance, #_1 is a PES punch plus (the data from column L). 

    This all works as it should EXCEPT when the punch code is PSS and the type is an EAVE STRUT (indicated by ES normally). If it is PSS and they need a special punch, after choosing VALIDATE HOLES and then CREATE NOTE, all works out well. Except when it is exported to a CSV for the shop machines, it also includes a PES punch in the data.

    The Macro for exporting is called RELEASE TO SHOP and it creates a readable CSV file for the machines to read and then cut/punch. 

    Somewhere in the code, when PSS is used in addition to a special punch, it ALWAYS adds the info for a PES punch. 

    A special punch is indicated by a #_ in the code and there is only three mentions of it and 3 commented out (with no notes as to why.) 

    Not being to savvy on VBA, this has been very frustrating. I am learning as I go but I've been spinning my wheels here for weeks now (not to mention pressing F8 in debugging mode for countless hours) and would like to produce some results. I know a lot of people may tell me to outsource this but I have no IT budget right now. And I am not asking for a complete fix for it (though it would be appreciated) as I do want to learn but at the very least, a nudge in the right direction.

    I was going to post the code but I am going over my text limit. If anyone needs to see it, I can post as needed. 

    Thanks so much!

    Brian

    Friday, September 7, 2012 2:59 PM

All replies

  • I do not know exactly where is a problem (without code) and describe on example).

    You can attach link to Skydrive or another to download this solution (or some of them).

    If this is a problem only with CSV file, preparing from worksheet - you can use my addin: XLS to CSV Add-in


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, September 7, 2012 3:29 PM
    Answerer
  • I do not know exactly where is a problem (without code) and describe on example).

    You can attach link to Skydrive or another to download this solution (or some of them).

    If this is a problem only with CSV file, preparing from worksheet - you can use my addin: XLS to CSV Add-in


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Excellent idea: here is the link to the code: Link To Code

    The PES punch data is being added to the PSS punch that has the special punches with it. That is not seen on the spreadsheet but only in the export (CSV) file. So somewhere, in one of the processes, either before or after export, it adds PES punch info to the PSS+special punch request. 

    Again this is only on an Eave Strut with a PSS punch with an additional special punch...it also adds in the PES punch. No other special punch has this problem and PSS alone without a special requests punches fine. 


    • Edited by Brian Wolters Friday, September 7, 2012 3:58 PM Added link
    Friday, September 7, 2012 3:57 PM
  • I got your code, but You do not give me data.

    Is no way to know what is going on looked only on procedures.

    Take the patch, I do not know either the punch or the operation of this program. I should to look on that (or representative samle).


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Sunday, September 9, 2012 2:30 PM
    Answerer
  • I got your code, but You do not give me data.

    Is no way to know what is going on looked only on procedures.

    Take the patch, I do not know either the punch or the operation of this program. I should to look on that (or representative samle).


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Understood...this may be too massive to get online help. I have taken a dive into the SQL Tables some and basically, every PATTERN name includes a PES punch. So, if PSS is encountered, it ignores the punch information in the pattern name and uses the information in the table for PSS. However, when it is a special punch with PSS and xxx type of punch, if will also pull in the punches from the pattern in the table AND the punches from PSS. So, I am going to see why PSS works by ignoring the punches in the pattern it requests and why it doesn't work when it is a special request.

    This is a lot for a non-programmer but I will say, I am learning. My head just hurts! ;(
    Thursday, September 13, 2012 7:12 PM
  • Hi Brian,

    Thanks for posting in the MSDN Forum.

    I suggest you create your issue in a simple project and share the scenario in the thread will help you. Everyone isn't a programmer at the beginning, is it right? Take it easy.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, September 17, 2012 4:49 AM
    Moderator