XLDataSoft - Data Analysis Experts
MS EXCEL/VBA TRAINING COURSES - 3
www.XLDataSoft.com
###
Introduction to
Object
Work w/ Work w/
Workbook Worksheet
Disclaimer: the user assumes the entire responsibility for using of this free training material provided by XLData
and company names mentioned herein may be the trademarks of their respective owners.
Want to Learn More about Excel/VBA? Please Contact Ychen@XLDataSoft.com or visit www.XLDat
ork w/
rksheet
training material provided by XLDataSoft. Product
marks of their respective owners.
DataSoft.com or visit www.XLDataSoft.com
XLDataSoft Data Analysis Experts
Return to Main
• MS EXCEL/VBA TRAINING COURSES - 3
- Introduction
VBA is an Object-Based (Not Object Oriented)
Programming Language Developed Especially for
Micorsoft Office Suite.
Containers or Collections
- A Group of Similar Objects Share Common Properties,
Methods and Events
- Such as Workbooks, Worksheets, etc.
- Worksheets is a collection of all the Worksheet objects in the
specified or active workbook.
- Worksheets(1) refers to the 1st worksheet of current active
workbook.
Objects
- Such as Worksheet, Workbook, Range, Cell, Chart, Name, etc.
- Worksheets(1) is an Object Referring to the First Sheet
- Range("A1:B15") is an Object Referring to a Range
Properties
- Properties are the Physical Characteristics of Objects and Can
be Measured or Quantified.
- Properties for Collections
- Worksheets.Count (Read Only)
- Worksheets.Visible = True (Read and Write)
- Properties for Object
- Range("A1:B15").Rows.Count (Read Only)
- Range("A1:B15").Font.Bold = True (Read and Write)
Methods
- Methods are the Actions that Can be Performed by Objects or
on Objects
- Methods for Collections
- Worksheets.Add
- Worksheets.Delete
- Methods for Objects
- Range("A1:B15").ClearContents
- ActiveCell.Copy
Events
- Objects Can Respond to Events, Such as Mouse Click, Double
Click on a Cell, Active a Worksheet, Open/Close/Save a Workbook,
etc.
- Such as Worksheet_Activate, Chart_Select
- Such as Sheet_Change, Chart_BeforeDoubleClick
XLDataSoft Data Analysis Experts
Return to Main
• MS EXCEL/VBA TRAINING COURSES - 3
- Work with Worksheets
===== Start of Example Code ====
' Refer A Worksheet
Worksheets(1) ' Use Index, The First Sheet of Active Workbook
Worksheets("Sheet1") ' Use Name, Sheet "Sheet1" of ActiveWorkbook
ActiveWorksheet ' Active Worksheet of Active Workbook
Workbooks("TestSht.xls").Worksheets("Sheet2") ' Refer to A non-Active Workbook's
Worksheet
' Add A New Worksheet
Workbooks("TestSht.xls").Worksheets.Add.Name = "NewSht"
' Activate A Worksheet
Sheets("NewSht").Activate
Workbooks("TestSht.xls").Sheets("NewSht").Activate
' Delete A Worksheet
Workbooks("TestSht.xls").Sheets("NewSht").Delete
' Copy A Worksheet
Workbooks("TestSht.xls").Sheets("NewSht").Copy
After:=Workbooks("TestSht.xls").Worksheets(2)
' ReName A Worksheet
Workbooks("TestSht.xls").Sheets("NewSht").name = "NewSht2"
' Count Sheets
MsgBox "Current Workbook Has " & Sheets.Count & " Worksheets", vbInformation
' Extract Sheet Name
MsgBox "Current Sheet Name is " & ActiveSheet.Name, vbInformation
' Loop All Sheets
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
MsgBox sht.Name & " C7 = " & Range("C7").Value, vbInformation
Next
' Used Range of A Sheet
MsgBox "The Used Range of Sheet [" & ActiveSheet.Name & "] is " &
ActiveSheet.UsedRange.Address, vbInformation
' Set Scroll Area
If ActiveSheet.ScrollArea = "" Then
ActiveSheet.ScrollArea = "C7:C16" ' Any Cells Beyond This Range Can Not Be Select by
Mouse or Keyboard
Else
ActiveSheet.ScrollArea = "" ' ' Use "" to Set the Scroll Area to All the Cells
End If
===== End of Example Code ====
ActiveSheet.ScrollArea = "C7:C16" ' Any Cells Beyond This Range Can Not Be Select by
Mouse or Keyboard
Else
ActiveSheet.ScrollArea = "" ' ' Use "" to Set the Scroll Area to All the Cells
End If
===== End of Example Code ====
ok
ive Workbook's
bInformation
an Not Be Select by
e Cells
an Not Be Select by
e Cells
XLDataSoft Data Analysis Experts
Return to Main
• MS EXCEL/VBA TRAINING COURSES - 3
- Work with Workbooks
===== Start of Example Code ====
' Refer A Workbook
Workbooks(1) ' Use Index
Workbooks("Results.Xls") ' Use Name
ActiveWorkbook ' Refers to the Active Workbook
' Create A New Workbook
Dim NewWkBk as Workbook
Set NewWkBk = Workbooks.Add
With NewWkBk
.Title = "Analysis Resultd"
.Subject = "Results for Analysis"
.SaveAs Filename:="Results.xls"
End With
' Open An Exisiting Workbook
Dim Fname as string
Fname ="C:\AnalysisResutls\Results.xls"
Workbooks.Open(Fname)
' Close An Open Workbook
Workbooks("Results.xls").Close SaveChanges:=False ' Not Save Changes
Workbooks("Results.xls").Close SaveChanges:=True ' Save Changes
Workbooks.Close ' Close All Open Workbook
' Activate An Open Workbook
Workbooks("Results.xls").Activate
' Save An Open Workbook
Workbooks("Results.xls").Save
' SaveAs An Open Workbook
Workbooks("Results.xls").SaveAs "Results2.Xls" ' Results2.xls is Open & Results.xls is
Close
' SaveCopyAs An Open Workbook Very useful for backup file
Workbooks("Results.xls").SaveCopyAs "Results2.Xls" ' Results.xls is Still Open and A Copy
of the Workbook is Created
' Extract Workbook Name and Path
Dim strName as string, strPath as string
strName = ActiveWorkbook.FullName
strPath = ActiveWorkbook.Path
' Print Preview
ActiveWorkbook.PrintPreview
' List All Open Workbooks' Name
Dim k As Integer
For k = 1 To Workbooks.Count
MsgBox Workbooks(k).Name
Next
' Loop All Open Workbooks
Dim wkbk As Workbook
For Each wkbk In Workbooks
' Print Preview
ActiveWorkbook.PrintPreview
' List All Open Workbooks' Name
Dim k As Integer
For k = 1 To Workbooks.Count
MsgBox Workbooks(k).Name
Next
' Loop All Open Workbooks
Dim wkbk As Workbook
For Each wkbk In Workbooks
MsgBox "Workbook " & wkbk.Name & " Has " & Workbooks(k).Sheets.Count & " Sheets",
vbInformation
Next
===== End of Example Code ====
nges
n & Results.xls is
Still Open and A Copy
ts.Count & " Sheets",
