# Why does everybody hate Excel?



## hellfire45 (Aug 8, 2016)

In the last 5 years of working with Excel and VBA, I have numerous times run across people who flat out seem to dismiss the utility of using Excel, sometimes entirely.  

I hear things like:

"Excel isn't robust, we have to put it in SAP, Alteryx, Access etc.."

"VBA doesn't add value to the end user."

"VBA and Excel isn't programming."

"Excel workbooks are too slow."



Everybody seems to reject Excel first as a potential solution to problems.    I have 100 page long VBA programs that do hundreds out hours of manual work in 10 minutes and when I write an excel formula that has 25 nested functions in it, surely that too is programming.

Anybody else run into this kind of attitude and feel frustrated by it?  As an analyst that primarily uses Excel and Tableau it is frustrating when an automation I create in Excel is easily dismissed, even after completion, in favor of virtually any alternative.

When I create an automation in 2 weeks that does a variety of things and then the IT department insists that it be put into SAP, and it takes them a month to even complete the scoping session I think to myself "hmm...Excel doesn't look so bad now for its versatility, flexibility and speed of implementation."

Thoughts?


----------



## Joe4 (Aug 8, 2016)

> "VBA doesn't add value to the end user."


It doesn't need to.  Do users understand the programming language behind every single software program they use?  If it is written well, they never need to touch or see the VBA code.



> "VBA and Excel isn't programming."


This just shows the ignorance of the people making these comments.  VBA is programming.  You can create fully automated applications using Excel and VBA that run without any human interaction.



> "Excel workbooks are too slow."


They can be, if not written well, too much data is stored in them, or if Excel is being used for something that would be better off in another program.

A good example of my last comment is that I have seen Excel used as a database program with a lot data in it.  
Can it do it?  Sure!  
Is it efficient?  No!  
Is it the best program to use for a database program?  Definitely not!  Database programs like Access, SQL, and Oracle were designed for this very purpose.

Like anything else, Excel is a tool.  It does many things very well, and other things not so well.  They key question to ask is "*Is Excel the right tool for the job?*".  
Someone one said, you could probably drive nails with a wrench, but why would you do that if you have a hammer at your disposal?  Pick the right tool for the job, whenever possible.

Sometimes, it comes down to what you have available to you.  I have seen many questions on this forum where people are trying to do simple database tasks which Access can do with ease, but are very cumbersome in Excel.  Unfortunately, many times it comes down to the fact that they are not able to use Access, so they are stuck using Excel.  Not ideal, but that is often the reality of the business world.


----------



## Arachnyd (Aug 8, 2016)

Same reason why people reject a Mazda Miata as a race car.

Its cheap, easy to access, and 99.9% of the people who use it don't know how to use it to its full potential.
If "normal" people use it, it can't be robust enough for complex tasks!

We've done some cool things in excel that we were told by high level IT individuals that they can't be done without some complex system... and we get it done in like a week. 


(The Mazda Miata is one of the top performing race cars available despite its low cost)


----------



## RickXL (Aug 9, 2016)

I can see this from both sides.

Certainly, Excel is a very capable tool. There is an awful amount of functionality in there and because it can link to other things its bounds extend beyond any fixed description that you might find. 

I used to use SAS as a reporting tool. One day I mocked up a report in SAS to get approval to proceed to write it in PL/1. I produced two iterations of a fully working mock up before lunch and it took me another five days to get it working in PL/1. Excel is a bit like SAS in that respect. If you want to do what it is good at then it can very quickly produce results.

However, professional programmers often have a different view of life. If an application has built-in limits then that acts as a red light and some will tend to shy away from it. Now Excel has a row limit of 1048576 it is so much more useful than the old 65536 rows but what happens if you need 1.1 million rows? You now start having to code to get round the system limits. 

You mentioned SAP, I used to use that as well. Ours was based on Oracle and we had many tables with row numbers well into 9 digits. We only needed to put one version of any program into production and all users could use it. That is much harder with Excel. You need to roll out workbooks to lists of servers. Then you get problems when the users are running different versions of Excel. If the data needs sharing then there are problems.

If you have used features in Visual Basic like Array Lists or Sorted Lists you discover that Excel does not have all the same functionality. Some of it is missing. Some of the Object Oriented features you might expect are not there either. There are inconsistencies between Worksheet formulas and VBA ones. Names are different and dates are not exactly the same, for instance. It has all kinds of little quirks.

Like all software, it has certain capabilities and limitations. If you need a new driver for your graphics card then Excel will not be much use. The same applies if you need a database with a billion rows or one that has 10,000 users. On the other hand, there will be times when it will be hard to beat. Horses for courses is the motto.


Regards,


----------



## Joe4 (Aug 9, 2016)

Also, regarding your title "*Why does everybody hate Excel?"*, I am guessing you were just using hyperbole to make a point.
In my experience, most people like Excel (well, most anyone who is not techno-phobic).  I have only come across a few who didn't.

In the tech world, I have come across many more people who have an issue with Access than Excel.  They often look down on it and say it is "not a real database program", because it is not as robust as programs like SQL or Oracle.  It is just a tool, like anything else, and serves a need/purpose.  Use it for what it was intended for, and you usually won't have too many issues.  It is actually used as a front-end for SQL a lot, as SQL does not have its own front-end for easy GUI access.


----------



## RoryA (Aug 9, 2016)

Joe4 said:


> In the tech world, I have come across many more people who have an issue with Access than Excel.  They often look down on it and say it is "not a real database program"



Totally agree with this. The additional issue being that many IT departments actively hate Access because there are so many important databases spread around the company, over which they have no control and quite often don't even know of their existence until something goes wrong.


----------



## RickXL (Aug 9, 2016)

My issue with Access is that it is neither one thing nor the other.

It is not simple enough for the average computer user and it is not robust enough for professional use. Conversely, if people could understand Access then I believe that they would make better use of Excel. We get many Excel problems in this forum which would not have been problems if people had based their design on the "third normal form" they would have been coerced to use in Access. Think of all those people with data across Worksheets or Workbooks that now want to combine it into one Worksheet - or vice versa.

I still remember my boss from many years ago. He had a classic spreadsheet with categories and years down the side and months across the top. Everything was going well until he was asked to perform a selection that was a whole number of years but starting in July. What could have been a straight application of AutoFilter now became a lot more difficult.


----------



## Joe4 (Aug 9, 2016)

> My issue with Access is that it is neither one thing nor the other.
> 
> It is not simple enough for the average computer user and it is not robust enough for professional use.


Can't say that I agree with that entirely.  
Would I put a big important database on it? NO (at least not the back-end).
But there are many things we have found it quite useful for, such as:

1.  Using the Unmatched Query Wizard to compare two lists of data (sure beats doing tens of thousands of VLOOKUPs), and the wizard is easy enough for the average user to learn (I have taught a few people in my day).
2.  Use as a tool to quickly re-format data.  At my last job, we got lots of data file in the wrong format.  I developed Access databases to quickly convert the files into the correct format, and produce a series of reports based on the data.  
3.  Develop a quick front-end to an existing SQL database

One of the first programs I created in Access at my previous job was a billing/invoicing database in Access. Over the years, they invested in a SQL server and moved the back-end to SQL, but the front-end is still in Access (to this day).

Back in the day, I also really stretched its limits and created a program that took data and converted it into dynamic participant statements (of an unknown number of pages), complete with page numbers and bar codes.  It was a bit clunky, but at the time we really didn't have access to any other tools other than the Microsoft Suite products, and it got the job done.

And most of the people who use the applications I built have no idea that they are even using Access.  Everything is form driven.

Probably one of the biggest weaknesses is security (or lack there-of), though Excel is not much better.

The point is, Access is more powerful than many people think.  Obviously, if you have more robust options, you probably want to use those, but I have found it to be a handy tool to get some database tasks or other tasks completed quickly.


----------



## ttray33y (Aug 9, 2016)

I dont maybe some, but me? well the application is not perfect but it really is useful as it meant to do, it depends on your requirement.
the only application i hated the most is adobe acrobat pro, expensive useless piece of garbage IMHO


----------



## BobbyDrus (Aug 19, 2016)

When I was in University back in the early 2000's I was forced to take a MS Office course.   I skimmed it doing only what was needed because my view of Excel was it was a waste of time.  I could use so many programming languages to do so much more than I could with excel, and I felt I could do it faster.  

Fast forward a few years I am working as a sales person traveling extensively getting excel documents in my inbox every morning to look over, I later cursed my distaste for excel and wished I spent more time with it.

Today I use Excel all the time, mostly simple tasks, I still consider myself a noob, but am often the go to guy for excel questions.  Excel is that program that people think they are too good for until they realize Excel is so useful for so many tasks.


----------



## hellfire45 (Aug 8, 2016)

In the last 5 years of working with Excel and VBA, I have numerous times run across people who flat out seem to dismiss the utility of using Excel, sometimes entirely.  

I hear things like:

"Excel isn't robust, we have to put it in SAP, Alteryx, Access etc.."

"VBA doesn't add value to the end user."

"VBA and Excel isn't programming."

"Excel workbooks are too slow."



Everybody seems to reject Excel first as a potential solution to problems.    I have 100 page long VBA programs that do hundreds out hours of manual work in 10 minutes and when I write an excel formula that has 25 nested functions in it, surely that too is programming.

Anybody else run into this kind of attitude and feel frustrated by it?  As an analyst that primarily uses Excel and Tableau it is frustrating when an automation I create in Excel is easily dismissed, even after completion, in favor of virtually any alternative.

When I create an automation in 2 weeks that does a variety of things and then the IT department insists that it be put into SAP, and it takes them a month to even complete the scoping session I think to myself "hmm...Excel doesn't look so bad now for its versatility, flexibility and speed of implementation."

Thoughts?


----------



## RickXL (Aug 19, 2016)

BobbyDrus said:


> Excel is that program that people think they are too good for until they realize Excel is so useful for so many tasks.


Agreed. It is useful and it is surprising what you can achieve with it.

However, I would argue that does not mean that you have to "like" it. I used to use SAS at work and I DID like that. It used tables rather than worksheets although it called them datasets. Instead of the keyhole programming that is worksheet formulas you could use a program editor to create the code for each column. So it worked a bit like Excel but you were not constrained to shovel all the code into a cell. You could spread it out over several lines to make it understandable and it did all that without needing the rigour of Access or any other database.

It would also work on most generally available machines including mainframes. It had its own version of userforms and all the controls that implies.

The code was often quite short for what it did. For instance: PROC SORT; BY A B C D; would sort the last dataset into order of the four variables quoted. Excel can make that about a third of a page and I would struggle to do it from memory.

My last SAS mainframe system had around 3000 users. To make a code change I just edited a file and saved it. All 3000 users had access. No concerns about Add-ins, versions of the application, server software distribution, refresh cycles etc.

The "professional" programmers did not like SAS, either. They viewed it as being like a toy Meccano set while they had access to quality materials and engineering workshops by comparison.

"Liking" is very subjective. I think that is about all you can conclude. 

Regards,


----------



## thes4s67 (Aug 23, 2016)

I don't hate Excel.. so not everybody hates excel :O


----------



## gallen (Aug 25, 2016)

8 years ago I started work at a global flooring company in a position that had little to do with excel though had almost everything to do with numbers. However as someone who had used VB6 a few years back I was aware of what excel COULD do if you knew how to do it. My brother who worked for IKEA told me almost all of IKEA's systems were written on Excel, at least the managers tools were.

Luckily my job had, sometimes, periods of 'waiting'. So I set about looking at the current systems and designing how they could be improved to reduce human error. As the current systems were mainly paper based, it wasn't too difficult to get people to see what CAN be achieved with a little IT knowledge.

I set about using Excel to help me improve things. Then I'd look at what I'd done and say "It would be great if it did this.." then I'd learn how to do it. Now I have written many many utilities in Excel that makes many people's jobs more efficient. Yes I'd have liked to have used visual studio and SQL but to be honest thats major overkill for the projects I've done. Excel is amazingly great value when you consider cost against what it can achieve. For most small to medium businesses it will provide a perfect solution especially when the decision makers see the cost compared to the 'better' programming languages. The directors here love what I've done and am currently being paid to do something that was pretty much just a geek's hobby. 

Haters gonna hate.


----------



## DataAnalyst (Aug 29, 2016)

I hate Excel because it can be like working with an uncooperative toddler. When it tries to "think" it almost always is wrong! I wish it would do only what I tell it to. For example, I came here today looking for a way to keep the task pane from opening automatically whenever I go to Print Preview. I have more than 20 sheets to format and print, and having to keep closing the task pane is really slowing me down! When I want the task pane, I will open it!!!

However, it is useful for many things and I do almost all my work in it. I analyze relatively small amounts of data and make reports for my boss. 

I also use MS Access - it's the only database I've used extensively - and I think it's very useful for making small databases and pulling data from a large database, which is what I currently use it for. I found it easy to learn and use back in the day. I think the difference between me and other users is, I have an affinity for technical stuff, but many people are intimidated by it. It's like math - it's not actually that hard, but a lot of people are afraid of it. It's a shame.


----------



## Joe4 (Aug 29, 2016)

> I hate Excel because it can be like working with an uncooperative toddler. When it tries to "think" it almost always is wrong!


Are you sure that you aren't talking about Word?
That's 100 times worse then Excel is!


----------



## DataAnalyst (Aug 29, 2016)

Yes, Word is bad too!!!


----------



## Joe4 (Aug 29, 2016)

> Yes, Word is bad too!!!


No, no!
Excel: GOOD
Access: GOOD
Word: VERY, VERY BAD!


----------



## RickXL (Aug 29, 2016)

DataAnalyst said:


> When it tries to "think" it almost always is wrong!


I have a great deal of sympathy with that.

It results in users making certain types of progress quickly then, seemingly without warning, it will "help" you with something that you did not want to be helped with and now you are stuck because you don't know how to "undo" the help.

Oh, and I agree about Word. I have spent many a happy hour arguing with the auto numbering feature for lists - and pasting in tables from disparate sources - and making lines in drawings stay put - and making objects appear where you want them - and changing the color of bullet points - and making all paragraphs format the same way and, well I could probably think of lots more if I put my mind to it.

Regards,


----------



## DataAnalyst (Aug 29, 2016)

Thanks RickXL!

When my boss wanted me to be faster I suggested we stop using bulleted lists for my meeting agenda. I turned off the automatic bullets and now I use tabs and a dash to begin the lines. Much faster! Ha! Outsmarted Word!


----------



## RickXL (Aug 29, 2016)




----------



## hellfire45 (Aug 8, 2016)

In the last 5 years of working with Excel and VBA, I have numerous times run across people who flat out seem to dismiss the utility of using Excel, sometimes entirely.  

I hear things like:

"Excel isn't robust, we have to put it in SAP, Alteryx, Access etc.."

"VBA doesn't add value to the end user."

"VBA and Excel isn't programming."

"Excel workbooks are too slow."



Everybody seems to reject Excel first as a potential solution to problems.    I have 100 page long VBA programs that do hundreds out hours of manual work in 10 minutes and when I write an excel formula that has 25 nested functions in it, surely that too is programming.

Anybody else run into this kind of attitude and feel frustrated by it?  As an analyst that primarily uses Excel and Tableau it is frustrating when an automation I create in Excel is easily dismissed, even after completion, in favor of virtually any alternative.

When I create an automation in 2 weeks that does a variety of things and then the IT department insists that it be put into SAP, and it takes them a month to even complete the scoping session I think to myself "hmm...Excel doesn't look so bad now for its versatility, flexibility and speed of implementation."

Thoughts?


----------



## My Aswer Is This (Aug 29, 2016)

I love Excel and Word.

If you "hate" Excel just go back to using Lotus.


----------



## shg (Aug 29, 2016)

My Aswer Is This said:


> I love Excel and Word.


Ditto.

That doesn't mean Excel fulfills every requirement, but it doesn't pretend to. For people that know how to use it, it is applicable and useful for an astonishing breadth of problems.


----------



## RickXL (Sep 7, 2016)

Maybe the rumour was started by gene researchers: Excel created major typos in 20 percent of scientific papers on genes.

It says: 'One mistaken gene conversion for example turns the gene symbol SEPT2, short for Septin 2, to “2-Sep.”'


----------



## skywriter (Sep 7, 2016)

My Aswer Is This said:


> I love Excel and Word.
> 
> If you "hate" Excel just go back to using Lotus.




What's that suite that used to come with Windows?

I think there was a word processing program and a spreadsheet program, maybe Outlook Express was in there too, anyone know what I'm talking about.

Works?


----------



## Harry Flashman (Sep 27, 2016)

I think a lot of programmers hate Excel because they don't use it often enough to get the most out of it. So occasionally when they are forced to use it they find it frustrating, but mainly because they don't know how to use it. Also there is lot of snobbery towards VBA as a programming language. 

A lot of non-programmers who use Excel on the other hand use Excel very poorly, often as a database, but with data entered inconsistently, meaning that it often has to be cleaned up before any proper analysis can take place, and this is often a laborious process. Then there are those who know how to use Excel to an extent, but go overboard on trying to make things look pretty without enough thought towards functionality.

On the plus side if you know what you are doing and use Excel a lot, especially if you know VBA, Excel sometimes provides the optimum balance between power and speed of development. In my work I do a lot of ad hoc projects that work best if I create a tailor made tool for the job (usually with VBA). These are once off sorts of projects so it doesn't make sense to develop a stand alone application with C# etc. Excel means I can get the job done right, quickly and cheaply.


----------



## Harry Flashman (Sep 27, 2016)

skywriter said:


> What's that suite that used to come with Windows?
> 
> I think there was a word processing program and a spreadsheet program, maybe Outlook Express was in there too, anyone know what I'm talking about.
> 
> Works?



Microsoft Works. It had a very basic word processing program and an even more basic spreadsheet program. It may have done other stuff too, but that is what I remember.


----------



## FDibbins (Sep 27, 2016)

RickXL said:


> I have a great deal of sympathy with that.
> 
> It results in users making certain types of progress quickly then, seemingly without warning, it will "help" you with something that you did not want to be helped with and now you are stuck because you don't know how to "undo" the help.
> 
> ...



I spent a few hours yesterday, trying to figure out why the text in a colleague's document had ALL turned red.  No amount of formatting, styles or anything else for that matter, would change the text back to black.  Eventually discovered that he had accidently somehow turned on the Show mark-up - Reviewers.  Just another "something" that a noob could activate that you have no way of identifying.

As far as excel is concerned, it is a REALLY good workhorse.  It can do most things most other programs can do - sometimes better, sometimes not so well.  I can be made relatively robust, and at the lowest level, can be fairly easy to start using.  If you want something professional/complex/secure, then pick something that more closely fits your requirements


----------



## Rhodie72 (Sep 27, 2016)

Harry Flashman said:


> I think a lot of programmers hate Excel because they don't use it often enough to get the most out of it. So occasionally when they are forced to use it they find it frustrating, but mainly because they don't know how to use it. Also there is lot of snobbery towards VBA as a programming language.
> 
> A lot of non-programmers who use Excel on the other hand use Excel very poorly, often as a database, but with data entered inconsistently, meaning that it often has to be cleaned up before any proper analysis can take place, and this is often a laborious process. Then there are those who know how to use Excel to an extent, but go overboard on trying to make things look pretty without enough thought towards functionality.
> 
> On the plus side if you know what you are doing and use Excel a lot, especially if you know VBA, Excel sometimes provides the optimum balance between power and speed of development. In my work I do a lot of ad hoc projects that work best if I create a tailor made tool for the job (usually with VBA). These are once off sorts of projects so it doesn't make sense to develop a stand alone application with C# etc. Excel means I can get the job done right, quickly and cheaply.



I agree with you here too. Excel is great for learning the basics of programming too without forking out huge amounts for a programming enviroment with more power and the training required to begin learning it proficiently. There is so much that can be achieved with VBA that goes beyond average expectations but as you said, it's limited only because it's an interpreted language rather than compiled machine code.

It's the sheer customizablilty of VBA that makes it so versatile too. The limitations we come up against are often because of ignorance of the language or lack of intelligence too. Often it's a fashion statement that one knows how to code C# or Perl etc... rather than being useful for the job. Being capable as opposed to being able are the differences between achieving and underachieving maximal benefit from the API. People just want a quick result that's accurate and they will do anything to avoid learning a new language to achieve it.

*We write English so why should we learn Spanish to deliver a result in  English if it can be done faster in Spanish without errors?* 
We write Formulas so why should we learn VB to deliver a result in Formulas if it can be done faster in VB without errors?
Logic. Few use it. Emotions are what drive people to do the silly things they do... feel too lazy, feel too bored, can't be bothered to learn, feel too pressured to attempt... so many reasons not to win. People love failure because it pays as much as success in a full-time salaried job.

A comedian recently interviewed stated that he loved baby talc after a shower until one day he dropped the talc bottle on the bathroom floor... he rolled in it. Whatever was left he scraped into a pile and dumped it into the bin. There was a small ring of hardened dust left on the floor that he couldn't clear up by hand and attempted to blow it away unsuccessfully. When his wife returned from work he appologised profusely when she simply asked why he never used the vaccuum cleaner like a sensible person?

VBA can vacuum up the work in a flash but people don't think of learning the nuts and bolts of their programs because they simply don't have a clue about it to begin with until people like us show them. It takes effort they often don't need to expend for identical gain.

human nature. Go figure.


----------



## FDibbins (Sep 27, 2016)

Rhodie72 said:


> Logic. Few use it. Emotions are what drive people to do the silly things they do... feel too lazy, feel too bored, can't be bothered to learn, feel too pressured to attempt...



While those are very valid reasons, you left out 1...I am pretty good with formulas (self-taught) but just simply cannot get VBA under my belt (and believe me, I want to - to the point of paying for classes).  My short term memory is not what it used to be, and I just keep forgetting what I have just done


----------



## Rhodie72 (Sep 27, 2016)

RickXL said:


> Maybe the rumour was started by gene researchers: Excel created major typos in 20 percent of scientific papers on genes.
> 
> It says: 'One mistaken gene conversion for example turns the gene symbol SEPT2, short for Septin 2, to “2-Sep.”'



Bwahahahaha, I love that! It's so funny!


----------



## hellfire45 (Aug 8, 2016)

In the last 5 years of working with Excel and VBA, I have numerous times run across people who flat out seem to dismiss the utility of using Excel, sometimes entirely.  

I hear things like:

"Excel isn't robust, we have to put it in SAP, Alteryx, Access etc.."

"VBA doesn't add value to the end user."

"VBA and Excel isn't programming."

"Excel workbooks are too slow."



Everybody seems to reject Excel first as a potential solution to problems.    I have 100 page long VBA programs that do hundreds out hours of manual work in 10 minutes and when I write an excel formula that has 25 nested functions in it, surely that too is programming.

Anybody else run into this kind of attitude and feel frustrated by it?  As an analyst that primarily uses Excel and Tableau it is frustrating when an automation I create in Excel is easily dismissed, even after completion, in favor of virtually any alternative.

When I create an automation in 2 weeks that does a variety of things and then the IT department insists that it be put into SAP, and it takes them a month to even complete the scoping session I think to myself "hmm...Excel doesn't look so bad now for its versatility, flexibility and speed of implementation."

Thoughts?


----------



## Rhodie72 (Sep 27, 2016)

FDibbins said:


> My short term memory is not what it used to be, and I just keep forgetting what I have just done


Hmmm, I thought I was alone in that. Pay for the classes. Get the glasses. Study the options and discover that learning a new language can improve your mental health immesuarably and delay the onset of dementia in later years. Scientific fact.

I wrote a great set of programs about 6 months ago. I read through the code tonight and discovered that I was a coding genius and have completely forgotten how to code like that. Now I feel like an idXt (ID 10 T) because I no longer understand what I wrote and cannot link the modules together as they should be linked. Now I want a genius to teach me too. <shakes head in shame> you're not alone. I think it's the age thing...


----------



## Harry Flashman (Sep 27, 2016)

FDibbins said:


> While those are very valid reasons, you left out 1...I am pretty good with formulas (self-taught) but just simply cannot get VBA under my belt (and believe me, I want to - to the point of paying for classes).  My short term memory is not what it used to be, and I just keep forgetting what I have just done



This is how I learned VBA.

I used the macro recorder to automate simple tasks
I started out finding macros on the net, and then I started tweaking those macros.
After a while I wanted to learn more so I started studying:

I mostly learned from books: VBA for Dummies, Excel Power Programming with VBA, Excel VBA Programmers Reference (Excel 2007), and 101 Ready to Use Excel Macros plus a few other occasionally. The first two are excellent but they don't illustrate the OFFSET and RESIZE properties, which you really need to know when you want to work with ranges.

I started out with some very basic macros that I would make myself write a few times a week.

For example, fill cells with ascending numbers, fill cells with descending numbers and a few other trivial macros. If I could not remember how to write the macro I would refer to my notes. Often my notes would simply consist of a sort of pseudo code followed by the actual code. Keep doing this until you can write the basic macros from memory without referring to your notes (you may find you remember but then forget that is okay, just relearn what you forgot, eventually you will stop forgetting).

Then I started writing some very simple but useful macros:
Create table of contents (list of worksheets with hyperlinks). If you can't figure out how to do this, find someone else code and learn it by rote.
Rank data. Let's say I have table of values, this macro would copy the data to a new destination and then enter a formula with the RANK function into the cells, referring back to the original table as inputs.
Autofilter on doubleclick: this was worksheet macro where if the use double-clicked a cell in table, that table would then be filtered on that value.
And lots of other small but useful macros.

Sometimes I could figure out how to write macro by myself, other-times I would find code snippets on the net. And when I really got stuck I would post to a forum like this.

Eventually I started with the book 101 Ready to Use Macros by Michael Alexander. I learned a number of these basically by rote. If I got stuck I would refer to pseudo-code, and if I still couldn't make it work I would refer to the actual code. Keep working on each macro until you can write without referring to notes then move on to another macro.

All the while I would write macros for basic work tasks, even if I could do them faster using another method such as formulas.
Eventually I started making bigger projects. For example a charting application which would allow the user to automate pivot charts and tables, using criteria stored on a "predefineds" worksheet that would specify would the pivot tables and charts were to be configured. I made this project from scratch a few times each time making it better and better (and incorporating more advanced VBA techniques like using Classes). Eventually I created an application which could fully automate the production of PowerPower reports: ie, click a button and create and send 50 charts to powerpoint. This Excel application used form controls that would allow the user to select things like the report, client, time period, chart to be made etc.

In a nutshell just make continual baby steps, but always force yourself to learn something new.

One more thing that really helped me: I store every macro I write in Google Docs. Each document has a name that tells me about the contents of the document: For example: Excel VBA - Navigate Pivot Table Ranges
I can't remember how to do do everything, but if I have ever done it I can find useful code snippets in my Google docs. My Google Docs library has thousands of macros and code snippets now. If I sort in by date I can see how I have progressed over the years, from basic stuff to more complicated stuff.


----------



## FDibbins (Sep 27, 2016)

Rhodie72 said:


> Hmmm, I thought I was alone in that. Pay for the classes. Get the glasses. Study the options and discover that learning a new language can improve your mental health immesuarably and delay the onset of dementia in later years. Scientific fact.
> 
> I wrote a great set of programs about 6 months ago. I read through the code tonight and discovered that I was a coding genius and have completely forgotten how to code like that. Now I feel like an idXt (ID 10 T) because I no longer understand what I wrote and cannot link the modules together as they should be linked. Now I want a genius to teach me too. <shakes head in shame> you're not alone. I think it's the age thing...



Im with you on that.  I have written some very complex formulas for forum members in the past, and when I review them, I often wonder what genius wrote them, because I have NO ide what they do lol.

Thanks to all who have offered help and suggestions, I will take a look at all that was offered


----------



## DataAnalyst (Sep 28, 2016)

RickXL said:


> Maybe the rumour was started by gene researchers: Excel created major typos in 20 percent of scientific papers on genes.
> 
> It says: 'One mistaken gene conversion for example turns the gene symbol SEPT2, short for Septin 2, to “2-Sep.”'



Autoformat strikes again!  So annoying when I enter the year at the top of a column and Excel tries to include it in the sum - Or I want to write out the date and it's automatically changed to "2-Sep"... One day I looked for a way to turn this off. I didn't find it, but I learned you can put an apostrophe in front of your entry and that makes Excel see it as text.

Yes, you'd think I would know that, but it somehow escaped me in my self-taught skills... Remembering the apostrophe makes it a lot easier. Maybe this would help the scientists?


----------



## DataAnalyst (Sep 28, 2016)

FDibbins said:


> While those are very valid reasons, you left out 1...I am pretty good with formulas (self-taught) but just simply cannot get VBA under my belt (and believe me, I want to - to the point of paying for classes).  My short term memory is not what it used to be, and I just keep forgetting what I have just done



Do you work in a distracting environment, or do you have too much going on in your life? Are you often interrupted by phone calls? I've observed that distraction is a common cause of this.


----------



## Rhodie72 (Sep 29, 2016)

DataAnalyst said:


> Autoformat strikes again!... put an apostrophe in front of your entry and ... Maybe this would help the scientists?



I'm afraid that Science is very much a religion. Unless you're preaching from the pulpit of their house of worship then you're   only another heathen voice with no accreditation. It would be easier to show them that creation is the source of life and the Darwinism is only a theory, but really, that's their religion. So with these religious adherents you have to be in their accreditation group to be recognised as somebody to be able to allow them to learn from you... good luck when it's a foreign language like VB for applications! It's too simple for a scientist type to understand. Not.


----------



## Rhodie72 (Sep 29, 2016)

DataAnalyst said:


> Do you work in a distracting environment, or do you have too much going on in your life? Are you often interrupted by phone calls? I've observed that distraction is a common cause of this.



YES!!!!! My ****ed life is a distraction! There is seldom a continuous hour that passes without one whilst I'm writing my code out. <sigh> I think it's time to sell the kids into servitude and slaughter the wife for dog food... Maybe I'll end up in a British cell with a laptop to code in peace for a few years and sell the result for millions! LOL. Oh! Did I say that out loud???? It will never happen. Time to put the monster headphones on and pretend I'm listening to rock music at full volume! That works! Little do they know...




.


----------



## FDibbins (Sep 30, 2016)

DataAnalyst said:


> Do you work in a distracting environment, or do you have too much going on in your life? Are you often interrupted by phone calls? I've observed that distraction is a common cause of this.



Not particularly, but I am no spring chicken anymore lol


----------



## Janissaire (Oct 8, 2016)

I don't like the way Microsoft want to put you into a box - you use excel fantastic - now we want you to run all your excel sheets in the same window and have you use window views. I have two monitors and would like to view sheets side by side - but if I do I can't copy paste as values from one to the other! Thanks Microsoft!


----------



## FDibbins (Oct 8, 2016)

Janissaire said:


> I don't like the way Microsoft want to put you into a box - you use excel fantastic - now we want you to run all your excel sheets in the same window and have you use window views. I have two monitors and would like to view sheets side by side - but if I do I can't copy paste as values from one to the other! Thanks Microsoft!



I dont see why you cannot to that?


----------



## hellfire45 (Aug 8, 2016)

In the last 5 years of working with Excel and VBA, I have numerous times run across people who flat out seem to dismiss the utility of using Excel, sometimes entirely.  

I hear things like:

"Excel isn't robust, we have to put it in SAP, Alteryx, Access etc.."

"VBA doesn't add value to the end user."

"VBA and Excel isn't programming."

"Excel workbooks are too slow."



Everybody seems to reject Excel first as a potential solution to problems.    I have 100 page long VBA programs that do hundreds out hours of manual work in 10 minutes and when I write an excel formula that has 25 nested functions in it, surely that too is programming.

Anybody else run into this kind of attitude and feel frustrated by it?  As an analyst that primarily uses Excel and Tableau it is frustrating when an automation I create in Excel is easily dismissed, even after completion, in favor of virtually any alternative.

When I create an automation in 2 weeks that does a variety of things and then the IT department insists that it be put into SAP, and it takes them a month to even complete the scoping session I think to myself "hmm...Excel doesn't look so bad now for its versatility, flexibility and speed of implementation."

Thoughts?


----------



## Krayons (Oct 8, 2016)

Janissaire said:


> I don't like the way Microsoft want to put you into a box - you use excel fantastic - now we want you to run all your excel sheets in the same window and have you use window views. I have two monitors and would like to view sheets side by side - but if I do I can't copy paste as values from one to the other! Thanks Microsoft!



This *is* possible; at least in Excel 2016 it is.

*1.* On the View tab, in the Window group, click *New Window*.
*2.* On the View tab, in the Window group, click *View Side by Side*.
*3.* In *each workbook window*, *click the sheet* that you want to compare.

When doing this, I can view two sheets simultaneously, one in each monitor, and freely copy and paste data between the two.


----------

