VBA for Excel

1
VBA for Excel
2
What is a spreadsheet?

• An Excel spreadsheet is a set of worksheets
• Each worksheets is made up of rows and columns of
  cells
• Rows are numeric 1, 2, 3,
• Columns are alphabetic A, B, C,
• A cell contains data, or a formula

3
Cell Ranges
column letters
A1 Single cell
G2 G8 Single Column Range
A3 E3 Single Row Range
Read as B6 to E13
Row numbers
4
Numerical Data

• Numerical data is any number (formatted or not)
• Numerical data can be used in a formula
• You can use Format/Conditional formatting with to
  alter the appearance of numeric data
• Examples
• 5344
• -34.5209
• 75

5
Formatting Numbers
6
Formatting Dates
7
Formulas

• A formula represents a mathematical calculation
• Always starts with
• A formula contains
• Numbers
• 25.32, -56, 78
• Cell addresses and ranges
• A3, G24, A2A54
• Mathematical operators
• Excel built-in functions
• Today, Month, Sum
• 100B5-D3sum(R2R15)

8
(No Transcript)
9
Today and Month functions

• TODAY()
• MONTH( a date)

10
Macros in Excel

• A macro is a sequence of steps designed to
  accomplish a particular task
• Just like a sub procedure in VB
• In Excel, a user may create a macro in 2 ways
• Record a sequence of steps in Excel using the
  macro recorder
• Write a VB sub procedure using the macro editor

11
Macro Statements

• A Macro always begins a Sub statement
• A Macro ends with an End Sub statement

12
A Macro

• To perform multiple actions on the same object
  Macros use
• With
• End With

13
The Selection object

• Just as a user can select multiple cells
  simultaneously, a macro can select a region of a
  worksheet and apply changes to all the cells in
  that region

14
Recording a Macro
15
Putting a button onto a spreadsheetin CS132 we
use Control Objects

• We can create a button and assign it to a Macro
• This button is called a button control
• View/Toolbars/Visual Basic
• Then choose the button control

16
Adding the button control
17
The Visual Basic Toolbar
View/Toolbars/Visual Basic
Text box
Check Box
Command Button
Option Button
List boxes
Label
Image
18
Using the Control Toolbox Property Box

• Click on the tools icon to display the controls
  toolbox
• After you add a control, right click on it to
  display the properties box

19
An object and its properties

• Sample Command Button
• name cmdStart
• caption text that appears on the button
  (Start)
• enabled true or false
• You must follow good programming practice
• Object names must be meaningful
• cmdExit
• cmdStart

20
Accelerator Property
s ( not S)

• This is done by putting the desired letter in the
  Accelerator property in the properties box
• (note lower case only matters if more than one
  letter is present)

21
Referring to a Worksheet in VBA

• An index number is a sequential number assigned
  to a sheet, based on the position of its sheet
  tab
• The following procedure uses the Worksheets
  property to activate worksheet one in the active
  workbook.
• Sub FirstOne()
• Worksheets(1).Activate
• End Sub

22
Cell Ranges in VBA

• Properties
• Range("A1").Value 99
• The Value property is what the cell holds, in
  this case 99
• Range("B2B7").Interior.Color vbYellow
• This changes the color of the cells in "B2B7" to
  yellow.
• Methods
• We can use Range(A1).Select
• This makes A1 the active cell

23
A1-style references using the Range property
24
Cells Notation

• Cells(2,2).Value refers to R2C2 below
• Remember, row
• first, then column
• B3 is the same as Cells(3,2)

25
Cells vs. Range

• Range("B1").Value 8
• Range("B2").Value 8
• Can also be written
• Cells(1,2).Value 8
• Cells(2,2).Value 8

26
Deleting data

• You can clear the contents of a range using the
  ClearContents method
• Private Sub cmdClear_Click()
• Range("DD").ClearContents
• End Sub
• Note ClearContents does not clear the
  formatting, just the numbers or formulas
  contained within the cells.

27
Event handling for VBA objects

• The various objects present in an excel
  application (application, worksheet, etc.)
  generate events that can be handled
• Working with such events is beyond the scope of
  this class

28
Documentation

• http//msdn.microsoft.com/office/understanding/exc
  el/documentation/default.aspx