Acquiring VBA knowledge

I am very keen to improve my VBA skills to an advanced level. Would buying a book like "C++ programming for Dummies" assist me in becoming more proficient in VBA? What would your advice be to take my limited VBA skills to the next level as I often need to do development that requires VBA? Are there online courses available?
Thanks in advance

Personaly, I would not spent $35 for a book. Yes, books are good for reference, but IMO, it is really bad idea to learn VBA from books. Most books do not teach you the nuts and bolts of practical VBA. Books tend to focus on user forms and dialogue box which are useless if you do not understand why the code behind these fancy pictures does. I have bought over 10 books and spent lots of money. I did not make any head way until I watched you tube videos and forums. The best and cheapest (0.01£) of the all the books I bought so far is a self thought book by Harrris. It is really very old book-excel 1997 to be precise. The book is over 2000 pages and covers every topic from A-Z.

It depends what your goals are, whether you want to be better at Excel VBA so you can do more stuff more easily, or really understand the language.

There are loads of books and resources out there that discuss the former, but fewer that attack the latter. One of the most comprehensive (and a bit of a beast) I've ever come across for the latter is the VBA Developers Handbook by Gilbert and Getz, it's difficult to get your hands on a copy, but it treats VBA as a language in its own right, rather than merely a method of automating Excel.

So it really comes down to what your goals are and what you want to do, though I'd argue that being good with Excel (Excel automation) rather than really understanding the language (which is really only a dumbed down, stripped back version of VB6) is far more useful, once you understand the Excel object model, you're away really.

If you're just wanting to learn a programming language, there are far better choices out there than Excel VBA

Kyle! Thanks for your input in to this thread.

I had a quick glance on Amazone customers' review on "VBA Developers Handbook by Gilbert and Getz". Your description of the book and its target market comments seem to agree with your view. The book priced at just under £900. Oops!
The most expensive and worst book I have ever bought( With out naming the Autor) was £45. I think this is my ceiling for a book. Nowdays, you can learn any stuff from the "University of Google"

haha I think that's probably out of print, I got it here https://play.google.com/store/books/...d=46toCUvklIQC

Originally Posted by AB33

Nowdays, you can learn any stuff from the "University of Google"

I'm inclined to agree on most things, but that book is something else it's certainly not for the faint hearted, there's a whole chapter on rolling your own dynamic custom data structures and they hit Classes and custom Objects as early as chapter 5 of 15, the only problem with buying an electronic copy is that you don't get the code to go along with it.

Kyle! I am still in te faint hearted space. I have searched the net on Dictionary and collections objects and found some useful links and articles. I do not mind buying a book on dic and create object stuff. I have yet to see any VBA books which covers these topics in details, some of the books merely touch them.

The book actually takes you through collections and how to create custom collections. I suspect that they aren't discussed in detail in many books since they aren't particularly complex & a dictionary is just a better collection.

A dictionary is actually a better name than a collection since you look up items by a name/key

Kyle!
"I suspect that they aren't discussed in detail in many books since they aren't particularly complex & a dictionary is just a better collection".
Funny you said it!
I hope if I were in your shoes. Yes, it might be easy to understand the basics of key and item, but it is a hell a lot difficult to use dic in real world. I am probably not the only who feels this way. If a dic were easy, I suspect I would have seen lots of people using them in their code. IMO,dic combined with arrays has really a powerful stuff like in a duplicate data situation.

Thanks to all for the reponses. I think the best route is a combination of books and practical application. Working on a specific project is time-consuming but a very rewarding learning experience and probably the best way to learn.

@AB33 apologies if I sounded condescending, that wasn't my intention. I merely trying to set the complexity of collections in the grand scheme of things.

The easiest way to think about collections is as an untyped associative array. Untyped meaning that they aren't fussy about what you stick in them whereas an array of integers would only allow integers for example and associative as in they have strings that you can refer to the elements by.

So whereas a normal array is just a list of data that you can access by knowing its index (position) a collection as well as allowing you to access its elements by index also allow you to give them a reference to call them by, for example:

Array of Doubles:
	1.00
	2.00
	3.00
	4.00

Access of array syntax:	 array(x)
Accessing 2nd element:	 array(1)

Collection:
	"Kyle" 	1.00
	"AB33" 	2.00
	"James" 3.00
	"john" 	4.00

Access of collection syntax: Collection(x) - or - Collection("key") 
Accessing 2nd element: Collection(2) - or - Collection("AB33")

The caveat is that all keys must be unique for obvious reasons, collections are useful in that you can add to them, remove from them by key or index and insert into them between 2 items. You don't need to know how many elements you will put in it in advance like you would with an array, you can just add more elements. This does come at a cost though, collections are slower than arrays more noticeably as the collections get larger since each time you add something to a collection, the whole collection gets re-indexed behind the scenes.

Collections are limited in that although you can reference an element by its key, you cannot return the keys from the collection. So you can't check to see if an element exists.

Dictionaries function in the same way as collections, but allow you to return the keys therefore to check if an element exists by its key. Dictionaries also allow you to remove all its elements.

So why aren't they used more? Well it's horses for courses, collection objects are slower than arrays, they're also less useful in most scenarios. For example, you can't assign a range of values to a collection object like you can a variant array:

dim var
var = sheets(1).currentregion.value

You have to loop through the whole range/array and add items individually and you can't use Excel functions on them e.g

application.sum(array)

, you can't use functions like join and filter etc on a collection either

You do often see them used for getting unique elements from a range, this works like this:

Dim oCell As Range
Dim coll As Collection: Set coll = New Collection

For Each oCell In Sheets(1).Range("a1:b10")
    On Error Resume Next
    coll.Add oCell.Value, oCell.Value
Next oCell

Since keys have to be unique, the collection.add will fail for duplicate values and the element will not be added (that's why On Error resume Next is needed) - remember you can't check to see if a key exists in a collection.

The same thing but for a Dictionary:

Dim oCell As Range
Dim dic As Dictionary: Set dic = New Dictionary

For Each oCell In Sheets(1).Range("a1:b10")
    If Not dic.Exists(oCell.Value) Then
        dic.Add oCell.Value, oCell.Value
    End If
Next oCell

Of course with both the above, if we wanted to write the data back to the sheet we'd have to loop through the full object - you can't assign an array to a collection or vice versa

This is more elegant since we can check if a key exists and if so, not add it again (no reliance on errors). So as I mentioned earlier, dictionaries are just richer collections. Collection objects are not hugely complicated, but the way they're used can be.

Here's a coincidence, a collection question I answered this morning: http://www.excelforum.com/excel-prog...rom-range.html

Hope this helps

Kyle! Do not be silly!

No you are not condescending. You are actually one of the first person who opened my eyes on dic. I asked this question on this thread and you went in to a greater length to explain to me. I have lots of respect for you. I am here to learn and do not feel in any way condescending if people try to explain to me in what ever they feel. I value their time and skills.

In the next post, I will show you an example how I find it difficult to understand some lines of codes and you may shade light on them to me. You never know if I understand these two lines, it may turn out to be a break through on a dic and you would be my bravo.
You see you have already converted Arlu in the world of Dic and collection.