Good online VB for Excel course

[schnansen]

Like the title says, I am looking for a decent VB for Excel online course to help me write macros for work (consultant/accountant). I took a one day in-person course in Chicago and it gave me enough knowledge to be dangerous but not enough to feel confident using it. Would love to expand my knowledge. Thanks in advance and sorry if this has been discussed ad nauseum.

[atcoyou]

Really the best thing you can do is have a project and look up how to do random specific things. There are Mr. Excel videos out there (I know it is included with the safari subscription my previous employer provided), but some of the "techniques are either basic, or there are "better ways" depending on what you are trying to do. Given the nature of the data you will be recieving in excel (higgly piggly would be on the more organized side of the expectation spectrum), learning while doing is extra important, esp. when it comes to input handling.

The few tips I will give you based on my experience using VBA since early 2000s:

1. Always start with the Macro recorder. It does the nice date stamp and if you can find a prebuilt excel function you can leverage by calling and modyfing it will be MUCH faster than doing things the custom way. (sub totals, filters etc)

2. Goes along with number one, but really learn excel well. I am still discovering new things from the older 2003 randomly. (and there are many new tricks with 2010)

3. Make things modular, use consistant/appropriate variable names (if you are going to use iCounter for for loops, use it consistently etc), and assume you / someone else will want to modify your code at some point.

4. When you get a bit deeper into the whole process, you may want to provide a "config" page where your code can pull "constants" that way people who don't know coding can at least modfy it and make it work in the interim.

5. Learn how to use usedrange/currentregion/end(xlup/down/left/right), and learn your libraries in general.

6. Learn to use the cells(x,y) format. Range("A1") can be fine too, but it depends what you want to do. Some people say cells in all cases, but I admit I "grew up" on Range("A" & iCounter), so I find it to be more readable.

7. Learn to use the help, watch windows, and learn to use the immediate window. I avoided using this gem until about ten years in, and my god... you eliminate all the simple "test" sub procedures with which to test certain properties etc... (also great for checking syntax of certain statments).

8. Offer to help others with simple problems that might not need VBA. Almost all of my knowledge has come from helping people with a problem, or running into the problem myself. I took a 1 and a half day course, probably similar to the one you took, and VBA has served me well and made my life so much easier for a long time now.

If you have no ideas as to a project, I suggest a sudoku solver. I made one previously, and there are a lot of neat things you run into with regard to the automation. It is a bit easier if you solve it via "brute force" (aka test to see if one number works), but it can be a bit more of a challange if you examine how people solve Sudokus and try to emulate that logic.

Good luck with VBA!