VBA 函数整理

定义数据类型

定义变量

Dim x As Variant
Dim y As Integer

定义数组

Dim arr_widetable(4) As String
Dim myArray(1 To 5, 5 To 15) 2行5列数组

数组元素赋值

arr_widetable(1) = 234

---

输入输出

创建输入框

InputBox(string a, string b)
a 在输入框中间显示作为提示，b 在标签页中显示。

创建输出框

MsgBox()
填入要输出的内容

---

字符串处理

其它数据类型转字符

str_a = Str(a)

替换字符串中的子字符串

target = Replace("W" & prev_week, Space(1), "")
将字符串中的空格去掉

判断字符串中特定子字符串的位置

InStr(a, b)
如果 a 中有 b，则输出 b 在 a 中的位置。
可以用于判断字符串中有无特定子字符串。
If InStr(a, b) > 0 Then

判断字符串中有无特定子字符串

* 代表任何字符串

1. a Like b*
   功能：判断 b 是否与 a 的开头一致
   例：If "Steve Jobs" Like "Steve"* – 返回 True
2. a Like *b*
   功能：判断 b 是否在 a 的中间
   例： If "vba" Like *"b"* – 返回 True

---

空格

Space(n)
输出的是长度为 n 的连续空格

截取字符串

Left(string, n) 从左起第n个字符开始取
Right(string, n) 从右起第n个字符开始取
Mid(string, m, n) 从左第m个字符开始取长度为n的子字符串

分割字符串

Split(string, char)
以 char 为识别符分割 string，返回一个数组。
例：Split("sha-256", "-")，返回值是[“sha”, “256”]

---

循环

最简单的

For i = 1 To 10
    anything
Next

遍历数组

假设数组为 arr
LBound(arr) 为数组的第一个元素

For e = LBound(arr) To UBound(arr)
    anything
Next

遍历工作表

For Each sh In Worksheets
    anything
Next

遍历工作表的单元格

For Each rng In Range()
    rng.xxxxxxx
Next

---

Excel 操作

工作表是 sheet，工作簿是 Workbook （Excel 文件）。
Excel 主要的操作的是「对象」，一般用 Range() 选择对象。

选择工作表

Sheets(工作表名)
Sheets(工作表序号)

选择工作表后在表中进行操作

With Worksheets(target_sheet)
    .Cell(i, j).xxxxxx
    .Range(xx).xxxx
    Anything
End With

选择单元格

Cells(i, j) 其中 i 是行， j 是列
Cells("A1") A1 单元格

激活

Worksheets(i).Activate
Cells(i, j).Activate
激活后可以选择已激活的对象进行操作
ActiveSheet.xxx
ActiveCell.xxx

插入一列

Columns(col_index).EntireColumn.Insert

计算区域非空单元格数量

可以用于计算列长度、行长度。
WorksheetFunction.CountA(Range(Cells(title_row + 1, 4), Cells(1000, 4)))

选择

ActiveSheet.Cells(1, insert_index + 1).Select
Range(xx).Select

复制

Range(xx).Copy
Columns(col_index).Copy

粘贴

普通粘贴

Selection.Paste

粘贴数值

Columns(insert_index).PasteSpecial Paste:=xlPasteValues

清除内容

Range(xx).ClearContents
Cell.ClearContents

---

自建函数

构建函数

Function a()
    anything
End Function

调用函数

1. 直接输入函数名
2. Call a()

函数返回值

要有一个变量名与函数名一致，函数会返回同名变量。

Function get_index(target, title_row, target_sheet)
    get_index = insert_index
End Function

例子

功能：输入一个英文字母，得到它后面的第shift个字母。

Function shift_char(char, shift)
    num = Range(char & 1).Column
    num = num + shift
    next_char = Split(Cells(1, num).Address, "$")(1)
    get_next_char = next_char
End Function

---

特殊方法

迷你图

将选定区域 area 中的数值做出迷你图。
ActiveCell.SparklineGroups.Item(1).Item(i - title_row).SourceData = area

清除剪贴板

运行宏后，可能会有大量内容在剪贴板上，但都是我们不需要的，可以清除。
Application.CutCopyMode = False

---

优化

Application.ScreenUpdating
值为 True / False ，控制宏运行时屏幕是否自动更新

---

PPT 操作

在 Excel 调用 PowerPoint 函数

Set PowerPointApp = GetObject(class:="PowerPoint.Application")
然后 PowerPoint 中的函数就能通过 PowerPointApp.xxx() 调用

设置当前操作 PPT

Set myPresentation = PowerPointApp.ActivePresentation

设置 PPT 页面样式

取第一页 PPT 的样式

Set pptLayout = PowerPointApp.ActivePresentation.Slides(1).CustomLayout
mySlide = myPresentation.Slides.Add(1, pptLayout)

新建只有标题的 PPT 页

mySlide = myPresentation.Slides.Add(1, 11)

将 Excel 内容复制到 PPT

MyRangeArray(x).Copy
mySlide.Shapes.PasteSpecial DataType:=1
如何选择 Datatype 参见这个链接

移动、放缩对象

Set myShape = PowerPointApp.ActiveWindow.Selection.ShapeRange '取最新添加的对象
myShape.Width = SlideWidth - 100  '宽度为页面宽度 - 100
myShape.Left = (.SlideWidth \ 2) - (myShape.Width \ 2)  '居中
myShape.Top = 90  '设置垂直位置

常用

PowerPointApp.Visible = True
PowerPointApp.Activate
加到最后确保 VBA 生效