PDA

View Full Version : Help Troubleshooting a VBA Application



garak0410
09-07-2012, 08:09 AM
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.

If anyone needs to see the code, I can post as needed.

Thanks so much!

Brian

snb
09-08-2012, 04:48 AM
We don't need to see the code, you need to show us the code if you want some reasonable answer...