Careers

Excel VBA Macros at work

  • Last Updated:
  • Apr 20th, 2014 1:53 pm
[OP]
Sr. Member
Oct 16, 2008
975 posts
176 upvotes
Toronto

Excel VBA Macros at work

For those those who are advanced/expert users of Excel VBA Macros and use them regularly to automate functions at work - how did you get so good at using VBA? Did you take courses? Or learn off books?

If so, are there some material you would recommend?
8 replies
Member
Aug 16, 2007
340 posts
103 upvotes
Toronto
Well Macros are most useful for repetitive tasks. Basically you only need to know how to record one instance of a series of steps, throw it in a loop if needed, and assign it to a button. If this is what you need then it can be taught in a few days.

Just know that VBA is extremely slow compared to compiled code. So while Excel is nice for prototyping, it may eventually be rewritten at the end of the day.
?
Deal Addict
Dec 10, 2012
3836 posts
1488 upvotes
Canada
Use google bro, u just need to search efficiently and modify the macro liitle bit and u r good to go... I used to do all these stuff on my last job and almost everyone was impressed with my excel skills.
Member
Dec 29, 2007
442 posts
244 upvotes
Obtain business requirements first.
Use the record macro function to start. Then for more complicated coding, use Google or even YouTube videos.
Deal Addict
Apr 7, 2011
2028 posts
518 upvotes
Hamilton
I use macros and VBA at work all the time. I'll write a quick macro to use only for an hour or so to clean up data, some macros I've used for years to format text. The best way is to have a problem then figure out a way to solve it. My last formal programming course was TurboPascal, that was pre-Excel and had none of this Visual nonsense.

Are you talking small scale stuff or building application within Excel? I do the former as I'm an accountant not a developer.

Mr. Excel is a good resource that I use all the time.
Banned
Jul 3, 2013
1508 posts
298 upvotes
Hey,

I have used macros extensively to automate routine or even not so routine tasks. Once you learn VBA you can start to built in if statements and loops to help you automate some things that aren't routine in a linear format.

I learned by buying VBA books from amzon. I highly recommend "excel 2010 power programming with VBA". That book is amazing!
Deal Addict
User avatar
Jun 12, 2009
1473 posts
192 upvotes
I've been using VBA for a long time. Learn through Mr. Excel, excel forums, video game maps (to understand application).

I can honestly say that it is way more efficient to utilize endless functions excel offers.

In the time to write a base algorithm, I can do whatever I need three times over with proofs, before even testing the logic driving my algorithm.


When you are dealing with major function-heavy routine tasks, then VBA is likely more efficient. Even then, VBA is very glitchy and can corrupt entire data sets. I've had enough headaches with Excel to utilize resources external to excel, in order to optimize virtually any task within:

Large data sets -> hyperion/access/sql

Dump only what you need into excel. Then play with it.

I call crap on however many processors the software is using on >25mb files.
Physicists are made of atoms. A physicist is an attempt by an atom to understand itself.
[OP]
Sr. Member
Oct 16, 2008
975 posts
176 upvotes
Toronto
Thanks! I've always just googled the answers myself as well and changing the codes, but I figured if I were to do this for a while, maybe I should really learn how to write the codes myself..?

They should really teach this stuff in university or have a course on it.
Deal Addict
User avatar
Jun 12, 2009
1473 posts
192 upvotes
Pho6 wrote: Thanks! I've always just googled the answers myself as well and changing the codes, but I figured if I were to do this for a while, maybe I should really learn how to write the codes myself..?

They should really teach this stuff in university or have a course on it.
Why? VBA is idiot for computer programming.

They do teach it, but it's dumbed down for the masses who don't even know what base algorithms are.

The best advice I can give you is how to approach problems. They don't teach this in school:

When you cannot get something to work, rationalize whether it's your data or you. Only one of the two will require you to say (and I really do mean this) "I need to be smarter".

If you utter that phrase, budget an hour to dive on google and research your problem. If it's the other way around, it's all on you.
Physicists are made of atoms. A physicist is an attempt by an atom to understand itself.

Top