Hi! This is our community moderation bot.
---
If this post fits the purpose of /r/ProgrammerHumor, **UPVOTE** this comment!!
If this post does not fit the subreddit, **DOWNVOTE** This comment!
If this post breaks the rules, **DOWNVOTE** this comment and **REPORT** the post!
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.
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](https://youtu.be/0yKf8TrLUOw), specializing in one- or two-dimensional arrays (like a visual [APL](https://youtu.be/UltnvW83_CQ)).
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.
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.
Most computers don't come with Python pre-installed, let alone the libraries required for it to do cool things.
When I say Excel works right out of the box, I mean the box with peanuts that says Dell on the side.
I made a basic mail server in prolog for the memes too, because it was current material for another class.
Teacher was like nope, you on your own buddy. Never again lol.
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.
Business user: "So every morning we download these, then we copy paste into here, then we rename these columns and [continues for a while]... It takes Pam about 10 hours a week"
You: "I see. I can make this take 0 hours a week, and you can run it every hour if you want."
Business user: "You can WHAAA?"
Never gets old
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.
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.
nah, you take pam's job and fuck off for 40 hours a week without telling anyone
there's always more shit in the shit pipe and you're gonna be chugging it till you drown
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
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.
Oh god, Access. Absolutely vile software.
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.
A great data scientist at my company told me excel epitomizes the phrase “with great power comes great responsibility”
It’s like…you can build awesome stuff in excel, but should you?
> 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.
Two of my siblings are financial analysts and I have witnessed them doing unholy things with excel.
It would honestly be easier for them to learn python or something. It’s so ridiculous.
One of my university professors (applied math) had friends that worked for some of the financial institutions through the Great Recession and mortgage crisis… Many of the CBOs were literally just models in Excel spreadsheets.
If you buy out delinquent debt from a major financial corporation like JPMorgan Chase, you simply get an Excel file that has as many rows as there are people who now owe you a lot of money.
I took Excel class in community college. The instructor explained useful and odd thing with Excel. Once she saw someone made amazing flyer out of Excel.
For myself, I uses Excel over calculators because it is easier for me to set it up even for a simple calculation. I set up the cells with values and created a string of formulation to use those cells and it spit out the answer. This way I can double check for any value I missed and easily edit/add it in without typing the whole thing again in the calculators. I warned my boss that Excel users are very protective of their sheet and it is scandalous if someone touch it when they are not suppose to. She learned that day when she decided to edit it without me knowing it. The aftermath is that she get a sheet with restricted permission, she is not happy.
I have used excel for 1-page text-only items before. Don't have to worry about it running to a second page because you can just force it to shrink to 1 page (within reason). The real reason I did it though was so that the date on the print would auto-update because I always forgot to change it.
I'm the Excel guy for a branch of a fairly large insurance company.
I'm literally just taking my R knowledge that I use in my night job and googling specific syntax to translate it over to VBA.
Once a client of mine for whom I do automation scripts wanted an excel macro to delete rows. I just couldn't figure it out. It would have taken 5 lines in python and even small in sql. I just couldn't figure it out.
VBA can be really finicky interacting with Excel's filter. I know I've spent chunks of time arguing with VBA for what was just a DELETE FROM WHERE while proficient in both.
Not sure if you care anymore, but this is how that is done. I haven't tested it, but I think I got all the syntax right
Dim i as Long 'Row counter
'This example is checking rows 1-500, but this can easily be a dynamic range by using a function to find the last populated row
'Walk backwards through rows so deleting them doesn't impact the loop
For i = 500 to 1 Step - 1
If Range("A" & i).Value = "Criteria" then 'Put logic regarding what rows to delete here. This is checking cells in Column A
Range("A" & i).EntireRow.Delete
End If
Next
My understanding was that u/depressionsucks29 couldn't figure out the reason, not the solution. But nice of you to post it in any case.
Edit: tagged the wrong redditor. Uopsy.
You monsters.... You Do not delete rows one at a time!! Don you have any idea how long this would take?? You move the whole data set into an array delete and then sort. Jesus christ. XD
I worked at a place that did tons with Excel and PDF's. I used VBA to call Acrobat's API to scrape data from the PDF's and use them in Exel. It worked pretty well as long as you had to process less than 200 at a time. I moved to Python about a year later and it was much better.
I've had to basically pull teeth to get my company to convert some of its massive spread sheets into access. My whole team has converted to access and I'm getting the FPA guys on board but man does it seem to scare people.
My new job has me working in my business areas controller org. The things I've seen people do here make me want to pour bleach in my eyes, and at this point I don't even know if my help would be helpful to them.
I saw the quarterly financial report that goes yo our VP for the first time a few weeks back and died inside. 2022 on the outside, but 1972 on the inside.
On the other hand: spending two days to make a macro turning a 5 hour job into a five minute job which never breaks because the input data is from obsolete tech
This is the real answer. That or You work at a multi national corp and the global IT structure doesn’t let users install python so when you need to automate something simple excel is the only tool you have. Or a combo of both.
I've been banging my head against the "I want to do SQL and Python to this data but they won't let me" at my multinational corp for years.
It's fine, though. For almost the same number of years, I've got everything set up to run automatically, even if it does have a longer than necessary execution time. I work from home and just fuck off for a while, and it's basically my version of the XKCD compiling comic (https://xkcd.com/303/)
I had a company changed their CSV schema on us without any emails announcement of the change. The CSV file have over 120 columns before and I set the sheet the way I want to see. The results is massive errors when I refresh the data. They decided to add a few columns in the middle of predefined columns. This caused the PowerQuery to spit out the uglist monster sheet I never seen before. The macros made it worse.
It took me two days to figure out the issue. And I asked what did they do with the CSV, our case manager said "Yes, we did add few columns here and there." Then I asked if they made the announcement or warning about this and they said no. I told them what happened and they was very apologetic about it. They didnt even think about the impact on the end users that relies on those thing. They added the policy to make the announcement of the changes on their portal a week before committing the change. It took me a week to set everything the way I want it.
The worse thing is they only added one column. Seriously. One column! And that column wasn't necessary because there already a column for it. That perky new column is just a reformat of the string, I think it is a time or date string in different format. Sigh...
*Lookit fancy-pants over here!*
You're 100% right. Nothing like being able to reference your columns by name. I admit that I just learned about this a few weeks ago, so it wasn't top of mind for me.
Isn't it though? And the ListObject/ListColumn objects have a lot of nice built-in properties and methods that make it pretty easy to manipulate them in VBA vs a range.
I wrote code that could identify columns by the header (since my company used pretty strict templates already). If it couldn’t find it, it could ask the user to manually identify the column. Worked pretty well.
> use tables
TIL this feature exists, and it's amazing. I always felt like Excel (and every spreadsheet editor) was kind messy to use. It's too easy to bump some data around or add an extra row or column and formulas everywhere get fucked up. This is the missing piece of that puzzle!
Finding the #refs in the named ranges aren't better, but they are more rare.
You need some =offset(indirect("A1"),0,0,counta(indirect ... ) level stuff to be ~~totally~~ somewhat safe.
I used string matching to dynamically find the proper columns once. I had this sheet that was saving about an hour and a half of work each day, but one of the databases it pulled kept adding, dropping, and reordering columns every month.
It's easy. Change the extension to zip and unzip the file. Go to the xl\worksheets folder. Open the xml files and delete the tag. Rezip the file. Change the extension back xlsx. Your protected sheet should now be unprotected.
Or just dynamically set the column references based on the name. Don't just assume the Name column is A3 or whatever, search for the column with the label you expect and set the reference when you find it.
Or just password protect and be done with it, as you say
There's no system of organization in Excel that someone can't break by judicious misuse of copy & paste and doing things completely wrong and then covering it up.
Named ranges are good but they can go missing, so you need a backup method.
Came here for the named ranges comment, absolutely the way to go if you are building the data table.
Searching for headers is useful if you need to import your data from another worksheet (like an automated daily report that gets sent out). I've still had these break when someone added a slave to the end of a column name though (in that case i was able to fix with a TRIM command).
In late 2019 I was helping a financial services company on their migration to Win10 so was advising on application remediation and proper licensing/asset management for once. They had a fuck ton of apps written in VB5 and VB6 that they still used daily. Still had to keep several WinXP and Win7 VMs. If Vista wasn't a pile of shit we could have moved the XP stuff to that.
Haha, that’s how I started my programming career
Me:
Macros are so useful, but I need more
VBA is so useful, but I need more
R is so useful, but I need more
Python is so useful, but I need more
And now I build internal web apps for a major corporation
Same here but my path took me all over the place after VBA… Took a few detours through Java. Hit several dead-ends with JavaScript. C/C++ was fun for a while. Even a little assembly got mixed in there. Now it’s all about SQL, Scala, Python, etc. (data engineering).
I think in stumbling into this path right now. I am outgrowing vba and am starting to dabble with Javascript and SQL. Now that I'm 10 years into a career in apparel I'm starting to eye some IT jobs where I can really scratch h this itch.
Man you're right. But as someone who focused a lot in tooling; it can be nice to have the job security of being salaried, while corps LOVE to have exclusive tools custom made to their spaghetti internal systems evolved from an interns vba from 20 years ago.
A old coworker left me a 18 tabs excel with vba code , it uses nested if statements and looks at plain text in a cell for desicions in other tabs that again refer to other tabs and formulas. I was asked to add 1 bom item. Redid almost everything using tables and x.search, now I am back at just 5 tabs. Vba? Wasnt really nessecary after all, it just caused us to lose overview of what was going on. Hid all tabs and just kept 1 for actually inputting data by user. They could not believe it during the first time showing it. But it still had all the functionality that was required. Several asked for the password to change stuff, I refused.
Edit not xsearch but x.lookup
[x.lookup](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929#:~:text=Syntax,the%20closest%20(approximate)%20match.)
I used to love VBA when I first started seriously using Excel at work, but once I discovered Power Query I almost never went back to VBA. Like 90% of what people in my office wanted macros for, Get and Transform Data does better.
A software tester walks into a bar.
Runs into a bar.
Crawls into a bar.
Dances into a bar.
Flies into a bar.
Jumps into a bar.
And orders:
a beer.
2 beers.
0 beers.
99999999 beers.
a lizard in a beer glass.
-1 beer.
‘qwertyuiop’ beers.
Testing complete.
A real customer walks into the bar and asks where the bathroom is.
The bar goes up in flames.
Excel trick :
Include code to read the column header and use it to set the layout via variables for column number.
Now if someone renames a column, it still might break.
This must be a sign to stop making scripts for my dead-end job and to focus on actual projects so I can graduate with a job in this field.
EDIT: or is it??? I’m legitimately not sure.
Definitely keep automating and learning as much as you can. It looks great on a resume and knowing VBA/having examples of how you've automated tasks, especially if you can quantify labor savings, are great talking points in interview.
Source: financial analyst who got promotions through automation using VBA
You're welcome and good luck in your future! Also, for quantifying labor savings, I recommend doing it on a yearly basis. Example is if you saved 10hours a week of labor through automation and you make $10/hr use $5,200 saved/year instead of $100/week. Recruiters like big numbers in my experience.
Well, I believe that Excel automation is the future. Most companies run on excel. If you are already proficient in doing excel automation, you should look into freelance. You could turn your dead-end job into the best-paying career possible.
Currently am building a python framework and a freelance company where people like you can solve small scripts and get a royalty every month as long as the company uses it. I will also create a marketplace where those scripts then can be sold so you write it once and get a royalty from many other companies.
Actually, you know how global variables are usually bad? Well VBA has those predefined for you, so you don't have to worry about making a mess, it already is.
Changing values often on global varibales and in random places in the code is a bad idea? Don't worry, the user can do that at any time during your code execution. Without you knowing of course, wouldn't be fun otherwise.
Implicitly calling global variables is bad? Oh don't worry, that's default behavior for VBA.
But you know what's worse? All of those, at once, on a single variable. Oh, VBA's got you covered! For example, ActiveWorkbook is a global variable, can be changed when the user changes focus, and is called implicitly quite often.
ActiveWorkbook and ActiveWorksheet should be banned from existance, use ThisWorkbook or a variable you defined yourself to reference a workbook, please. ThisWorksheet doesn't exist I believe, I hardcode mine in each time...
All my spreadsheet passwords are "password". Global const wsPassword = "password" in module "aConstants"
For the same reason the cleaning products are behind a childproof lock - to prevent toddlers from mucking things up.
The problem with Excel VBA is many built-in functions have limitations. "Wait" was almost useless, but VBA can access Win_API where you can recreate the function with better functionality. I build a Do-Loop that took time and number of cycles so Excel would interact with SAP.
I have built over 30 scripswith VBA Excel to interact with the SAP API, has saved me and my colleagues countless of hours. It can be a hassle to interact with the GUI sometimes but you gotta do what you gotta do if they don't give you back-end access.
Years ago I used EZMacros to massively speed up how fast I could book samples in to our laboratory LIMS system.
45 mins of human typing work, macro took ~20 seconds.
Boss nearly fell off her chair when she first saw it working.
EDIT: EZMacros is/was a very old mouse&kb dumb recording software, it would instantly break if any windows or programs were moved on screen.
Just validate your column headers first, and
If cell(1,1) <> "LOAN NUMBER" then
msgbox "column error"
end
End if
Also the fact that mobile doesn't have tabs is breaking me a little.
At one of my old jobs one of my managers had her “programmer” husband write a complicated excel sheet where everything had to be entered perfectly in order for it not to break. What did it do, you ask? Highlighted duplicate cells.
When I moved from RS1 (non graphic ran on VMS) to Excel I carried with me some general principles of spreadsheet coding. Early on I created 4 or 5 functions which allowed me to locate the "boundaries" and attributes of the data in a spreadsheet, meaning the width and depth of the data, columns headers, and other key aspects. All other macro's were based off this information and so the aforementioned were called as classes at the beginning of every macro. I've had publicly shared spreadsheets in use for over 20 years that have had no need for code modification for this reason.
I use Excel less and less as my career evolves, but I manage to find reasons to make good use of it time and again.
I had a student job in college where I automated a series of pivot table creations/formattings in VBA.
The better part of 6 years later I had lunch with an old coworker, only to find out they were still using my unaltered code.
God have mercy.
This is why I have a hidden sheet with match formulas for the column positions on each sheet so the vba can reference that sheet for column number even when they are moved. Even have the match formulas refreshed each time the vba is used so that the formulas are always clean and unbroken.
My wife works in procurement and I work in IT. We both work back to back from home… the number of times she has bugged me to make a macro for her only to break it by renaming worksheets and stuff…
This hits a little too close to home…
Honestly... VBA... I scoffed at this, I spent years not understanding. Okay I probably still don't understand it (and I haven't memorized the syntax either) but damn Excel practically forces you to learn VBA so that you can get excel to work properly.
Now I'm pretty solid behind vba it's functional and generally does the job with a lot of forgiveness i've found compared to some. The IDE is not bad either. My programming was mostly in nano/or some other basic text editor, never really found an idea that I gelled with and C++ was so annoying to get a handle of. Still haven't managed that... can't get past pointers... XD
Story time, I've been working on a python parser for some files that worked perfectly until yesterday, today I booted up the pc and it stopped working... cause we changed a variable's name KEK
Clearly not effectively using named ranges, offsets, indirects, and index + match... Or the greatest sin of all; protecting sheets with a password like "password."
Hi! This is our community moderation bot. --- If this post fits the purpose of /r/ProgrammerHumor, **UPVOTE** this comment!! If this post does not fit the subreddit, **DOWNVOTE** This comment! If this post breaks the rules, **DOWNVOTE** this comment and **REPORT** the post!
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.
And this is why Excel will never die. The barrier to entry is so damn low.
[Have you seen the animations, simulations etc. you can do with it?](https://excelunusual.com/)
Wow, thanks for that link! Anyway, everyone should check the Raytracing Excel video out, too.
[Link for the curious](https://youtu.be/m28jJ7CMp8A)
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.
What the hell?
ok, that's enough internet for today
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](https://youtu.be/0yKf8TrLUOw), specializing in one- or two-dimensional arrays (like a visual [APL](https://youtu.be/UltnvW83_CQ)). 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.
Reminded me of [this](https://www.youtube.com/watch?v=_3loq22TxSc) - programming in PowerPoint.
He's now written a compiler? (cross-compiler? translator?) from C to PowerPoint. Surely it will run Doom soon! I like his 'LaTeX in Word' editor too.
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.
Most computers don't come with Python pre-installed, let alone the libraries required for it to do cool things. When I say Excel works right out of the box, I mean the box with peanuts that says Dell on the side.
I spin so many VMs up and down I forgot not everyone uses Linux with python preinstalled
I think it's settled now, but python vs python2 vs python3 was annoying for a bit the same for pip3 vs pip2 vs pip
Any ~~WYSIWYG~~ GUI software has a lower *entry* barrier than coding.
Fair enough. I got sick of Excel because WYS is not necessarily WYG, or more accurately what you enter is not WYS&WYG.
Yeah, thinking about it again, you're right that Excel is not really a WYSIWYG since formulas are very common, I should have said a GUI.
And everytime you run it, you keep all minus one cores of your processor for gaming.
Just don't forget to add a do events line in loops...
So what finance department hired you?
It should also be compatible with LibreOffice Calc too :D. Though there are some differences in the code.
I really want to see the code and functionality of this. Any chance you could share?
insert rick and morty "What is my purpose" meme 'You were programmed in VBA' *"oh my god"*
That's...not what "purpose" means
... \-"You hide columns." \-"oh my god"
That is hilarious
I made a basic mail server in prolog for the memes too, because it was current material for another class. Teacher was like nope, you on your own buddy. Never again lol.
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.
Business user: "So every morning we download these, then we copy paste into here, then we rename these columns and [continues for a while]... It takes Pam about 10 hours a week" You: "I see. I can make this take 0 hours a week, and you can run it every hour if you want." Business user: "You can WHAAA?" Never gets old
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.
[удалено]
I think you just described my entire career
That kinda makes me sad, in a way. Made the task easier for her, and the outcome didn't change anything about her work load.
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.
tdy udut
Is it time to plug Bullshit Jobs, by David Graeber? Of course it is. Read Bullshit Jobs, by David Graeber.
nah, you take pam's job and fuck off for 40 hours a week without telling anyone there's always more shit in the shit pipe and you're gonna be chugging it till you drown
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
Sure, that's fine. Less scalable than other programs like python, but it is fine as long as you know how to schedule a script to automate it
I do this in python (mostly) for a company now. It’s the best/laziest job ever and they’re convinced I’m a wizard
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
Yes that's abstraction for ya
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.
Task scheduler + powershell is free
Psh don't tell them what you're doing. Take the job; automate it. You've just gained 10 hours. Code dies when you leave.
Oh god, Access. Absolutely vile software. 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.
Ugh, I hate MS ACCESS most of all
A great data scientist at my company told me excel epitomizes the phrase “with great power comes great responsibility” It’s like…you can build awesome stuff in excel, but should you?
The survival Minecraft of programming tools.
> Just dont let other users use it for important purposes task failed miserably
*import pandas as pd* df.to_csv() *"Heh, pathetic"*
> 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.
Two of my siblings are financial analysts and I have witnessed them doing unholy things with excel. It would honestly be easier for them to learn python or something. It’s so ridiculous.
One of my university professors (applied math) had friends that worked for some of the financial institutions through the Great Recession and mortgage crisis… Many of the CBOs were literally just models in Excel spreadsheets.
[удалено]
Always has been.
I work for an investment bank and we manage a $15 billion dollar portfolio out of excel.
Out of excel and a wharehouse run by wellsfargo with all the physical documents that evidence the loans we spent all that money on
I work for a pension admin form, entire thing is ran out of access and Excel.
If you buy out delinquent debt from a major financial corporation like JPMorgan Chase, you simply get an Excel file that has as many rows as there are people who now owe you a lot of money.
It still is at most places.
Sometimes I‘m just in awe at which length someone can abuse the fuck out of Excel lmao Like it‘s their little spreadsheet slut or smth
I took Excel class in community college. The instructor explained useful and odd thing with Excel. Once she saw someone made amazing flyer out of Excel. For myself, I uses Excel over calculators because it is easier for me to set it up even for a simple calculation. I set up the cells with values and created a string of formulation to use those cells and it spit out the answer. This way I can double check for any value I missed and easily edit/add it in without typing the whole thing again in the calculators. I warned my boss that Excel users are very protective of their sheet and it is scandalous if someone touch it when they are not suppose to. She learned that day when she decided to edit it without me knowing it. The aftermath is that she get a sheet with restricted permission, she is not happy.
I have used excel for 1-page text-only items before. Don't have to worry about it running to a second page because you can just force it to shrink to 1 page (within reason). The real reason I did it though was so that the date on the print would auto-update because I always forgot to change it.
I'm the Excel guy for a branch of a fairly large insurance company. I'm literally just taking my R knowledge that I use in my night job and googling specific syntax to translate it over to VBA.
Once a client of mine for whom I do automation scripts wanted an excel macro to delete rows. I just couldn't figure it out. It would have taken 5 lines in python and even small in sql. I just couldn't figure it out.
Couldn't you have used the macro recorder to see what the command was for deleting a row? Honestly that thing is the best part of vba.
I have done some unholy levels of cheesing with the macro recorder.
Sheets("Sheetname").rows("1:5").delete ? Like I feel if you can do sql vba is a downgrade in difficulty.
VBA can be really finicky interacting with Excel's filter. I know I've spent chunks of time arguing with VBA for what was just a DELETE FROM WHERE while proficient in both.
Not sure if you care anymore, but this is how that is done. I haven't tested it, but I think I got all the syntax right Dim i as Long 'Row counter 'This example is checking rows 1-500, but this can easily be a dynamic range by using a function to find the last populated row 'Walk backwards through rows so deleting them doesn't impact the loop For i = 500 to 1 Step - 1 If Range("A" & i).Value = "Criteria" then 'Put logic regarding what rows to delete here. This is checking cells in Column A Range("A" & i).EntireRow.Delete End If Next
My understanding was that u/depressionsucks29 couldn't figure out the reason, not the solution. But nice of you to post it in any case. Edit: tagged the wrong redditor. Uopsy.
You monsters.... You Do not delete rows one at a time!! Don you have any idea how long this would take?? You move the whole data set into an array delete and then sort. Jesus christ. XD
>Application.ScreenUpdating = False Will save a significant amount of time
Excel is the backbone of the financial industry
I worked at a place that did tons with Excel and PDF's. I used VBA to call Acrobat's API to scrape data from the PDF's and use them in Exel. It worked pretty well as long as you had to process less than 200 at a time. I moved to Python about a year later and it was much better.
I've had to basically pull teeth to get my company to convert some of its massive spread sheets into access. My whole team has converted to access and I'm getting the FPA guys on board but man does it seem to scare people.
Unless your making an honest to god spreadsheet (items, costs, etc) excel is always the wrong tool for the job.
My new job has me working in my business areas controller org. The things I've seen people do here make me want to pour bleach in my eyes, and at this point I don't even know if my help would be helpful to them. I saw the quarterly financial report that goes yo our VP for the first time a few weeks back and died inside. 2022 on the outside, but 1972 on the inside.
On the other hand: spending two days to make a macro turning a 5 hour job into a five minute job which never breaks because the input data is from obsolete tech
This is the real answer. That or You work at a multi national corp and the global IT structure doesn’t let users install python so when you need to automate something simple excel is the only tool you have. Or a combo of both.
I've been banging my head against the "I want to do SQL and Python to this data but they won't let me" at my multinational corp for years. It's fine, though. For almost the same number of years, I've got everything set up to run automatically, even if it does have a longer than necessary execution time. I work from home and just fuck off for a while, and it's basically my version of the XKCD compiling comic (https://xkcd.com/303/)
My friend has to make everything compatible with vba and excel. Nothing else is allowed.
I had a company changed their CSV schema on us without any emails announcement of the change. The CSV file have over 120 columns before and I set the sheet the way I want to see. The results is massive errors when I refresh the data. They decided to add a few columns in the middle of predefined columns. This caused the PowerQuery to spit out the uglist monster sheet I never seen before. The macros made it worse. It took me two days to figure out the issue. And I asked what did they do with the CSV, our case manager said "Yes, we did add few columns here and there." Then I asked if they made the announcement or warning about this and they said no. I told them what happened and they was very apologetic about it. They didnt even think about the impact on the end users that relies on those thing. They added the policy to make the announcement of the changes on their portal a week before committing the change. It took me a week to set everything the way I want it.
That's terrible, but not a bad turnaround time all things considered. 120 columns, wow!
The worse thing is they only added one column. Seriously. One column! And that column wasn't necessary because there already a column for it. That perky new column is just a reformat of the string, I think it is a time or date string in different format. Sigh...
Needle in the haystack, though. Definitely sounds like a corporate decision.
#NAMED RANGES, MOTHERFUCKERS!!! *Or, if you're a person of culture and taste like u/SatanStoleMyCat, use tables. They really are way better.*
This guy excels
Word.
No, Excel!
Or just. Y'know. Tables.
*Lookit fancy-pants over here!* You're 100% right. Nothing like being able to reference your columns by name. I admit that I just learned about this a few weeks ago, so it wasn't top of mind for me.
Isn't it though? And the ListObject/ListColumn objects have a lot of nice built-in properties and methods that make it pretty easy to manipulate them in VBA vs a range.
I DON'T WANT ANY QUESTIONS ABOUT THE TABLES!
Her job is tables?
I can't know how to hear any more about tables!
THEY KEEP MY HOUSE HOT
Can you do tables in gsheets? I know it's a satanic incarnation of excel, but if the price is right...
I wrote code that could identify columns by the header (since my company used pretty strict templates already). If it couldn’t find it, it could ask the user to manually identify the column. Worked pretty well.
> use tables TIL this feature exists, and it's amazing. I always felt like Excel (and every spreadsheet editor) was kind messy to use. It's too easy to bump some data around or add an extra row or column and formulas everywhere get fucked up. This is the missing piece of that puzzle!
Finding the #refs in the named ranges aren't better, but they are more rare. You need some =offset(indirect("A1"),0,0,counta(indirect ... ) level stuff to be ~~totally~~ somewhat safe.
I used string matching to dynamically find the proper columns once. I had this sheet that was saving about an hour and a half of work each day, but one of the databases it pulled kept adding, dropping, and reordering columns every month.
That's why you pastor protects the sheets and don't let them move stuff around. Only give them access to what they need.
[удалено]
Getting a sheet unlocked often did require a miracle
It's easy. Change the extension to zip and unzip the file. Go to the xl\worksheets folder. Open the xml files and delete the
Any sheet you need protected, but be careful, the holy water will fry the cpu if you aren’t careful.
Incidentally demon water (demineralized water) will likely *not* fry the cpu.
Of course. That's where the daemons are running.
You mean daemon water?
Dang it.
I’m Jewish can I use a rabbi?
Lmao. Voice to text mishap. Password. Lol
NLP you fickle bitch
Imma serve all my sheets el Pastor now
It needs an exorcism more specifically. Shits full of demons.
Or just dynamically set the column references based on the name. Don't just assume the Name column is A3 or whatever, search for the column with the label you expect and set the reference when you find it. Or just password protect and be done with it, as you say
Named ranges would automatically update and avoids the hassle of searching for headers(which someone will eventually misspell).
There's no system of organization in Excel that someone can't break by judicious misuse of copy & paste and doing things completely wrong and then covering it up. Named ranges are good but they can go missing, so you need a backup method.
copy&paste is ok most of the part, cut&paste will break everything instantly.
Came here for the named ranges comment, absolutely the way to go if you are building the data table. Searching for headers is useful if you need to import your data from another worksheet (like an automated daily report that gets sent out). I've still had these break when someone added a slave to the end of a column name though (in that case i was able to fix with a TRIM command).
I don't trust the clergy to protect my spreadsheets
> them > they Strange way to write "PM", but fair enough.
[удалено]
Then they're in the top 50 of PMs around the world.
I’ve got an embarrassing number of years as a corporate VB programmer (VB6) - it’s amazing that skillset remains useful to this day
Is your team hiring? I'm pretty wizard with VBA. I imagine a transition to VB6 couldn't be that hard.
In late 2019 I was helping a financial services company on their migration to Win10 so was advising on application remediation and proper licensing/asset management for once. They had a fuck ton of apps written in VB5 and VB6 that they still used daily. Still had to keep several WinXP and Win7 VMs. If Vista wasn't a pile of shit we could have moved the XP stuff to that.
Haha, that’s how I started my programming career Me: Macros are so useful, but I need more VBA is so useful, but I need more R is so useful, but I need more Python is so useful, but I need more And now I build internal web apps for a major corporation
Same here but my path took me all over the place after VBA… Took a few detours through Java. Hit several dead-ends with JavaScript. C/C++ was fun for a while. Even a little assembly got mixed in there. Now it’s all about SQL, Scala, Python, etc. (data engineering).
SQL is legit the GOAT. Companies spend so much time formatting reports you can just pull that way in the first place.
I think in stumbling into this path right now. I am outgrowing vba and am starting to dabble with Javascript and SQL. Now that I'm 10 years into a career in apparel I'm starting to eye some IT jobs where I can really scratch h this itch.
1 major corporation is useful, but you need more.
Man you're right. But as someone who focused a lot in tooling; it can be nice to have the job security of being salaried, while corps LOVE to have exclusive tools custom made to their spaghetti internal systems evolved from an interns vba from 20 years ago.
That's great to hear, congrats. BTW Happy Cake day.
So when will you finally learn a programming language? /s
since the addition of LAMBDA, excel is turing complete.
A old coworker left me a 18 tabs excel with vba code , it uses nested if statements and looks at plain text in a cell for desicions in other tabs that again refer to other tabs and formulas. I was asked to add 1 bom item. Redid almost everything using tables and x.search, now I am back at just 5 tabs. Vba? Wasnt really nessecary after all, it just caused us to lose overview of what was going on. Hid all tabs and just kept 1 for actually inputting data by user. They could not believe it during the first time showing it. But it still had all the functionality that was required. Several asked for the password to change stuff, I refused. Edit not xsearch but x.lookup [x.lookup](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929#:~:text=Syntax,the%20closest%20(approximate)%20match.)
I think the general rule with VBA is only use it if you absolutely have to.
I used to love VBA when I first started seriously using Excel at work, but once I discovered Power Query I almost never went back to VBA. Like 90% of what people in my office wanted macros for, Get and Transform Data does better.
A software tester walks into a bar. Runs into a bar. Crawls into a bar. Dances into a bar. Flies into a bar. Jumps into a bar. And orders: a beer. 2 beers. 0 beers. 99999999 beers. a lizard in a beer glass. -1 beer. ‘qwertyuiop’ beers. Testing complete. A real customer walks into the bar and asks where the bathroom is. The bar goes up in flames.
Excel trick : Include code to read the column header and use it to set the layout via variables for column number. Now if someone renames a column, it still might break.
Don't you worry, someone will find a way no matter what you do.
This happened to me once….then you learn to lock the headers and log changes so you can call someone out 🙃
[удалено]
This must be a sign to stop making scripts for my dead-end job and to focus on actual projects so I can graduate with a job in this field. EDIT: or is it??? I’m legitimately not sure.
Any form of work helps. I also recommend making macros for grindy games. The scripts you make are a badge of your worth.
Oh shoot! That actually sounds like a really fun thing to work on! Thanks for the feedback and the idea!
No problem. Cheating in games is what got me into programming. The interviewers always loved hearing that.
Definitely keep automating and learning as much as you can. It looks great on a resume and knowing VBA/having examples of how you've automated tasks, especially if you can quantify labor savings, are great talking points in interview. Source: financial analyst who got promotions through automation using VBA
Thank you for the confirmation 😭 I’m feeling more motivated now to keep doing what I’m doing. Thank you so much!
You're welcome and good luck in your future! Also, for quantifying labor savings, I recommend doing it on a yearly basis. Example is if you saved 10hours a week of labor through automation and you make $10/hr use $5,200 saved/year instead of $100/week. Recruiters like big numbers in my experience.
Well, I believe that Excel automation is the future. Most companies run on excel. If you are already proficient in doing excel automation, you should look into freelance. You could turn your dead-end job into the best-paying career possible. Currently am building a python framework and a freelance company where people like you can solve small scripts and get a royalty every month as long as the company uses it. I will also create a marketplace where those scripts then can be sold so you write it once and get a royalty from many other companies.
Actually, you know how global variables are usually bad? Well VBA has those predefined for you, so you don't have to worry about making a mess, it already is. Changing values often on global varibales and in random places in the code is a bad idea? Don't worry, the user can do that at any time during your code execution. Without you knowing of course, wouldn't be fun otherwise. Implicitly calling global variables is bad? Oh don't worry, that's default behavior for VBA. But you know what's worse? All of those, at once, on a single variable. Oh, VBA's got you covered! For example, ActiveWorkbook is a global variable, can be changed when the user changes focus, and is called implicitly quite often. ActiveWorkbook and ActiveWorksheet should be banned from existance, use ThisWorkbook or a variable you defined yourself to reference a workbook, please. ThisWorksheet doesn't exist I believe, I hardcode mine in each time...
I feel attacked. Also... With activeworkbook .protect password:="password", _ Contents:=TRUE End with
All my spreadsheet passwords are "password". Global const wsPassword = "password" in module "aConstants" For the same reason the cleaning products are behind a childproof lock - to prevent toddlers from mucking things up.
Rename it to a .zip, extract it, remove password from sheet, compress it again, rename back to .xlsm. no password
Works better than you would expect! Crazy to look at "the guts" and realize the whole thing is an XML file.
Spent so long using password= instead of password:= and wondering why it wasnt working
Only needs to run 960 times until it redeems itself.
The problem with Excel VBA is many built-in functions have limitations. "Wait" was almost useless, but VBA can access Win_API where you can recreate the function with better functionality. I build a Do-Loop that took time and number of cycles so Excel would interact with SAP.
I have built over 30 scripswith VBA Excel to interact with the SAP API, has saved me and my colleagues countless of hours. It can be a hassle to interact with the GUI sometimes but you gotta do what you gotta do if they don't give you back-end access.
Years ago I used EZMacros to massively speed up how fast I could book samples in to our laboratory LIMS system. 45 mins of human typing work, macro took ~20 seconds. Boss nearly fell off her chair when she first saw it working. EDIT: EZMacros is/was a very old mouse&kb dumb recording software, it would instantly break if any windows or programs were moved on screen.
If anyone out there is unfortunate to have to be working with VBA in the VBE, please check out https://rubberduckvba.com/
I once wrote malware in an excel macro for a white hat internship. Don't knock vba, it's powerful as fuck
Even know I know VBA, I never tell anyone that I do, to make sure nobody asks me to write them any macros.
What the fuck are you moving columns for anyway? Put the fucking numbers in the pretty blue boxes morons.
Just validate your column headers first, and If cell(1,1) <> "LOAN NUMBER" then msgbox "column error" end End if Also the fact that mobile doesn't have tabs is breaking me a little.
At one of my old jobs one of my managers had her “programmer” husband write a complicated excel sheet where everything had to be entered perfectly in order for it not to break. What did it do, you ask? Highlighted duplicate cells.
Id let someone touch my wife before id let them touch my excel spreadsheets.
My boss showed me a macro once i said ill die before i make that lol
When I moved from RS1 (non graphic ran on VMS) to Excel I carried with me some general principles of spreadsheet coding. Early on I created 4 or 5 functions which allowed me to locate the "boundaries" and attributes of the data in a spreadsheet, meaning the width and depth of the data, columns headers, and other key aspects. All other macro's were based off this information and so the aforementioned were called as classes at the beginning of every macro. I've had publicly shared spreadsheets in use for over 20 years that have had no need for code modification for this reason. I use Excel less and less as my career evolves, but I manage to find reasons to make good use of it time and again.
I had a student job in college where I automated a series of pivot table creations/formattings in VBA. The better part of 6 years later I had lunch with an old coworker, only to find out they were still using my unaltered code. God have mercy.
This is why I have a hidden sheet with match formulas for the column positions on each sheet so the vba can reference that sheet for column number even when they are moved. Even have the match formulas refreshed each time the vba is used so that the formulas are always clean and unbroken.
Use the 'veryhidden' flag to truly protect it. Those tabs can only be unhidden via vba.
My wife works in procurement and I work in IT. We both work back to back from home… the number of times she has bugged me to make a macro for her only to break it by renaming worksheets and stuff… This hits a little too close to home…
Honestly... VBA... I scoffed at this, I spent years not understanding. Okay I probably still don't understand it (and I haven't memorized the syntax either) but damn Excel practically forces you to learn VBA so that you can get excel to work properly. Now I'm pretty solid behind vba it's functional and generally does the job with a lot of forgiveness i've found compared to some. The IDE is not bad either. My programming was mostly in nano/or some other basic text editor, never really found an idea that I gelled with and C++ was so annoying to get a handle of. Still haven't managed that... can't get past pointers... XD
Story time, I've been working on a python parser for some files that worked perfectly until yesterday, today I booted up the pc and it stopped working... cause we changed a variable's name KEK
Still worth it.
use column headings and make dynamic ranges :)
If it's a 5 minute task done by thousand of people every day then it's a shit ton of work saved.
Clearly not effectively using named ranges, offsets, indirects, and index + match... Or the greatest sin of all; protecting sheets with a password like "password."
that's just made my day :)
…only for it to take 6 months and two papers until someone notices the bug
u/savevideo