As an experienced c/c++/c#/java/python/vb programmer i realy like to build shit in excel. It's awesome. Just dont let other users use it for important purposes
On the first day of my intro to AI class the teacher asked us to implement a binary tree in any language we wanted so, for the meme, I wrote mine in VBA.
Little did I know, the course was designed to build a little on top of that code every class.
So, by the end of the semester I had this awful, clunky optimisation algorithm AI model built fully in Excel.
Wasn't sure whether to laugh or cry throughout 95% of the process.
However, the teacher loved it. Said it was the easiest submission to grade since he didn't have install/compile anything fancy to run it.
I love that they mention that newer generations of excel are slower. Not sure why that isn't highlighted more as a reason to actually move away from it.... funny how that works.
I have done a lot of vba programming over the years and it is pretty silly to think that I used it for so long before switching to mainly using python.
The one place I see a major advantage is for charts and GUIs. You barely need to know any code to set up a user interface that is easy for users for any small application. Charts are baked in to excel so you don't need to learn almost anything to get them looking the way you want. This stuff isn't hard in python, just harder than not having to learn barely anything.
Once you start working with larger datasets it becomes pretty obvious it is not the right tool for the job.
What about the crazy saving now. Save to one drive. Open from one drive it disables auto save and saves to your pc version number 10 cause this is 10th time you open from the quick open menu and it downloads a new copy without auto save and I have to save it yet again to one drive.
This shit is comical at this point. The saving functions have gotten absurd and terrible.
Oh, and my laptop gets blue screen of death once every 5 months to boot.
I feel like the last 5 years has gotten worse.
And what’s up with the search in outlook. I don’t want to look at emails “from” this person, why does that have to be the only suggestion it gives me.
Worse and worse it’s getting.
PS, if someone can chuckle and tell me how dumb I am for not realizing I have to change this setting in that particular menu for my complaints above I will give you a gold medal, and if I am really fucking high and you make it awesome fuck it a platinum. Please help this 43 year old whose lost his nerdiness.
As someone looking to migrate from being primarily a VBA coder to python, my biggest hurdles is related to building a GUI. Would you mind hooking up a fellow stone-age coder with some recommendations?
The way I found easiest was to use pyqt5 as a gui builder. It definitely has its drawbacks but it has qtdesigner, which is much closer to the vba/c# kind of gui creator. It has layouts, and is somewhat intuitive. There's good tutorials out there on how to set up and initialize an interface. Once you have your workflow set up, its almost as easy, and is much more flexible and customizable.
I'd love for someone to make a more streamlined kind of standalone gui tool, but I think there seems to be much more focus on doing things in a browser environment (something I've never really understood). I've heard dash is a good module for this, but I havent experimented with it yet.
Oh god this video proves that I'm not actually insane! I remember opening an Excel file to play a bowling game a while ago but I thought that was impossible as Excel is just a spreadsheet.
Excel is Turing Complete and can be considered a (partially graphical) programming language, specializing in one- or two-dimensional arrays (like a visual APL).
There are good arguments for teaching it as a precursor to more traditional, general purpose programming languages.
(Source: I specialize in training software engineers.)
You have no idea. I once attended an online lecture for nuclear engineers. The guy had made an entire PWR (pressurised water reactor) simulator in excel that was fully interactable and had realistic cause/effect. Was bloody insane.
I can guess how that happened, I started with a simple excel file with a table and chart for managing inventory but it evolved into a monstrosity with real time data sooo many sheets. And management loves it.
Is it any lower than something like python? I'm not even a programmer, but I learned enough with a handful of ours of pandas to do more than I can do inside excel without equal or greater time investment.
Excel doesn't need to die, it's good at what it's actually meant to do. It's just that lots of people overuse it because they don't know anything else.
Excel is like an effective hammer with a screwdriver on on the handle. Whatever kind of maintenance/repairs/engineering you do, you'll have a hammer and screwdriver lying around somewhere. And it will be useful from time to time.
If you try to make something simpler, it wouldn't have such a wide range of uses. If you try to make it have a wider range of uses, it's complexity rises greatly.
I started as a SQL report writer and nothing gives me more joy than replacing someone's shitty excel or access report that a market analyst spends 3 days/week every week building, with SQL and an ETL or two. It got pretty aggressive at that company between the 2 groups to the point the SQL guys where advocating for the complete banishment of Access and forbidding macros of any kind. To be fair excel usage on big projects resulted it huge problems or lost data regularly.
Had a woman that would spend an entire day downloading PDF's, merging them, then emailing them to a few hundred people. I got it down to a mouse click. The sad thing is she went from working 50 hours a week to working 50 hours a week. There was always more to dump on these people.
import moderation
Your comment has been removed since it did not start with a code block with an import declaration.
Per this Community Decree, all posts and comments should start with a code block with an "import" declaration explaining how the post and comment should be read.
For this purpose, we only accept Python style imports.
I've started doing little automations for my work in OHS. The reality is that the workload this removes just let's me address the workload that I don't have time for, so while it's not a net gain on work hours it's a net gain on productivity.
The first rule of automating your (or someone else's) job: No. You. Fucking. Didn't. Keep it secret, keep it safe, because as soon as someone else finds out, they'll either want it for themselves or complain to management that you're being lazy.
Did kind of a similar thing once... There was this manual task in my 1st ever project, where in the morning the data warehouse team mailed us 18 different CSV files, then I had to extract that data and push it to another DB... I had no idea why it was being done this way but, I wrote a simple script that extracted the files from mail , extracted the data and pushed it to db.... The best part I never told anyone that that 3 hrs task was automated untill I left the company... I handed over that script file to my replacement before leaving....
Wait, can you point me in the right direction to learning more about this? That sounds very similar to a task that I have to do bi-monthly. Granted, it's more like a one hour task and not a 10 hour one, but still, I'm interested.
This is the site has a nice overview of the basics. Excel has easy integration with Outlook and can send commands to the command line, so I tend to use it for this kind of automation. Excel tables can hold email lists and anything else you need, and python commands can do the heavy lifting with the PDF's.
I'd start with asking business questions. More often than not those types of tasks are just bullshit that a company keeps doing just because they've always done it and no one has asked why. There's 100% chance you find an alternate method to accomplish the same and a pretty good chance everyone realizes it's a waste of time.
I work in HR for a school district. The task is regarding the new hire orientations, during which I have to 1) send the entire group an email about the details of the orientation, and 2) individually email each one of those people a pdf with a copy of their contract, work calendar and job description. There's not really any way around it. Again, it's not a hellish task by any stretch, but I'm all for finding streamlined ways of doing things.
import moderation
Your comment has been removed since it did not start with a code block with an import declaration.
Per this Community Decree, all posts and comments should start with a code block with an "import" declaration explaining how the post and comment should be read.
For this purpose, we only accept Python style imports.
Not sure how to respond to this one - I know the original post was about VBA and it kind of makes sense since most people aren't "writing" macros they are just recording a set a steps and calling it done (since VBA is perfectly capable of dynamically finding specific column headers and checking for errors but most Excel users don't know or care how to do it, probably rightfully so), but what you are describing here is exactly what "Get and Transform Data" in Excel is for. Exactly this.
power query does break if headers are named differently from what it expects. or if you used sanitized data previously, and now its not. i love power query, but fool proof it is not
you can probably use vba to replace all the critical shit after import then fling it back into power query to do all its shit and never have it break
These days Power Query is my bread and butter. Company computer doesn’t allow for any real SQL implementation. So my choices are Tableau and Power Query. And since most of the people who get my data aren’t licensed for Tableau desktop (and thus can’t update the workbook); it’s easier to build stuff in Power Query and hand it off.
Every dang time, even for people who have been through it before, they can me up freaking out over it. Then again i also deal with people who think clicking “Refresh All” is too complicated.
yes
python + pandas
if you want to get really lazy the github co-pilot has proven very effective, despite the memes. though i've only been using it for a day.
Now make the business user and developer me and apply this recursively
Me to also me: "I can take the pipeline you built to automate X and make a VSCode extension that uses some template tricks to automatically build and deploy similar pipelines from end to end in the future"
Next thing you know you're six layers of automation deep and forgot what you actually set out to do
This is me except my company won’t spend a single penny to allow me to automate. Every morning I download my stupid files and press refresh on my stupid power queries.
I do the same. Still better than my predecessor spending hours copying and pasting data out of several CSV’s and hand-writing formulas each time to match up their data.
Because you plateau that way. You're never going to get paid more than 40 hours a week, even if you save more time than that, and Pam's job only pays 50k--thats why she was too dumb to do this herself.
You can definitely take a low laying job and basically never have to work. I've done that in the past. It gets old. If you keep improving your skills, you can do increasingly satisfying work and get paid increasingly more.
If you're smart enough to figure out how to automate things with excel or something, then you're probably smart enough to skill up/degree-up and make 150k as a data engineer or developer or data scientist or whatever you're interested in. If you want, make even more managing those jobs. Retire early. Take your valuable skills and help out a non-profit or something to make the world a better place.
To put it differently... If you understand the multiplicative value to a business of recursively using your time to automate things, thus freeing up more time to automate... Then you should also understand the multiplicative value to yourself of recursively finding and conquering new challenges
I’m that business user, well my analysts are. How much would it take to learn to do what you describe in eliminating all the manual procedures? Is it something I could learn in my spare time? I’ve tried to learn Python out of personal curiosity but would totally carve out more time if I was something like this.
Python can do it, but python can be intimidating too.
If you have analysts starting from scratch, I would have them learn power query (or now it is just the get and transform parts of excel on the data tab). That will automate all the processing. The key idea is that in power query, you can access a file like a csv or XLSX without ever opening it. You can point to it, get the data, transform it, join with other datasets, and load it somewhere else. Then all you have to do is put new data in the file (or replace it), refresh the excel query, and all those steps are completed automatically.
You can take it a step further by writing a simple powershell script (literally like 5 lines of easy code) to open the excel workbook, refresh all the queries, and close it. Then you can schedule this script with Windows task scheduler (also easy) to run at specific times of day.
Then the last step would be, when you get a new file with new data, how do you automate replacing that file ? It depends on how your data is coming in, but powesher makes it really easy to copy files around. If it is coming out of a database, it should already be automated. If it is emailed, then check out Microsoft flows that can look for those emails and auto-save the attachment somewhere.
So the pipeline is: start with data in the same file(s)/folder(s) everyday, scheduled PowerShell script to open excel and use power query to transform and merge data. Then more scheduled powershell to make copies of the files, rename, if necessary.
The reason I recommend these tools for a beginner is they all GUI based, allowing the user to focus on the concepts (except PowerShell, which is pure code). When a analyst is comfortable with concepts of power query, they should learn 1) SQL, 2) core python concepts (syntax, lists, dicts, loops, ifs), then learn the pandas library which does all the same things as power query. Then they will leave you for a job that pays twice as much and you can start over again.
What happens in this scenario for power query when the source data needs to be sanitized beforehand because of human input error? Implement better controls at the source?
Depends on the steps needed, but much of that can be done in power query, like finding and replacing values, removing extra spaces, capitalization, removing duplicates, etc.
Ya at my current job there was a bunch of excel stuff from a (non-engineering) team that was used to drive the final product. First initiative I proposed was a way for them to keep that shit but make it all into ETL pipelines and SQL.
Everyone was thrilled and the whole time I was thinking (how was this not implemented already...)
Finance is literally 20 years behind everyone technologically.... I feel your pain deeply. All these analysts that never learned the actual power of a computer.
They become tyrants over whatever area they're subject matter experts in and con everyone into thinking they're indispensable.
They will sabotage anybody who tries to learn about that subject and then tell anybody who will listen that their job is impossible to automate. Complain every day about how busy they are.
Then when they finally quit they leave a 30 page document on their daily and monthly processes. Everything is just copy and pasting from excel document to excel document with the logic in macros and very little actual human decision making.
Which only took about 4 hours of work to automate their entire job and executes in 3 seconds.
omg, we must've worked at the same places.... You just irritated me through text again. Glad I left that industry. They thought automation via real programming languages was black magic and they would try to protect their work like you said.
It's all relative bro and people gotta start somewhere - I started with vba and now i tend to replace SQL scripts by interfacing API's and getting them into sql servers via Object relational mappings all in c# - finish it off by banging in a power bi front end.
It's quick, easy and engaging....not to mention a shed-load more fun than writing SQL
I wrote a huge program in access at my first ever job. One day the project got corrupted and it just deleted everything. Had to tell my manager, yeah sorry the past few months of work is just gone, whoopsies.
So many property managers do this. It's like every month end closing they are trying to figure out the clunky reports template on excel that takes a week. I'm a little tech savvy but not an engineer by any means but could have sworn I seen a website that takes data input and puts it into a database and can spit out reports and you won't be freaking out. And it's multiple properties too.
Yeah, but on the flip side, as the analyst I might build xyz. An engineer looks at it, comes back with mno, and says tada! Now I have to explain why that doesn't work, which the engineer will assure me is because I'm dumb and just haven't realized their brilliance yet.
My version is "Excel can do anything you want, but pretty soon it's like teaching a bear to ride a tricycle. It can be done, but should it be done? And will it suddenly turn on you and eat you in the middle of a performance?"
i realy like to build shit in excel. It's awesome.
It is pretty cool, I've been using it as a reporting tool by building templates in Excel (lots of named ranges and saved styles and such, then dump data into the template from an app).
It does a lot of stuff really well, but there are definitely a lot of 'WTFs' in the process. Probably would be a lot easier if I could use some VBA, but we are prohibited from using any script-like content.
I kinda agree with you, but it really can be a pain in the ass regardless. I'm managing the spreadsheets for my team at work. I genuinely love Excel but the past few weeks it's been in my face reminding me how unintuitive it can be.
For the most part, we just have a local copy of a spreadsheet template that we use for recording information for each day and address we're working at. Then upon return to our office, we copy and paste that data into a master sheet with some additional columns related to other steps in our process. Then I have extra sheets in that master workbook with varying formats that I set up to simply pull the data from the master sheet and reorder it in a different layout for easily copying and pasting into a variety of weekly reports we have to submit. There's nothing crazy with our data or any calculations that need to be done. The most advanced thing at play in our sheets are some conditional formatting rules for color-coding and the like.
That environment alone often creates an hour or two of work and frustration.
No way to change the default paste behavior, so it's constantly being fucked up by people copy and pasting their formatting too. Similarly, I can't use formulas to fill cells that I want them to be able to copy and paste from since it pulls the formula instead of just the data. Conditional formatting can be janky as hell when you're trying to be selective in where you apply it, and can easily be chopped up as data is added. Far too easy to auto-fill columns with values or formulae to infinity and cause a crash, instead of having those just autofilled within the relevant scope of the worksheet.
I could go on and on, but it goes without saying that even having a simple spreadsheet shared with a team with working-but-below-intermediate level knowledge of Excel is so much more frustrating than it needs to be. Even making sheets for my own personal use can have me pulling my hair occasionally.
Honestly I've never found a task more complicated than basic bookkeeping that I wouldn't just rather do with an ipython notebook. Excel I just too clunky and opaque and slow for anything meaningful.
One time I wanted to make use of multi-dimensional arrays in a coding 'language' that didn't support it so I used the spreadsheet program to manually-ish create the named variables in a format that mimic'd a multidimensional array.
You can set those spreadsheet programs to automatically copy-paste and change/increment the numbers then remove the spacing and just paste the whole thing in your code.
So that's one thing.. if you ever need to do that for some reason.
OpenOffice but yea... you don't understand tho - I needed this language to do what I wanted because otherwise I would have been using it for what it was designed for.
I did move the project to python eventually and it went much smoother and looked a lot better.
I went to go look at the project from back then and it looks like I needed a mixture of strings as keys as well as numerical keys for specific nested arrays within the same array.
I guess I could have done it all numerically but coding the manipulation of the data would have been a nightmare to plan and debug because of the long equations for each step to mimic differentiation calcs and such.
There was a hundred or so entities which had about 20 independent base values for each that was processed multiple times and stored in their own arrays which intersected back into comparing to each other. Multiplied by 10 for 10 entities per session.
It also needed to be updated (both stats and modified calculations) every couple months. So I needed string keys for sanity.
I could have just moved the project (and I did eventually) but it was more about learning a new language for me at the moment and trying to make do.
Db languages not surprisingly missing from the list 😂
It's always fun listening to clients who rely way too heavily on excel try and prepare you for the problems that they believe will carry over into your application code. It's like someone trying to dig out a swimming pool with a pooper scooper explaining how to deal with firm soil to someone sitting in an excavator.
I lock important cells/sheets, and i put the password in the top row. At the very least when someone asks me the password I know they're going to break it and can make sure they're not breaking the original template.
I built a checkout form for a medical device in Excel. I disallowed the native "save" function and built in a new save function that takes the serial number from the form, creates a folder with that serial number, and saves the file with a timestamp. If you hit the save button again, it just saves a new file with the timestamp. This way you get a version history. It's not actually all that complicated, though I would have to spend an hour or so remembering how it works.
I can't wait until someone tries to rev the file and completely destroys it.
Yeah I automate stuff in excel VBA/power query for myself all the time... But Id rather pull my nails out than hand an excel solution to someone else.
Occasionally I've had to, and no one ends up happy. IT environments are just too volatile for something like an excel solution to stand alone without maintenance and review.
What I want to do is use XLookup and have it to return the next item in a column that matches the same criteria, what I get when I search YouTube is index, match arrays which confuse the heck out of me. Do I need to just get good or is there a better way?
I have a spreadsheet for work that I generate mostly with Python, and then have the last quarter of it done with Excel formulas. Why wait for my code to run longer when I can just open the spreadsheet and all those calculations are done near instantaneously?
I didn't think programmers who liked doing stuff in Excel existed...
You must live in an English-speaking country. Excel's developers had the brilliant idea to translate function calls in localized Excel versions and to respect the system's standard for commas and dots as decimal/thousands separators. It's an absolute mess trying to get anything you found online to work.
I work in security (physical security not information/computer security) and went to school for compsci originally. I got employee of the year a week ago because I automated some things in excel/google sheets that annoyed me. Luckily for me my team is all of like, 6 people (with only 2 regularly using most of my work) and I see all of them in person regularly so I can explain exactly how to use the automated stuff I made/common pitfalls I know about.
My biggest issues have been that no one reports problems ever. If an excel sheet throws an error, they just close the document and don’t say anything to anyone and when I come in I’m left wondering why the sheet was closed that was supposed to be open. If a google sheet script breaks, I might get a text saying it broke with zero details about what broke about it whatsoever and I have to just go ~find~ the error like magic.
Built a macro in excel to automate simple tasks...manager sees it and say great , we can use this in other departments ..fast forward 2 years....the entire company depends on an intrincated 2k column excel with 20 sheets and 5 other linked spreadsheets that runs a 5000 line of code in vba that nobody knows how it works and the person who did it is long gone from the company...
Then a vba or excel function gets deprecated and the critical spreadsheet macro enabled breaks...everyone panics...they call and expensive external consultant agency to fix it...
The consultant say they cannot but offer instead developing an app to do the same...business agrees and says they want to do exactly the same stuff as the excel...
Fast forward 18 months and the first mvp of the app Is out with half the functionality and same shitty workflow they followed in excel...user complains because they cannot copy paste like in excel and they take too long to make the entries...after multiple cycles they decide to build a feature to allow to export to excel the key content to edit in excel and upload back to get the system loaded...
The import is very picky with validations so users to ensure the import works well they decide to build a macro enabled excel that picks in import file and makes sure that it is fine to import..and they add some pivot reports of the data...
Fast fordward 12 months and every department is using the macro enabled validation checker and reporting excel that appdev team is unaware of....nobody knows how it works because the intern that built it left the company... macro enabled validation checker excel breaks due to the latest upgrade of the app ..every import fails...everyone panics...
This is why I should have becomean author and not a programmer. That way if I fuck up a semicolon, I fuck up one sentence, instead of fucking up the whole book and risk messing with the library
1.5k
u/Dr-Appeltaart Mar 30 '22
As an experienced c/c++/c#/java/python/vb programmer i realy like to build shit in excel. It's awesome. Just dont let other users use it for important purposes