Is there any Excel VBA property, method, object etc which can't be observed using the macro recorder?

[makiwara]

I have a cognitive overload of how much different properties, objects, methods Excel has.

So my question is:
- have you ever tried to do something in excel which couldn't be done manually in excel? I mean is there any situation where you can't rely on the macro recorder?

--> if not really then my theory is: if you can do almost everything MANUALLY in Excel and record it using the macro recorder then all you have to do is simplify the code you get and build up the code logic around it.

So "the remembering objects, properties or methods" part is not part of my theory. Would you agree with this or not? I learn from a VBA book now and I feel dumb because there are so much examples with so much properties etc, for example how to generate a pivot table using macros... and I always forget that presumably neither the author remembers these things and uses the workflow I wrote about in the bold sentence.

So can I become good at VBA if I only now the language basic really well + can handle the macro recorder + can google?

Or I can't avoid having a lexical knowledge of objects, properties, methods to be let's say a VBA guru in the long run?

 

[RoryA]

How would you define a guru?

FWIW, there are sometimes things that the macro recorder simply doesn’t pick up and there are a few things that you can’t do in VBA that you can in the UI. I’m not aware of anyone who has memorised the Excel object model though.

 

[Wookiee]

Although the macro recorder is a very good source of code, I know there are plenty of things it won't capture (although specific examples escape me at the moment). Also, there's a lot of stuff that gets included in recorded code that you don't need to include. But I think it's important to have a working knowledge of how to do what you want manually in Excel rather than trying to code from scratch. While everyone learns and works differently, I wouldn't suggest trying to memorize every method or property of objects. Most of my Excel skills come from trial-and-error, recording macros, then checking forums when what I attempt fails to work (or work as I expected).

 

[makiwara]

Guru like you for example.. 35.000 answers, moderator status... i would like this level, this is guru in my eyes

How would you define a guru?

FWIW, there are sometimes things that the macro recorder simply doesn’t pick up and there are a few things that you can’t do in VBA that you can in the UI. I’m not aware of anyone who has memorised the Excel object model though.

 

[RoryA]

Moderator status is nothing to do with ability (in VBA or otherwise) - it’s just about helping to run this forum.

Personally, I would recommend learning the VBA language itself, independently of any specific object model, and then learn how to automate the application(s) you need.

 

[makiwara]

Moderator status is nothing to do with ability (in VBA or otherwise) - it’s just about helping to run this forum.

Personally, I would recommend learning the VBA language itself, independently of any specific object model, and then learn how to automate the application(s) you need.

Thx for the answer. So basically you agree with me theory?

 

[RoryA]

Partially. Recorded code is generally so inefficient, it‘s often a lot quicker to write from scratch than edit recorded code. Of course to do that, you do need to learn some things.

 

[makiwara]

Partially. Recorded code is generally so inefficient, it‘s often a lot quicker to write from scratch than edit recorded code. Of course to do that, you do need to learn some things.

Yes I know, iwould only use recordings for discovering methods, objects, etc. But seems nobody answers this part of the question. So if I know VBA in general + use macro recorder, is this everything I need? Or are there methods, objects etc which cannot be revealed using macro recorder? Assume that I am good at VBA, my question is will the macro recorder show every object, method etc which I need to work with? Hence I don't really need to memorize anything.

 

[RoryA]

Regardless of the macro recorder, you don’t need to memorise anything. You always have the object browser and search engines. But if you don’t learn anything about the object model, you’ll waste a lot of time. Think of it as the difference between being able to speak a language and having to look up every word in a dictionary.

 

[mjbeam]

Rory gave you a good tip. Check the Object Browser in the VBA editor. If you poke around in there you can discover a lot of things. It's in the View menu.