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.

"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.

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.

"dhstein" wrote:
Some additional information:

Valuable input. It certains changes my perception of the situation.

But you omitted the answer to a key question: what is the focus of interest
and the experience and age group of the students? What kind of problem
would they relate to?

And what would __you__ like the students to walk away knowing how to do, if
anything in particular?


We also did an Input Box and a MsgBox.

I hope you also cover Debug.Print, as well as how to abort a run-away
procedure (ctrl+alt+Pause on my keyboard).


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

"dhstein" wrote in message
...
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.

You might even look through some of the questions posted here for 'general'
ideas. One that comes to mind is using code to either transpose a column of
data on one sheet into rows on another; or going the other way from rows on
one sheet into columns on another. We see this from time to time where a
person has a list of names, addresses, phone #s in a column that they want in
rows to use for mail merge; or they have sheet with data in rows and want to
make a list of just names on another. Something like that could have general
use in several "areas of interest" as JoeU2004 brought up.
While these are problems that can be solved with formulas, I personally find
that a VBA solution is faster for me to develop.

One variation of the above would be to have a sheet with names, addresses,
other info in rows, with a list of the same names in a column on a second
sheet with the object of the exercise to match up the names, insert rows as
needed and bring the addresses and other information from the first (rows)
sheet into the same column under the matching names.

Just a couple of thoughts on one theme.

"dhstein" wrote:

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.

dhstein wrote:
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.


If your students are novices, I don't think you really want to teach
them something that takes 4-5 hours to do. That time frame suggests some
VBA code that is quite complex.

A set of smaller projects would seem more appropriate.

Bill