[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Excel 2010 VBA - Do you use the Object Browser?

Posted on 2014-08-03
Medium Priority
Last Modified: 2014-08-18
I find the object browser of limited usefulness.  Am I the only one?

Some subs/methods are displayed as functions.
It doesn't make sense because a function returns a value and a sub does not.

am I wrong?
Or are there some uses for the object browser that I'm missing.
Question by:brothertruffle880
  • 2
LVL 50

Expert Comment

by:Martin Liss
ID: 40238179
Some subs/methods are displayed as functions.
What do you mean by that?

If you open the Object Browser and click on a class like 'ComboBox' it will show you all the events, properties and methods. Clicking on one of them gives helpful information.
LVL 40
ID: 40238324
It's the best tool you can get to explore what is available in a library, and what are the properties and methods of each class.

And it still have a usable search mechanism, something that is missing in the last versions of the Office help.

In many instances, it's a lot faster to search in there than in online help to find the property or method that will do something you need. Once you have found it there, then F1 brings you in Help with more details.

Author Comment

ID: 40260519
HI Martin:
Range.Activate is also a sub (method) because it does not return a value but it is listed as a function in the browser.

Or do I have a misunderstanding.  (I'm still on my road to mastery!)
LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 2000 total points
ID: 40260620
This is because Excel is not coded in VB, it is coded in C++. If you explore the Explorer further, you will see that most of the methods that do not return a value are also presented as Function. There are relatively few Sub.

Without the source code, it is hard to explain exactly why this is so, and the tool that extract the information from the library to present it in the Explorer might also be at cause here, but here are a few possible pointers.

C++ does not make the distinction that VB makes between a Sub and a Function. In C++, what you call a Sub is a Function declared as type void instead of one of an Integer or a String or whatever. This is hidden for the VB developer because he would not know what a void is.

C++ also enables you to have many methods with the same name, as long as they do not have the same set of parameters. It's called overloading, something that does not exist in VBA. Some of these overloaded methods might also be declared as internal, once again something that does not exists in VBA. It's an alternative to Private and Public that leaves the method visible inside the application in which it is defined (Excel), but hides it from external applications such as VBA. This might come into play when the Object Browser builds its list.

If you really make the differentiation between a Sub and a Function in the object model, simply look for a As clause at the end of the declaration that is presented.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question