Developing an Excel
Applications
By Prof. H-M. Haav
For creating an application one
needs
• Macros
• Modules
• Controls
Macros can be created in two
ways:
• Recording
– plan actions you need to automate
– Tools>Record a new macro
– perform actions you need to automate
– stop recording
• Writing macros in VBA
• Tools>macros
Visual Basic for Applications
VBA
1. Structure of Procedure (module)
Sub name_of_program (parameters,…)
<statements>
…..
End sub
2. Reading data from active sheet
variable = cell (reading one value)
S= 5
S=Range(“A1”)
Cells(row index, column index)
S=Range (“Start”)
returns value of specified cell
S=Cells(1,1)
2. Writing data to active sheet
range = expression
5 Cells(1,1) = 5
Range(“A1”) = “last name”
Range (“Start”) = “last name”
Cells(1,2) = SUM (B1:B7)
Naming ranges is useful for
creating programs in VBA
Sheets(“Sheet name”).Range(“Start”)
4. Dialog Boxes allow dialog
with end-user of an application
Sub computer (optional Year)
Year =InputBox(“Input year of craetion of the
first PC”)
If Year = 1976 Then msg=“True!”
Else msg=“False!”
MsgBox msg
End Sub
Objects and classes in Excel
Application Command bars
dialog
chart Workbook(s)
windows
Chart objects Worksheet(s) Shape(s)
Range
References to objects
Workbooks(“BananaMadness.xls”)
Worksheets(“Title Sheet”)
Sheets(1)
ActiveWorkbook.Sheets(3)
Rows(3)
Columns(1)
Range(“Starts”).Cells (all cells in the range)
Properties of objects
Object.[property]
x=Range(“Starts”).value
assigns variable x a value of range called Starts
.ActiveSheet
.ActiveCell
.Selection
Range(“Starts”).CurrentRegion
…etc
Methods of objects
Object.method[arguments]
Worksheet(“Title Sheet”).Select
Worksheet(“Title Sheet”).Activate
Worksheet(“Title Sheet”).Calculate
Worksheet(“Title Sheet”).Delete
Range(“Starts”).Clear
…etc
Controls
• Button
• Radio button
• Combo box
To create a control
– turn on Forms toolbar
– select the control
– drag the control to needed area
– define properties
Assign macro to a control
• select the control
• on Tools menu, click Assign Macro
• enter or choose the name of your existing
macro
Examples of recorded macros
Sub TotalBonus()
Range("I7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("I7").Select
Selection.AutoFill Destination:=Range("I7:I10"),
Type:=xlFillDefault
Range("I7:I10").Select
Selection.Style = "Currency"
End Sub
