Office VBA语法

Office VBA语法

VB这个东西应该是初中时候就接触到的一门课程语言,当时的计算机课程和体育课一样,不在应试范围,学得也记忆模糊,基本上只记得语言应该是vb和vb的变量定义dim了, Office和Excel支持VB宏,可以基于VBA做一些嵌入Excel的界面或者其他交互的功能,当然在近几年各种脚本语言大行其道的时候(python、perl等),Excel中的VBA也确实是过时了,不过最近看了看以前基于VBA搞的一些工具,单纯从使用角度看,依旧是简洁合适的,所以今天就来整理下vba里的一些基本语法特征;

• 常用的一些变量类型

String 
Long
Boolean
Variant
Object
Collection

• 局部变量定义

Dim i As Long
Dim j As Long = 500

• 成员变量

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/dim-statement
You can declare a variable in a procedure, block, class, structure, or module. You cannot declare a variable in a source file, namespace, or interface. 
A variable that is declared at module level, outside any procedure, is a member variable or field. Member variables are in scope throughout their class, structure, or module. A variable that is declared at procedure level is a local variable. Local variables are in scope only within their procedure or block.

Public Xxx(3) As Variant ’ 数组类型
Public xx As String ’ 字符串
Public Const xx As Long = 10 ’ 常数

• 赋值语法

Dim ob As Object
Set ob = xx
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/set-statement

• vba := 的作用是指定形参名传参

https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-visual-basic-syntax
https://stackoverflow.com/questions/40768172/what-is-the-difference-between-and-in-excel-vba

• 函数指针

Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Declare PtrSafe Function OpenClipboard& Lib "user32" (ByVal HWnd&)

• 成员函数定义
  Public Function func(xx As Variant)
  …
  End Function
• 函数调用

Public Sub funcxxx(x As Long, y As Long)
...
End Sub

funcxxx i, i

• 字符串操作

len = Len(str)
Left(str, 2)
Trim(str)
Mid(sCell, 2)
UCase
InStr
LCase
xx  = Replace(xx, "yy", "zz")
Split(str, ",")
i = CLng(str)

• 条件宏

#If Win64 Then
...
#Else

#End If

• 逻辑控制

For i = 1 To i_len
	...
	
If xx And Not yy Or zz Then
    ...
ElseIf xx Then   
    Exit Function
End If

’ 单行

If str = "" Then str = "xxx"
If Trim(str) = "" Then Exit Sub

’ While

Do
    ....
Loop While i = xxx

While EOF(1) = False
    ...
Wend

For Each sheet In workbook.Sheets
    If sheet.Name = ws.Name Then 
        ...
        Exit For
    End If
Next sheet
                        
For Each a In Application.Names
    ...
Next

With Selection.Validation..

End With

Select Case xx
    Case "0": xx
End Select

For i = 5 To 1 Step -1
    ...
Next i

’ 跳转

GoTo label

’ 错误处理

On Error Resume Next
On Error GoTo 0 

’ 等待

delay 2

’ 注释

’ 访问环境变量
xxx = Environ$(“HOME”)

’ 多行拼接
If … And _
… _

• Excel相关
  ’ 文件对话框

Dim file_name As Variant
file_name = Application.GetOpenFilename(FileFilter:="Microsoft Excel Document(*.xlsm),*.doc,All Files(*.*),*.*", _
                                            Title:="title")
If file_name = False Then Exit Sub 

’ 应用阻塞

Application.ScreenUpdating = True
Application.DisplayAlerts = True

’ 消息提示

MsgBox "xx" & str & "yyy ""zzz"" www", vvv, "title"
.Cell(i, 1) = str

• Excel 对象、对象、窗体、模块、类模块
  ThisWorkbook

’ 类模块 ClassA

Public info As New Collection 
Private Sub Class_Initialize()  ' 构造
End Sub

Private Sub Class_Terminate()  ' 析构
End Sub
' 类实例
Set xxx = New ClassA   ' 实例

’ 窗体也有对应的类代码不过特别的是窗体有相应的对话框
’ Excel 对象也有相应的代码文件
’ 模块和类模块的差异

https://www.mrexcel.com/archive/vba/difference-between-module-and-class-module/

As you probably know a standard module can store procedures and functions which can be either Private or Public (the default) and can be accessed either from within that module only (Private) or from anywhere in the project (Public). Modules are also used to store variables, constants and declarations (i.e. API calls) that will need to be accessed from anywhere in the project.

• 强制必须有变量声明

Option Explicit

参考:
https://www.guru99.com/vba-data-types-variables-constant.html
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-explicit-statement
https://www.automateexcel.com/vba/class-modules/
https://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project