1

CS 106Computing Fundamentals II

Chapter 23“Controls And Events”

Herbert G. Mayer, PSU CSHerbert G. Mayer, PSU CSStatus 7/5/2013Status 7/5/2013

Initial content copied verbatim fromInitial content copied verbatim fromCS 106 material developed byCS 106 material developed by

CS professors: Cynthia Brown & Robert MartinCS professors: Cynthia Brown & Robert Martin

2

Syllabus Next StepNext Step Relating to VBA ProcessesRelating to VBA Processes MacrosMacros CodeCode EventsEvents Referring to Objects, PropertiesReferring to Objects, Properties

3

Next Step

• Previously, we discussed general problem Previously, we discussed general problem solvingsolving

• In this module, we’ll apply what we In this module, we’ll apply what we learned, from specification to design to learned, from specification to design to implementation and testing, to VBA on a implementation and testing, to VBA on a very simple processvery simple process

• Later we’ll gradually introduce VB Later we’ll gradually introduce VB features that let us implement more features that let us implement more complex processescomplex processes

4

But First…

• We have to learn enough about VBA and We have to learn enough about VBA and Excel to write an interesting program!Excel to write an interesting program!

• So our goal in this module is to learn So our goal in this module is to learn those basics and apply them to a problemthose basics and apply them to a problem

5

Relating VBA to Processes

• We will typically represent the objects in We will typically represent the objects in our process specification by Excel or our process specification by Excel or VBA VBA controls controls and other Excel and VBA objectsand other Excel and VBA objects

• Objects and controls have Objects and controls have eventsevents associated associated with themwith them

• The process events will be translated into The process events will be translated into control eventscontrol events for which we will write for which we will write proceduresprocedures

6

Macros

• We’re going to start by writing (We’re going to start by writing (notnot recording) a small macro with a button to recording) a small macro with a button to control itcontrol it

• This will introduce a number of basic This will introduce a number of basic conceptsconcepts

7

Code

Option ExplicitOption Explicit

'***************************************'*******************************************

' Turn the active cell red' Turn the active cell red

'***************************************'*******************************************

SubSub RedCell() RedCell()

ActiveCell.Interior.Color = vbRedActiveCell.Interior.Color = vbRed

End SubEnd Sub

8

The Video

• You can watch the steps of writing the You can watch the steps of writing the macro and adding the button on the video macro and adding the button on the video called Button Democalled Button Demo

• The workbook is available on the Spring The workbook is available on the Spring 2013 class site2013 class site

• After you watch, try writing your own After you watch, try writing your own macro, say GreenButton, in the same macro, say GreenButton, in the same worksheetworksheet

• Give it a button and/or a piece of clip Give it a button and/or a piece of clip art to activate itart to activate it

9

Events

• Clicking the button or the clip art is an Clicking the button or the clip art is an event that triggers the execution of the event that triggers the execution of the macromacro

• This is a pattern we will continue to This is a pattern we will continue to follow: our macros will be triggered by follow: our macros will be triggered by events that happen in the spreadsheetevents that happen in the spreadsheet

• Later we will also write macros that can Later we will also write macros that can be called by other macrosbe called by other macros

10

Referring to Objects, Properties

• Consider the expression Consider the expression

ActiveCell.Interior.Color = vbRedActiveCell.Interior.Color = vbRed

• ActiveCell is an object recognized by ActiveCell is an object recognized by Excel: the cell that has been selected in Excel: the cell that has been selected in the the active worksheetactive worksheet

• Interior is a subobject of the ActiveCell: Interior is a subobject of the ActiveCell: it’s just the interior of the cellit’s just the interior of the cell

• Color is a property of the Interior, and Color is a property of the Interior, and as such can be set to a value by an as such can be set to a value by an assignment statementassignment statement

11

Properties vs. Variables

• We’ll often set values of properties of We’ll often set values of properties of objectsobjects

• Cells in particular have a property called Cells in particular have a property called the Value: the Value: ActiveCell.Value = 5 ActiveCell.Value = 5 will make will make the number 5 show up in the cellthe number 5 show up in the cell

• Besides properties of objects, we’ll also Besides properties of objects, we’ll also use more general variables for quantities use more general variables for quantities we want our program to remember and work we want our program to remember and work withwith