Excel VBA for data analysis

[Blobajob88]

Hi,

I've been spending a lot of time trying to get up to speed with Excel VBA as I can see the benefits of it but I'm not sure how far down the rabbit hole I should go as I don't know how widely it is used or if there are better languages for data analysis out there. Would you recommend that I aim to become highly proficient in Excel VBA along with maybe SQL or would I be better devoting my energies elsewhere e.g. Python?

Thanks

Ben

 

[Jeffrey Mahoney]

I've worked with VBA as an application for data analysis for 20 years. It is the king of flexibility. If I am trying to solve for unknowns and discovery, Excel VBA would be my first choice. I can also build robust applications and disseminate those tools either remotely or by local servers. Excel VBA gives me the ability to start a task fast and take it very far.

I also have programmed in compiled basic. I used this to build tools that required repetitive tasks and to resolve large SQL compilations. Stand alone executables have a large overheard and take time for completing the user interface, especially for simple tasks.

I haven't worked with Python. I've investigated using it. Seems like a tool I need. I don't know what the overhead is.

 

[Jon Peltier]

If you're going to be using Excel, you should learn to use the new built-in tools, like Power Query and Power Pivot. VBA is. a powerful way to extend Excel's capabilities, and it's a good way to manage your workbook. Other data analysis tools don't interface as well with Excel, at least not yet

 

[jay_py]

I work in the financial service industry, and I deal with large amount of data daily.
VBA, PowerQuery, and Access used to be my go-to tools. However, it all changed after I learned Python.
Python is a game changer. It's a perfect tool for data analysis:

1. It's a lot faster than VBA or PowerQuery.
2. Less likely you will run into memory issues such as Excel memory cap, or Access file size cap.
3. You can use Python for not only any data analysis, but you can also use it to integrate with many different programs/apps (including Excel and other Office apps).
4. Automate any process, data extraction, analysis, producing report, sending emails, and the list goes on.