# Off Topic > The Water Cooler >  >  I need a macro to do this ...

## teylyn

Hi all, 

please bear with me while I rant. 

Recently I find an increasing number of posts asking for macro solutions to do what Excel does with formulas and/or user interface functionality.

Why is this? Are people getting lazy? Or is a general understanding of what a spreadsheet does no longer a given?

Is it easier to hop on an Excel web site and return with a macro than to spend a few minutes working on a formula that does the same thing?

So many requests for looping through data and summing/categorising/sorting data. 

I find it hard to believe that all these people asking for macros are using Excel strictly as a scripting tool.  Much more likely they just don't know which command or formula to use and cry "I need a macro to do this", just because they can't imagine that it can be done without VBA.

What is your take?

----------


## Domski

A lot of people see VBA as a book of magic spells that can cure anything (it can perform some pretty cool tricks). I think the fault in a large way lies with the type of training people are given in Excel, if at all. Even 'advanced' courses that I've seen the specs for generally focus on the features of Excel rather than good spreadsheet design or really explaining formulas etc. They then jump on the VBA bandwagon because they just don't know what can really be done.

Don't get me wrong I love tinkering with VBA and use it quite a lot to make my job easier and to be honest some of the formula that some of you guys come up with make my eyes water but if I recognise that a request for a VBA solution doesn't need one I'll explain that before providing any code.

I had been thinking recently about offering some training to people who I work with, finance guys mainly,  on getting down and dirty with some complex (for me anyway) formula. Unfortunately I mentioned this to the powers to be and was shot down in flames that it wasn't my job to be taking over from the training department's duties. Hey ho.

Dom

----------


## romperstomper

I think you are correct in your assumption that people just assume some things will require code. Often these seem to relate to what I would use a pivot table (or a database!) for.
OTOH, I'll use VBA over a formula everytime once the formula reaches a certain complexity (I'm not Daddy, after all!)

----------


## DonkeyOte

I think you mean "Daddy" Ror.. I mean romperstomper ... too many forums  :Wink:

----------


## romperstomper

Why? What did I say?  :Wink: 
You're quite right, I did mean DDL (names changed to protect the guilty now).
And I'm only in 6 forums at the moment  :Wink:

----------


## Domski

So few!!! What do you do with the rest of your time?

"DDL", not the best mask you could have come up with  :Wink: 

I noticed another fairly notable addition to this forums membership today who has joined within the the last couple of weeks.

Dom

----------


## romperstomper

> So few!!! What do you do with the rest of your time?



Don't tell anyone, but occasionally I do some work,  :Wink: 




> I noticed another fairly notable addition to this forums membership today who has joined within the the last couple of weeks.



Care to elaborate - there are too many to go through!  :Smilie:

----------


## mdbct

Imagine if you will...

Bossperson: Hapless UInderling, I want you do create a macro that will....
Hapless Underling: But honorable Bossperson, you can do that with a couple formulas and....
Bossperson: I don't want a formula, I want a macro - one button and done
Hapless Underling, after crawling back under their rock and firing up their browser: Dear excelfoum, I need a macro that will...

----------


## shg

The unfortunate thing is that many OPs use the forum in lieu of learning Excel, instead of as a means to do so. People happily install code that they neither understand nor can maintain, not even to adapt simple range references. 

Not clear we're doing them any favors.





> OTOH, I'll use VBA over a formula everytime once the formula reaches a certain complexity



I agree with that as well. I have a few hundred pages of atomic VBA routines that I use all the time.

EDIT: Teylin responded to an OP yesterday (I think), who had a link in his sig to his website, where he offers Excel consulting. She taught him how to hide columns  :EEK!:   :EEK!:   :EEK!:

----------


## DonkeyOte

> Care to elaborate - there are too many to go through!



I suspect Dom is referring to our learned friend from NZ with the penchant for World Cup spreadsheets...

----------


## romperstomper

Ah, yes!
Some very entertaining usernames signed up recently though...  :Smilie:

----------


## Domski

> I suspect Dom is referring to our learned friend from NZ with the penchant for World Cup spreadsheets...



And the prize goes to DK. I only noticed the post in the Development forum with the link to the world cup book after I posted that.

Dom

----------


## NBVC

> EDIT: Teylin responded to an OP yesterday (I think), who had a link in his sig to his website, where he offers Excel consulting. She taught him how to hide columns



Which I am sure he passed on to his client and made money off of...  :Roll Eyes (Sarcastic):

----------


## royUK

I seen this tendency for years & refuse to offer code when Excel doesn't need it.

This username is an Excel consultant!

----------


## NBVC

Roy.. your link goes to Page Not Found....

----------


## DonkeyOte

> Ah, yes!
> Some very entertaining usernames signed up recently though...



Now your turn... who else then has caught your attention of late ?

----------


## royUK

I've fixed the link in my earlier post

----------


## parrynz

> Why is this? Are people getting lazy? Or is a general understanding of what a spreadsheet does no longer a given?



Hi Teylyn, your astute observation is quite correct and the problem is not limited to this forum or even just Excel. Ignorance is not so bad to me since hopefully just some education is required. I guess it's partly the responsibility of the person answering to point out there is built in functionality to do things as opposed to blindly just giving them the solution on a plate. I don't mind spending extra time with people if they are obviously trying even though it can be frustrating at times.

Laziness is the bigger issue. I remember when I first started work many years ago that you ask alot of questions when you first learn a new job but after a while you find it easier just to ask people than investigate yourself. I had a really good boss who I think invented RTFM as he always used to say "what part of the manual are you looking at and I will try and help you". A subtle kick in the a**  :Smilie: 

Cheers,
Graham
A Kiwi in NZ  :Smilie:

----------


## shg

Some time ago, we discussed having members provide a self-assessment as to their capabilities so we had some idea how to frame a response. That never came to pass.

It would be convenient if the mods could rate members on three scales, with the average mod rating visible (to mods only) for each category:

*Ambition:* 0 (won't look at his watch if can ask someone the time) to 10 (soaks things up like a sponge)

*UI:* 0 (how do I add two numbers?) to 10 (DLL)

*VBA:* 0 (how do I insert code?) to 10 (Andy Pope, RomperStomper)

----------


## romperstomper

> Now your turn... who else then has caught your attention of late ?



Well, I'm guessing that f#?!off (censored by me - the actual name is not!) was having a bad day when he/she signed up...  :Wink:

----------


## parrynz

> Some time ago, we discussed having members provide a self-assessment as to their capabilities so we had some idea how to frame a response. That never came to pass.
> 
> It would be convenient if the mods could rate members on three scales, with the average mod rating visible (to mods only) for each category:
> 
> *Ambition:* 0 (won't look at his watch if can ask someone the time) to 10 (soaks things up like a sponge)
> 
> *UI:* 0 (how do I add two numbers?) to 10 (DLL)
> 
> *VBA:* 0 (how do I insert code?) to 10 (Andy Pope, RomperStomper)



Being the new boy on the block I think this is a good idea although I think it would defeat the purpose of not displaying this for everyone - it assumes only Mods answer questions.
I can't imagine lazy people would answer truthfully to Ambition (0 rating - who's going to answer these peoples questions?), although it would be a shame not to include it solely to include the comment "won't look at his watch if can ask someone the time" - Classic! :Smilie: 

Personally, I would have only 2 categories being General Excel/Formulas and Programming and within these only 3-4 set options. Other forums such as Ozgrid has one generic experience rating but I like the way this forum clearly differentiates General from Programming. To make it useful it would need to be a mandatory info when you register.

----------


## DonkeyOte

> Being the new boy on the block...



First rule of ExcelForum.com:  There is no ExcelForum.com (well, that's generally true about twice a week)

Second rule of EF.com: ideas put forward by the members shall be treated with equal disdain / bemusement by the owners and shall be discarded irrespective of merit forthwith.

(didn't someone once mention reorganising the forums............)

shg, you should know better...  :Roll Eyes (Sarcastic):

----------


## shg

> I can't imagine lazy people would answer truthfully to Ambition ...



That was the point of only allowing mods to assign the ratings (via a simple average), and only allowing the mods to see it. And I think mods would have a much more accurate assessment than the OPs themselves. 





> shg, you should know better...



Actually I do; still, it would be a nice system. If Simon's forum grows, we could ask him if it could be implemented there.

----------


## Domski

I think the number of forums here is a little OTT but then I think that Mr E&*($ could maybe split to a few more.

The rep system is interesting. I joined with pretty much the sole intention of answering peoples problems as I find the vast majority of the time if I don't know the answer to my own I know where to look to find it. Sometimes you'll get rep for the simplest answer and then spend half an hour coding something to get no rep but almost always the appreciation of the OP which at the end of the day is more than enough.

Having a mod determine your skill/endeavour etc could be a little contentious but not a bad idea in general. It might give a real heads up to some folk about their own skills and inspire them to improve whilst putting the noses of others out of joint. 

Speaking of rep I am slightly embarrassed that I gained my 3rd splodge by cracking a joke, but it was quite a good one I think  :Wink: 

Dom

----------


## Domski

> If Simon's forum grows, we could ask him if it could be implemented there.



Maybe a passport system recognising contributions elsewhere would be a nice idea. The top forums all know who they are but alas wouldn't necessarily agree to such a thing.

----------


## broro183

> ...
> Actually I do; still, it would be a nice system. If Simon's forum grows, we could ask him if it could be implemented there.



Hold on Shg...
Don't you mean when it grows?

Domski, I like your passport idea but agree - which Forum's would consider it?

Mods/former Mods,
DO's "second rule for EF" sent me looking for the absent Super_Admins & the traces seem to be a week old. Has there been any feedback to the  Mod's?

Graham, welcome aboard, it's good to have another Kiwi join the ranks  :Smilie: 

Rob

----------


## teylyn

Rob, 

Re www.thecodecage.com : It's growing all right! No "if". The "when" is well underway.

Re Super_Admin: nope. No comms to mods, either, unless you count having been told of for mentioning www.thecodecage.com

----------


## broro183

hi Teylyn,

Long time no chat - how's the 'Naki?

LOL, tsch tsch!
I'm doing my bit to help with the growth  :Wink: 

BUT it is a bummer about the lack of comm's here  :Frown: 
Perhaps there could be a subsection for comm's in a rating system. It could be quite telling...

Rob

----------


## DonkeyOte

> Having a mod determine your skill/endeavour etc could be a little contentious but not a bad idea in general. It might give a real heads up to some folk about their own skills and inspire them to improve whilst putting the noses of others out of joint.



Worth nothing that Mod status on this forum is not meant as a reflection of technical expertise so the Mod group be the sole arbitrator on such matters is flawed IMO - though undoubtedly better than nothing 
(and FWIW I made exactly the same point when I was a Moderating myself).

Regards reputation / skill level etc... to me this raises the age old debate as to whether or not the user tags currently in operation server any purpose whatsoever ?

The current settings of Guru, Valued Contributor etc are in essence automated (somewhat mysteriously) based on longevity and post count - occasionally the Mods have requested certain users have their tag changed manually to Guru to reflect their (blatantly) obvious skill level.

The result of the above is that post content matters little in determining your tag - you could post questions for 3 years and be a Guru... it doesn't really make sense on that basis to have these tags infer any kind of skill level.

This type of system is another area where EF differs to most other forums of it's type where the tags are either:

a) directly related to "points" gained (ie rep. awarded by other members - eg ExpertsExchange) ... 
_presently all that happens here is that you get a few blocks and occasionally the html title changes (not visible other than by hovering over the blocks themselves)._

b) where set manually - determined by other recipients of the "award" - eg MrExcel MVP

Rather than adding yet further information into the mix (visible only to a select few) I would simply suggest that either one bases the tag on the rep. system or it is instead determined manually by an appropriate peer group (Mods or other).

I would reiterate though that unless it's something that can be achieved by Roy then put simply it won't happen... unless of course, if opting for the Mod only system, they simply put it in a spreadsheet.

First priority for Vai and co. is the board structure and we've been waiting for action on that for around two years (was certainly being touted at the time I joined).  
A board revamp is undoubtedly a big exercise however it would be far more valuable to the board as a whole than say revamping a few style sheets (badly) and/or trying to capture user ability.

----------


## teylyn

Sometimes I wish this forum would only accept posts in plain language.

----------


## tony h

> Sometimes you'll get rep for the simplest answer and then spend half an hour coding something to get no rep but almost always the appreciation of the OP *which at the end of the day is more than enough*.



... I would also accept cash  :Smilie: 

I like the rep system even though, as people have mentioned, you get at maximum the same rep for a one line no-brainer as for a couple of hours work. But I think it would also be good if the reps were split by "received as responder" and "received as OP".

I will give reps to OPs who put effort into the solution. I am not so bothered about their skill level, especially if they are trying something new, but more their willingness to get their hands dirty, to explain things properly etc. In this the rep could work well (maybe mods could have the ability to post a -ve rep. 
All being publically visible this I would have thought would encourage a more professional attitude amongst OPs.

----------


## teylyn

Tony, the majority of OPs are one time wonders. They have a question, they ask it, they receive a reply that satisfies them and then they move on. The next time they have an Excel question, they may not even remember that they have a user account here and end up at some other web place.

Trying to educate new OPs who scoff at having to read the rules to come up with a proper thread title, but expect ppl to put in their free time to produce a solution, is rather unsatisfying.

The few who come here regularly are clearly exceptions.

----------


## tony h

> "what part of the manual are you looking at and I will try and help you".



Isn't that have the problem. When I started everything came with a manual. Now it isn't even an optional extra ... because everything is available on the internet.

I don't think there is any substitute to having a manual which you can leaf through. Partly because as you look one thing up you often happen on something else useful.

----------


## romperstomper

> I don't think there is any substitute to having a manual which you can leaf through. Partly because as you look one thing up you often happen on something else useful.



I couldn't agree more - that's why I keep reading Excel books: there's almost always a little gem (or a reminder of one, if you're as forgetful as me) somewhere in there!  :Smilie:

----------


## pb71

Hi all, I'm new to this forum and have just discovered the Water Cooler. It may be old news to you, but this takes the biscuit ... LOL
 :Smilie: 





> I seen this tendency for years & refuse to offer code when Excel doesn't need it.
> 
> This username is an Excel consultant!

----------


## Originalgoth

> Imagine if you will...
> 
> Bossperson: Hapless UInderling, I want you do create a macro that will....
> Hapless Underling: But honorable Bossperson, you can do that with a couple formulas and....
> Bossperson: I don't want a formula, I want a macro - one button and done
> Hapless Underling, after crawling back under their rock and firing up their browser: Dear excelfoum, I need a macro that will...



In that case Hapless Underling should work out the formulas & get them in the sheet and then have a macro that just has a message box that says.........

"I told you it could be done without a macro!!!!!!!!"

 :Roll Eyes (Sarcastic):

----------


## Mordred

I think you all do a splended job and this site, which wasn't my first to go to, has helped me in uncountable ways.  Furthermore, I like the way regular joes like myself can try to help others without being scoffed at, at least not on the forum.  If advice is wrong, then one of you pros correct it without so much as a boo.  I do get bothered though (and this happened just today) when someone like myself is helped, but am degraded as amateur and not analyzing code very well when a function that works is used.  Why is that?  I'm not one to get rattled very easy by people but to get upset at me because I used code that is not "cool" or widely accepted, even though it works for my application, seems....I don't know, pompus?!  I understand that everyone will have an opinion about what code to use for what but when it comes down to functionality, why should it matter.  Argg.  Sorry about the rant but one of the members struck a nerve on me and this is how I'm going to let it go.  I feel better already :Smilie: 

Me

----------


## royUK

Post a link to the post

----------


## DonkeyOte

see post 13

http://www.excelforum.com/excel-prog...statement.html

mordred - as time goes on you like all of us will learn to treat some posts as gospel and others less so.

I would just add to the post in question that in 15 years of coding its fair to say I've written plenty of bad code and to this day still do.

I'd say that holds true for most people - anyone who infers otherwise probably does not spend enough time around boards like this to know what really good code looks like  :Wink:

----------


## royUK

I don't think it was personal, Andrew is stating his opinion.

----------


## DonkeyOte

I guess that comes down to interpretation...

Regardless of the supposed slight - making unequivocal assertions is generally ill advised IMO.  
Such statements are, as most of us have found to our peril at one time or another on these boards, generally asking for trouble [red rag bull etc...]  :Smilie: 

_As a rule I think most of us tend to allow for a margin of error in our advice because we know the rompers & popemeisters of this world often know otherwise and can as a result make us look rather foolish_

----------


## romperstomper

> when someone like myself is helped, but am degraded as amateur and not analyzing code very well when a function that works is used.



I don't think the comment was aimed at you so much as at the use of Goto in general. I see it used too much, usually completely unnecessarily.




> Why is that?  I'm not one to get rattled very easy by people but to get upset at me because I used code that is not "cool" or widely accepted, even though it works for my application, seems....I don't know, pompus?!



Again, I suspect Andrew was merely trying to give you good advice. Goto can quickly turn your code into spaghetti and make it very hard to debug or alter. I have to confess that when I see questions that use Goto a lot, I tend to ignore them unless I have a lot of spare time to figure out the purpose.




> I understand that everyone will have an opinion about what code to use for what but when it comes down to functionality, why should it matter.



Probably the single most important thing I have learned is that maintainability is at least as important as functionality. If you can't read the code in 6 months when you need to tweak it, it's badly written an your life becomes *much* more difficult.

As DO said though, all posts are the poster's opinion and there is almost never *one* true way of doing things. Won't stop most of us from expressing our personal biases though.  :Smilie:  At the end of the day, you have to live with it, so the choice of who to listen to and how to do things is entirely yours.

----------


## royUK

[QUOTE=romperstomper;2363107]
Probably the single most important thing I have learned is that maintainability is at least as important as functionality. If you can't read the code in 6 months when you need to tweak it, it's badly written an your life becomes *much* more difficult.9/quote]

Deciphering some of the code posted here is good practice for this! Some OPs have never heard of commenting code

----------


## Mordred

I interpreted the comment to be at me because I made the thread and "you" and "your" was used.  I have made my points to Andrew personally and hope that this is settled.  Believe me, I don't want sloppy code and I know that code requires maintenance, I try to keep that and other things in mind like reusability of methods.  Also, when my skills are much better, I plan on fully automating right from our sources.  In saying that, I hope to write code that will be more generic and intigratable when full automation happens.  Do I still need to post a link or can we call this settled?

----------


## Cheeky Charlie

> Do I still need to post a link or can we call this settled?



Is really up to you.  Nobody here is going to do anything per se.

I enjoyed where you have taken this thread (off-topic - lol)

It reminded me of a couple of things:
http://www.excelforum.com/excel-prog...naming-it.html
I was unnecessarily terse here, but Dave was very gracious, that made me mellow out and become much more constructive.  This goes to show how anyone can steer something away from ugly just by being polite - perhaps shows how your conversation with Andrew might have gone?

I also remember a couple of run-ins with Roy and DO (both to my discredit).  One where I basically insisted good standard practice was bad because I didn't do it that way, and the other where I got uppity for no good reason at all...

Which brings me to my point (eventually).  There's a thread on the forum about 'thanks for all the help', for me, 'all the help' represents the character development I've gained by contributing regularly here, and, occasionally, having my knowledge totally trounced and/or ridiculed by somebody; and at least as often, having things I thought were great gently improved by people who clearly could have torn strips off me, but didn't feel the need to (shg is good at this).

I don't _really_ need the Excel for my job, but the humility you get from being around these zeppelin-sized-brains is a very valuable asset for me. I don't doubt it has helped me in my career.

My 2p.

----------


## Mordred

Thanks for that Cheeky and you are right, I could have probably responded more graciously.  In the end no harm came from this, we made our peace and all is good.  I'm going to keep learning by asking questions and trying to figure out answers to OP's questions.  I've also decided that tonight I am going to take the GoTo Skip that I used and figure out how to get out of the loop using If Then End If, just so I don't get into bad habits while writing code.

----------


## davegugg

> I do get bothered though (and this happened just today) when someone like myself is helped, but am degraded as amateur and not analyzing code very well when a function that works is used.  Why is that?  I'm not one to get rattled very easy by people but to get upset at me because I used code that is not "cool" or widely accepted, even though it works for my application, seems....I don't know, pompus?!



I would have thought the comment was aimed more at me than you, as I'm the one who provided you the code.  The important thing is don't let it get you down.  Most, if not all, of the experienced members here try hard to teach those of us that want to learn.  Many of us started out recording macros, and now when we look back at those they look SO UGLY!  I've done multiple re-writes on my previous codes in the last two years as I've learned better techniques.





> As DO said though, all posts are the poster's opinion and there is almost never one true way of doing things. Won't stop most of us from expressing our personal biases though.



Romper makes a good point here.  Some here were trained in programming.  Some had programming in school.  Some just took it up as a hobby.  I know for me, I studied math in school, and it has a big influence on the methods I prefer to use when programming.  I see excel as a big R X C matrix, and program accordingly.  I much prefer using multiple loops to get through the elements (or cells) rather than using some of the Range. functions.  I know it can be less (even much less) efficient, but it is easier for me to write and comprehend, especially for complex code.  You need to find your personal style, and adapt that style to each project you do.  Obviously using a GoTo on a ten line code isn't going to get confusing.  But although I love iterating through loops, if I have a project with a ton of rows and columns, I'm going to look for another way to do it.

----------


## Andrew-R

I've just read this thread for the first time and feel I should apologise here - although Mordred and I have already had a PM chat and squared things between us.  It was never my intention to suggest any one individual's code was sloppy/bad/whatever, it was intended as a generic statement - perhaps that sentiment wasn't clear, but in my defence it was late and I was sharing the keyboard with a bottle of Rioja, so I may not have been at my most eloquent.  Sorry, everybody - as the new boy here I didn't want to start any fights.

As for the main topic of this thread - I think that as long as there are people here who are willing to provide their time and expertise for free there'll be no shortage of others who want to use that resource to save them having to think about a problem or learn something new.  I can't imagine any amount of moderation techniques will make the slightest bit of difference.

That's not *terribly* helpful, is it?

----------


## Mordred

@ Andrew, what I have taken away from all this is to not be so darned sensitive about comments.  I'm just one that prefers constructive critism is all.  
Being a 36 year university student, I've come across many people/students, mostly out of high school, that don't want to learn; they just want stuff done for them because they feel owed it for some reason.  In my first year I took a Java course and found it valuable to help others with some of the logic of writing code (it helped for my own understanding).  I helped one fellow twice with some code and then he kept coming back to me with every assignment.  It even got to the point where I had to say "no" to helping him because he wanted me to write his code, from beginning to end, I felt I was starting to do his work for him.  When I said no, he told me it wasn't fair!!  He never spoke to me again.  I added this to the conversation because you are right, some people don't want to think for themselves.  That being said, at least on this forum, their lack of thinking is my increase in knowledge, either by figuring their problems out for myself or watching others, like yourself, figure out the problems for them.

----------


## Domski

I feel a group hug coming on  :Wink: 

Dom

----------


## Andrew-R

My name is Andrew, and I recognise my prejudice against those who use goto...  :Smilie:

----------


## romperstomper

You're not alone in that club.  :Wink:

----------


## Andrew-R

I wasn't always this way.  One of my early programs (written for the Acorn BBC Micro) ran as follows:




```
Please Login or Register  to view this content.
```


I was writing something which parsed 4-character commands and I couldn't be bothered with coding a long IF...THEN...ELSE statement to cope with them (no select or multi-line If's allowed in BBC BASIC), so instead I took the user's input, poked the 4 characters into the 2nd line of the program, in place of the xxxx (while the program was running!) did a GOSUB 20 and then had the REURN on line 30 return the program flow to where I'd left off.

In my defence it worked perfectly, but in retrospect it may not have conformed with some programming standards.

----------


## Domski

I was useless at programming when I first tried it on the BBC Micro. My computer studies teacher did try to explain to me the error of my ways but it worked and I couldn't see the point in all that structured stuff when a good old Goto would hack it's way to doing what I wanted.

A break of 20 years meant I started with a fresh perspective and swore I would try to learn to do things correctly this time. Stop laughing Rory  :Smilie: 

FWIW only time I use Goto now is in error trapping and instead of Exit Sub where I want to head to the bit at the end off my procedure which turns on screen updating etc again.

Dom

----------


## DonkeyOte

> FWIW only time I use Goto now is in error trapping and instead of Exit Sub where I want to head to the bit at the end off my procedure which turns on screen updating etc again.



What about on Input Dialogs - ie the old Re-Try or Exit process ?  I confess on occasion I use it in those circumstances .. rightly/wrongly

----------


## Andrew-R

To be fair avoiding gotos is much harder without block IF statements, WHILE loops or user-definable functions.

I was self-taught in BASIC, but most of my bad habits were beaten out of me when I went to college and was taught Pascal.  My lecturer was a stickler for structured programming and would go wild if we tried to sneak in a goto - he wouldn't even let us use string variable types, we had to used packed array of char!

Having to produce JSP diagrams for projects, instead of just hacking in the code, also helped.  It's damn hard to put a goto into a JSP diagram  :Smilie:

----------


## Domski

> What about on Input Dialogs - ie the old Re-Try or Exit process ?  I confess on occasion I use it in those circumstances .. rightly/wrongly



I would probably way overcomplicate matters by wrapping those into a Do Until loop.

Dom

----------


## romperstomper

> FWIW only time I use Goto now is in error trapping *and instead of Exit Sub where I want to head to the bit at the end off my procedure which turns on screen updating etc again.*



I am currently chewing on my arm in an effort not to type something rude.  :Smilie:

----------


## shg

> I am currently chewing on my arm ...



The programmer's equivalent of a coyote wake-up  :Smilie:

----------


## martindwilson

try teraterm ttl    its all goto ,do/loop ,call

----------


## shg

> I need to create a VBA macro that generate an excel table (like a checklist) combining data from two different tables ...







> Why use VBA for this?
> 
> On sheet Services,
> 
> In B1 =host!A2
> 
> In C1 and copy right, _(some other simple formula)_
> 
> In B2, _(some third simple formula)_ Copy right and down.







> When you say "Copy right and down" what do you mean?



Oh, my ...                    .

----------


## romperstomper

Now you know why a macro was wanted - just one button click.  :Wink:

----------


## royUK

Don't you just love the use of "simple"!!!!

----------


## JBeaucaire

*sigh* 

I missed the group hug, didn't I?

----------


## Domski

> *sigh* 
> 
> I missed the group hug, didn't I?



We'll have another one next month  :Smilie: 

Dom

----------


## Mordred

I need a Macro that will give me $1,000,000. Anyone?

----------


## JBeaucaire

I could write that for you....but MAN is that gonna cost you....

----------


## daddylonglegs

Who is Goto.......and why should I avoid him?

----------


## shg

Kinda defeats the purpose of having a go-to guy ...

----------


## Andrew-R

> Who is Goto.......and why should I avoid him?



When computers were first invented, in 1980, there were no programming languages at all, and the only way to make computers do stuff was to drip hot solder onto the motherboards.  Two Japanese guys - Goto and Gosub - recognised that this was sub-optimal and, between them, created the very first (and, to date, the best) programming language - BASIC.

To make it run faster BASIC was an interpreted language, so you didn't have to spend ages sitting around waiting for a compiler to run.  This is why, to this day, most supercomputers (such as the large hardon colluder) run BASIC as their main programming language, although some also run Pascal (developed by the French mathematician and philosopher, Blaze Pascal).

Unfortunately Goto and Gosub had a big argument about the development of the next version of BASIC.  Gosub wanted to return to the original design, whereas Goto was keen on heading off in all kinds of wild directions.  The two of them vowed never to work together again.  Gosub went on to develop Visual Basic, so called because you could read what you were typing in - unlike early versions of BASIC, where you just had to remember what you'd typed.  Goto developed a whole new programming language, based on the core 20% of BASIC, called C, but it was very unsuccessful and, to date, only 3 people in the world have learned how to use it.

So most programmers sided with Gosub and the mere mention of Goto's name is a huge taboo in programming circles.

So there you go, the full story ... at least that's what Wikipedia says.

----------


## Domski

I met Goto on a night out once. He said he just had to pop off and do something but he never came back  :Frown:

----------


## teylyn

Gee, nice to see this thread pick up again. LOL, Domski! That's a nice one.

So is a Goto the equivalent of the hubby who nips out to "get a pack of smokes" and never returns?

Interesting concept.

----------


## romperstomper

From Knuth's Structured Programming with Goto statements:
"At the IFIP Congress in 1971 I had the pleasure of meeting Dr. Eiichi Goto of Japan, who cheerfully complained that he was always being eliminated"
 :Smilie:

----------


## Mordred

@Andrew




> So there you go, the full story



Very informative, hopefully Goto isn't planning something diabolical as a form of jealous revenge on Gosub :EEK!: .

----------


## Cheeky Charlie

> Very informative, hopefully Goto isn't planning something diabolical as a form of jealous revenge on Gosub.



He was, but he forgot where he left his 'murdering stuff'

----------


## Domski

> He was, but he forgot where he left his 'murdering stuff'



Probably the same place he declared war instead of double ;-)

Dom

----------


## shg

I see increasing examples of us providing macros to people who don't have a clue as to how to write a formula -- several today alone. We even provide code that circumvents inherently bad designs that would be more appropriately addressed by changing the data layout and using simple formulas.

I think this does no favors for our members.

----------


## NBVC

Most of the time, though, after suggesting changing layout or using pivot tables instead, etc.. the OP comes back with "my boss doesn't want me to change the layout" or "We want it to work with the layout we have".  In the end, if that is what they want, that is what they want.. and if we provide workarounds and they come to realize the flaws, then they've learned a lesson, haven't they? (or not).  If not, well, on to the next question.

----------


## Domski

> I see increasing examples of us providing macros to people who don't have a clue as to how to write a formula -- several today alone. We even provide code that circumvents inherently bad designs that would be more appropriately addressed by changing the data layout and using simple formulas.
> 
> I think this does no favors for our members.



Suggest changing the layout??? You'll be wanting to sleep with their sister (or brother) next!!!

----------


## Mordred

> Suggest changing the layout??? You'll be wanting to sleep with their sister (or brother) next!!!



Hahahahahaha!
Maybe that should be standard form of payment for you guys from here on out!  
"Will work for *** with your kin"

----------


## broro183

> I see increasing examples of us providing macros to people who don't have a clue as to how to write a formula -- several today alone. We even provide code that circumvents inherently bad designs that would be more appropriately addressed by changing the data layout and using simple formulas.
> I think this does no favors for our members.



I'm with Shg on this one & have previously suggested to a couple of people that... 



> For some suggestions on good spreadsheet design check out http://www.eusprig.org/smbp.pdf which is sourced from http://www.sysmod.com/sslinks.htm



But, it is so easy to take on the challenge of creating an innovative work around  :Wink: 

Rob

----------


## Andrew-R

> But, it is so easy to take on the challenge of creating an innovative work around Rob



Too true, although I had to draw the line at creating a non-modal user form to save users the trouble of pressing CTRL-F  :Smilie: 

I think that too often we get given so little information it's hard to work out what the end result is really meant to be, by the time you realise that somebody doesn't have a clue what they're doing you're already emotionally invested in solving their problem for them.

----------


## Mordred

> by the time you realise that somebody doesn't have a clue what they're doing you're already emotionally invested in solving their problem for them.



Ya, but what else would you do while you are at work? :Wink:

----------


## teylyn

> I see increasing examples of us providing macros to people who don't have a clue as to how to write a formula --  [...]



My point exactly. We should start refusing to help further bad spreadsheet design.

----------


## Paul

Perhaps when it's mandated that all Excel users take a Microsoft Certified course on proper spreadsheet design prior to inserting the install disk, but until then...




```
Please Login or Register  to view this content.
```

----------


## Cheeky Charlie

> My point exactly. We should start refusing to help further bad spreadsheet design.



Obviously this won't work  :Frown: 

I agree wholeheartedly with the sentiment, like most here I have a trail of posts laong the lines of 'this wouldn't be a problem if you designed your spreadsheet better" (normally followed up with - then you could use a pivot table!!!)  :Smilie: 

When I have the heart, I try to give options along the lines of:
'if you want to do it the right way, which will take a bit of time to reorganise your data but will save you time in the long run...'
then
'alternatively, you can use...'
(solution fitting example exactly)

It wins engagement to show that you *can* solve the problem in their extant situation, but don't think that is a good way of doing it.  Otherwise people are inclined to believe you just can't do it.

More often I end up writing something a bit less thoughtful:
http://www.excelforum.com/excel-gene...in-a-cell.html
http://www.excelforum.com/excel-gene...yperlinks.html

 :Smilie:  :Smilie:  :Smilie:

----------


## sweep

> Don't use merged cells. Ever.



Some of the best advise I have ever seen posted.  Anywhere.

----------


## teylyn

@CC, I see your position, but where do you draw the line? Life can be so easy if a few simple rules are followed for spreadsheet layout (and for posting in forums, and for many other things). Let's try and spread the good practice rather than supporting the bad practice by writing complicated, convoluted formulae or VBA just because it can be done. 

More often than not, this complicated workaround is not something the OP will understand, anyway, so after a month, when they make some changes to their spreadsheet, the complicated solution no longer works and they don't have the stuff to fix it. With good spreadsheet design, you only need a few basic formulas to keep going in most situations.

Somewhere, in another forum, not too long ago. Today, actually:

OP: i need to detect when the value of the active cell is changed. I need to detect when the active cell on the worksheet is "A2" and after that I need to catch the value of the active cell if it is different to empty.
the problem is that i need to catch the value (for example in another cell) at the same time that the activecell is editingMe: Hello, you can do that with a Worksheet Change event macro.
Right-click the sheet tab and select "View Code". Copy the macro below into the code window and add the steps you want the macro to do.

cheers, teylyn



```
Please Login or Register  to view this content.
```


OP: thks teylyn for your example but is not what Im looking for.
your code show the value after the active cell change to another one
I need to catch the value of the active cell when you are writing on itMember A: Can you provide an example with the original cells and values and then - what they should look like after the change?Me: You cannot duplicate a cell content in another cell while you are still editing the cell. You need to confirm the data entry in the cell with one of the known methods (Enter key, tab key, etc) and only then is the cell content available for manipulation via VBA or formulas.Member B: While I'm not quite 100% on what you what

This code
- checks to see if A2 was changed
- if A2 is now blank then the value is restored to the prior non blank value (using a static variable)
- if A2 is not blank that the static variable is updated to the new value

Cheers




```
Please Login or Register  to view this content.
```


OP: mmmm I cannot catch the value????
Ok one last question it is possible to detect when the active cell is editing???Me: Well, kind of. As far as I know you can't run VBA code while Excel is in edit mode, so not being able to run code might be one indicator.
But maybe you need to step back a bit. Instead of assuming the solution can be achieved with specific technique, why don't you describe what you want to achieve or why you want to do this? That may take us to different approaches.
cheers, teylynMember B: > why don't you describe what you want to achieve or why you want to do  this?
Seconded
OP: Let me try to explain myself

this is what I try to do

when the cel "a2" is the active cell and is in edit mode I want to detect that state to make visible a combobox (instead the cell )with two values:
  1.- Value 1
  2.- Value 2

When the user select a value from the combobox I would like to assign that value to the active cell .Me:Ah! That is called data validation and can be achieved without macros.

Click the cell. Click Data - Data Validation [... bla bla on data validation]

OP:Sweet!!!

That was a great solution bro

thanks!!!!Errhmmm -- Duh!?

Should I have tried to find a way to work against Excel and VBA and somehow *MAKE* Excel do what the OP wants?

Hah!

----------


## Cheeky Charlie

> Should I have tried to find a way to work against Excel and VBA and somehow MAKE Excel do what the OP wants?



Clearly no (and we've all had threads like this, I think), but that's not what I was saying.  My point was, I thoroughly agree with the sentiment, but refusing to help until someone designs their spreadsheet according to our stipulations simply won't work - they'll just brand you 'fool' and go elsewhere. My point was about how to encourage better spreadsheet design positively - basically more than one way to crack the nut.

----------


## Domski

It's the...





> My boss has told me it's got to be like this



...ones that get me.

I often feel like suggesting they take the keyboard and wrap it around their boss's head. They should stick to bossing and leave the spreadsheet design to other people  :Wink: 

Dom

----------


## Marcol

Perhaps this approach?





> I have a spreadsheet that shows the scores a player gets in my game.
> If the score is 5 or more player gets 1 point, he gets none if score is less than 5.
> I need a macro to do this..... what is his scores and how much is his total score?



Assuming your data begins in A2



```
Please Login or Register  to view this content.
```


This will show you where the player scores a point




```
Please Login or Register  to view this content.
```


This will give you his/her  total score

If you just need the total score then you could try this



```
Please Login or Register  to view this content.
```


Hope this helps

Bingo!, the OP has a macro to do the job and the formulae to boot!.... :Smilie: 

Hopefully the clue might just be in the Sub names...... :Wink:

----------


## ChemistB

I think that often the OP doesn't have a choice with the layout, either in a highly regulated environment, or the spreadsheet belongs to a customer/vender/other department, etc.  I think it's important to inform the OP that they are working with an inferior design and suggest that it be fixed.  I'm not sure I buy the "My boss wants it that way" as a legitamate excuse.  Show the boss a good working spreadsheet and they will probably change their mind.

I wouldn't mind putting a good link to a site for proper spreadsheet design hints in my sig if anyone has one.  I'm going to read the one that Broro suggested but I think 102 pages is a bit much for the average OP.   :Wink:

----------


## JBeaucaire

> I'm not sure I buy the "My boss wants it that way" as a legitamate excuse.  Show the boss a good working spreadsheet and they will probably change their mind.



Heavens! I wish that were true.  But the small amount of "power" exercised by many management types I've had to deal with over the years included being very closed to suggestions that their "stuff" can be improved.

I once suggested what I thought was a very simply design change to a "Form" a manager was trying to use as a sort of database and he basically said, "If this is too hard for you, I'm sure I can find someone more skilled to add the stuff I need."

It's sad, but only half of the improvements I suggest to management types are even considered.

Now, when they give me a project to "get something done", I have much better success.  But any manager who views themselves as an Excel "user" ultimately is a pain in the backside.

----------


## romperstomper

Too true. Luckily for me, I have always (thus far) been in a position to say "if you want it done your way, you do it; if you want me to do it, don't tell me how."
Generally I try to only work for people who don't understand what I do...  :Smilie:

----------


## Cheeky Charlie

I tell the people I work with I'm likely to be the most talented Excel user they'll ever meet.  They believe me.

LOL

----------


## royUK

Hence Cheeky & not just Charlie!

----------


## martindwilson

my daughter is getting married  this Saturday.
Anyone got a macro that can do my speech?

----------


## royUK

You probably need a macro to pay the bill

----------


## NBVC

> my daughter is getting married  this Saturday.



If that is a true statement, then my congratulations to her and her husband and to the parents  :Smilie:

----------


## ChemistB

Congratulations Martin.  You don't look old enough to have a married daughter.   :Wink:

----------


## MarvinP

http://www.weddingspeechdigest.com/ 
You didn't say Excel template, did you?
For the father of the bride...
http://weddingspeech4u.com/fatherofb...e-speeches.php

----------


## martindwilson

thanxs all,yep it's true. just been to rehearsal. should be a laugh on the day if that was anything to go by!

----------


## Cheeky Charlie

```
Please Login or Register  to view this content.
```


Oh, guys, I just realised, I left my life somewhere

----------


## MarvinP

Martin,
You're gaining a son!  :Cool: 



```
Please Login or Register  to view this content.
```


 :Smilie:

----------


## TMS

@MarvinP ... that's really impressive!  That'd be great for encrypting data, passwords maybe ... any chance you can enlighten me as to how you come up with those numbers?

Regards

----------


## teylyn

TMShucks, you'll need to work that one out on your own. :-)

Start by exploring polynomial trend lines and Intercept().

----------


## teylyn

If I've managed to work it out, I'm sure you will!

----------


## teylyn

OK, you've got the 1% inspiration, now do the 99% perspiration ....

----------


## Domski

> OK, that's one of those sentences where I recognise all the words but, put together, it means nothing at all.  :-(



With you on that one  :Confused: 

Might have a look later if Friday is getting too much for me.

Dom

----------


## teylyn

Sheesh, don't you'all go spoiling my VBA noobie trust in your ability to cobble together this rather simple bit of code with a lazy shake of the left wrist.

OK, I *did* spend a few hours to work it out, and I *do* have an affiliation with charts, which may have been helpful. But at the time I figured out the stuff for that code I could hardly string together a Copy - Paste Special - Format in VBA. 

Tip: The insight is in the *function*, not in the VBA.

'nuff said.

----------


## martindwilson

thanx cc and mp!

----------


## romperstomper

Congratulations!  :Smilie: 
You could do the speech the same way I did my groom's speech and a best man's speech - get really drunk the night before, and make it up as you go along...

----------


## Domski

Hope you all enjoy the day Martin  :Smilie: 

Not too many Mojito's before the wedding!

Dom

----------


## MarvinP

The root of many, "I need a Macro to do this...," questions, are simultaneously character driven and self-congratulatory.  
We build macros, because we can.  

We create powerful formulas equal to simple letter codes and then call upon Excel to matrix-inversely reduce them to their nothingness.  
We build macros because we can.

The congratulations should go to ourselves by inspiring others in our matrix to learn and teach VBA in Excel.
We build macros because we can!

----------


## Cheeky Charlie

> If I've managed to work it out, I'm sure you will!



That's nothing, I did, with my piddly brains - if that's not incentive enough...!

----------


## ChemistB

The Trend continues

----------


## broro183

LOL, re the trend continues!  :Smilie: 





> ...I wouldn't mind putting a good link to a site for proper spreadsheet design hints in my sig if anyone has one.  I'm going to read the one that Broro suggested but I think 102 pages is a bit much for the average OP.



Good point ChemistB, 102 pages is a lot. I found it a reasonably easy read & I agree with most (all?) of the suggestions, which is certainly not the case when I compare it to some other guidelines that I've seen. It may be large, so you could guide OP's to Chapter 5, but personally, I appreciated the examples scattered throughout the book. 

Here are some links that I found with a quick search that may be more appropriate for a signature(EDIT: Or perhaps not, see the following post. Perhaps it is better to say that the links may be informative if provided for users in specific posts... END EDIT):
- Two from Ozgrid:
http://www.ozgrid.com/forum/showthre...t=76234&page=1
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm
- Codematic's (Simon Murphy?) site could have some useful info (I haven't gone through the links): http://www.codematic.net/spreadsheet...evelopment.htm
- Ross Mclean's blog (& links): http://www.blog.methodsinexcel.co.uk...dsheet-design/
- I didn't request/subscribe to actually get the Standards, but the below link may be of interest...
http://www.ssrb.org/best_practice_sp...standards.html
- Or you could link to the source of the 102 page book: http://www.eusprig.org/best-practice.htm

Rob

----------


## teylyn

> Here are some links that I found with a quick search that may be more appropriate for a signature:



Take heed. The powers that be in our forum have created rule 13: "Thou shalt not link to another (i.e. competing) Excel forum in your signature" or words to that effect, so, as much as I support the ideas laid out in the Ozgrid links, using the link in a sig would go against that rule. 

Let me point out again that rule 13 is the only one that has not been discussed among and agreed upon by the moderators.

----------


## broro183

Good point Teylyn,
btw, I apologise if I've been mis-spelling your name recently  :EEK!: 


ChemistB,
The below link isn't as specifically focused on spreadsheet design, but it is "in house" & every time I want to look for it (if I lose my bookmark), I go straight to OldChippy's signature which links to: http://www.excelforum.com/excel-gene...additions.html

Rob

----------


## teylyn

Bro,

we should "sticky" that!!

----------


## ChemistB

Thanks Bro,  I will check them all out and keep them handy.   :Smilie:

----------

