Excel Macros

[Nish]

Ok give mind.

Out of interest and mostly necessity, this idiot is trying to write macros for work. I'm mostly teaching myself with help from search and AI.

Suggestions and best practices?

[Carp]

No help, just some encouragement....

[Painthappy]

I'm pretty adept... Guess it all depends on what you're trying to do in the end though

[mercpb]

It REALLY depends on what your goal is.

Most of what I know comes from 20yrs of....
- exploring/modifying code written by a professional, that i inherited
- using the record feature in developer, then reviewing/cleaning/fixing it
- Boatload of googling/forums
- and, now, AI. Which is a turbo boost for someone with baseline skills, but tougher to utilize effectively if just starting.

People in my company think I'm a wizard.
I've worked with the wizards. So I'm aware I'm a hack.
Everybody is somebody else's wizard I guess.

Be aware nobody writes code from scratch.

If you're at square one, and this is both complex and high business impact, hire it out. Then learn how to maintain and modify the resulting product. It's not expensive to hire out VBA code

[Nish]

I am at best a hack, and the office archimage.

[Nish]

Mostly automating the same filters that are done constantly, moving data around so that it is in the correct order and format needed, lookups from other workbooks and conditional formatting to identify red flags.

I work in billing for higher ed so things like making sure we are billing for the class the student got the voucher for, and that the student didn't drop the class are big deals.

[mercpb]

I'd start with developer/record feature in this case --- map out the action/procedure in your head if you were doing it "by hand", break it into steps if it's long, record each step, and review resulting code after you recorded it. You'll quickly start to understand the syntax/mechanics of code as relates to user actions/commands you're already well versed in. It's a rudimentary but effective starting point.

Once you get your bearings, AI can show you how to write sexier shorter code snippets and help expand your vocabulary. Sky's teh limit from there.

[Cunha]

I was similar to many of you. I was mostly good at making my calculations easy to explain to decision makers. Therefore the spells I cast had to be transparent ones with no 'black box' macro calculations happening behind the scenes.

I enjoyed making good tools. I love excel.

Things that take too long like good data entry and formatting for your base tables is necessary anyways. All of those things need to be checked so they might as well be entered correctly.

But everyone's purposes are (not) the same. There are very good user tips on an intentionally nerdy looking guys Instagram page and probably YouTube that shows a lot of good shortcuts.

Good luck!

[BLachance75]

I do a lot of Googling when working with macros.

Most of my knowledge has come from working with sheets that other people wrote the macros on. I always thought that the guy I inherited the sheets from was a genius until I started to learn more about macros and then I figured out he was a hack much like I am. All of his code was done through the record feature and he never cleaned it up so I really struggled at first. I've been cleaning it up and adding descriptions to help the next person that ends up with the sheet.

[mercpb]

doing the lords work

[Grendel]

I hate Macros; there are way better things you can do than Macros. Get your data out of spreadsheets into proper databases and use appropriate tools I feel like I have been taking a step back 10-15 years with my new company because they rely heavily on macros, even when there are better options available. I absolutely hate reverse engineering macros someone else wrote that are broken, and the author no longer works for the company........

[Grendel]

Understood, but you should be able to write the queries to handle all of the macro functions for any downstream reporting, and it is way easier to learn and execute in SQL [or R, Cloudara, Snowflake...etc.] than use MACROS IMHO. Trust me, I had to do the Macro thing, but the moment I could stop, I did. Now, my new employer has me reverse-engineering someone else's abortions of VBA code.

[Nish]

I could do some of it from the database directly but all outputs are barely formatted plain text that can't be understood unless you know Banner and the codes we use. Because, capitalism, being able to format those outputs is another whole modual of the database that would need to be purchased.The reporting software we have helps a lot, but ITS are the only ones that can write new reports, or modify existing ones. They are understaffed and currently in crunch time working on transitioning the database to a SaaS version.

So this is the option I've got for now. Lots of notes about what each part is doing and any variable I think is subject to change is being made a reference cell in the macro file. I'm not going to say they are great but the are already saving time

[Siress]

There are free options. Anyone can spool up an SQL database for free (or Maria DB if you go the extra mile). Then there's free software like DBeaver for management. I'm not saying you need to convert an existing database, but I am saying it sounds like a better idea for you to create your own database rather than relying on Excel.

[Nish]

Honestly, so far I'm bouncing back and forth between pulling my hair out because I can't get it to work and being super excited that I finally got it to work.

It's been fun.

[Cunha]

Yeah its a special joy, achieving the desired outcome with a spreadsheet. Whatever you are using it to do. Anything you can imagine it doing with numbers, it can do. It is the similar with data analysis, like IBM SPSS.

[BLachance75]

I like to use macros to improve accuracy and save time. I’ll spend a few days fumbling around do that the people working with the sheets can save time everyday or to help eliminate errors.

Someday I’ll get with one of our HQ guys and get a lesson on one of our applications that pulls data from multiple databases. It’s a matter of finding the right person and scheduling time with them. The good guys never seem to have free time

[vijil]

https://excel-esports.com/

[Siress]

Mostly automating the same filters that are done constantly, moving data around so that it is in the correct order and format needed, lookups from other workbooks and conditional formatting to identify red flags.

I work in billing for higher ed so things like making sure we are billing for the class the student got the voucher for, and that the student didn't drop the class are big deals.

if you're (edit) NOT manually entering any associated data, or can do it in the original source for the data, then you might actually have easier way out here; Queries. It's basically Excel's integration of a true database architecture. With that comes DB-style coding instead of the typical excel formulas, but they have a lot of built in features that help ease the burden. How you start out doing this depends on where the raw source data is, and how it's being stored/updated. The general approach, though, is to go to Data > "Get & transform data" > {choose the path that makes sense for your source data} and then follow the guides. It might take a few hours/days to get the hang of it depending on the complexity of what you're doing. Bonus points for this approach as you don't need the macro-enabled workbook, which has become much more difficult to use due to the overwhelming cybersecurity concerns it poses.

[Nish]

Thanks for the idea. I'll take a look at that. Our existing database is huge and pretty robust but the ability to report from it is limited.
Last week I wrote a macro to to take a report that I had It build a while back and add conditional formatting to help identify the discrepancies between the class that a student was taking, and the one that they got approved to be paid but the military, then format it appropriately for invoicing depending on the branch.

Nothing magical but these are the kinds of things I'm trying to do to cut down on mistakes and time consuming manual stuff that needs to be done frequently.

[russonti]

I have been working my way up a software company from the bottom for over 8 years and I have written and fixed a ton of code for many languages that i don't actually know and i'm not even a developer. I never went to school, nor have i been able to finish any courses. I don't learn that way. I learn by hands on solving problems or creating something i need. I started off by doing what you're doing by creating small code for daily or repetitive tasks to help efficiency. I did that for so many years and the tools i wrote got better and bigger. Then I got tired of trying to find things or losing tools i wrote so i put it all in a github repo. Shortly after that my company took over my internal tools repo and now uses it for the whole company to use and share everyones internal tools and code. To this day i am still googling or referencing code websites to help me write these. I have spent this past year trying to use AI and just be careful. One you're not learning as much. Two, AI can be wrong, a lot. And has a memory of a goldfish so it can burn ya and waste your time. I spend more time trying to keep AI on track and writing what i want it to do that in the end sometimes it would have been faster for me to struggle to google and trial and error on my own. Just keep doing what you're doing and you'll be amazed how much you've learned and wrote year after year. Also get a github to store it all. i lost soooo much code and time over the years.