vba for excel. what is a spreadsheet? u an excel spreadsheet is a set of worksheets each worksheets...
TRANSCRIPT
VBA for Excel
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
B6:E13
Row numbers
column letters
G2 : G8 Single
Column Range
A3 : E3Single Row Range
Read as “B6 to E13”
A1Single cell
Cell Ranges
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%
Formatting Numbers
Formatting Dates
Formulas A formula represents a mathematical calculation Always starts with =
A formula contains: Numbers
25.32, -56, 78 Cell addresses and ranges
A3, G24, A2:A54 Mathematical operators Excel built-in functions
Today, Month, Sum
=100*B5-D3+sum(R2:R15)
Today and Month functions
=TODAY() =MONTH( a date)
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
Macro Statements
A Macro always begins a Sub statement
A Macro ends with an End Sub statement
A Macro
To perform multiple actions on the same object Macros use
With End With
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
Recording a Macro
Putting a button onto a spreadsheet—in 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
Adding the button control
Command Button
Label
Text box
Option Button
Check Box
Image
View/Toolbars/Visual Basic
List boxes
The Visual Basic Toolbar
Click on the tools icon to display the controls toolbox
After you add a control, right click on it to display the properties box
Using the Control Toolbox & Property Box
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
An object and its properties
• 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)
s ( not S)
Accelerator Property
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
PropertiesRange("A1").Value = 99The Value property is what the cell holds, in this case 99
Range("B2:B7").Interior.Color = vbYellowThis changes the color of the cells in "B2:B7" to yellow.
MethodsWe can use Range(“A1”).SelectThis makes “A1” the active cell
Cell Ranges in VBA
A1-style references using the Range property
Reference Meaning
Range("A1") Cell A1
Range("A1:B5") Cells A1 through B5
Range("C5:D9,G9:H16") A multiple-area selection
Range("A:A") Column A
Range("1:1") Row 1
Range("A:C") Columns A through C
Range("1:5") Rows 1 through 5
Range("1:1,3:3,8:8") Rows 1, 3, and 8
Range("A:A,C:C,F:F") Columns A, C, and F
Cells(2,2).Value refers to R2C2 below
Remember, rowfirst, then column
B3 is the same as Cells(3,2)
Cells Notation
Range("B1").Value = 8Range("B2").Value = 8
Can also be written:
Cells(1,2).Value = 8Cells(2,2).Value = 8
Cells vs. Range
Deleting data You can clear the contents of a range using the
ClearContents method:
Private Sub cmdClear_Click() Range("D:D").ClearContentsEnd Sub
Note: ClearContents does not clear the formatting, just the numbers or formulas contained within the cells.
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
Documentationhttp://msdn.microsoft.com/office/understanding/
excel/documentation/default.aspx