在VBA中访问一个对象,必须清楚该对象在对象体系中处于何种位置,然后通过对象访问符合“.”进行访问,从包含该对象的最外层对象开始,由外及里逐次取其子对象,一直到达要访问的对象为止。例如要访问当前工作表的第4列第3行单元格的存储内容,Application.activesheets.range(“d3”).value。
【VBA中主要包含6层基本对象】
Application对象 | 代表整个 Microsoft Excel 应用程序 |
Workbooks 集合对象 | 代表应用程序中当前打开的所有 Workbook 对象的集合 |
Workbook对象 | 代表 Microsoft Excel 工作簿 |
Worksheets集合对象 | 代表指定的或活动工作簿中所有 Worksheet 对象的集合 |
Worksheet对象 | 代表一张工作表 |
Range对象 | 代表某一单元格、某一行、某一列、某一选定区域 |
Application 对象_代表整个 Microsoft Excel 应用程序 | |||
常用属性 |
| 常用方法 | 常用事件 |
ActiveCell | OnWindow | Calculate | SheetActivate |
ActiveSheet | Path | ConvertFormula | SheetDeactivate |
ActiveWindow | PathSeparator | OnKey | SheetFollowHyperlink |
ActiveWorkbook | Range | InputBox | WindowActivate |
AskToUpdateLinks | Rows | Run | WindowDeactivate |
Assistant | ScreenUpdating |
| WorkbookActivate |
AutoRecover | Selection |
| WorkbookBeforeClose |
Caption | Sheets |
| WorkbookOpen |
Cells | ShowWindowsInTaskbar |
|
|
Charts | StandardFont |
|
|
Columns | StandardFontSize |
|
|
CommandBars | StartupPath |
|
|
CommandUnderlines | StatusBar |
|
|
Creator | ThisCell |
|
|
Cursor | ThisWorkbook |
|
|
DefaultFilePath | UserControl |
|
|
DefaultSaveFormat | Workbooks |
|
|
DisplayFormulaBar | Worksheets |
|
|
DisplayFullScreen | DisplayScrollBars |
|
|
Name | DisplayStatusBar |
|
|
|
|
|
|
Workbooks 集合对象_应用程序中当前打开的所有 Workbook 对象的集合 | |||
属性 | 方法 |
|
|
Application | Add |
|
|
Count | Close |
|
|
Creator | Open |
|
|
Item | OpenDatabase |
|
|
Parent | OpenText |
|
|
|
|
|
|
Workbook 对象_代表 Microsoft Excel 工作簿 | |||
属性 | 方法 | 事件 |
|
Charts | Activate | Activate |
|
CodeName | Close | BeforeClose |
|
FullName | Protect | Open |
|
FullNameURLEncoded | ProtectSharing | SheetActivate |
|
HasPassword | RunAutoMacros | SheetDeactivate |
|
InactiveListBorderVisibl | Save | SheetFollowHyperlink |
|
Password | SaveAs | WindowActivate |
|
Path | SaveCopyAs | WindowDeactivate |
|
ProtectStructure | Unprotect |
|
|
ProtectWindows | Add |
|
|
ReadOnly |
|
|
|
Saved |
|
|
|
Sheets |
|
|
|
Windows |
|
|
|
Worksheets |
|
|
|
WritePassword |
|
|
|
|
|
|
|
Worksheets 集合对象_指定的或活动工作簿中所有 Worksheet 对象的集合 | |||
属性 | 方法 |
|
|
Application | Add |
|
|
Count | Copy |
|
|
HPageBreaks | Delete |
|
|
Item | FillAcrossSheets |
|
|
Parent | Move |
|
|
Visible | PrintOut |
|
|
VPageBreaks | Select |
|
|
|
|
|
|
Worksheet 对象_代表一张工作表 | |||
属性 | 方法 | 事件 |
|
Application | Activate | Activate |
|
AutoFilter | Calculate | BeforeDoubleClick |
|
AutoFilterMode | ChartObjects | BeforeRightClick |
|
Cells | CheckSpelling | Calculate |
|
CircularReference | CircleInvalid | Change |
|
CodeName | ClearArrows | Deactivate |
|
Columns | Copy | FollowHyperlink |
|
EnableSelection | Delete | PivotTableUpdate |
|
Hyperlinks | Evaluate | SelectionChange |
|
Name | Move |
|
|
ProtectContents | OLEObjects |
|
|
Protection | Paste |
|
|
ProtectScenarios | PasteSpecial |
|
|
Range | PivotTables |
|
|
Rows | PivotTableWizard |
|
|
ScrollArea | SaveAs |
|
|
Type | Select |
|
|
Visible | Unprotect |
|
|
|
|
|
|
Range集合对象_代表某一单元格、某一行、某一列、某一选定区域 | |||
属性 | 方法 | 方法 |
|
AddIndent | Activate | FillUp |
|
Address | AddComment | Find |
|
AddressLocal | AdvancedFilter | FindNext |
|
AllowEdit | ApplyNames | FindPrevious |
|
Application | ApplyOutlineStyles | FunctionWizard |
|
Areas | AutoComplete | GoalSeek |
|
Borders | AutoFill | Group |
|
Cells | AutoFilter | Insert |
|
Characters | AutoFit | Insert |
|
Column | AutoFormat | Justify |
|
Columns | AutoOutline | Merge |
|
ColumnWidth | BorderAround | NavigateArrow |
|
Comment | Calculate | NoteText |
|
Count | CheckSpelling | Parse |
|
Creator | Clear | PasteSpecial |
|
CurrentArray | ClearComments | PrintOut |
|
CurrentRegion | ClearContents | PrintPreview |
|
Dependents | ClearFormats | RemoveSubtotal |
|
DirectDependents | ClearNotes | Replace |
|
Font | ClearOutline |
| |
FormatConditions | ColumnDifferences | Run |
|
Formula | Consolidate | Select |
|
FormulaArray | Copy | SetPhonetic |
|
FormulaHidden | CopyFromRecordset | Show |
|
FormulaLabel | CopyPicture | ShowDependents |
|
FormulaLocal | CreateNames | ShowErrors |
|
FormulaR1C1 | CreatePublisher | ShowPrecedents |
|
FormulaR1C1Local | Cut | Sort |
|
Height | DataSeries | SortSpecial |
|
Name | Delete | Speak |
|
Value | DialogBox | SpecialCells |
|
Text | Dirty | SubscribeTo |
|
Row | EditionOptions | Subtotal |
|
Rows | FillDown | Table |
|
Range | FillLeft | TextToColumns |
|
RowHeight | FillRight | UnMerge |
|
每一个对象基本包括:属性、方法、事件三类特征。
事件应用举例:
Application 中的worksheet事件
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
End Sub