VBA%20Programming

1
VBA Programming

• Session 2

2
Things to Review

• Variables
• Procedures Subs Functions
• IfThen
• ForNext

3
Variables

• Just like in math, variables
• Have a name
• Have a value
• Value can be changed
• Examples
• x 1 debug.print x
• x 2 debug.print x

4
Procedures

• Subroutines (Subs)
• Can do things but not return values.
• Subs without arguments can be called from the
  Excel macro toolbar.
• Functions
• Functions return values in VBA with the form
  FunctionName SomeValue
• Functions can be inserted into Excel sheets from
  the InsertFunction menu.

5
IfThen

• IfThen has two basic forms
• Statement
• If (x 1) Then Debug.Print "Yes"
• Code Block
• If (x 1) Then
• y 1
• Else
• y -1
• End If

6
ForNext

• Loops through a range of values and executes the
  statements in the block.

For x 1 To 10 Debug.Print x Next 1 2 3 4 5 6 7 Etc.
7
New Stuff
8
VBA Variable Types

• The most commonly used VBA types are
• Boolean (True or False)
• Numeric Types Integer, Double, Currency
• Date
• String
• Variant (holds almost anything)
• Object Types Ranges, Worksheets, etc.

9
Objects

• A combination of code and data that can be
  treated as a unit, for example, a control, form,
  or application component. Each object is defined
  by a class. (MS)
• Examples
• Workbooks
• Worksheets
• Charts
• Ranges

10
Declaring Variables

• Declaring means
• Telling VBA that you are going to use it
• Defining the variable's type
• Declare variables using the Dim statement
• Dim MyName as String
• Dim MyBirthday as Date
• Dim BigMoney as Currency
• Dim wks as Worksheet

11
Assigning Values to Variables

• Most variables can simple be assigned like this
• X 5
• MyName "Bob"
• Objects need to be assigned using Set
• Set MyDataSheet ActiveSheet
• Set MyChart ActiveChart

12
Forgot to set?
13
Range Object

• Represents a cell, a row, a column, a selection
  of cells containing one or more contiguous blocks
  of cells, or a 3-D range. (MS)

14
Understanding Ranges
15
Ranges Writing Code

• Sub SimplestHello()
• Range("A1") "Hello"
• Range("A2", "B4") "Goodbye"
• End Sub

16
Ranges Using a Range Variable

• Sub HelloRange()
• Dim a1 As Range
• Set a1 Range("A1")
• a1 "Hello world!"
• End Sub

17
Offsets If only there was just "A1"
18
Combining it all... Data Table
19
The Macro Recorder Part II
20
Specific -gt Generic

• Instead of...
• Range("A1")
• Sheets("Sheet1")
• Charts("Chart1")

• Use
• Selection or ActiveCell
• ActiveSheet
• ActiveChart

21
Collection Object

• Most of the VBA objects you use are parts of
  collections. For example
• Charts a collection of Chart objects
• Worksheets a collection of Worksheet objects
• Collections have the following
• Count number of items in the collection
• Add add an item
• Item get the value of an item
• Remove delete item

22
Collection Example

• Sub AddWorksheet()
• Dim wks As Worksheet
• Set wks Worksheets.Add
• MsgBox "Added " wks.Name
• End Sub

23
Looping Through Collections

• Collections are handy to use because we can
  easily look at each item in the collection using
  a "For Each" loop.
• For example

Sub ListWorksheets() Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
MsgBox wks.Name Next End Sub
24
Collections Exercise