View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
dhstein dhstein is offline
external usenet poster
 
Posts: 266
Default Need An Idea For Teaching VBA

Joe,

Thanks for your suggestions. Some additional information: the class has
been meeting for several weeks and we covered the Excel basics already.
We've also done some record macros and have had an introduction to writing a
subroutine with a For .. Next Loop to fill a range of cells. We also did an
Input Box and a MsgBox. They seem to do better with "hands on" examples
rather than just reading from the book. I have Walkenbach's Excel 2007 Power
programming book and I may try to find something there.

David

"JoeU2004" wrote:

"dhstein" wrote:
The students are novices for the most part - some have never
programmed before. I'm looking for a suggestion for a
programming problem of some sort that could be used in the class.


"VBA programming" is huge topic. There is a lot of VBA that I don't know
myself.

The first step is to decide what principles and features of VBA programming
that you want to introduce and to what degree.

In part, that depends on how much teaching time you have. You mention
something "should take about 4-5 hours to do". I'm not clear on whether
that's the programming problem itself, or the teaching time, or both.

As for programming problems, in part that depends on the areas of interest
and experience of the students. For example, are they accouting people; or
are they math people; or ...?

Anecdote.... I was asked to teach graduating high school seniors how to use
programmable calculators "to prepare them for the real world". It was a
60-90 minute extracurriculum class. I was told that they were "committed"
to learning. I came in with an accounting problem that would require
iterating over a formula. I built up the problem and the solution
step-by-step, starting with basic data entry. I lost them the moment I
pressed the key to enter a program. As it turns out, they just wanted to
learn how to add, subtract, multiply and divide, which I covered in the
first 10 minutes. Klunk!

Having decided what your audience is and you want to teach, you might post
back here for suggestions. Off the top of my head, at a minimum I would
cover:

1. Record Macro. Not really VBA programming; but an important tool for
learning how to (and how not to!) manipulate Excel objects.

2. How to reference individual cells in VBA macros. How to declare,
reference and pass cells to VBA UDFs.

3. Write a simple macro to modify the value and/or formula of one cell.

4. Modify #3 to work on a range. Introduces For loop constructs.

5. Modify #4 to work on a range based on conditions. Introduces If and Do
statements.

Based on those fundamentals, a simple programming assignment might be:
write a macro that deletes cells (or rows or columns) with specified data.
They can use Record Macro to learn how to delete things and pull up data.
You might need to fill in the blanks by providing some methods for them to
use.

That might not sound like a "4-5 hour" programming assignment to us. But
for someone who "never programmed before", the concepts might be very
challenging.

I would caution against "interesting" programming assignments, something
that might take __you__ 4-5 hours to programs, and problems that require
special knowledge or interests, unless your audience is homogeneous and very
familiar with the techniques on paper or with a calculator.

PS: You might take a loot at the problems in Walkenbach's "Excel 2003 Power
Programming with VBA". While I suspect that much of the material is over
the heads of someone who has never programmed before, it might give you some
ideas for "interesting" programming assignments.


----- original message -----

"dhstein" wrote in message
...
I'm teaching a class on VBA for Excel. The students are novices for the
most
part - some have never programmed before. I'm looking for a suggestion
for a
programming problem of some sort that could be used in the class.
Something
not too complex, but maybe with enough of a challenge to touch on many of
the
properties and methods. I'm not that creative and so far I can't think of
anything. Ideally it should take about 4 - 5 hours to do - just to give a
sense of the type of problem I'm shooting for. Thanks for any suggestions
on
this.