Reserved Words in Excel vba

[Micron]

I have a link to a reserved word list for Access vba & use it often, but while helping out here I wondered if there was such a list for Excel. Google returned 1.2 million hits and the few I looked at were of no help in providing a list, save but one. However, one link did suggest that all you have to do is type the suspect word in the object browser and search on it. So for "description" I found that it is reserved in Office, VBA and Excel libraries as a property of about 18 different classes.

So this isn't a question (I hope that's OK) but rather a tip about using the Object Browser to check if a word used anywhere in your workbook or its code is totally safe to use. Next time someone searches on the subject, perhaps they'll end up in this forum, which wouldn't be a bad thing, yes?

 

[Joe4]

Thanks for the tip!

One little "trick" that I often do that works is create a new procedure in VBA, and just type in the word I want to check in lower case letters like this:

Sub Test()
    instr
End Sub

Then, when you move off of the word, if VBA automatically capitalizes it, like this:

Sub Test()
    InStr
End Sub

you know it is a reserved word (or it could possibly be a variable that you already used elsewhere).

Or if you get some sort of error (i.e. if you try the word "name"), you know that is also reserved.

Someone once pointed out that this isn't totally foolproof, but I believe it does catch most of them.
At the very least, you can use it to quickly "rule out" words you may be considering.

BTW, could you post that list of Access reserved words somewhere?
I would love to have "access" to that!

 

[dmt32]

Here's one that may help: VBA reserved words |

Not fool proof but One way to check a word is to try & create it as a Sub Name

Sub Resume()

End Sub

Whilst generally, it’s not a good idea that you use names that are the same as the function, statement, or constant names used in Visual Basic as you end up shadowing the same keywords in the language it does not mean that you always cannot.

If you use such a name in your project that conflicts with an assigned name, you must explicitly identify it with the name of the associated type library.

For example, if you have a variable called Month, you can only invoke the VBA Month function by using VBA.Month.

To avoid conflicts, most programmers use a naming convention by prefixing the name with its data type such as intMonth.

Hope helpful

Dave

 

[Siddharth Rout]

Thanks for the tip!

One little "trick" that I often do that works is create a new procedure in VBA, and just type in the word I want to check in lower case letters like this:

Sub Test()
    instr
End Sub

Then, when you move off of the word, if VBA automatically capitalizes it, like this:

Sub Test()
    InStr
End Sub

you know it is a reserved word (or it could possibly be a variable that you already used elsewhere).

Or if you get some sort of error (i.e. if you try the word "name"), you know that is also reserved.

Someone once pointed out that this isn't totally foolproof, but I believe it does catch most of them.
At the very least, you can use it to quickly "rule out" words you may be considering.

BTW, could you post that list of Access reserved words somewhere?
I would love to have "access" to that!

Yes you are right Joe4. That is not foolproof.

If you declare

Dim instr As String

VBA will accept it. Now if you will try to type instr and move away, the first character will not capitalize.

BTW "Instr" is not a reserved word. Reserved words are displayed in Blue by default in the Code window and you cannot DIM them. Alias is an exception.

For example, you cannot do this

dim Case as String
dim byval as Long

HERE is a list of reserved words.

 

[Joe4]

If you declare

Dim instr As String

VBA will accept it. Now if you will try to type instr and move away, the first character will not capitalize.

Yes, note that I said just to type the word itself, not to declare it first.
That was quite intentional on my part, but perhaps I should have clearer and mentioned that to remove any doubt.
You would need to just type that word in WITHOUT trying to declare it as a variable first.

 

[Micron]

@Joe4; @dmt32 posted the link to the list you asked for. As for if you have a variable called Month, IMO you just don't. That's the whole point of this post. Also, some words would be easy to vet as described, but when it comes to properties (e.g. Description) you'd have to preface the word with the object and likely one or more parent objects in order to get any help from intellisense or automatic capitalization.

"Reserved words are displayed in Blue by default in the Code" I don't totally agree with that. Without testing, which of these words are reserved and which one(s) will show up blue? Excel.PivotFilter.Description

Keywords show up as blue though - but as alluded to, that depends on your option settings. There is a list of text types in the editor format tab of options if you're not familiar with the word types.

Is this a thread that I need to mark as solved when I wasn't really looking for a solution?

 

[Siddharth Rout]

Yes, note that I said just to type the word itself, not to declare it first.
That was quite intentional on my part, but perhaps I should have clearer and mentioned that to remove any doubt.
You would need to just type that word in WITHOUT trying to declare it as a variable first.

Oh I agreed with you that it is not foolproof.

I have seen so many people declare variables with such names and then they are perplexed as to why the code is behaving in a certain way and hence I supported your comment with an added information.

 

[Siddharth Rout]

@Joe4; @dmt32 posted the link to the list you asked for. As for if you have a variable called Month, IMO you just don't. That's the whole point of this post. Also, some words would be easy to vet as described, but when it comes to properties (e.g. Description) you'd have to preface the word with the object and likely one or more parent objects in order to get any help from intellisense or automatic capitalization.

"Reserved words are displayed in Blue by default in the Code" I don't totally agree with that. Without testing, which of these words are reserved and which one(s) will show up blue? Excel.PivotFilter.Description

Keywords show up as blue though - but as alluded to, that depends on your option settings. There is a list of text types in the editor format tab of options if you're not familiar with the word types.

Is this a thread that I need to mark as solved when I wasn't really looking for a solution?

I think you missed the word default. By default it appears in Blue but if you tweak your options then of course, this doesn't apply. Then it will take the color that you choose.

Anyways thank you for this post. It is quite informative and I am sure it will defnitely help future visitors.

 

[Joe4]

I have seen so many people declare variables with such names and then they are perplexed as to why the code is behaving in a certain way and hence I supported your comment with an added information.

Oh, yes I agree. It was "implied" in my post, but not "explicit", which means it could easily be interpretted differently and missed.
Thanks for bringing attention to that so we could clarify that, so as to remove any doubt.

 

[Micron]

Uh, no I did not. I echoed your comment with

but as alluded to, that depends on your option settings

Can a moderator please close this thread before it goes completely off the rails?