A list of all available commands in Excel VBA?

Evening friends, I'm trying to fully understand the syntax in VBA as I'm mixing properties with objects, methods and all sorts and I don't properly understand what is what.

I've been advised to have a good look-through Microsofts help and I've been at it all day. I see that Microsoft's site goes into good detail on some stuff but I find it to be very poorly set-out in terms of navigating. It took me hours to find certain pages (I've hand-coded a few websites so I'm not a 90 year old man who's never used a computer before).

I'm using Excel 2010 and wanting to learn everything it has to offer in VBA. This webpage https://msdn.microsoft.com/en-us/lib...ffice.14).aspx appeared good.

So I go to the first item; Objects "Phonetics"; now that says to me that there is an object in Excel called Phonetics.
I experiment in Excel Phonetics and try to add a method and nothing is showing up in the intellisence. So I think it doesn't have an intellisence list; so go to run it anyway and I'm told no variable with that name.

What is going on here? There is a webpage from Microsoft advising of this command and it's not there!

Hi Dal123,

Do yourself a favor and step on the BRAKES!!!

I challenge anyone to claim he/she knows everything about Excel and/or VBA Coding, even if they have access to a list of all available commands in Excel VBA!!!

That is why Forums of similar nature exists. We are here to help one another with a specific project when one gets stuck. So, relax and take it step by step, and when you need help on a specific project, place your Thread on the Forum, and take it from there. As time passes you will learn and grow in the subject knowledge to the extend where we may soon welcome you aboard as a Guru!

Kind Regards.

Thanks Winon, check out that link though; or maybe I'm reading it wrong.

Hello Dal123,

The Phonetic object was added in Excel 97 to contain the formatting attributes for any phonetic text in the specified Range. For example, this would apply to Japanese but not English. This was not the best example Microsoft could have given.

Personally, I think you should buy a copy of John Walkenbach's "Excel 2010 Power Programming with VBA". This would be a book to learn VBA. From there, it will be blood, sweat, and tears to learn VBA's deepest secrets.

Dal123,
I asked a similar question a couple of years back, I would still like to get a list of “All”. But I think as Winon suggested VBA is just too big. I doubt anyone knows in the meantime knows all the Excel VBA stuff. When you further consider that you can also use external Libraries within VBA , then it extends almost to infinity.
Microsoft Help has a reputation of being often very bad.

Also it does not Help that once you get the hang of Object Orientated Programming and VBA, you get told VBA is not Object Orientated Programming anyway.
Also it does not help that Objects and Properties often use the same name ( Range being the best example of that ). – also does not help that the distinction between a Method and a Property is sometimes vague.
http://www.mrexcel.com/forum/excel-q...ml#post3847226
http://www.mrexcel.com/forum/excel-q...ml#post3847176


_.....

Some people swear by mastering the Object Browser
http://excelmatters.com/the-object-browser/
I usually swear at it as I haven’t mastered it and find I usually need to know the answer to know what to look for

_.....

Having access to the Experts here can be a life saver when you get stuck. Prepare a good Thread and you will usually get a lot of help ( as well as often finding you go some way in answering your own question when you prepare it thoroughly)
Sometimes the Helper also learns through answering, so it can be a great 2 way thing.

_......

As for learning VBA generally..
You can check out stanleydgromjr’s latest list, he keeps it pretty extensive and up to date_..
http://www.excelforum.com/excel-prog...materials.html
_..but the quantity may be a bit overwhelming

I found participating in Forums like this the best, along with downloading and building up a massive play list of YouTube videos
http://www.excelforum.com/excel-gene...ml#post4272300
I tended to let them run in the background or as an alternative to watch instead of the Telly to fall asleep to!

_....
Alan

P.s. I missed the point of what was good about the link you gave? It is one of many thousands.
I would read it and understand it ( probably wrongly ) as the Following_..
_..Your link-....
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx
_.....is just telling you about VBA things that are language dependant

_.___but

Originally Posted by Dal123

.....So I go to the first item; Objects "Phonetics"; now that says to me that there is an object in Excel called Phonetics.
I experiment in Excel Phonetics and try to add a method and nothing is showing up in the intellisence. So I think it doesn't have an intellisence list; so go to run it anyway and I'm told no variable with that name.
What is going on here? There is a webpage from Microsoft advising of this command and it's not there!

.. I well understand your frustration..


The Phonetics Object was possibly an unfortunate example for you to take, as I can find no information what so ever on a practical use. And I can find no clear information as to what it is. It would not be totally unlikely that no one has ever used it !!

But I had a look....
That link does not seem to say much about the Phonetics Object ( I think Leith threw a bit of light on that Object , but I could not quite follow )
The link gives a Phonetic and a Phonetics property and suggest that they can be applied to a Range...

So I had a go, clicked on the_..
_1 ) Phonetic
And then
_2) Phonetics
_..properties, ( from the Table that lists Properties that return or set language-specific attributes. ) I got

_1) Phonetic
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx
That link told me nothing I can understand.
So I knocked up a test code.

Please Login or Register  to view this content.

Range("A1"). had lead to intellisense giving Phonetic as property offered. So I tried it
It errored – got nowhere!!

But at that Link, it said ...”...Returns the Phonetic object, which contains information about a specific phonetic text string in a cell. So I click on Phonetic and ... Bingo.... got a link to a Phonetic Object
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

It tells me a bit about the Phonetics(index) and once again tells me about a Phonetic Property , which if I hit takes me back where I started. But there is another Link to the Phonetic Object Members.

Members I have found is often used to refer loosely to Under Objects, Methods and Properties of an Object

So we are here:
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

If you are not confused now then you are a better man than me. VBA as “pseudo” Object Orientated Programming is a confusing bloody mess. You transgress up and down the Hierarchy sometimes in an ordered way and sometimes totally disorganised way being able to jump back and forth.
http://www.mrexcel.com/forum/excel-q...ml#post3847176
http://www.mrexcel.com/forum/excel-q...ml#post3818458

But if you keep your wits about you get there....At this point I go back to my Code... but do this

Please Login or Register  to view this content.

Now I do possibly appear to get intellisense which appears to give me the Members ( for example properties ) of a Phonetic Object. .. so I tried a few. They did not give me anything, but the code did not error. ( vTemp was given 0 or “” ).
But I think I am getting there... At this point ( I apologise if I am wrong, ) but I think the codes given in the link from Logit had nothing to do with the VBA Phonetic or VBA Phonetics things. But I am glad he gave them as to be honest I did not know what a Phonetic was.. lol..
The codes referenced are self made Functions to do something that the VBA Phonetics Object possibly does , ( or not. In the end I could not find out what it does anyway !! ).

I still have not quite sussed it all out.. but somewhere in all the confusion I read ...Phonetic Object ... Contains information about a specific phonetic text string in a cell..... So I took a migraine Pill and had another go.

_....
You should note that intellisense is no guarantee that what it offers is relevant or available. I think it is based on some simple look up. It gives suggestions that are often available to aid in selection and save a bit of full word typing. That is all. So if and when and what it shows is a bit hit and miss. It may show a Property when you actually are adding or looking for an Object.

Note, however, Generally you apply Properties or Methods to an Object to return another Object or Property.
You were unable to get intellisense for ......

Originally Posted by Dal123

....So I go to the first item; Objects "Phonetics"; now that says to me that there is an object in Excel called Phonetics.
I experiment in Excel Phonetics and try to add a method and nothing is showing up in the intellisence.....!

(There may be a Typo there in the Microsoft link. Possibly they meant Phonetic. God knows. I needed another migraine Pill at this point. I found another link..
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

This seemed to show me some Hierarchy arrangement Range – Phonetics -- Phonetic _ .....
I went then back ( or back and forth or god knows in what direction – i was lost at this point ) to my original
_2) Phonetics
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

_..... a good point to go to the next Post as I am running out of space.....

_...from last Post...._

_2) Phonetics
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx
I cannot remember whether if I was here already or not. But this last link says ..” ..Returns the Phonetics collection of the Range. Read only...” .

Collection is often loosely used in VBA to refer to an Object which contains a group of similar things, : - like “Worksheets” is the collection of Worksheets in a Workbook. So like
Workbooks(“myFile.xlsx”).Worksheets(“Sheet1”)._____ etc...

VBA can be both helpful and a pain in the arse as it lets you miss bits out and guesses what you mean.
So it may be that if one uses_..
Phonetic
_..then it would be read by VBA as
Phonetics.Phonetic

Now if you want an Object of the Phonetics type to give something from intellisense, you do not generally type
Phonetics.
That would be a bit like typing
Workbook.
Workbook is a “workbook” Object. But you need a specific Workbook to apply Methods, Properties etc. to.

So you do it something like this:

Please Login or Register  to view this content.

At this point you are where you wanted to be getting intellisense to give you the Methods and properties of a Phonetics Object:
MyPhonetics was declared as an Object of the type Object Phonetics.
I used the Phonetics Property applied to a Range Object to return an Object which is of a type Phonetics. I was able to do this because Apparently it appears a Range can have a Phonetics Under Object.

At this point I am a bit stumped. Mainly due to the example used. There does not appear to be any information actually explaining how or what the Phonetics Object is about anywhere. It was unfortunate you picked that one

You probably were just taking this as an example, so are not too interested on actually using it. If you were I expect the only chance to get any information would be to start a thread, something like
“WTF is VBA Phonetics about. Please give and explain a simple working Example”
Then you would have to hope that the one or two people in the world that have actually used it, if there are any, might catch your Thread. That may or may not end up doing something like the codes Logit referenced, which as I mentioned, make no use of the VBA Phonetics Object ( WETF that is ! )

It would be a nice addition to this Thread if someone could give a working example explained in English to demo a VBA Phonetics Object thing. I cannot. But I can for another example:
_......

_.___________________-


This is probably a better example.
You probably know that you can give a Range in a Spreadsheet a Name. So then instead of always writing like A1:C1 for the first three cells in a Worksheet you could write instead something like myRangeName

_.....Let me do that in the next post_....

_...from last post...
This is probably a better example.
You probably know that you can give a Range in a Spreadsheet a Name. So then instead of always writing like A1:C1 for the first three cells in a Worksheet you could write instead something like myRangeName


In this example of VBA stuff we do not simply have a string name for the range. It is more convenient to have an Object containing a lot of stuff to do with that named Range, only one of which would be its Name Property. I specifically choose this example as it demonstrate nicely how VBA can be a real pain in the arse: Someone in their wisdom decided to Call the Object a Name Object and give that word Name also as the Property to return the name from that Named Range Object. And just to confuse us more, for the Range Object we have a Name Property to return the Name Object for that Range Object
Finally we may have a Name Property to assign string name of the Named Range Object for that Range Object... Possibly.. The documentation is not Clear.

Examine the code below.
Line 10 Declare the name Object https://msdn.microsoft.com/en-us/lib.../ff839264.aspx
Line 20 A string name for the actual written name I want
Line 30 How VBA decides to do things is also not all that well organised and has a lot of shortcuts to do things which you get in the habit of doing from instinct and forget what you are doing. If you want to Add a named range Object properly then see this:
https://www.thespreadsheetguru.com/b...named%20ranges
In that link you see how to actually create a named Range Object properly by adding one to
either
The Workbooks Names Collections Object of Named Ranges ‘ refers to a name usable in all worksheets to get at that Range ( - referred to as Workbooks scope )

or
The Worksheets Names Collections Object of Named Ranges ‘ refers to name usable just in a particular worksheets to get at that Range ( which can be any Worksheet, not necessarily that where the Range is.. You may assign it with myRangeName – but when you retrieve that string name it will have added at the front a bit like Sheet! To distinguish it as a Worksheets Scope Named Range )
I use in my code below a short hand way here available to create an Object of type of Named Range ( ’which for the short cut is for a name usable in all worksheets to get at that Range ( Workbooks scope ) )
This way assigns the Name Property of a Range Object. VBA then automatically creates a Named Range Object for the same Range Object
Actually you are relying on VBA recognising the “myRangeName” is a String and so it assigns the Name Property. For some reason in **this case it does not error because no Named Range Object exists there: instead it makes one

Line 40 is the Fun one. Most people get confused because they use the Short cut of line 30. By virtue of line 30 Range("A1:C1") now has ( automatically somehow ) a Name Object and also a Name Property. It is very important to tell VBA what you want. As VBA is “pseudo Object Orientated Programming Hierarchy type stuff” it organises itself a bit in that sequence of - Object -- UnderObject -- Property, - ( at least often ! – as It does here. ) . If you use Range("A1:C1").Name you will be surprised what you get. It actually uses the Name Property to return the Named Range Object ( which however strangely will return a string reference instead like “=Sheet1!$A$1:$C$1” when used where a string is expected. You sometimes find that if you assign a String variable to an Object ( or use it where a string is expected like in a message box text string ) then, rather than error with type incompatibility, instead you get some string That string is often a string reference , or name , or similar. ).
So to get our “myRangeName” we must apply first the
.Name Property to return the Named Range Object
and then
further apply the
.Name Property to that returned Named Range Object return our string name “myRangeName”

Line 50 Sets a Variable of type Named Range ( which is referred to as Object type Name ) to The Named Range Object of Range("A1:C1") using the Name Property of the Range Object Range("A1:C1"). ( so you see VBA is behaving itself here doing a correct sequence Object Orientated Programming Hierarchy thing going to the next under Object, not like in line 30 which is debateable exactly what it is doing.. the only thing we can say about line 30 is that it is not doing Object Orientated Programming properly ) Line 50 allows us to alternatively in
Line 60 retrieve our name “myRangeName” from the Name Property of that Named Range Object.

_...
Lines 70 and 80 just demonstrate that the thing is confusing and a bit unclear. In Line 70 I can assign a second name semi shorthand. ( Line 80 I have to use a different way to get at it as the Name Property just gives the first assigned name. I use the a Workbook Names collections of Named Objects and then Name Property thereof to get the second objects name. ) . It is not too clear why the short hand works in line 30. Line 70 seems closer to correct. But the form of Line 70 would not work at line 30 as the Named Range object did not exist, ** and in this case it would error when at that point the Named Range Object did not exist. Possibly as I would be specifically referring to it, rather than as is written in line 30 I infer it. Maybe VBA is fooled into thinking it is there, then is too embarrassed to admit it so makes it quickly... or WhoTF knows.. no one expect ! If they do they have long since given up reading my ramblings. I expect it won’t be long before I do get banned again for them
Lines 90 and 100 showing that VBA is guessing correct and somehow not getting confused by trying to Set the Named Range Object when going back to the full shorthand form.

Please Login or Register  to view this content.

Alan

..just as an aside and since 'phonetics' is mentioned you may on some occasion also find the VBA speech functionality useful.
e.g.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi Richard,
Thanks for adding this. ...

Originally Posted by Richard Buttrey

.. you may on some occasion also find the VBA speech functionality useful

I am going to find this.. well not sure about useful.. but I have a feeling I will be using it a lot.. Lol...
You may just have changed my plans for the Week End.. Lol.. Some people may suffer... Lol...
_....
_.... it does add another interesting possibility, for example, in “telling” an Op what to do... or a great alternative to a message box.

_......_____________

Originally Posted by Richard Buttrey

..just as an aside and since 'phonetics' is mentioned you may on some occasion also find the VBA speech functionality useful

_.... I only found the 'phonetics' “useful” in getting a headache. – After many hours of googling I gave up trying to find any information or any example to demonstrate what on earth it was about ??!!
Alan

@Richard Buttrey,

I think your code may be good for Excel 2003 and maybe earlier, but From Excel 2007 onwards, one could just add the Speak Icon to the Ribbon, select the cell with Text and Click on Speak. With that in mind for Excel 2007 and later versions one could adapt your Code to simply;

Please Login or Register  to view this content.

Kind Regards.