# Off Topic > The Water Cooler >  >  Grrrr, Excel amateurs

## Andrew-R

For a meeting later this week I'm doing a presentation on activity within my team.  To help me out I've written a macro which reads in their timesheets (which are in a heavily locked-down template, created by our FD).

My macro started bombing out, with a type mismatch error, but I couldn't work out why.  After about half an hour of de-bugging I realised that it was bombing out at the first blank row it found in a timesheet, but there was plenty of code in my macro to stop it processing empty rows.

Eventually, after some very dull stepping through of code, I realised that the problem was that the rows which are "blank" actually have a VLOOKUP formula in, to match a project code and auto-populate the project name and the FD, obviously not knowing about ISNA, has used conditional formatting to set the text to white on white if there's an error in the cell.  I was assigning what I thought was a blank cell, but was actually a #N/A, to a string and that was the cause of all of my woes.

I think I may have to sit down with the FD and explain some basic Excel functions to him  :Smilie:

----------


## teylyn

Hi Andrew-R,

white text on white background is only one option. 

Another one is to mask zero values with custom format 0;;; -- you see a blank cell, but the value is really zero. (Of course, you can use Tools - Options (in XL 2003) to hide zero values (and with another path of settings in later versions)).

That's why I LOVE the Evaluate Formula tool for troubleshooting. It shows what a cell REALLY delivers to Excel calculations.

I just wish Microsoft made the actual evaluation window bigger than four rows (or allow the window to be re-sized), so I don't have to scroll endlessly on a Sumproduct or an Array formula!!

With regards to your FD (whatever that may be), they are using Excel as well as they've been trained to use it. Then, after they have exploited their capacities, they try to hack the desired results. You should actually congratulate her/him on this ingenuity: s/he made the system do what s/he wanted to see, without knowing what s/he was really affecting. 

Then you came along, with your expertise and popped the balloon. Be gentle. S/he seems to have great potential, bite, and creativity. Nurture her/him and make her/him see the light. You'll probably be able to develop a great Excel-geeky relationship.

It can be fun. Otherwise, how would forums like these survive?

cheers

----------


## Andrew-R

Funnily enough, one of the long-standing items on my "Things to do" list is to run an internal Excel course.  There are a number of people interested in learning Excel macros and I'd like to get them started.  Unfortunately their existing skills range from barely being able to open Excel through to skilled writers of formula and some who are familiar with other programming languages and just don't happen to know VBA.

I've written the course as far as explaining what a macro is, the basics of programming (sequences, iterations and conditions, plus variables, constants and literals), a high-level view of objects and Excel's object model and on to writing a "Hello world" macro.  Then I sort of lost any sense of where to go next - if anybody's got any suggestions I'd be delighted to hear them.

----------


## teylyn

Andrew, I don't think you can learn VBA on a theoretical approach. You need a hard and fast case to try on. 

Some people will thrive on writing UDFs that will figure out what the shoe size is if the eye color is dark blue and the birthdate is in a leap year. Others will want to move data from sheet1 to sheet2 based on a certain condition. These are quite different animals and require different VBA skills.

I think your users would very much benefit from a VBA class if you can work out what they would want to do with VBA. How can it make their daily tasks easier? Hello world is fine for a start, but when it comes to the day to day stuff, what do they really need? That's what your course should focus on.

----------


## Andrew-R

Well the purpose of the training course is threefold:

1.  To stop people I work with rocking up to my desk and asking, "Andrew, can I borrow your Excel skills for 5 minutes", before describing a job which will take somewhere between 30 seconds and the age of the universe.  This will free up my time to answer more Excel questions on here, where I can at least imagine that the people I'm helping are physically attractive.

2.  To make people realise that "good with Excel" does not equate to "can magic data from thin air".  Nor does it mean "can make two fundamentally different sets of numbers sum, or in any other way collate".  And it certainly doesn't mean, "Is happy to wade through pages and pages of your incomprehensible spreadsheet to find out why the numbers don't 'feel right' to you".

3.  To ensure that some of the 157 different Excel templates that this company seems to run on are reasonable and robust and don't look like the output of a mad, drunken chimp trying to induce an epileptic fit in a lab rat.

Or maybe I'm setting the bar too high and it would just be nice if they knew how to get started on the life-long adventure that is Excel macro creation.

----------


## romperstomper

1. Never going to happen. (I ran an internal training course last year; the net effect appears to have been that 4 or 5 people have learned a lot and are very happy, whilst 45 people have learned that I can solve all their Excel problems. They, too, seem happy; me, not so much.
2. Also not going to happen, due to human nature.
3. Could happen, but only if you end up doing it yourself.

I agree with Frau teylyn though - best to find some things they spend ages doing by hand and show them how to automate it. Once they see one, they will either get the bug, or they'll make your life even worse from then on...  :Smilie:

----------


## Whizbang

I don't mind ignorance when it comes to Excel.  What I do mind is people defeating themselves before they even start.  "Oh, that's way over my head."  "I can't do that."  "I'm not good with computers."  "I'm not smart enough."

B/S!  Motivation and patience are required more than intelligence.  

What gets me is that I am there holding their hand, showing them step by step.  But because "I'm not good with computers" they don't even try to remember what I show them.  They are just my little mindless puppets doing what I tell them to do.

I cannot work on cars.  To me, if I start it up and it goes the direction I want it to, then it works.  If it it doesn't, then it is broken and I might as well hoof it because it will be faster than me trying to fix it.  BUT!  I know that this is because of a lack of interest on my part, not because "I'm not smart enough".  I've seen some people that I am sure the elevator doesn't go all the way up that can take one look at an engine and tell me every specification and detail.  Excel is the same way.  It just takes a little motivation, patience and creativity.

It just aggravates me when they shoot themselves in the foot.  I try to argue with them and explain that it is mostly how you organize your data rather than some mysterious ritual.  To them, however, it might as well be magic.  If they hold their faces just right, tap their feet together and say "buzzle wuzzle" then things will magically happen.

Anyway, I think the focus of the class should be more on attitude than on execution.

----------


## Domski

> I think I may have to sit down with the FD and explain some basic Excel functions to him



I recommend looking back at something you designed a few years ago and see how many things you would sit down and explain to yourself  :Wink: 

Dom

----------


## Andrew-R

I don't have to go back years, Dom, just to before I started reading this forum.  I can't believe I managed so many macros while being ignorant of the Offset function, for example.  I look at macros I wrote a few months ago and they're full of:




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


It looks so ... messy now.

----------


## NBVC

> I've seen some people that I am sure the elevator doesn't go all the way up ...



Ha! Ha! That's funny.. never heard that one before: ROFL  :Smilie:

----------


## Cheeky Charlie

I love this thread, I had lots of thoughts to contribute, but they've all been said by others...

----------


## Whizbang

> Ha! Ha! That's funny.. never heard that one before: ROFL



http://answers.yahoo.com/question/in...1101956AAcWXQI

Enjoy the link.  :Smilie:   I know I did.

----------


## Paul

> Some people will thrive on writing UDFs that will figure out what the shoe size is if the eye color is dark blue and the birthdate is in a leap year. Others will want to move data from sheet1 to sheet2 based on a certain condition. These are quite different animals and require different VBA skills.



You might be surprised to know that the difference is very slight.  Don't tell Cheeky, but all you have to do is throw in a few GoTo's, and then instead of using just Enter to confirm the formula, use UP, UP, DOWN, DOWN, LEFT, RIGHT, LEFT, RIGHT, B, A.

----------


## teylyn

back to the amateurs: Spent an hour today troubleshooting a colleague's Vlookup formula. 

Seemed straightforward: =Vlookup(A1,Sheet2!$A$1:$B$1000,2,False)

For one value he got a #N/A. The value was clearly in the lookup range. A43=Sheet2!A77 was TRUE. He had just inserted a row and typed the value in. It was something like

AA~BB1

Other values looked like AA~BB44 and did *not* throw an error. So I told him about the significance of the ~ and we doubled the ~ in the Vlookup, like

=Vlookup(substitute(A1,"~","~~"),Sheet2!$A$1:$B$1000,2,False)

Now the line with the manually inserted code was fine. But copied down, some of the previously returned values showed errors instead.

Long story short: I undid all the changes and finally switched to formula view to inspect his original formulas. I couldn't quite fathom that strings with a tilde would return matches.  I found that half of the cell contents was hard-coded instead of the Vlookup, and halfway through the column he had changed the last parameter of the Vlookup to True, hence all his strings with ~ returned approximate results, which he never questioned.

Scary.

People like him are responsible for moving big amounts of money around my company. 

Very scary.

----------


## NBVC

I've got one of those kinds in my Accounting department too... 

Everytime he calls me over with any troubles in excel, I start by inspecting the formulas at random down the columns.

He is well known for tweaking formulas half-way down the columns or even hard-coding results.. and then when inputs change he can't understand why the results look funny!  :Roll Eyes (Sarcastic):

----------


## Domski

Vlookup is one of those scary formula that pretty much everyone seems to think they can use but the forum is littered with examples of people getting odd answers and not understanding why.

I came across a finance spreadsheet recently that was used for some quite important analysis. The vlookups in it didn't have the 4th argument as false even though it was an exact match they were after and the lookup table wasn't sorted in the correct order hence some interesting results were coming up that nobody seemed to have noticed. The guys in the department all think they're pretty hot at Excel but it took me a while for them to get the problem.

I do worry...

Dom

----------


## romperstomper

I always *used* to assume that Finance people were good with spreadsheets (that being, after all, all they do all day). Mind you, I also _used_ to assume that the Inland Revenue could calculate tax...  :Wink:

----------


## Cheeky Charlie

> I always used to assume that Finance people were good with spreadsheets (that being, after all, all they do all day). Mind you, I also used to assume that the Inland Revenue could calculate tax...



LOL

You guys may find some mileage in this (you've probably already seen it):
http://www.excelforum.com/2208808-post28.html

----------


## romperstomper

It's also one of the inbuilt error checking options - the inconsistent formula in region one. Dave Brett has a free mapping tool that you can use that basically maps the contents of a workbook.

----------


## Andrew-R

I happened to pop by the desk of a colleague last week while they were being 'assisted' in Excel by another person in the same team.  They were trying to convert an entire column to upper-case and they advice that they were getting was to put =UPPER(B1:B1000) in cell B1.

I'm not quite sure why the person giving the advice had any belief that he was qualified to do so, giving that earlier that day he'd asked me to write a macro to find the highest value in column A of a sheet, divide every value in column B by that number and put the answer in column C.

Oh well, shows enthusiasm, I suppose.

----------


## Cheeky Charlie

There are a lot of idiots out there.  I searched for something earlier and found some truly horrendous advice given as a techrepublic article!!

----------


## shg

> Dave Brett has a free mapping tool that you can use that basically maps the contents of a workbook.



I saw "Spreadsheet SomethingOrOther"  described last week that puts a single symbol on a blank worksheet in corresponding positions to the analyzed worksheet; 

T for literal text 
N for literal number 
F for a formula 
< for a formula the same as the one at left 
^ for a formula the same as the one above 
+ for a formula the same the the one above and left

I thought it would actually make a good didactic example; it's very visual. I've seen lots of worksheets where the OP uses a different formula in each cell in a row, rather than using mixed references so that the formula adjusts automatically. 

Seems like it would take 15 minutes to write.

----------


## Andrew-R

Sounds like a good idea.  I'd suggest adding a symbol specifically to indicate a number represented as a string ... that's got me a lot of times.

----------


## shg

http://www.excelforum.com/tips-and-t...sheet-map.html

----------


## Cheeky Charlie

> Sounds like a good idea.  I'd suggest adding a symbol specifically to indicate a number represented as a string ... that's got me a lot of times.



He's too good to you, that guy...

----------


## broro183

ROFL!

This whole thread is packed with little gems of hilarity, but I esp. liked Paul's secret "key combo" with a goto or two included for good measure.

Shg, I hadn't taken the time to think how simple the coding for a mapper would be because I've been using John Mote's version (Formula Mapper.xla) for a couple of years now. It's very similar in output to your posted version but has protected code so I can only guess what's going on in the inside.

On reading this thread, I thought I'd check if/how many others use version, so I had a look & only spotted the below thread where the OP asks for it - so I added a zipped version of John's addin to the thread. John's version comes with a userform for various user parameters & works quickly enough for me. That is, if I ignore the couple of times where I've sent my laptop whimpering into a corner for a while when I asked the addin to deal with a 10-15 sheet file that included data sheets with 60K+ rows! Ooopps, time for a break  :EEK!:   :Wink: 

http://www.excelforum.com/excel-gene...er-add-in.html

RS,
I *used to assume* the same about Finance people being good with spreadsheets, esp because I was a Finance graduate when I learnt what macros are. Then I found I didn't need to be an accountant to use spreadsheets, so off I went & now I'm looking at Finance from the outside. Each time I look in, there are at least one or two chances for me to go, _hmmm..., what is that hardcoded value doing in the middle of all these formulae in a monthly finance report?_

Rob

----------


## DonkeyOte

> mmm..., what is that hardcoded value doing in the middle of all these formulae in a monthly finance report ?



If there's no Fudge it isn't a real Finance spreadsheet.

Fudges are obligatory for any figure related to the Sales Forecast.
The Fudge itself traditionally reflects the impact of the "imminent" hiring of the best EVER sales person.
This sales person - whilst being happy to work for less than the minimum wage and no bonus - is still capable of fulfilling the annual sales quota of at least 5 other sales persons - and all in Q4 alone.

----------


## broro183

Yes, true about the Fudge factor, but I still get concerned when from month to month the fudge factor "changes location" & yet the previous hardcoding stays constant (although ) - in effect, it's cumulative.

LOL, Sales Forecasts eh?!
Before a recent role, I wouldn't have known what you meant, but recently, I tried... I really did. I tried to provide a standard template with some consistency of 1 formula per column, separated input/calc/output areas & stated assumptions etc - _I never managed to "sell" it_  :Wink:  & I kept receiving all sorts of fanciful numbers.

Ahhh, the best EVER Sales person & the great expectations of Q4 - some times the expectations are met, but I haven't yet come across the mythical Sales person.

Rob

----------


## Domski

Does anyone else want to have a go at this one?

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

I'm losing the will to live  :Frown: 

Dom

----------


## Andrew-R

Another classic ...

http://www.excelforum.com/excel-prog...-vba-code.html

Seriously, what was somebody thinking?

----------


## Andrew-R

> I'm losing the will to live



Pah, quitter.  You get one little incomprehensible request from somebody who takes hours to tell you your code doesn't work and you give in.

Where's your sense of responsibility?

----------


## Domski

I prefer the role of the socially irresponsible. I find I fit in much better.

Dom

----------


## Domski

> Another classic ...
> 
> http://www.excelforum.com/excel-prog...-vba-code.html
> 
> Seriously, what was somebody thinking?



This 'looping' through 1 value is catching:

http://www.excelforum.com/excel-prog...ing-macro.html

Dom

----------


## Andrew-R

<Shakes head>

Anyway, I see your favourite poster has re-posted their question again and the mods appear not have noticed yet.

----------


## romperstomper

Wanna bet?  :Smilie:

----------


## Andrew-R

Ah, I was enjoying his efforts at scaling the fortress of ineptitude, armed only with the water-pistol of repetition.

----------


## romperstomper

I have absolutely no doubt that he will try again, backed by the arquebus of obstinacy.

----------


## Domski

I hope not. I'm having trouble explaining the forehead shaped dents in the filing cabinet as it is.

This did make me chuckle though  :Smilie: 





> Ah, I was enjoying his efforts at scaling the fortress of ineptitude, armed only with the water-pistol of repetition.

----------


## ChemistB

Come on Dom,   I think you're almost at the pinnicle.

----------


## romperstomper

> This did make me chuckle though



Me too - so much so, I +repped him for it. Only laugh I've had all day.  :Smilie:

----------


## Andrew-R

Thank you, maybe there should be two different rep options, one for praising somebody for their Excel skill and another for just making people laugh, otherwise people will end up trusting my skills until they find out that my macros are a joke as well  :Smilie:

----------


## Domski

> I +repped him for it



Me too, he'll be thinking he's funny if we're not careful  :Roll Eyes (Sarcastic):

----------


## Mordred

> Does anyone else want to have a go at this one?
> 
> http://www.excelforum.com/excel-prog...-workbook.html
> 
> I'm losing the will to live



Wow Dom, you have a lot of patience.  That thread definitely came down to the OP not being able to articulate his/her self.

----------


## Domski

I'd unsubscribed from the thread but now I see RS has taken up the gauntlet I'm watching again :-)

Dom

----------


## romperstomper

I have a feeling this could occupy my entire day...

----------


## Domski

...or maybe not. What did you say?

Dom

----------


## ChemistB

ChemistB <-- Also in the mezzanine.  ::::::pass the popcorn::::::

----------


## JBeaucaire

JB.... <stuck in the lot looking for a place to park...again!>

----------


## davegugg

Ha, here's a good story.  I get some excel sheets used for pricing from a company that my company works with.  I have to take some of their pricing adjustments and transfer them to a new sheet that I send out to various customers.  Some of these adjustments are positive and some are negative.  Rather than using that troublesome negative key, this company sets the format of the cell to -0.000 for all negative numbers, and +0.000 for all positive numbers.  As an extra kick in the pants, they use this on only some (seemly random) adjustments, and the others are entered properly.  

It would be difficult to find a more complicated and burdensome way to set up a spreadsheet.

----------


## Cheeky Charlie

> It would be difficult to find a more complicated and burdensome way to set up a spreadsheet.



I consider a gauntlet thrown down, I think you will find some worthy challengers...

----------


## romperstomper

I find users a troublesome set, and accept all excuses to ignore the same, anything else is just lazy commonality!  :Smilie:

----------


## JBeaucaire

*Lazy Commonality*

...if I started a band, that's what we'd be called.

----------


## Domski

Pretty much every spreadsheet that gets issued by our ICT department has 'issues'. I've improved on a few and sent them back but as I'm not ICT I'm not allowed to develop stuff for general distribution without it being picked apart by the people who designed the original and they don't like being told that they're wrong so sit on my revisions for months. I try to be nice about it.

Dom

----------


## ChemistB

Working in a regulated environment, our validated spreadsheets go into a controlled software system which is under the control of a document management group who are used to working with Word Docs so they are clueless on Excel and very much afraid of screwing things up.  Whenever we want to make changes to spreadsheets, they always push for revalidation.  The last time, I had to fight to get a single for information only cell changed without a validation protocol.  Drives me crazy.

----------


## Domski

Do you reckon Leith has negotiated a fee for this one? Is it Nancy123 or Aman1234? Wonder who the client is?

Dom

----------


## teylyn

Lately I'm starting to wonder if it's really the amateurs that are trouble, or rather the apparently brilliant coders, who don't bother to document their complex and cryptic code, but advise in their signature not to use code the OP does not understand. Apparently, they were born with VBA knowledge and can't be bothered to explain a single line of code.

I mean, the following is pretty clear to everyone, innit?




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


And of course, this macro was *written*, not merely *recorded*, like us doofoos do.

----------


## Domski

What does document mean?

Dom

----------


## teylyn

I tnihk it uesd 2 B an acneint from of atclulay pttunig pen 2 paepr or tocuhnig kyes on a kybeaord 2 witre dwon thuohgts or fatcs aobut a tpoic. Raethr out of dtae now.

----------


## Andrew-R

> Lately I'm starting to wonder if it's really the amateurs that are trouble, or rather the apparently brilliant coders, who don't bother to document their complex and cryptic code, but advise in their signature not to use code the OP does not understand.



I agree, some of the formula especially that get posted seem incredibly obtuse and if somebody actively wants to understand how something works then we should at least make an effort to help them out.

----------


## Domski

It's a bit like the argument for using a proper variable name convention or even declaring variables which not everyone who posts answers does.

I will always happily explain code if someone asks (to the best of my ability) but much of the time either the OP doesn't really care how it works or understands it and just didn't know how to achieve what they wanted without a nudge in the right direction.

I always like the knowledge base at VBA Express where users can submit articles with properly documented code for approval. Not sure people have got the time to be documenting every answer given.

Dom

----------


## Cheeky Charlie

I write plenty of through explanations for people which go completely ignored.

It gets boring.

----------


## teylyn

Nice, CC. Will read more of your posts.

----------

