best reference/guide for learning VBA

I learned a bit of VBA modifying recorded macroes but I now want to become a real VBA expert (with Excel 2010).

What are the best compact / regular / complete guides for learning VBA on the net?

I mean something like these:
http://www.excel-spreadsheet.com/vba/vba.htm
http://www.itu.dk/people/slauesen/UID/VBAcard2.pdf

Is there an official complete reference source done by Microsoft?

__
Message crossposted here: https://www.mrexcel.com/forum/excel-...ml#post4986771

Hi 6diegodiego9
Practice is really the best in my opinion.
Second to that, I found a good set of learn video play lists:
https://www.excelforum.com/excel-gen...ml#post4272300
I found it good to download those and make along play list to run in the background while I was practicing, or I fell asleep to watching them in the evening.
At the top of this Excel Programming / VBA / Macros Sub Forum you will find a “sticky” Thread ( one that does not move down ) That is full of learning material, but it is a bit overwhelming in amount.
https://www.excelforum.com/excel-pro...materials.html Stanley does keep a large list there up to date

This site I use sometimes https://www.thespreadsheetguru.com/
In recent years I have found this site to give very full and detailed blogs, but you will need to take the time to go through the blogs https://powerspreadsheets.com/

Otherwise it is difficult to suggest a Blog site. As time goes on you pick up the odd particular blog that is useful, so it is difficult to suggest a very full source. One problem is that VBA is so big that even the best people will tell you there is always more for them to learn.

Best bet is, those Playlists, the first one is very good. I put them in approximate order of how useful I found them.
Try to get involved with a good Forum like this one. Prepare any questions you have carefully and you will find a lot of people keen to help you. You will also find that if you prepare a question carefully then in doing so you often find that you answer at least part of your question yourself.

You can pick up a lot of books cheap over ebay. For learning it is not important to have a new book. You can learn coding with a book which is based on an older Excel version, even pre Excel 2007, with no problem. In fact I found some of the older books much better than newer ones. People are losing interest to produce a good book I think, as they find it difficult to sell since so much free material is available on the internet.
If you have You tube videos as well , then that help keeps you to keep up to date with exactly how things may currently “look”

I do not think that there is a complete Microsoft reference source. Their documentation is extremely varied in its quality. Occasionally it is totally missing. Sometimes it is simple short and precise. That is good. Sometimes it is appalling. I am not a professional, but have often found errors in their information given.

The video play lists are the nearest I know that comes to a compact / regular / complete guides for learning VBA on the net.
I do not have any experience with paid course. There may be some good ones there. If you are learning for work then I guess it might be worth considering if they will pay for you to go on such a course. I have no experience there.

Alan

DELETED my original post here ( deleted it at approx at 01:06 PM ) after I saw Kyles Moderation post.
( I did not see it when I posted )

New Post: 01:06 PM
Hi
6diegodiego9,

Drop in the URL link please copied from the Browser window to the other Threads that you have posted and then I will repost what i just DELETED.
Alan

( Rule 8 https://www.excelforum.com/forum-rul...rum-rules.html
https://www.excelforum.com/word-form...ml#post4815481
https://www.excelforum.com/forums-rules/ )

Originally Posted by 6diegodiego9

About Microsoft, really? they don't offer an official complete reference guide anywhere?

https://msdn.microsoft.com/vba/office-vba-reference
https://msdn.microsoft.com/vba/vba-language-reference

Originally Posted by buran

https://msdn.microsoft.com/vba/office-vba-reference
https://msdn.microsoft.com/vba/vba-language-reference

@ buran
Hi buran
Probably I missed something…. But I do not see any complete reference guide there – I think the OP wanted a list of all the available variants/relatives of a command….
I did have a suggestion of where to find such a list… ( but we shouldn’t tell him yet, until he has told us where he also started a similar Thread ) ..
Alan

well, maybe you should look better. I posted link to top (start of MS reference guide) but if you browse around it, you will find
https://msdn.microsoft.com/bg-bg/vba...-vba-reference
https://msdn.microsoft.com/bg-bg/vba...nce-for-office

That is in addition to Object Browser in excel as well as the help (in earlier version, as latest one help refers to the online help)

Originally Posted by buran

well, maybe you should look better. I posted link to top (start of MS reference guide) but if you browse around it, you will find
https://msdn.microsoft.com/bg-bg/vba...-vba-reference
https://msdn.microsoft.com/bg-bg/vba...nce-for-office

Thanks ( I still cant find those links in the original links you gave..never mind , Thanks again )
( One of them comes up in German by me - I keep telling Microsoft how they mess up the translations. They are often have errors in them. Maybe something got lost in the translation )
Alan

Originally Posted by Doc.AElstein

TI still cant find those links in the original links you gave..

https://msdn.microsoft.com/vba/office-vba-reference
On the left hand side there is Contents->Excel VBA->(again left hand side menu) Object model

but this is just the Object model, and the whole MSDN is huge reference itself for various topics

OK … two Hours later....

VBA Object Model:
Start with buran’s first link:
_ ---- https://msdn.microsoft.com/vba/office-vba-reference
then in left margin
_ ---- Contents --- Excel VBA ( or VBA in Excel )
VBAinExcel.JPG : https://imgur.com/75ARkhY
_ ---- https://msdn.microsoft.com/de-de/vba/vba-excel
_ ---- again left hand side menu) Object model
ObjectModel.JPG : https://imgur.com/5YSovDD

Bingo!!! https://msdn.microsoft.com/de-de/vba...-vba-reference
( https://msdn.microsoft.com/en-us/vba...-vba-reference )
https://msdn.microsoft.com/de-de/vba/excel-vba/articles/object-model-excel-vba-reference
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/object-model-excel-vba-reference

Got that one….
_.____________________________

But where does this _..
https://msdn.microsoft.com/bg-bg/vba/office-shared-vba/articles/reference-object-library-reference-for-office
come from ??
_.. One way …
Office VBA Reference
_ --- https://msdn.microsoft.com/bg-bg/vba/ _ Or _ https://msdn.microsoft.com/vba/
_ --- look in the left margin , hit _ Office VBA Reference _ Or _ at the bottom _ Office VBA Object library reference
OfficeVBAReferenceOrOfficeVBAObjectlibraryreference .JPG https://imgur.com/TTjC0ug
_ --- If you chose in the left margin _ Office VBA Reference _ , then now choose again _ Office VBA Object library reference _ at the bottom
AgainOfficeVBAObjectlibraryreference.jpg https://imgur.com/a/Ld6n6
_ --- With a bit of luck, a few hours later…. you end up here….
https://msdn.microsoft.com/bg-bg/vba...rary-reference
Now it is a game: “ Find the hidden reference. “ This is the tricky one.
FindTheHiddenReferrence.JPG https://imgur.com/fUHTf4i

Found it: https://msdn.microsoft.com/bg-bg/vba/office-shared-vba/articles/reference-object-library-reference-for-office https://msdn.microsoft.com/bg-bg/vba...nce-for-office
That was a tricky one to find.. and I knew the answer from buran … if I did not have that answer then I don’t think I would ever of found it.

No wonder most people do not know about it… most people never found it, I expect…
In the past I have asked many people, including many senior MVPs, - they never knew.
Thank god the Thread was not closed after the cross post warning… this is astounding information that even Microsoft themselves did not know about.
Many Professionals have told me the following….”…. To know where to search for by Microsoft, you need to know the answer…” ….. I see what they mean now. Microsoft Help is crap. ( Currently I report a mistakes in there articles every few days… ( they usually make the corrections though, which is some encouragement… ) )

The Secret is out: … there IS an official complete*** reference guide to all the available variants/relatives of a command ( ***but it is not complete, by the way. A lot is missing or wrong – it is “official complete” – not “complete”).
( …. better save the links quick in case the posts get deleted !!! )
My original comments still stand… ….”….Their documentation is extremely varied in its quality. Occasionally it is totally missing. Sometimes it is simple short and precise. That is good. Sometimes it is appalling. I am not a professional, but have often found errors in their information given….”…..
Alan

( And it is worth noting that many Microsoft articles are not in that list, but cover a similar or the same topic and are sometimes better…. Occasionally worse…. On average the older the better… Hence the official complete*** reference guide to all the available variants/relatives of a command may not be the best option as it is probably full with the newest articles… )
And one last thing , a good tip , ( I got from Lazarus cytop I think ) .. try searching old VB stuff as an alternative to VBA – it is sometimes very revealing…. )

For most of us here, I think the Excel OM is more useful than the Office one
https://msdn.microsoft.com/en-us/vba...-vba-reference

Edit: NVM, I see you had that linked at the top of your post, before you digressed to the Office one.

Alan, first of all the help from within VBE is online since 2010. Do you never ever hit F1 - it opens MSDN. Even before that there was reference on MSDN. It's a matter of simple google search...
https://www.google.com/search?q=MSDN+VBA+reference
Sorry, if someone cannot search info online...

I keep telling Microsoft how they mess up the translations.

Tell me about it, it looks like bunch of articles in MSDN are translated using robo-translator.

One such example is MID function in Swedish.
https://support.office.com/sv-se/art...8-4ecb12433028

At any rate, using Object Browser, Macro Recorder and using reference to external library with early binding is best tool to explore vba object model.

Note: There are few items that are not well documented, such as difference between regular PivotTable object model and OLAP based PivotTable object model.
There are may properties, or members of object, that's not accessible in one or the other.

Originally Posted by buran

Alan, first of all the help from within VBE is online since 2010. Do you never ever hit F1 - it opens MSDN. Even before that there was reference on MSDN. ....

I have not used that F1 thing much. That is another source of info. I guess. I am not too familiar with it.
I believe one highlights a word when in the VB Editor and then hits F1.
F1helpOnlineExcel2007.JPG : https://imgur.com/BOWojAO F1.JPG : https://imgur.com/eMNegOq
I do not have much experience with the F1 thing.
I do not have above Excel 2010.
I have noticed that it occasionally catches people out that there is an option for online and offline in versions Excel 2010 Excel 2007.
The possibility to change it is often overlooked:
https://www.mrexcel.com/forum/excel-...or-2010-a.html
https://www.mrexcel.com/forum/excel-...ml#post3847765
https://www.mrexcel.com/forum/excel-...ows-vba-3.html
http://excelmatters.com/excel-forums/#comment-158376
F1helpOnlineExcel2007.JPG : https://imgur.com/BOWojAO

Originally Posted by CK76

Tell me about it, it looks like bunch of articles in MSDN are translated using robo-translator.

Yep – often they translate by mistake the named argument words !!! I frequently catch those ... just yesterday I caught one: Endgültig löschen was given ( as a Speak optional parameter argument thingy Error Endgueltigloeschen.jpg: https://imgur.com/UwvTlbG Error Reported.jpg https://imgur.com/3qG4hot ( the correct option in English is _ Purge _ .. and VBA “speaks” English not German.. Lol… )

Please Login or Register  to view this content.

Hi 6diegodiego9,
I see you added the link to the cross post, thanks…
What I wanted to say is a bit outdated especially as it seems I was partly wrong about no “…list of all the available variants/relatives of a command…”

But , then just to add a few things… …
_ 1)
as buran and CK76 mentioned in the meantime, there is the “Object Browser”…
_ _.... Some people will say that the use of Help in the VB Editor window ( F2 once you got into the VB Editor from Alt+F11 in Excel ) is something close to a complete reference guide.
http://excelmatters.com/the-object-browser/
I guess that as Helping “Object Browser Thingy” is from Microsoft then it is a form of a Microsoft “…list of all the available variants/relatives of a command…”

I never got around to mastering that “Object Browser Thingy”. Some people love it. Some people say it is crap and getting crapper for newer Excel versions.

_ 2) Intellisense is a form of help that I find very useful. Also that was touched on on the meantime: You get it for all standard “object libraries”, that is to say
Visual Basic For Applications
Microsoft Excel X.0 Object Library
OLE Automation
Microsoft Office X.0 Object Library
That means if you use stuff “from” there in when writing code then you get a drop down list of command suggestions when you type a . after the stuff.
Also CK76 mentioned that if you an external library with “early binding” ( “early binding” is sort of making a reference to the library before a code runs ), then you also get that intellisense foir stuff from that Added library

_ 3) The other thing in the meantime also mentioned by CK76 is the macro recorder. I see you mentioned that you have used that. Even Professionals use it to do a quick recording of a short manual action done in Excel so as to get the correct code syntax from the code which the recorder produces. ..

Alan

I guess after a while you get a “feel” for navigating around the Microsoft stuff, ( and possibly understanding the warped thinking of the individuals at Microsoft )
As I mentioned before, it can be a bit of a “Chicken and Egg thing”._.....
_....If you know the answer, then you can find the answer quicker…
_...Or, worse, you may need to know the answer in order to know where to look to find the answer, which of course is not much help to you.
But that is the nice thing about these Forums where the people in the know can help point you in the right direction.

_._____________________-
Starting at where you where:-..
https://msdn.microsoft.com/it-it/lib.../ff822931.aspx
_.. now, because you now know:

Originally Posted by CK76

, .Formula is property of Series. …
SeriesCollection is collection of series......

You see you where almost there… - Click on series
SeriesInSeriesCollection.JPG : https://imgur.com/D1IRM2a
Then you are here, at the series object:
https://msdn.microsoft.com/it-it/VBA...s-object-excel
Scroll down and find your Property
ScrolDownAndFindObjectsProperty.JPG : https://imgur.com/Tc5EwQn
After a while you pick up an instinct for knowing where to look.

But the key to the answer this time was that , as CK76 said, that SeriesCollection is a collection of Series.

You may get caught out by this sort of things a lot. Often the object that is a collection of a certain object shares almost the same name. Sometimes Microsoft seem to pick words to really confuse us.

Here you were lucky.
SeriesCollection is the collection of all Series objects.

Often Microsoft really confuse with their choice of words.
For example - … Areas … in a spreadsheet.
More exactly this is all the areas of contiguous cells ( contiguous cells = full rectangular groups of cells ) in a Range object . ( A Range object can contain one or more areas of contiguous cells )
They will tell you that the Areas object is the collection object of all spreadsheet areas of a spreadsheet range of cells . But then they will tell you that there is no Area objects. They choose to use the word Range to refer to the “area” objects in the Areas. Or each one is a Range , or if you like , each one of the things in the Areas collection object of a Range object is a Range object itself.
To really confuse us… In addition you have a Range Property which can be applied to any Range object area , ( not to a Range object.### )
You have no chance of understanding all that until you understand it. It is a real catch 22 situation

I sometimes wonder if someone at Microsoft tried very hard to confuse us all with their poor choice of wording

###In fact, Microsoft confuse themselves and frequently get their documentation wrong. You will often read that you can apply the Range Property to a Range Object. You can’t. You apply the Range Property to a single area of the Range object.
If you write this ( pseudo code )_..
RangeObject.RangeProperty
_..then VBA guesses that you have only one area in your Areas object, so your code line is actually
RangeObject.Areas.Item(1).RangeProperty
or
RangeObject.Areas(1).RangeProperty
_.__________________

But they are getting better at Microsoft.. I have mentioned a lot of their errors and to my great surprise they corrected their documentation quite quickly after I had done that. That was a very big surprise to me.

_.____________________

I am guessing that you may have the problem also that possibly Italian is your Mother tongue? I sometimes find the German documentation easier to understand ( I am English but use almost always German Excel ). Here is a tip that sometimes helps me in German. ( I expect there is limited translated versions of Microsoft documentation in Italian. It may occasionally help you )
If there is a version of documantation in Italian, then you can get it quickly like this
English:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-object-excel
Italian:
https://msdn.microsoft.com/it-it/vba/excel-vba/articles/range-object-excel
So you manually change the link in your browser URL bar. ( Possibly some browsers can be set to recognise the en-us and change it to it-it automatically )
( I did just try a few… it appears they have not got around yet to translate much to Italian … maybe sometime in the future ) ..
But least the top bit is Italian:
TopBitItalian.JPG https://imgur.com/JPyMIqv

Alan

Originally Posted by Doc.AElstein

###In fact, Microsoft confuse themselves and frequently get their documentation wrong. You will often read that you can apply the Range Property to a Range Object. You can’t. You apply the Range Property to a single area of the Range object.

That is not correct. As you stated prior to this, the Areas property is a collection of Range objects and there is no Area object. Range is, as the Object browser will clearly show, a property of a Range object.

Originally Posted by xlnitwit

... Range is, as the Object browser will clearly show, a property of a Range object.

I rest my case.. They got it wrong… or arguably at least they are not being too precise.. but that is OK – they do not have too much space there to be more precise. ..
Range is a Property of a single Range object area. I write area in italics to try to indicate that it is not an object.
That area is itself a Range object, admittedly. We are playing with words to some extent. Technically, what you quote, could be seen as not to be wrong. But it is misleading in my opinion.
The Range object being referred to there is specifically a Range object of a single area of contiguous cells. It must be….

If you try to apply the range Property to a range Object of more than one area, it won’t work., ( at least in all the situations that I can immediately think of ) It will not necessarily error, but VBA will default to applying the range Property to the first area range object.
Some properties will work on a multi area range object. Some won’t. I think most Properties and Methods that don’t work on a multi area range object will default to working on the first area if you miss out the_..
.Areas(x) ____ ‘ x being 1, 2, 3 … etc – item number of area counting from order in assignment code line
_.. bit.


“Areas”
This is one of those occasions when the poor choice of wording from Microsoft can confuse in my opinion.
The Areas Property applied to a range object returns a collection object ( https://msdn.microsoft.com/en-us/vba...property-excel )
That collection object is referred to as the Areas Object of that range object( https://msdn.microsoft.com/en-us/vba...s-object-excel ) That collection object “contains” …..( that is to say it did collected and has then done got in it ) ….
… all the range objects, one for each of the areas in the range object
( Most people only ever see or concern themselves with the single area case, and never come across the Areas stuff. I expect for that reason VBA defaults to the single ( first ) area case )

_.______________________

Demo code ( for OP or anyone else learning – I know you, Don, know all this.. )
It mucks about referring to / putting things in cells of a two areas range object
Best is to go in the VB Editor, ( Hit Alt+F11 to get there ) paste in the code in the big code window, select anywhere in the code and then step through the code in debug mode ( F8 )

Please Login or Register  to view this content.

The above code gives finally the following on the Active worksheet
Using Excel 2007 32 bit

Row\Col	A	B	C	D
20	Top left areas1
21
22			Top left areas2	Areas2
23			Areas2	Areas2

Ref areas - and copy them all if they are in line !!
http://www.excelforum.com/showthread...t=#post4551080
https://www.excelforum.com/developme...ml#post4551484
http://www.eileenslounge.com/viewtop...=25002#p195791
http://www.excelforum.com/excel-prog...ml#post4529679

TLDR

Range is a property of a Range object. That is not wrong. A cell/row/column/area or multiples of those are all Range objects. It is true that the range returned by the Range property is relative to the top left cell of the first area but that does not make it wrong to say that Range is a property of a Range object because it is.

I don't really see what point your overcomplication (in my view) of the matter is trying to make.

Hi Don
The point was that the range Property can only be applied to a single areas range object
But never mind..##
I think we are saying similar things just a bit differently. I think the subject is complicated if you look at it a little further, as I prefer to, just a personal choice.
The simplified approach will get you through 99% of the time, or more if you are lucky.
As I mentioned, the extra info I gave was aimed at the OP or others learning that may view the Thread later.
Most people will agree that the Excel VBA Range object is one of the most important and also the most complicated things in VBA.
In my opinion, it is worth taking the time at an early stage to get to grips with it. I did not initially and it proved a false economy.
I have some notes on it that I made:
http://www.excelfox.com/forum/showth...eadsheet-cells
http://www.excelforum.com/tips-and-t...eet-cells.html
I was thinking of trying again with a more Layman’s approach looking at just “How Excel organises its boxes”. There would be less emphasis on the range as an object and the theme of areas and muliti area ranges would possibly come out more clearly.
##But as always it is difficult to give a good explanation whilst avoiding it being Too Long To Read
That’s life, so much to do and learn, so little time
Thanks for the inputs
Alan








along the lines of Leith Ross’s signature . Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causesed me delays!) – …. and it makes Forum Threads longer .. in total .. actually

Originally Posted by Doc.AElstein

Most people will agree that the Excel VBA Range object is one of the most important

Yes, that I do agree with! I'm not sure it is quite as complicated as you are making out though, and I still disagree with your assertion that Microsoft is wrong here- they are not. (Wrong in many other places for sure, but not here)

I appreciate the point you are trying to make about many- but not all- properties of a Range object (including Range) applying to the first area of a range object, and I know you like to know all the ins and outs of things, but this seems to me to be a bit like taking a learner driver round the 'Ring' in a Formula 1 car as their first driving lesson. Start small, and grow as the need arises.