Any Suggestions for Visual Basic?
I've been trying to solve a lot of my issues at work with C++ (not day-to-day type of things but just general automation) and considering everything I work with is spreadsheets, C++ just seems like a bit of overkill... I'm also a supervisor now so C++ is even less optimal considering it has to be compiled to specific architectures. I hate VS Basic, but I've had experience with it writing macros in my previous job but never any actual 'programming' - is anyone aware of any sites like this for modern VBA? I'm intimately familiar with Excel and writing macros in VBA but have never actually written anything I would call a full-on program with it. I would default to Python but it's not installed on our work laptops by default and I need anything I write to work across my entire team.
VBA is a macro language for MS Office.
I doubt that you can write write full programs in VBA.
Do you maybe mean VB.NET or the very old Visual Basic?
I doubt that you can write write full programs in VBA.
Do you maybe mean VB.NET or the very old Visual Basic?
It's really unclear whether you want to write Office macros, complete programs, or what. Could you give an example of a problem you're trying to solve programmatically?
it sounds like you need cross platform, given your comment on c++ needing to target various tech? VB is not portable, its a microsoft language that extends basic a great deal so it can do gui work and more. Its a long long way from portable basic.
Python is portable and free, and you are the supervisor, can you not request they install it or that it be put on? Its not like there is much cost to it, 10 min per person to DIY or factor that to some IT push if your group works that way...
another issue, is who will inherit your work? Who else knows the languages/techs you will use? You want to be able to write it and then if it needs an update get a flunky to do it, not be saddled with supporting every little thing you wrote forever, so it needs to be a language a number of people on your team know.
excel programming is amazing. Within the limits of what it can do, a tiny bit of code does a great deal of work.
Python is portable and free, and you are the supervisor, can you not request they install it or that it be put on? Its not like there is much cost to it, 10 min per person to DIY or factor that to some IT push if your group works that way...
another issue, is who will inherit your work? Who else knows the languages/techs you will use? You want to be able to write it and then if it needs an update get a flunky to do it, not be saddled with supporting every little thing you wrote forever, so it needs to be a language a number of people on your team know.
excel programming is amazing. Within the limits of what it can do, a tiny bit of code does a great deal of work.
Last edited on
To expand a bit my job is basically to send out demographical updates for doctors off to insurance companies - usually updated licensure or completely new doctors at the practice altogether. The issue we’ve been running into with our new client is they are all across the US so when we export the ‘license’ (or any kind of state specific info) field - it will export one row for every combination of license/DEA/Medicaid #/Medicare # etc. Those state specific fields are causing our reports to blow up in size... Initially I just filtered the reports to only pull whichever state we needed the info for but this client is huge and there’s a good chunk of ‘State’ fields that our data entry team just didn’t fill out so it excluded those since State = “TX” wasn’t true for some fields that data entry didn’t explicitly select the state (so it was blank)
Last edited on
Adding to the above - even outside of the state specific issues most of my job is really correcting formatting issues from our reporting software to the clients excel template - stuff like exported info is TRUE/FALSE and the template expects a Yes/No or dates export as mm/dd/yy and the template wants mm/dd/yyyy. It just seems like all of this could be completely automated but I’m not completely sure how to start doing that in a way that would be translatable to the rest of my team (both functionally as a program or macro but also something that makes sense to them - I’ve had to teach them all how to use xlookup so the learning curve would be steep)
ex:
Raw export
I would need row 2 only in the above but our reporting software exports everything for each field. Add in all the other state specific fields and the file explodes pretty quickly, and I can't filter to specific states on the reporting side because our data entry team will occasionally skip over the 'State' field for something and we then need to double back and fill it out on our end...
Raw export
ID License LicState DEA DEAState PracticeState 1 1234 TX 5678 NH TX 1 1234 TX 9876 TX TX 1 4567 NH 5678 NH TX 1 4567 NH 9876 TX TX |
I would need row 2 only in the above but our reporting software exports everything for each field. Add in all the other state specific fields and the file explodes pretty quickly, and I can't filter to specific states on the reporting side because our data entry team will occasionally skip over the 'State' field for something and we then need to double back and fill it out on our end...
Last edited on
Hmm... Sounds like your "export" is just blindly doing an SQL join on some database. The (IMO) smartest way to do this would be, rather than postprocessing the exported data, to add a "report" function to whatever software is generating the exported file, that gets whatever data it needs from the user. If that's not doable for whatever reason, the report generator could connect directly to the database and get only the relevant data.
I've spent my entire career populating Excel spreadsheets. It's embarrassing but it pays well. It is my experience that you normally do not get to dictate what the data looks like; the data is what the data is.
My advice is not to write VBA macros they often scare the end users. If the data comes in an Excel spreadsheet exported to a CSV file. Then do your post processing with a scripting language. I personally use perl, perl like c++ allows you to write really unreadable code, but if you're write it right a novice will be able to hack it as needed in the future.
Have your scripting language export to a CSV file and then import it back into Excel to comfort the end users.
My advice is not to write VBA macros they often scare the end users. If the data comes in an Excel spreadsheet exported to a CSV file. Then do your post processing with a scripting language. I personally use perl, perl like c++ allows you to write really unreadable code, but if you're write it right a novice will be able to hack it as needed in the future.
Have your scripting language export to a CSV file and then import it back into Excel to comfort the end users.
Last edited on
Topic archived. No new replies allowed.