Need An Idea For Teaching VBA
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. |
Need An Idea For Teaching VBA
"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. |
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. |
Need An Idea For Teaching VBA
"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. |
Need An Idea For Teaching VBA
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. |
Need An Idea For Teaching VBA
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 |
All times are GMT +1. The time now is 03:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com