VBA Training with certification??

[tcnt9176]

Hello all. I have been dabbling in VBA for a couple of years now and have developed several macros for my employer that have been very valuable for our department. I have never received any formal training so I just rely on this board and trial and error. I know that I could be building much more effecient macros if I had some formal training. My company has told me that if I take courses they would pay for them.

I went through similar posts on the board but really didn't see much on formal classes. I am really looking for a course/courses with some sort of certification if possible.

So my question is, what are some suggestions you guys have on the best type of course and from where. How are online courses compared to actual in class training? Since I have no formal vba training, should I just simply start with a basic entry level vba course?

Any suggestions you have are appreciated!!! Thanks!!!

 

[Johnny C]

The VBA trainng I've seen is very, very basic. You might be better looking for a VB course with MS certification if one exists.

 

[Dazzawm]

I am also looking to do a course in VBA. I googled for VBA courses in my local area. As I have basic knowledge of excel and some formulas, there suggested that I start with VBA 1. There are 3 all together each one being a 2 day course. So I think a class is better as there is more one to one where if you find diffuculty you can always ask the tutor. Online courses its more difficult as there is nobody to ask.

 

[Johnny C]

to be honest, once you've a grasp of VBA - objects, properties etc - the best place you can learn is boards like Mr. Excel. There's too much to put in a book, never mind a 2 days course. I've been using VBA (in it's current form) for 12 years or more and still learn something new most days; Today I learnt about get/set and let (knew about set before but not the other two) and learnt that I can set variable values in the immediate window during a breakpoint stop (only done it in the Locals window previously). Nothing earthshattering in either case, the point I'm making is that you're not going to learn much in a 2 day course.

A course would give you a simple task or two. Not a real-life situation. it's the errors that real-life tasks throw up that you learn from.

I was once asked to do a 2 hour presentation to teach a team of people with little knowledge of Excel how to programme VBA. I created the presentation (once I'd stopped laughing), it's about 70 slides long, but I doubt they ever used it; it takes years to learn it properly.

PS if either of you (or anyone else) wants the PowerPoint presentation, drop me a PM with your email addie, you're welcome to have a look.

 

[Dazzawm]

But a course has to be a start. I don't know any of the language like DIM, SUB, LONG, INTEGER, STRING etc..

 

[Johnny C]

In that case, start with a book. You'll only be able to take in so much on a course, learn the basics before going.
John Walkenbach has written some excellent books, depending on your level
Excel VBA For Dummies is quite basic and well explained
Power Programming is aime at the more technically minded but will get you to a good level. It also has a CD with examples.

I'm not saying a course isn't any good; if someone else is paying, go for it. Just don't go on a course expecting to come out of it as a technically competent VBA programmer. And the more you can learn beforehand from a book like JW the more you'll get from the course.

Drop me a PM with your email addie, I'll send you the powerpoint slides. Can't hurt!

 

[Johnny C]

And to shed a little light on the things you mentioned:
SUB - short for Subroutine, tells XL it's teh start ofa piece of code
FUNCTION - what it says on the tin. Like a Sub except that with a function, you assign a value to the name of the function so it returns a piece of data to wherever it was referenced (in a sub, on a worksheet).

DIM = Dimension. Tells XL what sort of value the variable is. You don't need to DIM each variable, Excel makes a fairly good guess at what sort it is. However, ti's good practice to DIM each variable. Say you think a cell has a number in, but in fact it's got a piece of text. Doing a numeric function to that would give an error, but would have something uninformatove like 'type mismatch'. If you DIM'd it at the start as a number, as soon as you assigned the Text to it it would crash, but at least you'd know where it had gone wrong.

A good habit to learn; at the top of each module, put the following:
Option Explicit
This forces you to DIM all variables. if you're forced to DIM them you don't get lazy and end up in a muddle

INTEGER = a whole number. problem is it stops after +/-32,000 or so (32K) so if you were counting rows on a sheet it wouldn't reach the end. Long takes up more memory but goes a whole lot higher.
LONG = a big whole number.
SINGLE = a real number (i.e. decimal, btu with limited size/precision)
DOUBLE = a real number like a single but with bigger potential size and precision.
STRING = a piece of text. it's from when you could only define single characters, a String was a String of text characters you'd process one by one (what fun!)
BOOLEAN = TRUE or FALSE. Use to 'flag' a condition (hence known as a Flag)

Some things to be aware of; when you DIM a variable, you don't have to say INTEGER, LONG etc. there are shortcuts for lazybones like me by sticking a symbol at the end.

Dim X% means X is an Integer
Dim X& means X is a Long number
Dim X$ means X is a string.

One thing which will make your life easier; when naming a variable, put something at the start in lower case to tell you (or someone else) what the type of variable is. Just makes life easier. Known as Hungarian notation plus other names.

Dim intX%
Dim lngX&
Dim strX$

The reason is that if you're 200lines down, and you see

     intX = strY

you know it's going to crash without having to scoot up 200lines.

it's one of those useful habits a lot of people don't bother learning until they get totally knotted in complex code and end up doing it anyway to figure out what is going on and realising it's just easier to do it in the first place.

Cheers
HTH

 

[xenou]

Take any intro to programming, programming logic, or introductory course to a programming language ( such as Visual Basic I ). The concepts will apply to office programming and you'll write better VBA code. I don't think you'll find many semester long courses on VBA as such - I've not seen one around here (meaning Northeast Ohio).

 

[Dazzawm]

In that case, start with a book. You'll only be able to take in so much on a course, learn the basics before going.
John Walkenbach has written some excellent books, depending on your level
Excel VBA For Dummies is quite basic and well explained
Power Programming is aime at the more technically minded but will get you to a good level. It also has a CD with examples.

I'm not saying a course isn't any good; if someone else is paying, go for it. Just don't go on a course expecting to come out of it as a technically competent VBA programmer. And the more you can learn beforehand from a book like JW the more you'll get from the course.

Drop me a PM with your email addie, I'll send you the powerpoint slides. Can't hurt!

Hi Johnny I sent you a PM with my addy. Thanks.

 

[VBABEGINER]

to be honest, once you've a grasp of VBA - objects, properties etc - the best place you can learn is boards like Mr. Excel. There's too much to put in a book, never mind a 2 days course. I've been using VBA (in it's current form) for 12 years or more and still learn something new most days; Today I learnt about get/set and let (knew about set before but not the other two) and learnt that I can set variable values in the immediate window during a breakpoint stop (only done it in the Locals window previously). Nothing earthshattering in either case, the point I'm making is that you're not going to learn much in a 2 day course.

A course would give you a simple task or two. Not a real-life situation. it's the errors that real-life tasks throw up that you learn from.

I was once asked to do a 2 hour presentation to teach a team of people with little knowledge of Excel how to programme VBA. I created the presentation (once I'd stopped laughing), it's about 70 slides long, but I doubt they ever used it; it takes years to learn it properly.

PS if either of you (or anyone else) wants the PowerPoint presentation, drop me a PM with your email addie, you're welcome to have a look.

First of all, Thanks for the question. This is very much important question for all the learner.

Also, Johny -- I am requesting you to please put down the ppt to my e mail id as you have mentioned in above post. I would like to learn that in interesting manner.

So pls provide the ppt.
Thanks again in advance.
Mail id - Removed E-Mail address - You can use the secure PM feature instead - Moderator
---

Also, I like to appriciate your effort to make understand basic of vba to someone. Thats really great of you and much appriciated your effort.
Thanks again..