Transcript Overview • Introduction to VBA • The Visual Basic Editor (VBE) – First Program • Getting Started with VBA – – – – Subroutines Declaring variables Input boxes and Message boxes With.

Overview
• Introduction to VBA
• The Visual Basic Editor (VBE)
– First Program
• Getting Started with VBA
–
–
–
–
Subroutines
Declaring variables
Input boxes and Message boxes
With construction
1
Introduction to the VBA
• VBA, Visual Basic for Applications, is an
implementation of Microsoft's Visual Basic, an
event driven programming language and
associated integrated development environment
(IDE) which is built into most Microsoft Office
applications .
• However, VBA and VB are not the same.
– Similar in programming elements
– VBA is the language to manipulate Excel (and other
applications).
2
Visual Basic Editor
• Visual Basic Editor (VBE) is the
Integrated Development Environment (IDE)
for programming with VBA.
• It can be accessed by pressing
[Alt] + [F11]
• Code window
• Project explorer, Properties window,
(just like IDE for VB.Net)
3
VBE
4
First Program
• Open
“FirstProgram.xls”
downloaded from Web
site.
• Press [Alt] + [F11]
5
FirstProgram (cont.)
• Insert a module to the
project
• Start typing
Sub CountHighSales
and hit [Enter]
• You will see a Subroutine
block is inserted
automatically.
6
FirstProgram (cont.)
• Type the rest of the syntax in the module:
Dim
Dim
Dim
Dim
i As Integer
j As Integer
numberHigh As Integer
salesCutoff As Currency
salesCutoff = InputBox("What sales value do you want to check for?")
For j = 1 To 6
numberHigh = 0
For i = 1 To 36
If Range("SalesRange").Cells(i, j) >= salesCutoff Then _
numberHigh = numberHigh + 1
Next i
_
MsgBox "For region " & j & ", sales were above " &
Format(salesCutoff, "$0,000") & " on " & numberHigh & " of the 36 months."
Next j
7
FirstProgram (cont.)
• Run the program from
the VBE
• Run -> Run
Sub/USerForm
• F5
• or
• Type in a number in
the InputBox, such as
150000.
8
FirstProgram (cont.)
• Run the program with
a button
– Right click tool bar and
choose “Form” tool bar
– Drag button form in
somewhere on the right
side of the data
– Choose the macro you
just created
– Change the name as
appropriate
9
Subroutines
Sub CountHighSales()
End Sub
10
Declaring variables
Dim i As Integer
Dim j As Integer
Dim numberHigh As Integer
Dim salesCutoff As Currency
• Option Explicit – need to declare variables
before you can use it
11
Input boxes and Message boxes
• InputBox(“prompt”, “title”) – returns user’s
input as data
• MsgBox “Prompt”, type, “title”
– vbInformation – information display
– vbYesNo – display information and Yes and
No buttons; returns vbYes or vbNo
– vbExplanation, etc.
• Require parentheses if the box captures the
result (input or yes/no answer)
12
With construction
• Properties are described top down, separated by “.”
(period)
e.g.,
Workboolks(“Sales”).Woksheets(“March”).Range(“A1”).Value
Workboolks(“Sales”).Woksheets(“March”).Range(“A1”).Font.Name
Workboolks(“Sales”).Woksheets(“March”).Range(“A1”).Font.Size
• Using “With” key word, it may be structured without
repeating the same.
With Workboolks(“Sales”).Woksheets(“March”).Range(“A1”)
.Value = “Sales for march”
With .Font
.Name = “Times New Roman”
.Size = 14
13
Exercise
• The file ExamScores.xls contains scores for an
exam in the range A1:A100.
• Write a program that reports the average,
standard deviation, minimum, and maximum of
the scores in a message box.
• Range is set in a module already.
• Use Excel’s functions (with
Application.WorksheetFunction. )
14