Conflicted on use of VBA
Our take
I work in tax and am creating a new tax pack at my new job - I had been considering using VBA initially but due to all the people recommending avoiding it I decided not to and that turned out quite well since I learnt a lot about Power Query and its does a great job for most of what I want it to do!
That said, there are some aspects that I wanted to build into the tack pack that I could only use VBA for - mainly automatically generating certain tables with unique values from source data model tables and refreshing the target tables without impacting the data I've already placed in existing rows. Basically I have tried to really limit my use of VBA to nothing more than simple repetitive tasks which:
I can easily build a check in to confirm work well; and
In the event the code does not work I am easily able to replicate - although of course at a slower pace
I say all this to say I feel like there is a great benefit in learning basic VBA even at this point in time, I can see the sense in not delving too deep into it and writing a really long complex macro that will be a nightmare to maintain but I think writing a couple of shorts macros to do certain easy tasks and limit/eliminate the element of human error can really have a big ROI!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Do you use VBA regularly or have you moved entirely to Power Query and formulas?I have been using Excel for years and VBA was always my go to for automation. Lately I have been seeing more people say they barely touch VBA anymore because Power Query and dynamic arrays cover most of what they need. I still use VBA for things like automating reports across multiple files or generating custom email bodies from data. But I am wondering if I am behind the curve. For those of you who work in data heavy roles, what is your current workflow? Do you still use VBA regularly or have you replaced it with other tools? Curious if I should be spending more time learning Power Query and the newer formula features instead of maintaining my VBA skills. submitted by /u/ProfessionalOk4935 [link] [comments]
- is vba, macros easy, for someone who came from sql and python, or are they unrelated?i am a fresh graduate who was originally aiming for data science or llm engineering, but the market require experienced people for such roles, so i am transitioning to data analysis, i i aimed for learning excel and powerbi, and so far learned text and list formulas, filtering, sorting and all these stuff, you would see what i learn if you see my posts in this sub from my profile, but now i think the next step would be look up, vba and macros, would they take time, or can i get a grip of them in a couple weeks? i still need to learn powerbi and dax so i am kinda rushing it but python wise i think i am solid and have more than enough knowledge for the tools and libraries, since it was my main thing for 2 years submitted by /u/Difficult_Warning126 [link] [comments]
- how did you improve your workplace's legacy vba macros?I recently transitioned to a non-clinical role in a public health care system. part of the on-boarding was a 12 page, 20 step tutorial on how to 'do the macros'. The workflow simplified is: - Get source data from EHR/BI - Open the excel online (microsoft 365) "Daily Review" workbook in the desktop ms excel. (hopes and prayers it doesn't crash) - copy data (columns of patient ID, demographics, medications... you get the idea) from EHR, paste into this Daily Review - run macro (click a button) which cleans, filters, applies conditional formatting i think - save - go back to excel online and resume editing there. The VBA code was created (not sure if it was written coz it has no documentation) by a colleague who is on extended mat leave. I can see a lot of 'modules'. Can't tell which is active. There are probably lots of historical decisions. The daily review file with its many many sheets is saved in multiple locations in case newcomers like me or others break it by accident. I am told we can't change anything like move a column closer to the beginning coz well we can't. I don't know VBA but could probably figure it out if I watch a tutorial on it. I am linux user and know basics of C, python and make good use of my claude code with the pro subscription but never really worked with spreadsheets. I am wondering if anyone was in a similar situation and how you managed it. Is moving to office scripts (typescript) a viable alternative? Any other life improving tips would be appreciate it. Or maybe I should just give up and focus the energy elsewhere? submitted by /u/Neat-Badger-5939 [link] [comments]
- Quality of Life Macros Ideas - AccountingI am shamelessly looking for quality of life macros to work into a custom add in for my day to day accounting job. Items I have worked/am working on include: a) bulk hide and unhide tabs b) list visible sheets (returns array of all visible sheets, with index number and link to said sheet; effectively builds a TOC) c) List External Workbooks (searches for formulas containing brackets [ & ], returns all externally linked formulas. d) Print FS to PDF button. Not part of the add in; built into the workbooks. Some financials have in excess of 90 tabs with 5+ sets of financials in the same workbook. Having a button to print Fund A or Fund B has been helpful. Not as dynamic as I would like it but 80% solution today is better than a 100% solution never. My question is, what quality of life Macro or other automations has made your life easier? Edit - because I know I'm going to see it a lot - Power Query 100% is my go to tool. Sadly I don't get to use it as often as I would like. A lot of the data being ingested is "report ready" and not tabular which is frustrating. submitted by /u/Traditional-Wash-809 [link] [comments]