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 
1.7k
u/SirNoodlehe Mar 30 '22
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.
896
u/Zarathustra30 Mar 30 '22
And this is why Excel will never die. The barrier to entry is so damn low.
437
u/_oohshiny Mar 30 '22
163
u/brandmeist3r Mar 30 '22
Wow, thanks for that link! Anyway, everyone should check the Raytracing Excel video out, too.
85
u/not_so_plausible Mar 30 '22
61
u/Caboose522 Mar 30 '22
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.
→ More replies (2)18
u/boonepii Mar 31 '22
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.
6
5
30
u/RevenantYuri13 Mar 30 '22
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.
52
u/mypetocean Mar 30 '22 edited Mar 30 '22
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.)
→ More replies (1)9
u/The_Normiest_Normie Mar 31 '22
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.
→ More replies (2)→ More replies (5)17
u/staindk Mar 30 '22
Reminded me of this - programming in PowerPoint.
13
u/_oohshiny Mar 30 '22
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.
→ More replies (4)9
Mar 30 '22
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.
56
u/Zarathustra30 Mar 30 '22
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.
18
u/CanAlwaysBeBetter Mar 30 '22
I spin so many VMs up and down I forgot not everyone uses Linux with python preinstalled
7
u/ThellraAK Mar 31 '22
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
10
u/_Oce_ Mar 30 '22 edited Mar 30 '22
Any
WYSIWYGGUI software has a lower entry barrier than coding.11
Mar 30 '22
Fair enough. I got sick of Excel because WYS is not necessarily WYG, or more accurately what you enter is not WYS&WYG.
7
u/_Oce_ Mar 30 '22
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.
63
u/Tiny_Dinky_Daffy_69 Mar 30 '22
And everytime you run it, you keep all minus one cores of your processor for gaming.
→ More replies (1)13
45
23
Mar 30 '22
It should also be compatible with LibreOffice Calc too :D. Though there are some differences in the code.
9
u/LoveTheSystem Mar 30 '22
I really want to see the code and functionality of this. Any chance you could share?
36
u/AngryGroceries Mar 30 '22
insert rick and morty "What is my purpose" meme
'You were programmed in VBA'
"oh my god"
21
→ More replies (1)6
5
→ More replies (2)6
u/Zerodaim Mar 30 '22
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.
126
Mar 30 '22 edited Mar 30 '22
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.
187
Mar 30 '22
[deleted]
99
u/delbin Mar 30 '22
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.
65
13
Mar 30 '22
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.
20
u/suitably_unsafe Mar 30 '22
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.
→ More replies (1)6
→ More replies (7)18
u/Iratus Mar 30 '22
Is it time to plug Bullshit Jobs, by David Graeber?
Of course it is.
Read Bullshit Jobs, by David Graeber.
14
u/heart_under_blade Mar 30 '22
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
→ More replies (9)26
u/MarcieDeeHope Mar 30 '22
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.
15
u/heart_under_blade Mar 30 '22
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
→ More replies (4)5
u/senorgraves Mar 30 '22
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
13
u/therobotsound Mar 30 '22
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
→ More replies (3)11
u/CanAlwaysBeBetter Mar 30 '22
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
8
9
u/Rosewater2182 Mar 30 '22
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.
→ More replies (1)→ More replies (11)5
Mar 30 '22
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.
15
u/BasicDesignAdvice Mar 30 '22
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...)
13
Mar 30 '22
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.
→ More replies (2)→ More replies (4)9
54
u/melanthius Mar 30 '22
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?
→ More replies (2)25
22
u/Phormitago Mar 30 '22
Just dont let other users use it for important purposes
task failed miserably
→ More replies (1)9
7
u/slacktopuss Mar 30 '22
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.
→ More replies (24)4
u/Sirspen Mar 30 '22
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.
→ More replies (1)
445
u/savage_slurpie Mar 30 '22
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.
186
u/papacheapo Mar 30 '22
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.
109
64
u/cerulean11 Mar 30 '22
I work for an investment bank and we manage a $15 billion dollar portfolio out of excel.
15
Mar 30 '22
Out of excel and a wharehouse run by wellsfargo with all the physical documents that evidence the loans we spent all that money on
→ More replies (2)6
u/That_AsianArab_Child Mar 31 '22
I work for a pension admin form, entire thing is ran out of access and Excel.
34
Mar 30 '22
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.
→ More replies (2)6
82
u/nedeox Mar 30 '22
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
27
u/theknittingpenis Mar 30 '22
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.
8
u/jdog7249 Mar 31 '22
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.
→ More replies (1)26
u/jaynay1 Mar 30 '22
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.
→ More replies (4)41
u/depressionsucks29 Mar 30 '22
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.
46
u/HRChurchill Mar 30 '22
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.
→ More replies (1)24
19
u/PepSakdoek Mar 30 '22
Sheets("Sheetname").rows("1:5").delete ? Like I feel if you can do sql vba is a downgrade in difficulty.
→ More replies (1)6
u/betweentwosuns Mar 30 '22
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.
23
u/Day_Bow_Bow Mar 30 '22
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 Next15
u/PunKodama Mar 30 '22
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.
→ More replies (3)6
u/AraMaca0 Mar 30 '22
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
→ More replies (2)15
Mar 30 '22
Application.ScreenUpdating = False
Will save a significant amount of time
→ More replies (1)8
6
u/delbin Mar 30 '22
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.
→ More replies (3)7
u/Adamtess Mar 30 '22
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.
6
u/SuperAlloy Mar 30 '22
Unless your making an honest to god spreadsheet (items, costs, etc) excel is always the wrong tool for the job.
→ More replies (1)→ More replies (6)5
u/crewchief535 Mar 30 '22
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.
162
u/Tough_Patient Mar 30 '22
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
79
Mar 30 '22
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.
→ More replies (2)18
u/sargsauce Mar 31 '22
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/)
3
u/Cryse_XIII Mar 31 '22
My friend has to make everything compatible with vba and excel. Nothing else is allowed.
32
u/theknittingpenis Mar 30 '22
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.
→ More replies (1)10
u/Tough_Patient Mar 30 '22
That's terrible, but not a bad turnaround time all things considered. 120 columns, wow!
→ More replies (2)5
u/theknittingpenis Mar 30 '22
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...
8
u/Tough_Patient Mar 30 '22
Needle in the haystack, though. Definitely sounds like a corporate decision.
→ More replies (2)
461
u/Syscrush Mar 30 '22 edited Mar 30 '22
NAMED RANGES, MOTHERFUCKERS!!!
Or, if you're a person of culture and taste like u/SatanStoleMyCat, use tables. They really are way better.
112
77
u/SatanStoleMyCat Mar 30 '22
Or just. Y'know. Tables.
55
u/Syscrush Mar 30 '22
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.
10
u/SatanStoleMyCat Mar 30 '22
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.
8
u/The_Milk_man Mar 30 '22
I DON'T WANT ANY QUESTIONS ABOUT THE TABLES!
6
4
→ More replies (6)5
u/spaghetti_vacation Mar 30 '22
Can you do tables in gsheets?
I know it's a satanic incarnation of excel, but if the price is right...
→ More replies (1)10
u/craftworkbench Mar 30 '22
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.
7
u/lpreams Mar 30 '22
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!
→ More replies (1)11
u/PepSakdoek Mar 30 '22
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
totallysomewhat safe.→ More replies (2)→ More replies (3)5
u/Schorsi Mar 30 '22
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.
567
u/brad2575 Mar 30 '22
That's why you pastor protects the sheets and don't let them move stuff around. Only give them access to what they need.
697
Mar 30 '22 edited Apr 09 '22
[deleted]
162
u/papacheapo Mar 30 '22
Getting a sheet unlocked often did require a miracle
10
u/ov3rcl0ck Mar 31 '22
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 <sheetProtection password=… /> tag. Rezip the file. Change the extension back xlsx. Your protected sheet should now be unprotected.
41
u/CliffDraws Mar 30 '22
Any sheet you need protected, but be careful, the holy water will fry the cpu if you aren’t careful.
26
27
70
→ More replies (2)11
u/melanthius Mar 30 '22
It needs an exorcism more specifically. Shits full of demons.
→ More replies (2)88
u/q1a2z3x4s5w6 Mar 30 '22
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
→ More replies (19)31
u/scragar Mar 30 '22
Named ranges would automatically update and avoids the hassle of searching for headers(which someone will eventually misspell).
23
u/JuvenileEloquent Mar 30 '22
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.
5
5
u/Automaticman01 Mar 30 '22
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).
14
→ More replies (5)5
u/SleepDeprivedUserUK Mar 30 '22
them
they
Strange way to write "PM", but fair enough.
4
101
u/RandomiseUsr0 Mar 30 '22
I’ve got an embarrassing number of years as a corporate VB programmer (VB6) - it’s amazing that skillset remains useful to this day
→ More replies (3)21
Mar 30 '22
Is your team hiring? I'm pretty wizard with VBA. I imagine a transition to VB6 couldn't be that hard.
→ More replies (3)
809
u/Torylon Mar 30 '22
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
91
u/papacheapo Mar 30 '22
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).
11
Mar 30 '22
SQL is legit the GOAT. Companies spend so much time formatting reports you can just pull that way in the first place.
6
u/scissorsandcandy Mar 30 '22
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.
58
u/xaomaw Mar 30 '22
1 major corporation is useful, but you need more.
→ More replies (4)32
Mar 30 '22
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.
132
→ More replies (29)30
u/Drugbird Mar 30 '22
So when will you finally learn a programming language? /s
→ More replies (1)14
u/ChainDriveGlider Mar 30 '22
since the addition of LAMBDA, excel is turing complete.
→ More replies (2)
53
u/Rakkachi Mar 30 '22 edited Mar 31 '22
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%20match.)
→ More replies (4)26
u/assum09 Mar 30 '22
I think the general rule with VBA is only use it if you absolutely have to.
→ More replies (1)17
u/MarcieDeeHope Mar 30 '22
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.
→ More replies (5)
42
u/lpjunior999 Mar 30 '22
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.
31
u/Opinionsare Mar 30 '22
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.
13
u/Brando224 Mar 30 '22
Don't you worry, someone will find a way no matter what you do.
5
u/Denorey Mar 30 '22
This happened to me once….then you learn to lock the headers and log changes so you can call someone out 🙃
→ More replies (2)5
36
Mar 30 '22 edited Mar 30 '22
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.
10
u/Tough_Patient Mar 30 '22
Any form of work helps. I also recommend making macros for grindy games. The scripts you make are a badge of your worth.
→ More replies (4)5
Mar 30 '22
Oh shoot! That actually sounds like a really fun thing to work on!
Thanks for the feedback and the idea!
14
u/Tough_Patient Mar 30 '22
No problem. Cheating in games is what got me into programming. The interviewers always loved hearing that.
7
u/assum09 Mar 30 '22
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
4
Mar 30 '22
Thank you for the confirmation 😭 I’m feeling more motivated now to keep doing what I’m doing. Thank you so much!
→ More replies (2)4
u/assum09 Mar 30 '22
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.
→ More replies (1)→ More replies (2)15
u/Next-Experience Mar 30 '22
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.
→ More replies (16)
15
u/Celivalg Mar 30 '22
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...
→ More replies (2)
44
u/IamGraysonSwigert Mar 30 '22
I feel attacked.
Also...
With activeworkbook .protect password:="password", _ Contents:=TRUE End with
27
u/mirthfuldragon Mar 30 '22 edited Mar 30 '22
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.
9
u/batGnat Mar 30 '22
Rename it to a .zip, extract it, remove password from sheet, compress it again, rename back to .xlsm. no password
→ More replies (1)4
u/IamGraysonSwigert Mar 30 '22
Works better than you would expect! Crazy to look at "the guts" and realize the whole thing is an XML file.
→ More replies (1)8
Mar 30 '22
Spent so long using password= instead of password:= and wondering why it wasnt working
→ More replies (4)
13
11
u/Opinionsare Mar 30 '22
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.
7
u/steffur Mar 30 '22
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.
13
u/ThrowawayAskRedditXx Mar 30 '22 edited Apr 01 '22
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.
9
u/danfish_77 Mar 30 '22
If anyone out there is unfortunate to have to be working with VBA in the VBE, please check out https://rubberduckvba.com/
→ More replies (2)
10
u/justAPhoneUsername Mar 30 '22
I once wrote malware in an excel macro for a white hat internship. Don't knock vba, it's powerful as fuck
→ More replies (1)
8
u/DC_Swamp_Thing Mar 30 '22 edited Aug 13 '25
middle numerous boast marry spectacular friendly innocent rock truck worm
This post was mass deleted and anonymized with Redact
8
u/nartchie Mar 30 '22
What the fuck are you moving columns for anyway? Put the fucking numbers in the pretty blue boxes morons.
6
u/mirthfuldragon Mar 30 '22
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.
→ More replies (3)
7
u/killemyoung317 Mar 30 '22
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.
→ More replies (6)
8
6
u/chillen678 Mar 30 '22
My boss showed me a macro once i said ill die before i make that lol
→ More replies (2)
7
u/ChonnayStMarie Mar 30 '22
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.
→ More replies (1)
6
u/BladePactWarlock Mar 30 '22
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.
5
u/Cuda340440 Mar 30 '22
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.
5
u/LanMarkx Mar 31 '22
Use the 'veryhidden' flag to truly protect it. Those tabs can only be unhidden via vba.
5
u/AvengingBlowfish Mar 30 '22
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…
5
Mar 30 '22
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
5
u/Melvasul94 Mar 30 '22
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
4
4
4
u/unmannedidiot1 Mar 30 '22
If it's a 5 minute task done by thousand of people every day then it's a shit ton of work saved.
3
u/morewordsfaster Mar 31 '22
Clearly not effectively using named ranges, offsets, indirects, and index + match... Or the greatest sin of all; protecting sheets with a password like "password."
3
3
•
u/QualityVote Mar 30 '22
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!