How long do you think it would take the average excel user to master VBA?
Master? A few years. Become proficient? A few months. However, that’s only if you have a dedicated project you plan on developing a solution for. It helps to have a vision of what you’d like to accomplish, then spend all of your time figuring out how to do that thing. Maybe it is developing a dashboard, or maybe it is figuring out how to automate a certain report. I recommend coming up with a generic work plan for yourself, and then Google the Excel help forums as you test out each building block along the way.
I recommend investing in the latest edition of the John Walkenbach Power Programming with Excel book series as a start.
For basic familiarity, a couple of weeks. Like destroyer said, look at a specific project. If you don’t have a specific project, I’d also suggest Leila Gharani’s Udemy class " Unlock Excel VBA and Excel Macros". She has about 20 hours of instruction, and it’s well constructed from a pedagogical perspective - lots of examples, well explained, and well structured, and it involves putting together a number of projects. And it doesn’t hurt that Gharani is easy on the eyes.
If you’re not familiar with Udemy, they set their “retail” prices pretty high (her undiscounted price for the course is about $200), but if you can wait, Udemy regularly offers the course heavily discounted - I bought it for $24, and it’s currently offered at that price. I actually crib some of her material when I teach VBA, need additional examples, and don’t feel like making them myself.
Great advice! I’ll check out that book.
I have no experience with VBA and I agree with you with creating small projects to work on.
Something as simple as pulling historical stock prices for free.
Yes! Leila is mesmerizing. I watch her formula videos and I love them.
Skip VBA and learn Python. It is being integrated more and more into the Windows ecosystem and I wouldn’t be surprised if it is native to Excel soon. You could learn what you need in Python and start your project before you get hello world running in VBA. Of course I’m exaggerating, but I would only learn VBA if your project must be done in VBA. But if it can be done another way, I’d choose any other language. VBA is at the bottom of any developer ranking for desirable or enjoyable programming languages. But hey, if you want to code like a boomer skip the VBA and go straight FORTRAN. At least FORTRAN will get you paid!
and just an FYI – you can run Python in Excel if you must. There are plugins to do it, but it is not native yet
I agree with this, but the reality of many companies is that they still use Excel quite a bit, and in certain instances, Python is overkill within the Excel structures they already have in place. Sometimes you’re not building the next crazy strategic program for the firm, and if you’re only augmenting what already exists, you can get away with VBA. Sometimes, you don’t need a nuke when a few well placed grenades will do. The best long-term approach is to know both. Excel isn’t going away anytime soon.
Yeah but you can’t just forward a python file to random people in your organization. Everyone has excel and you can create what you need with VBA and know that your audience will be able to use it without installing some software.
Well there are ways to send people Python files that run. But ignoring that and agreeing with you, the output can be in Excel and they don’t have to know Python was involved in any way. I haven’t coded in VBA in over a decade, but I believe if I would have done Python first, I would have learned VBA way easier.
My main message to the OP is do not learn VBA because it is what the excel wizards are using. Only learn it if its mission critical. Otherwise you can do things like calculate the data in Python, output formatted specific data, and use Power Query in Excel to ingest the data and hold it like a database along with whatever formulas / graph / excel wizardry you want to do.
What projects do you code in VBA that should be coded in Python or R?
For me is pulling historical stock prices from a free provider and running statistics on over 250 stocks. I rank them based on CAGR and other criteria. The more tabs I use, the slower and more time consuming things get.
Alpha_Cap I’d use python / pandas for that project.
Yes, this is the perfect thing to use Python / Pandas for. You could do this without really learning any programming concepts in Python by scripting it like you would in Excel. And once you learn functions, loops, classes, etc. then it will be light years better than Excel.
I’d encourage you to pay the $30/month and take the Python / Pandas intro course here. In a weekend, you could be ready to start building your project: https://www.datacamp.com/tracks/data-scientist-with-python