The basics needed to be a good Excel programmer

[xenou]

I know its been asked before but I am in a hurry...

What would a "student" need to know in order to be a good excel programmer. Imagine you have 3 weeks in which to teach it.

 

[xenou]

Here's what I have so far:

Understanding the variant data type
Functions vs Subs
Error Handling
Working with in memory data structures
VBA Collections vs VBA Arrays
Working with Named Ranges
Manipulating Range Objects
Validating and Cleaning Data (extremely important in Excel)
Screen updating
Excel Calculation Model
Linked workbooks
Connecting to external data (CSV, other worksheets, databases)
Sheet code vs Module code
Excel Userforms
Creating and Installing Excel Addins
Excel Formulas and Functions (there are dozens of extremely important ones)

 

[alansidman]

Intro to Power Query. Not learning it, but knowing what it can do for you. Too many Excel users today do not have a clue as to how powerful this Excel feature is or that it even exists.

 

[xenou]

ah, I confess I need that lesson

 

[alansidman]

The best place to start is clicking on the link in my signature. Then pick up a copy of "M Is for (Data) Monkey" by Ken Puls and Miguesl Escobar. I think it is available in the Mr Excel Store. It is amazing how much time you can save with this Excel feature.

 

[Rick Rothstein]

For VBA programming... one should learn how to use the Like operator. For the final lesson on it, see if they can understand how this works...

If Not SomeText Like "*[!0-9]*" Then
  MsgBox "SomeText contains only digits in it."
End If

 

[Celly]

If you are teaching Excel error handling, it is important in my opinion to also teach why it is a flawed design.

On Error Goto -1? Really?

 

[Joe4]

If you are teaching Excel error handling, it is important in my opinion to also teach why it is a flawed design.

On Error Goto -1? Really?

Could you expand upon that?
In what situation do you think it is flawed (I suppose anything could be flawed if not done properly)?
Do you think it is better to not address errors at all?

 

[Celly]

Could you expand upon that?
In what situation do you think it is flawed (I suppose anything could be flawed if not done properly)?
Do you think it is better to not address errors at all?

It's based on obsolete VB6 technology and goto statements. It is a bit awkward to control the program flow. Modern VB.NET employs structured exception handling which is far more elegant and straightforward, as do many other programming languages like C++.

That said, as a developer you still need to carve out best practices using the tools you have and address errors in the most robust way possible.

My first contract as a software developer in the 90's was to develop a VB6 application. I'm not averse to the technology and can mostly forgive it. That said, the error handling implementation in particular has not aged well.

 

[RoryA]

I've never seen anyone teach people to use On Error Goto -1. Most people don't even know it exists in VBA. (and I'd just clarify it's VBA error handling, not Excel ) IMO, if you're using that, you've dug yourself into a hole with some bad code and gone looking round the internet for a solution that didn't involve rewriting your code properly.