VBA学习记录

Excel公式

基础学习

微软官方文档：Office Visual Basic for Applications (VBA) 参考 | Microsoft Learn

工作簿常用方法和属性：

• 方法：

  1. Workbooks.Add：创建新工作簿。
  2. Workbooks.Open：打开现有工作簿。
  3. Workbooks.Close：关闭工作簿。
  4. Workbooks.Save：保存工作簿。
  5. Workbooks.SaveAs：另存为工作簿。
  6. Workbooks.Count：返回打开的工作簿数量。
  7. Workbooks.Item：通过索引返回工作簿。
  8. Workbooks.Application：返回 Excel 应用程序对象。

• 属性：

  1. Workbook.Name：获取或设置工作簿的名称。
  2. Workbook.FullName：获取工作簿的完整路径和名称。
  3. Workbook.Sheets：返回工作簿中的所有工作表集合。
  4. Workbook.ActiveSheet：返回当前工作簿的活动工作表。
  5. Workbook.ReadOnly：指示工作簿是否为只读。
  6. Workbook.Saved：指示工作簿是否已保存。
  7. Workbook.Close：关闭工作簿。

工作表常用方法和属性：

• 方法：

  1. Worksheets.Add：在工作簿中创建新工作表。
  2. Worksheets.Delete：删除工作表。
  3. Worksheets.Copy：复制工作表。
  4. Worksheets.Move：移动工作表。
  5. Worksheets.Select：选择工作表。

• 属性：

  1. Worksheet.Name：获取或设置工作表的名称。
  2. Worksheet.Cells：返回工作表的所有单元格。
  3. Worksheet.Range：返回工作表上的单元格范围。
  4. Worksheet.UsedRange：返回工作表上使用的单元格范围。
  5. Worksheet.Visible：获取或设置工作表的可见性。
  6. Worksheet.Index：返回工作表的索引位置。
  7. Worksheet.Rows 和 Worksheet.Columns：返回工作表的所有行和列。
  8. Worksheet.Tab：获取或设置工作表的标签颜色。

单元格常用方法和属性：

• 方法：

  1. Range.Select：选择单元格或范围。
  2. Range.Copy 和 Range.Cut：复制或剪切单元格内容。
  3. Range.Paste：粘贴内容到单元格。
  4. Range.Value：获取或设置单元格的值。
  5. Range.Formula：获取或设置单元格的公式。
  6. Range.Clear：清除单元格内容。
  7. Range.Comment.Text 修改批注（如果已有注释）
  8. Range.AddComment 添加批注（润国没有批注，需要用此方法添加批注）

• 属性：

  1. Range.Row 和 Range.Column：返回单元格的行号和列号。
  2. Range.Address：返回单元格的地址。
  3. Range.Font 和 Range.Interior：获取或设置单元格的字体和背景颜色。
  4. Range.Borders：获取或设置单元格的边框样式。
  5. Range.NumberFormat：获取或设置单元格的数字格式。
  6. Range.Merge：合并单元格。
  7. Range.Validation：设置单元格的数据验证规则。
  8. Range.Comment.Shape.TextFrame.AutoSize = True 设置批注尺寸属性为自动

Ribbon

选项卡（Tab）：

• 名称（Name）：例如，您可以创建一个名为"数据分析"的选项卡，以汇总包括数据分析功能的一组命令。
• 显示位置（Position）：您可以将"数据分析"选项卡放在Ribbon的最前面，确保用户首先看到它。

组（Group）：

• 名称（Name）：在"数据分析"选项卡上，您可以创建一个名为"图表工具"的组，用于包含与图表相关的命令。
• 显示位置（Position）：确保"图表工具"组位于"数据分析"选项卡的顶部，以便用户可以轻松找到它。

按钮（Button）：

• 标签（Label）：在"图表工具"组中，创建一个按钮，并使用"创建图表"作为按钮的标签。
• 图标（Icon）：为"创建图表"按钮添加一个表示图表的图标。
• 大小（Size）：将按钮大小设置为中等，以使其与其他命令保持一致。
• 动作（Action）：配置"创建图表"按钮以启动一个宏，该宏将创建用户所需的特定图表类型。
• 提示（Tooltip）：为按钮添加一个提示，例如"点击此按钮创建图表"，以帮助用户了解按钮的作用。

分隔线（Separator）：

• 在"图表工具"组中，使用分隔线将不同类型的图表命令分隔开，以提高可读性。

自定义UI（CustomUI）XML：

• 使用自定义UI XML 可以实现更高级的定制。例如，您可以创建一个包含多个选项卡、组和按钮的自定义功能区，用于不同的数据分析任务。您可以详细定义每个元素的属性，包括颜色、样式、尺寸等，以实现高度个性化的 Ribbon。

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
    <tabs>
        <tab id="myTab" label="my tab">
            <group id="group1" label="worksheet">
                <button id="button1" label="show name" size="large" onAction="show_activesheet_name"  />
            </group>
        </tab>
    </tabs>
</ribbon>
</customUI>

以下转载至:EXCEL自定义功能区Ribbon_excel ribbon-优快云博客

其中id属性是按钮的id名称，label属性是按钮显示的文本，size属性是按钮的大小，onAnction属性指定回调函数名称，

imageMso属性设置按钮的图标（excel内置图标），可通过下载的文件“XML内置图标”查看，按钮加入图标比如：
<button id="button1" label="show name" size="large" onAction="show_activesheet_name" imageMso="MultiplePages />"
 

窗体编程（UserForm）

窗体是一个集合对象

Label（显示文字）

属性（Properties）：

1. Caption： 这是 Label 控件上显示的文本内容。你可以通过设置 Caption 属性来更改 Label 控件上显示的文本。

2. Name： 这是 Label 控件的名称，用于在代码中引用该控件。

3. BackColor： 这是 Label 控件的背景颜色。你可以设置它来改变 Label 的背景色。

4. ForeColor： 这是 Label 控件的前景颜色，即文本的颜色。你可以设置它来改变文本的颜色。

5. Font： 这是 Label 控件上文本的字体属性。你可以设置字体的名称、大小、样式等。

6. AutoSize： 这个属性控制 Label 控件是否自动调整大小以适应其内容。如果设置为 True，Label 将根据 Caption 文本自动调整大小。

7. WordWrap： 如果 WordWrap 属性设置为 True，当文本太长无法适应 Label 控件的宽度时，文本将自动换行显示。

8. Visible： 这个属性决定了 Label 控件是否可见。如果设置为 False，控件将隐藏不可见。

9. Enabled： 这个属性决定了 Label 控件是否处于启用状态。如果设置为 False，控件将变为灰色，不可编辑。

方法（Methods）：

1. AddItem： 这个方法通常用于 ListBox 或 ComboBox 控件中，不是直接与 Label 控件相关的方法。

2. Copy： 用于复制 Label 控件及其属性。

3. Cut： 用于剪切 Label 控件及其属性。

4. Move： 这个方法可以用来移动 Label 控件的位置。

5. SetFocus： 设置 Label 控件为焦点控件，使其可以接收键盘输入。

UserForm1.Label1.Caption = "这是一个标签"
UserForm1.Label1.ForeColor = RGB(255, 0, 0) ' 设置文本颜色为红色
UserForm1.Label1.Visible = True ' 显示 Label 控件

TextBox（文本框）

属性（Properties）：

1. Name： 这是TextBox控件的名称，用于在代码中引用该控件。

2. Text： Text属性表示TextBox中的文本内容。你可以使用它来获取或设置TextBox中显示的文本。

3. BackColor： 这是TextBox控件的背景颜色。你可以设置它来改变文本框的背景色。

4. ForeColor： 这是TextBox控件文本的前景颜色，即文本的颜色。你可以设置它来改变文本的颜色。

5. Font： Font属性允许你设置TextBox控件中文本的字体属性，如字体名称、大小、样式等。

6. MaxLength： MaxLength属性规定了用户可以在TextBox中输入的最大字符数。

7. MultiLine： 如果设置为True，表示TextBox是一个多行文本框，允许输入多行文本。如果为False，表示单行文本框。

8. PasswordChar： 如果TextBox用于密码输入，可以设置PasswordChar属性来指定密码字符的显示（通常是“*”）。

9. Enabled： 这个属性决定了TextBox控件是否处于启用状态。如果设置为False，控件将变为灰色，不可编辑。

10. Visible： 这个属性决定了TextBox控件是否可见。如果设置为False，控件将隐藏不可见。

11. BorderStyle： BorderStyle属性用于指定TextBox控件的边框样式，如None、FixedSingle、FixedDouble等。

方法（Methods）：

1. SelStart和SelLength： 这两个属性用于在TextBox中选择文本的起始位置（SelStart）和选择文本的长度（SelLength）。

2. SetFocus： 设置TextBox控件为焦点控件，使其可以接收键盘输入。

3. Cut、Copy和Paste： 这些方法用于在TextBox中剪切、复制和粘贴文本。

4. Clear： Clear方法用于清除TextBox中的文本内容。

UserForm1.TextBox1.Text = "这是文本框的内容"
UserForm1.TextBox1.ForeColor = RGB(255, 0, 0) ' 设置文本颜色为红色
UserForm1.TextBox1.MaxLength = 100 ' 设置最大字符数
UserForm1.TextBox1.Clear ' 清空文本框内容

ListBox（列表框）

属性（Properties）：

1. Name： 这是ListBox控件的名称，用于在代码中引用该控件。

2. List： List属性是ListBox中的选项列表，可以通过该属性添加、删除或读取列表中的选项。

3. ListCount： ListCount属性表示ListBox中的选项数量。

4. MultiSelect： 如果设置为1 - fmMultiSelectMulti，则ListBox支持多选，允许用户选择多个选项。如果设置为0 - fmMultiSelectSingle，则只能选择一个选项。

5. Selected： Selected属性用于读取或设置ListBox中当前选定的选项。对于多选 ListBox，可以使用Selected数组来获取多个选定项。

6. BoundColumn： 这个属性指定了与ListBox关联的数据源（通常是工作表中的一列），并确定在ListBox中显示哪个列的数据。

7. ColumnCount和ColumnWidths： 这些属性用于设置ListBox中列的数量和各列的宽度，以便显示多列数据。

8. Value： Value属性用于获取或设置ListBox中所选选项的值。对于多选 ListBox，可以使用Value数组来获取多个选定选项的值。

9. Enabled： 这个属性决定了ListBox控件是否处于启用状态。如果设置为False，控件将变为灰色，不可编辑。

10. Visible： 这个属性决定了ListBox控件是否可见。如果设置为False，控件将隐藏不可见。

方法（Methods）：

1. AddItem： 这个方法用于向ListBox的选项列表中添加新项。

2. RemoveItem： 这个方法用于从ListBox的选项列表中删除指定索引的项。

3. Clear： Clear方法用于清除ListBox中的所有选项。

4. ListIndex： 这个属性用于获取或设置ListBox中当前选中项的索引。

5. ListFillRange： 如果ListBox与工作表中的单元格范围关联，可以使用ListFillRange属性来指定该范围。

6. SetFocus： 设置ListBox控件为焦点控件，以便用户可以通过键盘选择选项。

UserForm1.ListBox1.AddItem "选项1"
UserForm1.ListBox1.AddItem "选项2"
UserForm1.ListBox1.MultiSelect = fmMultiSelectMulti ' 允许多选
UserForm1.ListBox1.Clear ' 清空选项

ComboBox（下拉列表框）

属性（Properties）：

1. Name： 这是ComboBox控件的名称，用于在代码中引用该控件。

2. List： List属性是ComboBox中的选项列表，可以通过该属性添加、删除或读取列表中的选项。

3. ListCount： ListCount属性表示ComboBox中的选项数量。

4. Value： Value属性用于获取或设置ComboBox中当前选定的选项的值。

5. Text： Text属性表示ComboBox中当前选定选项的文本内容。

6. DropDownStyle： 这个属性决定了ComboBox的下拉列表框的样式，可以是简单的下拉列表、下拉列表框或可编辑的下拉列表框。

7. AddItem： 这个方法用于向ComboBox的选项列表中添加新项。

8. RemoveItem： 这个方法用于从ComboBox的选项列表中删除指定索引的项。

9. Clear： Clear方法用于清除ComboBox中的所有选项。

10. Enabled： 这个属性决定了ComboBox控件是否处于启用状态。如果设置为False，控件将变为灰色，不可编辑。

11. Visible： 这个属性决定了ComboBox控件是否可见。如果设置为False，控件将隐藏不可见。

方法（Methods）：

1. SetFocus： 设置ComboBox控件为焦点控件，以便用户可以通过键盘选择选项。

2. ListIndex： 这个属性用于获取或设置ComboBox中当前选中选项的索引。

UserForm1.ComboBox1.AddItem "选项1"
UserForm1.ComboBox1.AddItem "选项2"
UserForm1.ComboBox1.Clear ' 清空选项
UserForm1.ComboBox1.DropDownStyle = fmStyleDropDownList ' 设置为简单下拉列表
UserForm1.ComboBox1.Value = "选项2" ' 设置选中的选项

CheckBox（复选框）

属性（Properties）：

1. Name： 这是CheckBox控件的名称，用于在代码中引用该控件。

2. Caption： Caption属性表示CheckBox旁边显示的文本标签。

3. Value： Value属性用于获取或设置CheckBox的选中状态。如果设置为True，表示CheckBox被选中；如果设置为False，表示未选中。

4. Enabled： 这个属性决定了CheckBox控件是否处于启用状态。如果设置为False，控件将变为灰色，不可编辑。

5. Visible： 这个属性决定了CheckBox控件是否可见。如果设置为False，控件将隐藏不可见。

方法（Methods）：

1. SetFocus： 设置CheckBox控件为焦点控件，以便用户可以通过键盘改变其选中状态。

UserForm1.CheckBox1.Caption = "选择项"
UserForm1.CheckBox1.Value = True ' 设置为选中状态
UserForm1.CheckBox1.Enabled = False ' 禁用复选框
UserForm1.CheckBox1.Visible = True ' 显示复选框

OptionButton（单选按钮）

属性（Properties）：

1. Name： 这是OptionButton控件的名称，用于在代码中引用该控件。

2. Caption： Caption属性表示OptionButton旁边显示的文本标签。

3. Value： Value属性用于获取或设置OptionButton的选中状态。如果设置为True，表示OptionButton被选中；如果设置为False，表示未选中。

4. Enabled： 这个属性决定了OptionButton控件是否处于启用状态。如果设置为False，控件将变为灰色，不可编辑。

5. Visible： 这个属性决定了OptionButton控件是否可见。如果设置为False，控件将隐藏不可见。

方法（Methods）：

1. SetFocus： 设置OptionButton控件为焦点控件，以便用户可以通过键盘改变其选中状态。

UserForm1.OptionButton1.Caption = "选项1"
UserForm1.OptionButton1.Value = True ' 设置为选中状态
UserForm1.OptionButton1.Enabled = False ' 禁用单选按钮
UserForm1.OptionButton1.Visible = True ' 显示单选按钮

CommandButton（命令按钮）

属性（Properties）：

1. Name： 这是CommandButton控件的名称，用于在代码中引用该控件。

2. Caption： Caption属性表示CommandButton上显示的文本标签。

3. Enabled： 这个属性决定了CommandButton控件是否处于启用状态。如果设置为False，控件将变为灰色，不可点击。

4. Visible： 这个属性决定了CommandButton控件是否可见。如果设置为False，控件将隐藏不可见。

5. Default： Default属性指定是否将该按钮设置为默认按钮。默认按钮通常在用户按下“Enter”键时激活。

6. Cancel： Cancel属性指定是否将该按钮设置为取消按钮。取消按钮通常在用户按下“ESC”键时激活。

方法（Methods）：

1. SetFocus： 设置CommandButton控件为焦点控件，以便用户可以通过键盘触发它。

2. Click： Click方法用于通过VBA代码模拟用户单击按钮。这可用于在代码中执行与按钮关联的操作。

UserForm1.CommandButton1.Caption = "执行操作"
UserForm1.CommandButton1.Enabled = True ' 启用按钮
UserForm1.CommandButton1.Visible = True ' 显示按钮
UserForm1.CommandButton1.SetFocus ' 设置按钮为焦点
UserForm1.CommandButton1.Click ' 模拟点击按钮

ScrollBar（滚动条）

属性（Properties）：

1. Name： 这是ScrollBar控件的名称，用于在代码中引用该控件。

2. Value： Value属性用于获取或设置ScrollBar的当前值或位置。用户通过拖动滑块来改变这个值。

3. Max： Max属性表示ScrollBar的最大值。通常，当Value达到Max时，滚动条达到最右（水平滚动条）或最下（垂直滚动条）的位置。

4. Min： Min属性表示ScrollBar的最小值。通常，当Value达到Min时，滚动条达到最左（水平滚动条）或最上（垂直滚动条）的位置。

5. SmallChange： SmallChange属性表示用户按下滚动条上的箭头按钮时，Value将增加或减少的小幅度值。

6. LargeChange： LargeChange属性表示用户点击ScrollBar背景时，Value将增加或减少的大幅度值。

7. Orientation： Orientation属性表示ScrollBar的方向，可以是水平（vbHorizontal）或垂直（vbVertical）。

8. Enabled： 这个属性决定了ScrollBar控件是否处于启用状态。如果设置为False，控件将变为灰色，不可拖动。

9. Visible： 这个属性决定了ScrollBar控件是否可见。如果设置为False，控件将隐藏不可见。

方法（Methods）：

1. SetFocus： 设置ScrollBar控件为焦点控件，以便用户可以通过键盘来改变其值。

UserForm1.ScrollBar1.Value = 50 ' 设置ScrollBar的值为50
UserForm1.ScrollBar1.Max = 100 ' 设置最大值
UserForm1.ScrollBar1.Min = 0 ' 设置最小值
UserForm1.ScrollBar1.SmallChange = 1 ' 设置小幅度值
UserForm1.ScrollBar1.LargeChange = 10 ' 设置大幅度值
UserForm1.ScrollBar1.Orientation = vbVertical ' 设置为垂直方向
UserForm1.ScrollBar1.Enabled = True ' 启用ScrollBar
UserForm1.ScrollBar1.Visible = True ' 显示ScrollBar
UserForm1.ScrollBar1.SetFocus ' 设置ScrollBar为焦点

内置函数

文本处理函数：

• Len：返回字符串长度。
• Left：返回字符串左边的指定字符数。
• Right：返回字符串右边的指定字符数。
• Mid：返回字符串的中间部分。
• Trim：去除字符串两端的空格。
• UCase：将字符串转换为大写。
• LCase：将字符串转换为小写。
• Replace：替换字符串中的文本。
• InStr：返回字符串中的子字符串位置。

日期和时间函数：

• Date：返回当前日期。
• Time：返回当前时间。
• Now：返回当前日期和时间。
• DateValue：将文本日期转换为日期。
• TimeValue：将文本时间转换为时间。
• Format：格式化日期、时间和数字。
• Year：返回日期的年份部分。
• Month：返回日期的月份部分。
• Day：返回日期的日部分。
• Hour：返回时间的小时部分。
• Minute：返回时间的分钟部分。
• Second：返回时间的秒部分。

数学和数字函数：

• Abs：返回绝对值。
• Sqr：返回平方根。
• Int：返回整数部分。
• Round：四舍五入。
• Max：返回最大值。
• Min：返回最小值。
• Log：返回自然对数。
• Exp：返回e的指数次幂。
• Sin：返回正弦值。
• Cos：返回余弦值。

数组和集合函数：

• Array：创建数组。
• LBound：返回数组的下界。
• UBound：返回数组的上界。
• Split：拆分字符串为数组。
• Join：将数组元素连接为字符串。
• Filter：过滤数组元素。

工作表和工作簿函数：

• Worksheets：引用工作表集合。
• Sheets：引用所有工作表和图表对象。
• ActiveCell：引用当前活动单元格。
• ActiveWorksheet：引用当前活动工作表。
• Workbooks：引用工作簿集合。
• ThisWorkbook：引用当前工作簿。
• Application：引用Excel应用程序对象。

文件和文件夹函数：

• FileExists：检查文件是否存在。
• Dir：返回目录中的文件列表。
• FileCopy：复制文件。
• FileDelete：删除文件。
• MkDir：创建文件夹。
• RmDir：删除文件夹。

其他常用函数：

• MsgBox：显示消息框。
• InputBox：显示输入框。
• CreateObject：创建OLE对象。
• Shell：运行外部程序。
• SendKeys：模拟键盘输入。

Applicaiton.worksheerfunction可用函数

注意：使用时加上  （Applicaiton.worksheerfunction.）

1. Sum：计算一组数字的总和。
2. Average：计算一组数字的平均值。
3. Max：返回一组数字中的最大值。
4. Min：返回一组数字中的最小值。
5. Count：计算一组数字中的非空单元格的数量。
6. CountA：计算一组值中的非空单元格的数量（包括文本值）。
7. CountIf：计算满足指定条件的单元格数量。
8. SumIf：计算在满足指定条件的情况下，一组单元格的总和。
9. VLookup：在表格中进行垂直查找并返回相应的值。
10. HLookup：在表格中进行水平查找并返回相应的值。
11. AveDev：计算一组数字的平均绝对偏差。
12. StDev：计算一组数字的标准偏差。
13. Match：在一组值中查找指定值并返回其位置。
14. IsNumber：检查单元格中的值是否为数字。
15. IsText：检查单元格中的值是否为文本。
16. Date：返回日期部分。
17. Time：返回时间部分。
18. Year：返回日期的年份部分。
19. Month：返回日期的月份部分。
20. Day：返回日期的日部分。
21. Hour：返回时间的小时部分。
22. Minute：返回时间的分钟部分。
23. Second：返回时间的秒部分。
24. Days360：计算两个日期之间的天数。
25. Text：将数字格式化为文本。

常见程序

破解Excel密码程序 

Sub PasswordBreaker()

'Breaks worksheet password protection.

Dim i As Integer, j As Integer, k As Integer

Dim l As Integer, m As Integer, n As Integer

Dim i1 As Integer, i2 As Integer, i3 As Integer

Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _

Chr(l) & Chr(m) & i1 & i2 & i3 & i4 & i5 & i6 & Chr(n)

If ActiveSheet.ProtectContents = False Then

MsgBox "One usable password is " & Chr(i) & Chr(j) & _

Chr(k) & Chr(l) & Chr(m) & i1 & i2 & i3 & i4 & i5 & i6 & Chr(n)

Exit Sub

End If

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

End Sub

说明：本文为个人学习记录文章，方便后续复习时查阅，部分资料收集来源于网络整理以及个人总结，如有侵权，请联系删除。