# Volunteering to Teach High School Students Advanced Excel and VBA -- Seeking Input!



## pawest (Sep 26, 2012)

Hello Advanced Excel and VBA community,
Separate from the typical questions seeking help on this form, I am seeking input. I'm starting up a volunteer program (3 trial sessions) to teach Advanced Excel and VBA. These session will be around 90 minutes and with private high school students apt to the math and sciences. These kids can handle fast pace instruction. I would like to get your opinions on what you think is absolutely necessary to teach. 

Here's my very general plan right:

Session 1:

Introduction to capabilities of Excel 
Using the keyboard/Alt and learning hot keys 
Walking though each section of the ribbon 
Basic finance and math models using Excel functions 
Session 2:

Advanced excel formulas 
Pivot Tables, Handling Flat Files, Possibility of External Data Add-Ins and Solver 
Proper Formatting 
Advanced excel models and handling of reports (relating to my real world work) 
Session 3:

Basic VBA 


Any input would be valued. Thanks!


----------



## JLGWhiz (Sep 26, 2012)

pawest said:


> Hello Advanced Excel and VBA community,
> Separate from the typical questions seeking help on this form, I am seeking input. I'm starting up a volunteer program (3 trial sessions) to teach Advanced Excel and VBA. These session will be around 90 minutes and with private high school students apt to the math and sciences. These kids can handle fast pace instruction. I would like to get your opinions on what you think is absolutely necessary to teach.
> 
> Here's my very general plan right:
> ...



IMHO, the most important of all is teaching how to use the help files of both Excel and VBA.  90 percent of the questions asked in this forum can be answered in those files, if the user knew how to use them.


----------



## shg (Sep 26, 2012)

Amen.


----------



## pawest (Sep 26, 2012)

Noted. Great input. 

One of my proposals was to teach the students how to learn on their own by looking into great books 
(such as John Walkenbach's texts), using great youtube videos, using this site, etc. 
I should include the help files (which is something I admittedly don't take advantage of as much as I should).

Many people would argue there are two people: people who don't know VLOOKUP, and people who know VLOOKUP. Do either of you have any opinions on this and other things that should be taught?

Thanks.


----------



## T. Valko (Sep 26, 2012)

pawest said:


> Hello Advanced Excel and VBA community,
> Separate from the typical questions seeking help on this form, I am seeking input. I'm starting up a volunteer program (3 trial sessions) to teach Advanced Excel and VBA. These session will be around 90 minutes and with private high school students apt to the math and sciences. These kids can handle fast pace instruction. I would like to get your opinions on what you think is absolutely necessary to teach.
> 
> Here's my very general plan right:
> ...


I hope "proper formatting" includes "use minimal formatting"!

Teach them that Excel is not an electronic coloring book for adults!


----------



## shg (Sep 26, 2012)

> Excel is not an electronic coloring book for adults!


Say it, Brother!


----------



## pawest (Sep 26, 2012)

Exactly what I meant!
A spreadsheet with little to no colors is the best kind of spreadsheet in my opinion.
I would include how to format continuous data sets/tables (at least how I think they should be formatted-- autofit with bold headings and non-bold raw data and that's it) and financial statements (with bold headings, currency formats, proper spacing, red and black text, and cell borders). 

"Less is more"


----------



## NewOrderFac33 (Sep 26, 2012)

Something that I would definitely teach is the optimum way to set up a database that consists of data headers followed by data with the whole thing surrounded by empty space (blank row above and below, empty column to the left and right).
I have improved on this over many years and now have what i consider to be an optimum database format where the first field header is always called "DatabaseStart" and VBA is called to define a range called "Database" that includes the field headers and a range called "Data" that doesn't. The best way in which these ranges can be defined is using Excel's CurrentRegion property, which in turn allows you to create range names for each column of data, which can subsequently be used by SUMPRODUCT formulae.
Thus, it's vital to build the database correctly first so that all the other stuff can follow on from it.
In fact, I'm so pleased with where I've got to with this that I'd be happy to share a sample of the model if you'd be interested.


----------



## pawest (Sep 26, 2012)

see following post...


----------



## pawest (Sep 26, 2012)

Fellow Pete (my name is Pete too!)

I do not disagree with your formatting. However, I am not an advocate of using Excel like a database. I believe that's what Access, SQL Server, MySQL, etc. have been made for. I believe Excel should only be used for performing analysis on data. This may entail a lot of data. For example, I use spreadsheets with millions of cells filled with data on a daily basis. Yet, I don't think I would advocate to young students that Excel should be misconstrued as a database. 

What do you think?


That being said, when I use FTP files for example that contain huge sets of data, I virtually calculate the data in ways you would with VBA, as well. I would be pleased to see your an example model. I will message you my email address.


----------



## pawest (Sep 26, 2012)

Hello Advanced Excel and VBA community,
Separate from the typical questions seeking help on this form, I am seeking input. I'm starting up a volunteer program (3 trial sessions) to teach Advanced Excel and VBA. These session will be around 90 minutes and with private high school students apt to the math and sciences. These kids can handle fast pace instruction. I would like to get your opinions on what you think is absolutely necessary to teach. 

Here's my very general plan right:

Session 1:

Introduction to capabilities of Excel 
Using the keyboard/Alt and learning hot keys 
Walking though each section of the ribbon 
Basic finance and math models using Excel functions 
Session 2:

Advanced excel formulas 
Pivot Tables, Handling Flat Files, Possibility of External Data Add-Ins and Solver 
Proper Formatting 
Advanced excel models and handling of reports (relating to my real world work) 
Session 3:

Basic VBA 


Any input would be valued. Thanks!


----------



## JLGWhiz (Sep 26, 2012)

One other thing that would be helpful to beginners is to learn how to diagram their projects.  Not just VBA, but using Excel with formulas also.  The database layout is very important when they first think of accumulating data.  They need to understand what the data may be used for in the future and learn to organize the data so that formulas or VBA can be easily applied to achieve desired results.  Of course, this goes hand in hand with understanding how the other part works also.  But the point is, just teaching how to construct a formula or develop code will only be as useful as their knowledge in how to organize data.


----------



## Rick Rothstein (Sep 26, 2012)

JLGWhiz said:


> IMHO, the most important of all is teaching how to use the help files of both Excel and VBA. 90 percent of the questions asked in this forum can be answered in those files, if the user knew how to use them.


Agreed!

With respect to the VBA help files, in case you are not aware of it yourself, the most effective way to get help on a VBA keyword is to type that word into any code module and, with the text cursor embedded within or next to the word, press F1.

As to teaching VBA itself... the one thing I have always found most useful when I learned a new programming language was to learn what functions, subroutines, objects, methods and properties are available... not to learn the syntax nor usage of each, but rather to just learn that it is there and what is generally does... then, when you need the particular functionality offered by the keyword, you will remember it is available. Syntax and usage for it can come from the help files, but if you don't know a it's available in the language, you will never know to use it in a program you write. I feel that, above all, is critical. They don't use the word "language" in when for referring to a programming language for nothing... functions, subroutines, objects and their methods/properties are like the words of a language and code is like the sentences of that language... if you don't know of a word's existence in a language, you will never be able to write a sentence using it. Of course, there is much too much in VBA to cover everything related to the objects in the Excel VBA model, but exposing the students to the more common ones and directing them to the help files (Help/Microsoft Visual Basic Help from the VBA Editor's menu) for them to study the less common ones would be useful in my opinion.


----------

