Not in Text
Winter 2012
No VBA Required
“Regular” Programming
• traditional programming is sequential in nature
o one command executed after another
o even with looping and conditional statements
o program has a “flow” to it
Sub myProgram()
Dim var As String ' Do this
result = Msgbox ("Welcome") ' Then do this
If result = vbYes Then ' check this
Msgbox "Great!" ' Either do this...
MsgBox "Not good!" ' or this
End Sub
Event-Driven Programming
•modern, graphical oriented programs are driven
by events
•a program will be running in memory, waiting for
the user to do something
oor possibly waiting for the system to do
things the user does (or the system) are called
A Typical Event Driven Program
Events and Event Handlers
•typical events would include clicking a button,
moving the mouse, or typing in text
•when an event happens, the code that responds
to that event is executed
othis code is called an “event handler”
• the event is said to have fired or been triggered
Graphical User Interfaces
•in the early days programs were written to run
from command prompts such as DOS or Unix
othe only “event” was a user typing something
and pressing enter
• creating a program with a graphical user interface (GUI pronounced “gooey”) uses event-driven programming
• windows, buttons, forms, icons, desktop, dialog boxes
are standard terms in graphical user interface design
• user interface design is an art and a science
• google "user interface design" "human-computer
interaction" "hci" "human factors" or other related topics
GUI Controls
• the primary layer for a GUI is a window or a form
• as a programmer, you add controls (also called
widgets) to the form
GUI Controls
o Java has a complete library of controls called the Swing
 makes writing GUIs quick and easy
 well...easier than doing it by hand!
o VB and VBA (and Microsoft Visual [Anything]) allows for
drag-and-drop GUI design
o simply make a form, add some controls and you have a
very fast prototype
 a (usually non-functioning) sample of your application
Simple Events
•you can then add code to the event handlers
for each widget
•some code you write will never be executed!
•try it out: drop a control on a form
odouble-click the control to display the default
event handler
• click the drop-down list on the right side to see the
events you can program for the current object
• the list on the left allows you to choose from the
objects on the form
Basic Events
• Userform events:
o Initialize (default), Activate, Click, DblClick, KeyPress,
Button events:
o Click (default), Enter, MouseUp, MouseDown, Exit (will fire
when the form closes)
TextBox events:
o Change (default), AfterUpdate, BeforeUpdate
Other Events
• Application, Workbook, Worksheets and Charts can all have
• Workbook_Open: runs code as soon as file is opened, not
recommended when distributing to the public, most apps
have macros disabled anyway.
Chart_Activate: fires when a chart is activated (clicked on)
Worksheet_Change: cells on the worksheet are changed
Worksheet_Activate: a worksheet is activated
Worksheet_Deactivate: a worksheet is deactivated
Experiment in the VBE to find other events
Design Options
• developing GUI software can start with simple sketches on a
• software mockups can be created in a graphics program like
Adobe Photoshop
o nice to look at but no interaction
other software is availble to help design the look of the
iPhone Design Template (Photoshop)
Layered, to
layouts easy.
Rapid Prototyping
• one of the benefits of VB / VBA
• quickly and easily create a mockup of your software
o no code required!
can let users see it and play with it
o can add basic message boxes to make it interactive
allows users to work with the product and see what
works and doesn't work
can easily make changes based on their feedback
UX - User Experience
• UX - the intersection of Art, Science and Craftsmanship
• The subjective experience a user has when performing
a task while using your software (make them happy, not
•Jakob Nielsen: A User Design Specialist
• User Interface Design Tips, Techniques and Principles
• Excel 2007 Power Programming with VBA by John
Walkenbach (now in an Excel 2010 version)
• Mockup Screens - $99 software! (video)
• Free GUI design add-on for
Firefox: http://www.evolus.vn/pencil/

similar documents