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)：表示重新计算工作表时跟踪的区域。
• 工作表工作簿对象
  • 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):::collectionApp --> AppNames(Names):::collectionApp --> AppWins(Windows):::collectionApp --> Watchs(Watches):::collectionApp --> RTD[RTD]:::object%% 全局快捷属性App -.->|ActiveWorkbook| ActWb[ActiveWorkbook]:::activeApp -.->|ActiveSheet| ActWs[ActiveSheet]:::activeApp -.->|ActiveCell| ActCell[ActiveCell]:::activeApp -.->|Selection| Sel[Selection]:::active%% === Workbook 层级 ===Wbs --> Wb[Workbook]:::objectActWb -.->|引用| WbWb --> Wss(Worksheets):::collectionWb --> Charts(Charts):::collectionWb --> Sheets(Sheets):::collectionWb --> WbNames(Names):::collectionWb --> Styles(Styles):::collectionWb --> WbWins(Windows):::collection%% XmlMaps 层级修正Wb --> XmlMaps(XmlMaps):::collectionXmlMaps --> XmlMap[XmlMap]:::objectXmlMap --> XmlSchemas(XmlSchemas):::collectionXmlSchemas --> XmlSchema[XmlSchema]:::object%% === Worksheet 层级 ===Wss --> Ws[Worksheet]:::objectActWs -.->|引用| Ws%% Worksheet 的主要子对象Ws --> Ranges(Range返回属性):::collectionWs --> ListObjs(ListObjects):::collectionWs --> PivotTables(PivotTables):::collectionWs --> AutoFilter[AutoFilter]:::objectWs --> WsSort[Sort]:::objectWs --> Outline[Outline]:::objectWs --> WsNames(Names):::collectionWs --> Comments(Comments):::collection%% Range 属性归属Ranges --> CellRange[Cells/Rows/Columns]:::objectCellRange --> RangeObj[Range]:::objectActCell -.->|引用| RangeObj%% === Range 层级 (核心修正) ===RangeObj --> Areas(Areas):::collectionRangeObj --> Font[Font]:::objectRangeObj --> Interior[Interior]:::objectRangeObj --> Validation[Validation]:::objectRangeObj --> FormatConds(FormatConditions):::collectionRangeObj --> RangeComment[Comment]:::objectRangeObj --> XPath[XPath]:::objectRangeObj --> ListObjRef[ListObject]:::object%% 注：Range 有 Sort 方法，但没有 Sort 对象属性%% FormatConditions 展开FormatConds --> FormatCond[FormatCondition]:::objectFormatCond --> Top10[Top10]:::objectFormatCond --> UniqueVals[UniqueValues]:::object%% === ListObject 层级 ===ListObjs --> ListObj[ListObject]:::objectListObj --> ListCols(ListColumns):::collectionListObj --> ListRows(ListRows):::collectionListObj --> TableStyle[TableStyle]:::objectListObj --> LoSort[Sort]:::objectListCols --> ListCol[ListColumn]:::objectListRows --> ListRow[ListRow]:::object%% TableStyle 展开TableStyle --> TableStyleElems(TableStyleElements):::collectionTableStyleElems --> TableStyleElem[TableStyleElement]:::object%% === AutoFilter 层级 ===AutoFilter --> Filters(Filters):::collectionFilters --> Filter[Filter]:::objectAutoFilter --> AfSort[Sort]:::object%% === Window 层级 ===AppWins --> Win[Window]:::objectWbWins --> WinWin --> SheetViews(SheetViews):::collectionSheetViews --> 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:32excel数据表可以插入切片器。这样能实现筛选器的单选功能。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、orIF 函数，英文状态的前后双引号。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：在参数二这个区域中，通过条件参数一与参数三，找到需要的结果。参数一作为唯一的IDIndex 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、searchmid（要截取的字符串，从第几位开始，截取几位）
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 = falsegroup_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_rowsif (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.UsedRangelet blank_row_int = loc_column_from_name(fieldName) // 5rng.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:自定义创建表头