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?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
in programming there are things like dictionaries, stacks, queues, collections that will not be created via the macro recorder. also, arrays and some uses of recordsets & similar.
so purely learning from the macro recorder will not make for a complete programmer
and purely learning from a book will not be adequate. best IMO to do lots of programming. and lots of Excel too!
some books are fantastic resources; internet resources (via google) are endless & unbeatable
best wishes on the journey

PS. even simple looping can't be done via the macro recorder. there are many different ways to loop & branch progam execution. simple IF statements, SELECT CASE, etc, etc
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

makiwara

Board Regular
Joined
Mar 8, 2018
Messages
135
in programming there are things like dictionaries, stacks, queues, collections that will not be created via the macro recorder. also, arrays and some uses of recordsets & similar.
so purely learning from the macro recorder will not make for a complete programmer
and purely learning from a book will not be adequate. best IMO to do lots of programming. and lots of Excel too!
some books are fantastic resources; internet resources (via google) are endless & unbeatable
best wishes on the journey

PS. even simple looping can't be done via the macro recorder. there are many different ways to loop & branch progam execution. simple IF statements, SELECT CASE, etc, etc

Seems I can't explain my question well... :))
I know everything about VBA... The question is not this.

If I know general VBA, + can use macro recorder --> is this the key for solving EVERY problem?

So the macro recorder can show every object, property, method? If yes, then I dont have to focus on memorizing objects, properties etc... if i want to do something, I record a macro and build up the logic using VBA (conditional statements, loops, VBA built in functions etc)

Or macro recorder is not enough for the "identifying every Excel specific object, method, property etc " part and I have to know these things from memory?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
My apologies - I think you're explaining the question perfectly, I didn't read it sufficiently. The answers you've already received from others excellently answer your question.
 

makiwara

Board Regular
Joined
Mar 8, 2018
Messages
135

ADVERTISEMENT

I find that unlikely. I've never met anyone who knows everything about VBA.
I dont know everything of course, I just wanted to emphasize the other part which is not get answered. But nevermind :)
 

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
I think the other part has actually been answered pretty comprehensively over the various responses you have had. In brief: the macro recorder can record about 99% of what you can do manually in Excel.
 

makiwara

Board Regular
Joined
Mar 8, 2018
Messages
135

ADVERTISEMENT

I think the other part has actually been answered pretty comprehensively over the various responses you have had. In brief: the macro recorder can record about 99% of what you can do manually in Excel.
thank you, this was my question :)
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,922
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
tl;dr: Yes there are plenty of things that aren't exposed up in the macro recorder.

I often tell people that my favorite programming tools are the macro recorder and Google. I use the macro recorder to learn the syntax I need for a particular task, and I use Google to find more detailed help. If there's a problem, someone has blogged about it, right, and Google reads everyone's blog.

In general, the macro recorder spits out inefficient code, but usually it provides useful syntax. Not always, and the recorder has had problems keeping up with newer features (newer meaning sometimes as old as Excel 2007). There are even some things the macro recorder doesn't record, and sometimes it waits for a few more actions before some actions are recorded, which is confusing.

The object browser is a good supplement. Make sure you right click on it and choose to show hidden members.

When you use Google, make sure you include helpful keywords. Don't just ask for LoadPicture (which I searched for this afternoon); ask about Excel VBA UserForm LoadPicture. If you encounter an error, ask Google about the whole error message; it's tedious, but type in the entire error message verbatim.
 

Watch MrExcel Video

Forum statistics

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