Excel VBA对象模型分类列举
- 单数通常是某个具体对象，复数大多是对象的集合（Ranges 本身不是集合，而是 Range 对象）。
- 对象
- 属性
- 方法
- 事件
- 应用级别功能
- Application：代表 Excel 应用程序。可以通过引用Application对象的属性返回不同的子对象。对于某些特殊的对象可以省略Application。
- .ActiveCell：当前活动单元格
- .ActiveChart：当前活动工作簿中的活动图表
- .ActiveSheet：当前活动工作簿中的活动工作表
- .ActiveSheet.UsedRange
- .ActiveWindow：当前活动窗口
- .ActiveWorkbook：当前活动工作簿
- .Charts：当前活动工作簿中所有的图表工作表
- .Selection：当前活动工作簿中所有选中的对象
- .Sheets：当前活动工作簿中所有Sheet对象，包括普通工作表、图表工作表、Ms Excel 4.0宏表工作表和Ms Excel 5.0对话框工作表
- .Worksheets：当前活动工作簿中的所有Worksheet对象（普通工作表）
- .Workbooks：当前所有打开的工作簿
- .Cells：活动工作表上的所有单元格。
- .Columns：活动工作表上的所有列。
- .Rows：代表活动工作表上的所有行。
- .ScreenUpdating：是否将程序运行过程中的计算结果显示到屏幕上。
- .DisplayAlerts：属性决定在程序运行中是否显示警告信息
- .EnableEvents：属性来启用或禁用事件
- .WorksheetFunction：属性调用部分工作表函数。
- 枚举：有关 Excel 对象模型中的枚举
- Watch(es)：表示重新计算工作表时跟踪的区域。
- Application：代表 Excel 应用程序。可以通过引用Application对象的属性返回不同的子对象。对于某些特殊的对象可以省略Application。
- 工作表工作簿对象
- Sheets：指定的或活动工作簿中所有工作表的集合。
- SheetViews：指定的或活动工作簿窗口中所有工作表视图的集合。
- Workbook(s)：代表工作簿。
- Worksheet(s)：代表工作表。
- .UsedRange
- WorksheetFunction：用作可从 Visual Basic 中调用的 Microsoft Excel 工作表函数的容器。
- WorksheetView：定义单个工作表视图的行为的对象。
- Window(s)：代表一个窗口
- PivotTable(s)：代表工作表上的数据透视表。
- 选区
- Areas：由选定区域内的多个子区域或连续单元格块组成的集合。
- Range(s)：表示一个单元格、一行、一列、一个包含单个或若干连续单元格区域的选定单元格范围，或者一个三维区域。
- ListObject(s)：工作表中的一部分的表格区域（插入-表格）
- ListColumn(s)：代表表格中的一列。
- ListRow(s)：代表表格中的一行。
- Name(s)：命名区域，代表一个定义名称.
- 样式
- Style(s)：代表区域的样式说明。
- Font：包含对象的字体属性（字体名称、字号、颜色等等）。
- FillFormat：代表形状的填充格式。
- TableStyle(s)：代表表格、数据透视表或切片器的一个或所有元素定义格式。
- TableStyleElement(s)：代表表格样式元素。
- Comment(s)：代表单元格批注。
- 功能
- AutoFilter：代表对指定工作表的自动筛选。
- Filter(s)：单数代表单个列的筛选。复数为自动筛选范围中的所有筛选器。
- Sort：代表数据区域的排序方式。
- SortField(s)：包含 Worksheet、ListObject 和 AutoFilter 对象的所有排序信息。
- Outline：代表工作表上的分级显示。
- Top10：代表条件格式规则的前十项。
- Validation：代表工作表区域的数据有效性规则。
- UniqueValues： 提取数据列中唯一值。使用 DupeUnique 属性返回或设置一个枚举。
- FormatCondition(s)：表示组合框或文本框控件的条件格式
graph TD
%% 定义样式
classDef root fill:#f9f,stroke:#333,stroke-width:4px;
classDef collection fill:#e1f5fe,stroke:#01579b,stroke-width:2px;
classDef object fill:#fff9c4,stroke:#fbc02d,stroke-width:2px;
classDef active fill:#f3e5f5,stroke:#7b1fa2,stroke-dasharray: 5 5;
classDef correct fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px;
%% 根节点
App[Application]:::root
%% === Application 一级对象 ===
App --> Wbs(Workbooks):::collection
App --> AppNames(Names):::collection
App --> AppWins(Windows):::collection
App --> Watchs(Watches):::collection
App --> RTD[RTD]:::object
%% 全局快捷属性
App -.->|ActiveWorkbook| ActWb[ActiveWorkbook]:::active
App -.->|ActiveSheet| ActWs[ActiveSheet]:::active
App -.->|ActiveCell| ActCell[ActiveCell]:::active
App -.->|Selection| Sel[Selection]:::active
%% === Workbook 层级 ===
Wbs --> Wb[Workbook]:::object
ActWb -.->|引用| Wb
Wb --> Wss(Worksheets):::collection
Wb --> Charts(Charts):::collection
Wb --> Sheets(Sheets):::collection
Wb --> WbNames(Names):::collection
Wb --> Styles(Styles):::collection
Wb --> WbWins(Windows):::collection
%% XmlMaps 层级修正
Wb --> XmlMaps(XmlMaps):::collection
XmlMaps --> XmlMap[XmlMap]:::object
XmlMap --> XmlSchemas(XmlSchemas):::collection
XmlSchemas --> XmlSchema[XmlSchema]:::object
%% === Worksheet 层级 ===
Wss --> Ws[Worksheet]:::object
ActWs -.->|引用| Ws
%% Worksheet 的主要子对象
Ws --> Ranges(Range返回属性):::collection
Ws --> ListObjs(ListObjects):::collection
Ws --> PivotTables(PivotTables):::collection
Ws --> AutoFilter[AutoFilter]:::object
Ws --> WsSort[Sort]:::object
Ws --> Outline[Outline]:::object
Ws --> WsNames(Names):::collection
Ws --> Comments(Comments):::collection
%% Range 属性归属
Ranges --> CellRange[Cells/Rows/Columns]:::object
CellRange --> RangeObj[Range]:::object
ActCell -.->|引用| RangeObj
%% === Range 层级 (核心修正) ===
RangeObj --> Areas(Areas):::collection
RangeObj --> Font[Font]:::object
RangeObj --> Interior[Interior]:::object
RangeObj --> Validation[Validation]:::object
RangeObj --> FormatConds(FormatConditions):::collection
RangeObj --> RangeComment[Comment]:::object
RangeObj --> XPath[XPath]:::object
RangeObj --> ListObjRef[ListObject]:::object
%% 注：Range 有 Sort 方法，但没有 Sort 对象属性
%% FormatConditions 展开
FormatConds --> FormatCond[FormatCondition]:::object
FormatCond --> Top10[Top10]:::object
FormatCond --> UniqueVals[UniqueValues]:::object
%% === ListObject 层级 ===
ListObjs --> ListObj[ListObject]:::object
ListObj --> ListCols(ListColumns):::collection
ListObj --> ListRows(ListRows):::collection
ListObj --> TableStyle[TableStyle]:::object
ListObj --> LoSort[Sort]:::object
ListCols --> ListCol[ListColumn]:::object
ListRows --> ListRow[ListRow]:::object
%% TableStyle 展开
TableStyle --> TableStyleElems(TableStyleElements):::collection
TableStyleElems --> TableStyleElem[TableStyleElement]:::object
%% === AutoFilter 层级 ===
AutoFilter --> Filters(Filters):::collection
Filters --> Filter[Filter]:::object
AutoFilter --> AfSort[Sort]:::object
%% === Window 层级 ===
AppWins --> Win[Window]:::object
WbWins --> Win
Win --> SheetViews(SheetViews):::collection
SheetViews --> WsView[WorksheetView]:::object
对于计算机而言，进行 1000 项更改与进行 10 项更改并无不同，因此这里对于较大的文档和问题而言就有了规模效应，而这正是 VBA 能够真正出彩和节省时间的原因。
事件过程：因为Workbook（工作簿）对象能识别Open（打开）这个动作，所以打开工作簿就会自动运行相应的程序。像这种当某个事件发生后自动运行的过程称为事件过程。事件过程也是Sub过程。事件过程必须写在特定对象所在的模块中，而且只有过程所在的模块里的对象才能触发这个事件。事件过程的过程名由Excel自动设置，以“对象名称_事件名称”的形式存在，不能更改。
Sub 是一种子过程，用于执行一系列操作，但不返回值。它通常用于完成任务。Function 的特点 Function 是一种函数过程，用于执行计算并返回一个值。
适用于 Visual Basic for Applications (VBA) 的 Excel 对象模型 | Microsoft Learn
VBA 编程的第一个挑战是了解每个 Office 应用程序的对象模型以及阅读对象、方法和属性语法。
薛奔Excel课程 Excel函数
2022年06月16日 14:32
excel数据表可以插入切片器。这样能实现筛选器的单选功能。
excel的逆透视能够将二维表转为一维表。tomz笔记有一个不知道现在还能不能用的基于一维表的双链跳转程序。
用excel的旭日图能更高效的展示大纲。
#DIV/0！：公式被0除，分母为0。
#NAME？：名称错误，函数名称写错了。
#VALUE！：错误参数，数值与文本直接相加会出现这个错误。
#REF！：引用错误。原先所引用的单元格消失了。（追踪引用单元格可以看到这个现象） #N/A：无可用数值，最常见于VLOOKUP时，不存在的值。
#NAME？和#VALUE！是一组对应函数的名称和参数。
1. F9键可以把公式表达式一键转换为值
2. 快速输入公式技巧：输入等号，可以使用按TAB键补全公式，然后可以使用鼠标选中单元格填充参数，最后可以按回车或者TAB键完成公式的输入。
3. 编辑栏输入公式会比较好\(^o^)/~
4. 公式前敲一个空格可以保留公式然后编辑其它单元格。
逻辑函数
if、and、or
IF 函数，英文状态的前后双引号。
1. 函数判断得到结果，对结果进行标记。
2. 条件格式设置条件规则，在规则中填写公式，对条件区域中符合条件的单元格进行标记。
isna、istext、isnumber、isnontext、islogical、isblank、iserr、iferror。
iserr（）与isna（）构成了错误的全部判断函数。
iferr（）：捕获错误并处理。
查找与引用函数
所谓的升序排列就是从小往大排，从1往n排。
Lookup：在一行或者一列中进行数据查询，Index Match横纵查询
LOOKUP有两种形式：向量形式和数组形式。
- 向量形式： LOOKUP(lookup_value,lookup_vector,result_vector) ；
- 数组形式： LOOKUP(lookup_value,array)。
array和lookup_vector的数据必须按升序排列，否则函数LOOKUP不能返回正确的结果。文本不区分大小写。
1. 如果函数LOOKUP找不到lookup_value，则查找array和 lookup_vector中小于lookup_value的最大数值。
2. 如果lookup_value小于array和 lookup_vector中的最小值，函数LOOKUP返回错误值#N/A。另外还要注意：函数LOOKUP在查找字符方面是不支持通配符的，但可以使用FIND函数的形式来代替。
关键的部分：如果函数LOOKUP找不到lookup_value，则查找array和 lookup_vector中小于lookup_value的最大数值。最大值就是查找区域的最后一列。
lookup逆序模糊匹配公式：=LOOKUP（1,0/（find（条件区域，条件），计算区域）） 条件区域相当于VLOOKUP中的参数二，条件相当于参数一，计算区域相当于参数三。
Vlookup最容易出错的地方是查找区域的首列必须含有查找的内容。
Vlookup：在参数二这个区域中，通过条件参数一与参数三，找到需要的结果。参数一作为唯一的ID
Index Match横纵查询
MATCH函数含义：返回指定数值在指定数组区域中的位置
match查找重复值，有重复的返回一个值，没有重复的返回N/A错误
MATCH函数是EXCEL主要的查找函数之一，该函数通常有以下几方面用途：(1)确定列表中中某个值的位置；(2)对某个输入值进行检验，确定这个值是否存在某个列表中；(3)判断某列表中是否存在重复数据；(4)定位某一列表中最后一个非空单元格的位置。
index：在一个区域中，指定行号与列号（数字）将会得到指定位置的单元格内容。或者说，获得单元格的内容，通过指定区域与行、列号。
我们可以使用match找到区域中重复项的位置，然后使用index显示内容。
也就是说，index与match函数的组合是VLOOKUP函数的进阶版，功能更加强大
文本处理函数
text、value文本转换
对于文本型的数字，可用“-”将其转换为数值型数字，但这样一来，数值变成负数了，所以再加一个“-”将其变为正数。 其效果与+0，*1，/1是相同的。
文本处理函数：right、left、mid、len、replace、substitute、find。还有upper、proper、lower、trim。补充未学习的：concat、search
mid（要截取的字符串，从第几位开始，截取几位）
MID的高级用法，截取任意字符串中包含有特征分隔符后的几位字符。 =MID(要截取的字符串,FIND(作为分隔符的字符串,要截取的字符串)+n, 截取几位)
Trim（）删除字符串首尾的空白,但会保留字符串内部作为词与词之间分隔的空格。
if（{1,0}，A2，A1），{1,0}是数组。第三个参数必须要在查找区域的左边，如果不在可以使用if函数夹数组变换一下。
数组：就是单元的集合或是一组处理的值集合。可以写一个以数组为参数的公式，即数组公式，就能通过这个单一的公式，执行多个输入的操作并产生多个结果——每个结果显示在一个单元中。Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。
数学和三角函数
Subtotal返回一个数据列表或数据库的分类汇总。
SUMPRODUCT函数是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
sumifs（实际求和区域，第一个条件区域，第一个对应的求和条件，第二个条件区域，第二个对应的求和条件，第N个条件区域，第N个对应的求和条件）
sumif、count、countifs、Hyperlink超级链接、超级表格
round、int；
roundup，rounddown；
floor、ceiling；
trunc求商、sign、sqrt、mode众数、trimmean、clean
时间日期函数
Today、now、year、month、day、hour、minute、second、time、date、weekday、weeknum、datedif。
DATEDIF函数是Excel隐藏函数，其在帮助和插入公式里面没有。 返回两个日期之间的年\月\日间隔数。常使用DATEDIF函数计算两日期之差。包含D,M,Y,YD,YM,MD。
统计函数
COUNTA函数功能是返回参数列表中非空的单元格个数。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格个数。如果不需要统计逻辑值、文字或错误值，请使用函数 COUNT。
COUNTA函数功能是返回参数列表中非空的单元格个数。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格个数。如果不需要统计逻辑值、文字或错误值，请使用函数 COUNT。
mod、indirect给定单元名字引用返回最终单元格的值。
Rank、 Small 、Large（统计函数），row、column（查找与引用函数）。
// Summary //
function Summary() {
let category_field = "规格型号"
let summary_field = "总重（kg)"
let up_or_down = "below"
let fill_field = ["B:C", "L:M"]
let invert_is_summary = false
group_and_summary(category_field, summary_field, up_or_down)
fill_empty_cells(fill_field, up_or_down)
remove_reference()
remove_redundant(category_field, invert_is_summary)
}
function group_and_summary(category_field, summary_field, up_or_down_option) {
ActiveSheet.UsedRange.Select()
let category_column_number = loc_column_from_name(category_field)
let summary_column_number = loc_column_from_name(summary_field)
// Warrning: 第一行必须是字段行。
if (up_or_down_option === "above") {
Selection.Subtotal(category_column_number, xlSum, Array(summary_column_number), true, false, xlSummaryAbove);
}
else if (up_or_down_option === "below") {
Selection.Subtotal(category_column_number, xlSum, Array(summary_column_number), true, false, xlSummaryBelow)
} else {
throw null
}
Range("A1").Select();
}
function fill_empty_cells(fill_field, fill_up_or_down = true) {
ClearCellsFormats()
fill_field = fill_field.join(",")
Range(fill_field).Select()
let empty_cells_arry = []
let rngIntersection = Application.Intersect(ActiveSheet.UsedRange, Selection)
//Console.log(rngIntersection.Count)
for (let cell of rngIntersection) {
if (cell.Text === "") {
empty_cells_arry.push(cell.Address())
}
}
let empty_rng = empty_cells_arry.join(", ")
let first_empty_cell = Range(empty_cells_arry[0])
Range(empty_rng).Select();
first_empty_cell.Activate();
if (fill_up_or_down) {
let target_cell = first_empty_cell.Offset(-1, 0).Address().replace(/\$/g, '')
Selection.Formula = `=${target_cell}`;
} else {
let target_cell = first_empty_cell.Offset(1, 0).Address().replace(/\$/g, '')
Selection.Formula = `=${target_cell}`;
}
AddCellsFormats()
}
function remove_reference() {
ActiveSheet.Cells.Select();
ActiveSheet.Cells.Copy();
ActiveSheet.Cells.PasteSpecial(xlPasteValues, xlPasteSpecialOperationNone, false, false);
Application.CutCopyMode = false;
Range("A1").Select();
}
function remove_redundant(category_field, invert_is_summary) {
let rng = ActiveSheet.UsedRange
// let category_field = "规格型号"
// let invert_option = true
// Warrning: 第一行必须是字段行。
let del_rows
if (invert_is_summary) {
rng.AutoFilter(loc_column_from_name(category_field), "*汇总");
del_rows = `2:${rng.Rows.Count}`
Rows.Item(del_rows).Select();
Selection.Delete(xlShiftUp);
} else {
rng.AutoFilter(loc_column_from_name(category_field), "<>*汇总*");
del_rows = `2:${rng.Rows.Count}`
Rows.Item(del_rows).Select();
Selection.Delete(xlShiftUp);
}
ClearOutline()
ShowAllDataAndScrollTop()
}
function loc_column_from_name(fieldName) {
let rngIntersection = Application.Intersect(ActiveSheet.UsedRange, Rows.Item("1:5"))
for (let cell of rngIntersection) {
if (cell.Text === fieldName) {
// Console.log(cell.Column)
return cell.Column
}
}
}
function ShowAllDataAndScrollTop() {
ActiveSheet.ShowAllData();
ActiveWindow.ScrollRow = 3;
}
function ClearCellsFormats() {
Cells.Select();
Cells.ClearFormats();
Range("A1").Select()
}
function AddCellsFormats() {
ActiveSheet.UsedRange.Select();
const borders = [
Selection.Borders.Item(xlEdgeLeft),
Selection.Borders.Item(xlEdgeTop),
Selection.Borders.Item(xlEdgeBottom),
Selection.Borders.Item(xlEdgeRight),
Selection.Borders.Item(xlInsideVertical),
Selection.Borders.Item(xlInsideHorizontal)
];
const setBorderProperties = (border) => {
border.Weight = xlThin;
border.LineStyle = xlContinuous;
border.ColorIndex = xlColorIndexAutomatic;
border.TintAndShade = 0;
};
borders.forEach(setBorderProperties);
Selection.HorizontalAlignment = xlHAlignCenter;
Range("A1").Select()
}
function ClearOutline() {
Cells.Select();
Selection.ClearOutline();
Range("A1").Select()
}
function Sort1() {
const sheet = ActiveSheet;
sheet.Sort.SortFields.Clear();
sheet.Sort.SortFields.Add(Range("B2:B377"), xlSortOnValues, xlAscending, "", undefined);
sheet.Sort.SortFields.Add(Range("C2:C377"), xlSortOnValues, xlAscending, "", undefined);
sheet.Sort.SortFields.Add(Range("D2:D377"), xlSortOnValues, xlAscending, "", undefined);
sheet.Sort.SortFields.Add(Range("L2:L377"), xlSortOnValues, xlAscending, "", undefined);
sheet.Sort.Header = xlYes;
sheet.Sort.Orientation = xlSortColumns;
sheet.Sort.MatchCase = false;
sheet.Sort.SortMethod = xlPinYin;
sheet.Sort.SetRange(sheet.UsedRange);
sheet.Sort.Apply();
}
// 整理tekla导出清单 //
function trimUsedRange() {
// for (const cell of ActiveSheet.UsedRange) {
// if (typeof (cell.Value2) !== 'undefined') {
// cell.Value2 = String(cell.Value2).trim()
// }
// }
for (let i = 1; i <= ActiveSheet.UsedRange.Count; i++) {
let trimmedText = String(ActiveSheet.UsedRange.Item(i).Value2).trim()
if (trimmedText !== "undefined") {
ActiveSheet.UsedRange.Item(i).Value2 = trimmedText
}
}
}
function orgnaize_table_headers() {
Range("1:4, 6:6").Select();
Selection.Delete(xlShiftUp);
Range("A1").Select();
}
function delete_blank_rows(fieldName) {
let rng = ActiveSheet.UsedRange
let blank_row_int = loc_column_from_name(fieldName) // 5
rng.AutoFilter(blank_row_int, Array(""), xlFilterValues, undefined, undefined);
Rows.Item(`2:${rng.Rows.Count}`).Select();
Selection.Delete(xlShiftUp);
ActiveWindow.ScrollRow = 1;
ActiveSheet.ShowAllData();
}
function moveColumn() {
let origin = "F:F"
let target = "D:D"
Columns.Item(origin).Select();
Selection.Cut(undefined);
Columns.Item(target).Select();
ActiveSheet.Select(false);
Selection.Insert(xlShiftToRight, undefined);
}
// ActiveSheet.Cells.Select();
// ActiveSheet.Cells.AutoFilter(undefined, undefined, xlAnd, undefined, undefined);
// 填充
// 清理第二空白行，零件号为空的行，需要填充后才能执行这一步
// 重用delete_blank_rows即可。
// TODO:自定义创建表头
浙公网安备 33010602011771号