Why does everybody hate Excel?

[hellfire45]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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?