VBA reference material

[djb3086]

All,
Can anyone suggest good reference materials for learning VBA? Prefer CD or EBook with examples. This forum is great but would like to better understand the mechanics and not just copy paste code and modify to work. Primarily looking comprehensive list of commands and required parameters.

Thanks in advance.

 

[ChrisM]

For your list of commands and parameters, you actually can't beat the built-in VBA helpfile.

If you are dealing with worksheet stuff, review the section titled "Excel Object Model". Find the object you are interested in, perhaps "Range", and it will show you all the properties and procedures available for a range object along with code samples.

If it's a pure visual basic coding question, look in the section called "Visual Basic Language Reference" and it will show you commands, key words, data types, etc.

I've used VBA for over ten years and I go in the help file all the time looking up object model stuff.

 

[djb3086]

Thanks, after searching I found the help files you posted. Had trouble finding these earlier.
Thanks again.

 

[ChrisM]

Another good reference to use is the Object Browser. It's on your VBA toolbar, a box with shapes flying out of it, underneath the Add-Ins menu.

Start in the Classes section, these are your objects. Choose "Range", then look at all the stuff that appears in the right hand window. Actions have a green box, properties have white boxes. Choose one, and look at what it tells you. Pick "Advanced Filter".

It prompts you for Action as xlFilterAction. Note it is in green. Click it.

Now you're in a new section, with yellow box icons. These are kind of like containers, and each one contains only certain allowed values. In this case, it's "xlFilterCopy" and "xlFilterInPlace". So to run your autofilter, you need to specify one of those two as the action.

The VBA help system is SO SO helpful once you understand it.

 

[john21wall]

Thanks for the valuable tips were useful