编写VBA脚本示例代码

Posted on | In

VBA检测脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

Function Match(str As String, pattern As String)
    Dim reg As Object
    Set reg = CreateObject("VBScript.Regexp")
    With reg
        .Global = False '非全局则匹配到第一个即停止
        .pattern = pattern '正则部分
        Match = .Test(str) '返回结果
    End With
End Function


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


    has_error = False '全局判定

    For Each sht In Worksheets
    
        Dim matched As Boolean
        
        ' 跳过被#注释的sheet
        matched = Match(sht.Name(), "^#")
        If matched Then GoTo Continue
        
        '进行sheet内的格式检测
        matched = Match(sht.Cells(1, 1).Value, "^convert")
        If Not matched Then
            MsgBox "sheet:<" & sht.Name() & ">格式错误: 数据表首行必须为convert声明, 否则请用#注释表名.", vbOKOnly + vbExclamation
            GoTo Error
        End If
        
Continue:
    Next
    
    
Finish:
    MsgBox ("表格检测通过,已保存成功.")
    Exit Sub
    
Error:
    Dim ret
    ret = MsgBox("表格检测失败,请根据提示修正后再次尝试保存!", vbOKOnly + vbCritical)
    'If Not ret = vbOK Then Cancel = True
    Cancel = True
    Exit Sub

End Sub