I’ve asked my boss to set aside some money in the training budget so I can take some kind of VBA course specific to Excel. It’ll probably be self-study, an online course, or just a few good beginner books since I can’t find any “live” instruction available in my area. Can anyone recommend a good course or some good books?
I recommend picking up a copy of Microsoft Excel Power Programming with VBA by John Walkenbach. Amazon Link.
It has a lot of good examples, comes with a CD containing all the code used in examples in the book, and is written at the “Intermediate to Advanced” level but isn’t too hard to get into if you haven’t used VBA before.
I learned VBA the same way you are looking at – from books, etc. and I am certainly no expert at it, but I have learned enough to help me tremendously in my job.
My only mistake was I first tried to teach myslef using the Microsoft-published guide we have at work (I think I was included with the software purchase). That was sort of like learning how to drive by listening to someone talk abstractly about how engines were built.
Thanks, I’ve been to his web site before but it was a while ago and I forgot about him when it came to books. I hear ya on the Microsoft guide. I’m pretty sure that’s what we have too and it’s impossible.
Unfortunately, my best reference books are at work. I’ll try to get back to this thread on Monday. In the meantime, one of the things I strongly recommend is try recording a macro which does something similar to what you want to do and looking at the code behind it. For the record, I started off teaching myself to program Excel and wound up a Visual Basic programmer and database designer who can get Excel to do anything short of whistling Dixie and given the right sound file . . .
Just a few quick questions.
[ul][li]What would you like to program Excel to do?[/li][li]How good are you at working out the logic for a program?[/li][li]Are you at all familiar with Object-Oriented programming? I realize Excel isn’t fully Object-Oriented, but knowing the basics of how it works does help when dealing with Excel.[/li][li]What do you want more help with? Figuring out the programming language? Structuring a program to run efficiently and logically? Something I haven’t mentioned?[/ul][/li]The way you answer these questions will affect the sort of books I’d recommend. I’ll also take a shot at answering any specific questions you have.
Good luck!
CJ
I strongly recommend the macro approach.
Honestly, I thought “book-learning” was ancient history as regards to programming. I remember teaching myself C, BASIC, FoxPro, and various macro languages from books in the late 80s/early 90s, and was overjoyed by the advent of the F1 key.
I have more to say but find myself unusually bummed by John Ritter’s death. Have a nice weekend, everyone. 
Sorry it took me so long to get back to this. Busy times at work lately.
I can record macros and edit what I record so I’m not a true beginner at this but much beyond that and I’m just copying and pasting things from Excel message boards into my code without understanding what’s going on. I guess I’d say I’m looking for a book with more advanced concepts presented in a way that will be easy for someone new to intermediate/advanced concepts to understand.
Since I brought it up the company is going to spring for some on-site training. It’ll be a two day class and will be somewhat customized for us. My experience with these classes is that people who have no business being in them sign up and 75% of the time is spent going over the basics for those people. And the workbooks or whatever they give you aren’t always the best reference books so I’m still going to try to get them to buy me some books.
It’s hard to say what I want to learn without knowing what I need to learn. Here are some things from the class outline I was given that I think are important.
Objects, Properties & Methods - This seems to be the key to just about everything.
User Forms - I don’t want to be able to make user forms per se, but the elements of user forms seem to be popping up more often in my work.
Worksheet calculation and Worksheet change events
Function Procedures - I’m not sure what this is but it sounds handy.
Not that this is true in your case, but I find that a lot of people use Excel as a storage repository and data massager rather than what it’s suppose to be used for - a spreadsheet. It’s like using a screwdriver to hammer a nail.
That being said, are you sure you want to use Excel as opposed to Access? You will have a lot more flexibility if you need to use the data in other applications or need to build user forms. Access is also, IMHO, more suited for “programming.”
You may want to pick up a basic book on Object Oriented Programming - objects, properties, and methods (and, to a lesser extent, events) are building blocks of OOP. I learned my VBA from Access Programming for Dummies, and recommend the Dummies series for easy-to-read tutorials (but make sure that it covers the correct topics - Excel for Dummies will have maybe one chapter on VBA, while Excel/VBA for Dummies would be more targeted for what you’re interested in).
I agree with you about the Excel two day class. I’d find an intro on OOP - you’re going to find people more interested in programming and less interested in a particular application. Once you have the basics of OOP down, you can pick up any language (like VBA) fairly easily.
Of course, this is all opinion, and I may be suggesting to use a sledgehammer where a flyswatter may be more appropriate. However, in programming, there’s usually a dozen ways of doing something, and 11 of them will either give you incorrect results, crash when exceptions occur, or be so awful to maintain that it will take you days to change it when it needs to be changed. If you want to program (which is what you’re asking), learn how to program.
Visit the Ozgrid.com website. Dave Hawley offers many free tips, hints, etc. He also offers an online course in VBA – I found them very good at answering questions about the course materials. There are also a large number of links to Excel/VBA sites.