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

makiwara

Board Regular
Joined
Mar 8, 2018
Messages
135
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?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,651
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Nov 27, 2012
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Mar 8, 2018
Messages
135
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,651
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Board Regular
Joined
Mar 8, 2018
Messages
135
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,651
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Board Regular
Joined
Mar 8, 2018
Messages
135
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,651
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Apr 6, 2002
Messages
756
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,951
Members
410,713
Latest member
TaremyLunsil
Top