Our IT guy at work briefly showed me how you can write up a code to have Excel do what you want it to - and he went on about how you can literally save yourself time and are able to take maybe 15 minutes writing up a code that will do a task that would normally take you 3 days and have the code pull everything you need in less than 5 minutes.
It fascinated me and he suggested I read up on it and he ordered a couple of books for me. I'm finding it a little difficult to follow the books though - is there an alternative (better) way of learning VBA?
I would love to be able to grasp the basics and more so if anyone could lead me in the direction - I'd be sooooo thankful.
Yeah, the best way is to download snippets of code from other people from various websites. Google is the key, just google "VBA tutorial for Excel" or Access and you'll find tons of stuff. You can use VBA to extract data from Databases directly and put it in your Excel spreadsheet fields automatically. Even set forms and buttons to automate things for other users. I've even seen little games designed to work with Excel and Word using VBA.
You can also use VBA with Word, Outlook, Powerpoint..it's also an excellent introduction to full Visual Basic and then later VB.Net too if you're interested. VBA also allows you to run functions in one MS app from another. Such as emailing something from Excel using Outlook. I've been using VBA with different apps for 6 years so I've seen how much it can do.
Excellent point! It seems almost so obvious I don't even know why I hadn't thought of doing that. Thanks for the suggestion - I'll give it a go. I hope that it won't be as complicated as trying to read straight from the books. I think I am more of a visual learner ...it's hard for me to follow text - I learn much faster when watching someone else. We'll see how it goes, thanks again!
I already new a little VB when I encountered VBA, so I already had some idea about ifs, and loops, etc.
However, one thing I found very useful when I was learning was to record macros and then see what code is generated. This gives you a great idea about what's going on, and you can copy it into your own macro.
A word of warning though: if you do record a macro, excel always makes the cell you want to change active and then performs the change on it (that is, it copies the way a user does it). It is much quicker to simply change the cell without activating it first, and it only takes a quick rearrangement of the code to do that instead.
Yeah, I found using direct access to a cell in Excel (such as Activesheet.Range.Cells(x,y)) instead of using the Activecell is handy (and much faster to process when running graphically).
Also, to use Outlook you might need to download a small additional library, since MS Office 2003 and later has special security blocks on sending emails through Outlook. The library is free, but I can't remember the name of it at this moment.
Finally, don't forget to research handy tools like ODBC/ADO database connections through Excel/Access VBA which can really speed up automation. Much faster than processing things manually.
I think that if you actually know an OO language, then VBA will be a complete snap.
Most of the problems in learning VBA is getting a feel for the 'API's of the office programs (ie. How do I do that?) and learning the quirkiness of VB's syntax. In most apps, there's no real need to actually write your own classes. There's really no need to do much more than use the objects already created by Excel/Word/etc... at least in my experience.
One of the fun things you can do is actually access the Window's API functions directly from VBA. That way, you can do a lot of powerful things indeed..!