Excel expert opinions please 3

[N1GHTEYES]

I need some suggestions for ideas on "tips & best practice" for Excel.

At the end of a recent presentation to our department, my boss gave an additional ~20 min lecture on tips and best practice when using Powerpoint to give good presentations. He then dropped me right in it by suggesting that sometime soon I should do something similar with respect to Excel. The fact that I'm seen as the local Excel guru tells you a lot about the state of everybody else's knowledge in our dept!

Most people here tend to use it for fairly technical data analysis purposes, though not necessarily with a great deal of technical skill. So I think it would be best to avoid dealing with issues like making a spreadsheet look "pretty". Instead I reckon more useful issues would be:

keyboard shortcuts - e.g. F4 to toggle between relative & absolute refs

use of array formulae

named ranges - including dynamic ones using offset

data layout - e.g. Skip's constantly repeated exhortation not to mix datatypes in a single column

At this stage I have not decided what to include or how "deep" to go, but I'd really appreciate any suggestions for topics to include and any "hot tips" that would be worth including.

Thanks in advance,

Tony

 

[BNPMike]

Never use VBA under any circumstances. Look for an Excel solution even if you need to dig a bit.

VBA is just fine but it's like you want to give good presentations so why not learn German for when it's neater?

 

[GlennUK]

Never use VBA under any circumstances. Look for an Excel solution even if you need to dig a bit.

VBA is just fine but it's like you want to give good presentations so why not learn German for when it's neater?

While I strive to use non-VBA solutions in every case, that is sometimes not possible for a solution.

If fact there are many things for which VBA is the only solution, and so I must point out to anyone else reading this that VBA is acceptable in many circumstances.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[BNPMike]

While I strive to use non-VBA solutions in every case..."

Glenn - keep up the good work.

 

[N1GHTEYES]

BNPMike - Really? Please tell me you're joking.

I agree with Glen - looking for an Excel solution first is a good idea as it is often faster and sometimes neater. But a blanket VBA ban?

They say extraordinary claims demand extraordinary evidence. I'd say your suggestion fell into that category.

Tony

 

[SkipVought]

It is an ignorant, self-imposed restriction.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BNPMike]

Tony

I think your original list is quite appropriate for your audience.
Asking them to become seasoned programmers in a rich and complicated textual programming language would need some major justification.
SkipVought likes it because he's a nerd and wants some friends to talk to in the bar.

It's just advice. I've used VBA and I haven't been scarred for life but I just think if you want to be a programmer you should move to IT.

 

[SkipVought]

What a parochial point of view. You are welcome to it, but it will not carry very much weight among your contemporaries here.

But no one in this forum is asking anyone to "become seasoned programmers..." We encourage Excel native solutions in this forum and members who desire to pursue a VBA solution, are most often directed to post in forum707.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BNPMike]

I'm a project manager by trade. Regularly I inherit systems built by people who have learnt a bit of VB or C# or whatever.

I'm the one who has to work long, torturous days and unpaid weekends to try and wade through the horrible digusting messes that these people create.

Getting a grip of the basics of something like VBA is no mean feat but it does not make you a competent programmer.

If you want people to start using VBA then you should put them in an environment that supports professional IT. But of course no-one in professional IT uses VBA any more, except occasionally.

VBA is a nice enough language, especially compared to what I was brought up on, but I struggle to see why you would encourage people to use it nowadays, particularly if they already have some familiarity with a much more friendly platform ie spreadsheets.

 

[SkipVought]

Then your IT ought to set permissions to prevent using macros.

There are some things that spreadsheets cannot do with native features.

There are labor saving macros that many of my users employ, some that they record and use and MANY more that I create for them. In the words of my boss, you just completed my mid-year review, "Skip has created many labor-saving tools that have saved the Resource Analysts tens of thousands of non value added hours..."

So when I go away, it may not be maintained and they may have to go back to doing the grunt work, but the effort is well accepted and has a very positive ROI.

One cannot make a blanket statement. I agree that certain things may not be cost effective in YOUR world, but IT is only one part of the elephant.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BNPMike]

But you are a seasoned programmer. I'm trying to think how you make Excel usable for ordinary people without their having to go through the personal development needed to get competent and efficient at VBA.

I wasn't referring to Excel VBA bad programming by the way. I was referring to .NET web sites and services. You get youngsters who can put together 8000 lines of code but unfortunately don't realise that spaghetti is meant for eating.

 

[N1GHTEYES]

I'm trying to think how you make Excel usable for ordinary people without their having to go through the personal development needed to get competent and efficient at VBA

That is precisely the kind of thing I already do using VBA.

My dept consists of very competent sensor / electro-optics engineers, some of whom are competent programmers, some aren't. But they can all use Excel - at least the basics. I have made life easier for myself and everybody else by creating, and making generally available, an add-in which implements a slew of topic-specific functions, which saves everyone an enormous amount of time. I wrote that add-in in VBA. What's wrong with that?

Tony

 

[BNPMike]

Two things wrong with that.

Number one is the average person hasn't got you to provide the easily digestible functionality. So when I say don't use VBA, I mean don't use your own VBA; only use Tony's.

Number two is you aren't usually around during the morning.

 

[combo]

I think that the vba is not the real problem. A company has to have policy to control what's going on in your everyday job and, if necessary, easily replace person involved. And, of course, company's tasks and tools should be under control of IT professionals and documented, together with shared excel (or other office apps) add-ins. I remember some posts on this forum asking for a tool to help to open documents password-protected by fired person. Such documents are a pain, without no line of code.

Excel is a flexible, good analytical tool, and vba is a natural extension, both in case of UDFs and automation of tasks. You can't block people, at least in some areas, to use the full power of excel. On the other hand people can develop sophisticated excel applications without vba at all, it can use dynamic multi-level names, external data queries, links to external workbooks, hidden sheets, scenario managers, solvers and other advanced stuff. Such workbook will be harder to understand than some spaghetti vba code.


combo

 

[N1GHTEYES]

Number two is you aren't usually around during the morning

Oh, I'm here alright, bright and early at 9:30 (the end of Flexi start period). At least, my body is. My brain usually ambles in about noonish.

Seriously though, can we agree to disagree on the whole VBA thing?

Or maybe we can all agree on the following:

1 In most cases it is better to at least look for a native Excel solution rather than diving straight into VBA without any thought beforehand, because native Excel is often faster and sometimes neater.

2 If you are addressing someone else's problems with a workbook solution, then, if you have to use VBA, insulate them from it by providing buttons & UDFs that look, to them, like "normal" Excel.

3 Commenting and controlling your code is usually a good idea anyway, but especially if it is going to form part of a tool your company commonly uses and may continue to use after you are gone.

4 Whatever the circumstances, logical, neat code, properly indented and commented, using closed loops (no GOTOs) and sensible names for variables is always a good idea.

Does that sound fair?

Tony

 

[SkipVought]

I suggest that you look thru the code I have posted, and you will notice that although I am not a prince, there is no Wednesday in my code.

[sub]Google as required to decipher[/sub]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

Tip: The least intuitive and most useful thing I've gleaned from this site: put your column totals etc. at the top of the columns instead of the bottoms (that way you see them straight away, and you can add extra data in further rows at the bottom).

There are too many things to cover! Excel is full of magic key combinations that no one can remember.

I sort-of sympathise with both sides of the VBA argument. Too often people try to solve a problem with VBA because they are unaware of how it could be solved more easily with built in functions. But VBA is very useful.

Really Important Point: learn the limitations of Excel. If you need extensive VBA, and are really struggling, perhaps another piece of software would be better. In particular, for extensive numerical analysis, Matlab, R, or any numerical package will be vastly better (if you're looking regularly at matrices that fill a complete worksheet, you shouldn't be in Excel).

If you're writing a database, you should be using Access, not Excel.

 

[N1GHTEYES]

Skip,

Huh?

Tony

 

[SkipVought]

Did your Google? ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Did your Google? (sic)

Google gave a flood of results, like a river in spate. So now I know more, but I'm still no wiser.

Maybe my brain is taking longer to catch up today than usual...

Tony

 

[SkipVought]

What did you google?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]