Macro Recorder Events and / or Starting Macro Recorder

[niceguymattx]

This is really stumping me:

Is there any events associated with the record macro action in excel?

OR / AND

Can you fire the Record Macro procedure from within vba code, in excel?

Thanks for any help

Matt

 

[SkipVought]

Hi,

Not that I know of, but...

...what's the REASON for wanting to record from within a procedure?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[niceguymattx]

What I was trying to build was a tool that allows you start the macro recorder, and view & edit the code that it produces in real time. So you could effectively edit or copy the code as the macro recorder created, and use other tools with it.

It should be fairly simple to accomplish, but I wanted it to fire when you start the macro recorder. So i figured either have it run via an event (that doesn't seem to exist) or actually overwrite the macro recorder startup procedure (but you cant fire the macro recorder from code).

There are other options which I'll explore, but it looks like I've fallen at the first hurdle!!!

Matt

 

[fumei]

View and edit the macro code...IN what? Are you thinking of making some view/editable window to see and edit the macro code...as it is written into the VBE? What? What? How are you going to see it?

Gerry

 

[niceguymattx]

Yeah, the idea was mainly to build a view.

As for the code itself, it is written into module as you record each action. So I would have to read it from there, probably using the extensibility library.

I'm still at a proof of concept type phase at the minute, but I dont think it should be that difficult.

Matt

 

[fumei]

I am with Skip on this one. WHY is this desired?

I don't use the macro recorder much anymore. I write most of my procedures directly, in the VBE. Generally, and of course there are exceptions, using the macro recorder is a way of figuring out what to do. I fail to see where someone requires this, that they are going to have the expertise to edit...on the fly.

the idea was mainly to build a view

Yeah, I gathered that...but you did not answer the question. HOW are you going to do that? You are going to create a new document window?

Frankly, this seems kind of silly. You would have to be constantly retrieving text line from the VBE. PLUS, what ever you have in the window will NOT be what is in the actual module - it will be in the window. Which means at the end you will have to get that text back into the module in the VBE.

Seems like a heck of a lot of processing...for what? Please explain, as Skip asked.

Like I say, it seems like a lot of processing for little gain. After all, you can make the application window not max, have the VBE open (not max) and do exactly what you are describing. In other words, you can do it now.

Gerry

 

[SkipVought]

Gerry, you nailed it.

Matt, although there is already a way to SEE what code is being generated INTERACTIVELY, I, frankly, find that dual view unremarkable as it has never really added any value to the macro recorder in my opinion. And, since the macro recorder does not REALLY generate MACRO CODE, that merely duplicated keystrokes, but it generates VBA object related code, the keystroke to code relationship is not really that important. It's pretty easy to figure out what's happening just looking at the code.

If you want some feature that helps you discover Object Properties and Methods, then check out

How to use the Watch Window as a Power Programming Tool faq707-4594

and the Object Browser.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment...
Pinot More!}

 

[niceguymattx]

All fair comments.

This isn't something that I was required to do, I was just exploring some ideas for something to do / develop. In actual fact I hadn't even decided exactly what I was going to do. This wasn't a solution driven thing.

Anyway, you seem to have answered my questions with a resounding no and no.

 

[fumei]

Chalk one up for sanity.

Gerry

 

[fumei]

I would also like to point out that recorded code is very often NOT the most efficient code. For example the recorder always uses the Selection object. It will NEVER use a Range object.

I am not sure I agree with Skip's comment that the recorder does not record macro code. It sure seems like it does. If you DO split windows so you can see the procedure in the VBE, and also the application windows where you are recording, the code lines in the procedure update dynamically with every completed instruction.

But again,....so what?

Gerry

 

[niceguymattx]

About the macro code thing, not sure if Skip was talking about the old Excel4Macro code from older versions?

But on the subject, I am well aware that Macro recorder generated code is not that efficient. Unofrtunately I have to deal with it all the time in my job (not my choice), and my half baked idea was generally working towards educating the people generating this stuff. With the view to either stopping it, or at least cleaning it up.

It does appear that there were obvious gaps in what I had in mind, but as I said before, I was just exploring the idea.

 

[combo]

Just for fun to test the idea (I totally agree that this is useless) and write something here. Worksheet with CommandButton1 and its code:

Private Sub CommandButton1_Click()
Application.CommandBars.FindControl(ID:=184).accDoDefaultAction
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim vbCompCM As VBIDE.CodeModule
Set vbCompCM = ThisWorkbook.VBProject.VBComponents.Item("Module1").CodeModule
MsgBox vbCompCM.Lines(1, vbCompCM.CountOfLines)
Set vbCompCM = Nothing
End Sub

combo

 

[xlbo]

Skip / Gerry - please bear in mind that some of us have to try and educate people who think that they can 'write vba' when they mean that they can record a macro. I assume this was to be used to allow these users to tweak the recorded code as it was produced - maybe with various auto - options such as 'Strip Out Selects' or 'Remove scrolling' or 'Stop window flicker' etc etc. Our job is not just about writing code but allowing others to do their job - sometimes that means creating things that might sound odd but would actually save someone a lot of time and effort


Bit surprised at the animosity generated against this actually - would've thought it might pique someone's interest...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Hmmmm. I must be getting unknowingly crabby. I did not feel any animosity at all.

sometimes that means creating things that might sound odd but would actually save someone a lot of time and effort

Frankly, it did not seem odd at all, not really. My point was that I do not think it would save any time and effort. I still do not. Plus the development time to do it has to count for something.

My point was that someone, right now, CAN look at the procedure as it is created. Make the VBE and the application windows share space. You can see the code, you can edit the code. So the end result - to see and edit the code window real time - is attainable without any development of anything different.

So....

1. why develop something (which I am still not sure can really be done)?

2. how does this actually help anyone? How does this educate anyone?

I totally agree that educating and assisting people is a good thing. Absolutely. I try to do so all the time. But I fail to see how this does- especially when the functionality (see/edit code real time) is do-able without anything new required.

Curious? Yes, I am. In fact, I am trying myself to do it, out of pure interest.

Geoff, could you expand on the idea that making a new, separate, view window for recording code - if it can be done - helps someone to their job?

Gerry

 

[xlbo]

Gerry - if you worked with some of the people here you would understand. We have had to design an intranet MIS that basically mimics the functionality of windows explorer simply because certain people have a complete mind block about using it - we would prefer to teach but it is much easier if you give them soemthing that is essentially the same but tell them it is different !! That is what this is all about - dressing up a very simple thing that you can do with a bit of logic and thinking and replacing it with something that does exactly the same but that the user thinks is an entirely new thing - if it is a 'new thing', they will learn it - for some reason they refuse to learn skills that they should already (IMHO) have

Basically, we have "commercial acountants" who think they are whizzy on excel and reckon they can "write code". Problem is, all they do is record code - usually pages and pages of it to do a simple calculation - this causes us all kinds of problems later on so we wanted to create something to help them 'write' better code

Maybe it doesn't need to be in real time but the basic premis is that they would have a set of options available - like a little Add-In menu - they could then choose options such as

'Remove Screen Flicker'
'Remove All Select/Active Statements from selected code'
'Remove Scrolling'
'Add variable'
'Reference Variable'
'Add comment'

These items would then be carried out on the code to clean it up and (hopefully) mean that next time we have to debug something they have written, it only takes a few minutes instead of a few hours !!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

OK, I understand. Essentially a "user friendly" kind-of, sort-of code editor/creator. An "insert here" code snippet interface.

Could be done, but I'm not sure it would decrease your debug time. I will take your word for it, but it seems that if they CAN put these snippets - and by the way that was certainly not suggested in the original post - how on earth can anyone determine at the time if it is in the correct place.

Sigh, I sympathize. Really I do. We have had the same thing for years. While I resisted the management level decision to do so, I am now in agreement with our new policy. Which is:

You can do create all the development macros you want.

ALL macros you create are only to be used on your machine. There can be NO distribution of your macros/code.

If you can not / will not learn to use code properly, while you may still do whatever you want, if anything goes wrong your computer will be re-imaged to standard. As soon as it is reported (by you) that you have a major problem, OR it is determined by performance that there IS a major problem, re-imaging will occur. You will NOT be notified of when this will occur. You will NOT be permitted to save any personal data to a server. You will NOT get any technical support for anything you have created.

Any macro code (either in concept or actually developed) that you feel is useful for others can be submitted for approval.

We offer an in-house course on design and development of Office macros. It is available to everyone. Time to take it is freely given by management. That is the deal. You learn...or you don't. If you don't (or won't)...you are on your own. Period. We will not support it. Any mess ups are your responsibility, and will be cleared not by support, or troubleshooting, but by full re-imaging. Period.

Geoff, I really do understand the issue.

for some reason they refuse to learn skills that they should already (IMHO) have

We have 30,000 users on our network. We now longer accept this. These are supposedly computer literate people. We no longer coddle them. We DO offer constant training. I would not be comfortable about this policy if we did not. However, we DO. In a multitude of forms - web-based, instructor led, short little "lunch & learn" sessions, peer groups...you name it.

Not only that, but we test 'em. We actually developed an in-house Office skills assessment tool. It is mandatory. Every position has had a skills level determined for it, and these are also constantly looked at. For example, a manager may need level 3 (out of 5) skills in Word, but the secretary needs 4 (maybe even 5). But the manager needs 4 in Excel - depending of course on what they are the manager of.

In any case, it drills down deeper than that. The assesment tool can pin point specific areas of, say, Word, that are weak for that person. However, say that area is NOT an strong part of their job. Then, OK, they do not have a serious need to learn it. But if it IS a strong part of their job...then they do need to learn it. So our in-house training is quite modular. Edible chunks. It was absolutely useless to send staff on a week course - use it or lose it...right? So they lost it. We now have bit size learning chunks.

A chunk that ONLY deals with tables in Word, for example. And using that example....we had this executive secretary that just did not get even the basics of using tables. (Even though they rated themselves as "High" in their knowledge of Word.) Every week - EVERY week - this person went to the local tech to get help on dealing with this table. The tech would help. We stopped that. The tech now refuses to help, and IMHO rightly so. It is not their job. The information to do it correctly is available to the person. If they resist learning...that is their responsibility and decision. If it affects their performance enough to put their position in jeporady...so be it.

I am not talking about way out there sophistication. I am talking about basic skill sets. These, in this day, are required skill sets. In most cases it is a skill set the person states they have. Fine...then...well that is that. You say you have them, then use them...if you DON'T have them (and even though you state that you do) we offer a vast array of ways and means to learn them. If you DON'T learn them...tough.

In terms of basic literacy and skills - the time is past for coddling. Sorry. If people persist on thinking they know more than they do - well:

1. test 'em
2. offer to assist them to learn
3. give 'em the tools to actually do that
4. give 'em time to actually do that
5. test 'em again

Gerry

 

[xlbo]

Gerry - well stated and very true. We are obviously a much smaller (in terms of employees) business here - just 300 or so at head office with probably the same again in the field. Whilst management are trying to bring in competency based role profiles, it is resisted heavily as the culture is to "just do it". There is not much in the way of technical training so we cannot rely on that.

My own personal opinion is that the people I work with are (supposedly) adult and should be able to think and learn for themselves - it can get VERY frustrating as the business continues to steadfastly refuse to admit that a large % of its employees just are just not up to even basic standards in basic MSOffice applications. Hence the need for trying to achieve (what should be) a pointless exercise !!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

I hear you. It was a long difficult haul to move to a competency based system. BTW: the biggest (by far) resistance was from management. Once staff understood that it was not a job killer, but a job enhancer they could buy into it...well sort of. But it can only really work if managment is willing to let staff have time to learn, AND is willing to put some money into it.

Hahahahahahahaha. Yes Virginia, there really is a Santa Claus.

But we have pretty much got there. The major thing to getting there was in fact the assessment tool. It produced real numbers, and proved that a lot of staff were not up to speed on Office products. It proved that a gigantic amount of time was taken up with repeated peer-to-peer "learning".

The other thing was that by getting assessments of staff in a particular office we could arrange training by need. We could gather people from various offices who needed similar training - rather than have an office all get the same wholesale training. This created classes where everyone was at the same level. Instead of classes with widely disparate levels. This helped the participants (as they knew everyone else was at the same level; no very advanced people mixed with tenderfoots), and it helped the instructors as they could focus on the entire class.

All in all, real competency based learning works well.

Gerry

 

[xlbo]

agree totally Gerry - management are the roadvlocks here too - my last appraisal was 3 months late and was pretty much already written before we even sat down - doesn't give you much faith in the system !! Basically, the big bosses want to implement this but they will not give the middle managers any slack on their targets so the middle managers just don't implement the training requirements because it means losing staff for a time :-( oh well - doesn't bother me really - I just teach myself anyway

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

I do myself as well....hmmm, that sounds a wee bit off...anyway, self starters have never been the problem. Mind you self starters who think they know everything ARE a problem.

Hahahaha.

Gerry