VBA相关知识点

最新推荐文章于 2021-09-01 11:19:29 发布

wolfsun3

最新推荐文章于 2021-09-01 11:19:29 发布

阅读量613

点赞数

分类专栏： vba 脚本

本文链接：https://blog.csdn.net/wolfsun3/article/details/77371458

版权

脚本同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

vba

1 篇文章 0 订阅

订阅专栏

1   打开方式                                                                                                                                           宏
   开发->宏->编辑                                                                                                                                           四个预置宏名:
   Alt+F11                                                                                                                                           Auto_Open
                                                                                                                                               Auto_Close
   自动运行VBA                                                                                                                                           Auto_Activate
       双击ThisWorkBook                                                                                                                                       Auto_Deactivate
       右边选择WorkBook
       添加需要的代码即可

   包含宏的excel要保存为xlsm;

2   变量声明
   Public ERR_END                                  As Long     ' 楢懕幚峴梡偵捛壛
   Public Const EXCEL_END_GYO                      As Long = 65536                             'EXCEL嵟廔峴
   Dim line As Long
   Dim line (29) As Long                                                                                                       数组
   Dim line (29, 33) As Long                                                                                                       数组

3   function
   Function MsgBoxEx(ByRef prompt, Optional ByRef buttons = 0, Optional ByRef title = "")                                                                                                       ByRef按地址传递,可以作为输入和输出,值会跟随变化,且效率高,节约空间
        If toollog_cnt = Empty Then                                                                                                       ByVal按值传递,只能作为输入
            toollog_cnt = 0
        End If

        If TOOL_BATCH_MODE Then                                                                                                       有then
            '
        Else If xxxxx                                                                                                       没有then
            MsgBoxEx = MsgBox(prompt, buttons, title)
        Else
            MsgBoxEx = MsgBox(prompt, buttons, title)
        End If

        ReDim Preserve TOOL_LOG(toollog_cnt + 1)
        TOOL_LOG(toollog_cnt) = prompt
        toollog_cnt = toollog_cnt + 1
   End Function

4   sub                                                       Function有参数，sub没有参数
   Public Sub parasheet_copy()

        RUN_MODE = 0                                                       中间可以使用Exit Sub来推出
        Call parasheetcopy                                                       调用sub使用call

   End Sub

5   select case语句
                    Select Case hikisu1

                       'TP
                       Case "10"
                           '張棟側偟
                       'CTL
                       Case "20"
               Case Else
           End Select

6   InputBox()
   信息输入函数

7   MsgBox()
   信息输出函数

8   Dir
   检查某些文件或目录是否存在

   MyFile = Dir("C:\WINDOWS\WIN.ini")                                                           返回“WIN.INI” （如果该文件存在）。
   MyFile = Dir("C:\WINDOWS\*.ini")                                                           返回带指定扩展名的文件名。如果超过一个 *.ini 文件存在，函数将返回按条件第一个找到的文件名。

   ' 显示 C:\ 目录下的名称。
   MyPath = "c:\"   ' 指定路径。
   MyName = Dir(MyPath, vbDirectory)   ' 找寻第一项。
   Do While MyName <> ""   ' 开始循环。
       ' 跳过当前的目录及上层目录。
       If MyName <> "." And MyName <> ".." Then                                                                                       <> 英文半角符号, 运算符号,表示"不等于"的意思
          ' 使用位比较来确定 MyName 代表一目录。
          If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
             Debug.Print MyName   ' 如果它是一个目录，将其名称显示出来。
          End If
       End If
       MyName = Dir   ' 查找下一个目录。
   Loop

9   Kill
   删除文件
   Kill (tmpFile)

10   文件操作
            tmpNo = FreeFile
            If (MAC_ABYTE <> 0) Then
                Open tmpFile For Append As #tmpNo                                                           打开文件
                If (MAC_ABYTE = 3) Then
                    Print #tmpNo, "00"                                                           输出到文件
                ElseIf (MAC_ABYTE = 2) Then
                    Print #tmpNo, "0000"
                ElseIf (MAC_ABYTE = 1) Then
                    Print #tmpNo, "000000"
                End If
                Close #tmpNo                                                           关闭文件
            End If

   Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]
       mode           必要。关键字，指定文件方式，有 Append、Binary、Input、Output、或 Random 方式。如果未指定方式，则以 Random 访问方式打开文件。

11   字体设置
        Cells.Font.Name = "Calibri"
        Cells.Font.Size = 9

12   内容消除
   Range("R10:V1000").ClearContents                                                   ClearFormats

13   单元格消除
   Cells(i, 46).Clear

   Sub struct_clear()
        Application.Calculation = xlManual                                                           在2007版本中，如果表格中有大量公式，会严重拖慢速度，所以一般VBA程序开头都是把该状态设置为手动，结束后恢复为自动
        Application.ScreenUpdating = False                                                           关闭屏幕刷新，提高效率

        Dim i As Integer        '僐僺乕尦
        Dim MaxRow As Integer

        MaxRow = Range("H7").End(xlDown).Row

        For i = 8 To MaxRow
            If Cells(i, 10) = "struct" Then
                Cells(i, 46).Clear
            End If
        Next i

        Application.Calculation = xlAutomatic                                                           状态恢复为自动
        Application.ScreenUpdating = True                                                           恢复屏幕刷新

        MsgBox ("廔椆")

   End Sub


14   行列数取得
        MsgBox Workbooks(name_excel2).Sheets(NAME_SHEET_CTL20).UsedRange.Columns.Count                                                                                                                           前提是顶部和左侧无空行空列
        MsgBox Workbooks(name_excel2).Sheets(NAME_SHEET_CTL20).UsedRange.Rows.Count

15   设定某个单元格双击后的操作
   Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)                                                                                                                           双击捕获
        If Target.Row = 3 And Target.Column = 2 Then
            Cancel = True
            Call analyzeFFT(Target)
        ElseIf 3 <= Target.Row And Target.Row <= 5 And Target.Column = 3 Then
            Cancel = True
            Target.Offset(0, 1).Value = FileReference(Target, Target.Offset(0, 1).Value, msoFileDialogFilePicker)                                                                                                                           打开文件选择对话框，并将文件路径放入后一个单元格内
        End If
   End Sub

16   判断文件是否存在
   Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
        Reset
        If Not (FSO.FileExists(BaseFilesPath(0)) And Right(BaseFilesPath(0), 14) = "analyzeFFT.exe") Then
            MsgBox "Please input analyzeFFT.exe path.": Exit Sub
        ElseIf Not (FSO.FileExists(BaseFilesPath(1)) And Right(BaseFilesPath(1), 4) = ".txt") Then
            MsgBox "Please input iq data text path.": Exit Sub
        End If

17   调用其他sheet的方法
   Call Sheet2.GetOverView(Tempfolder, Prefix)

18   调用shell命令
   Shell "cmd.exe /k " & Target & "", vbNormalFocus

19   调用另外一个excel里的相关命令
   Sub 幚巤帋尡嶌惉()
        ChDrive ThisWorkbook.Path
        ChDir ThisWorkbook.Path
        Workbooks.Open Filename:="..\..\common_tool\L1DL_UL擖椡僨乕僞惗惉僣乕儖.xls"
        Windows("L1DL_UL擖椡僨乕僞惗惉僣乕儖.xls").Activate
        Sheets("L1擖椡僨乕僞惗惉僣乕儖").Select
        Workbooks(ThisWorkbook.Name).Activate
        Range("C3").Copy
        Workbooks("L1DL_UL擖椡僨乕僞惗惉僣乕儖.xls").Worksheets("L1擖椡僨乕僞惗惉僣乕儖").Activate
        Range("C6").PasteSpecial Paste:=xlPasteValues
        Windows("L1DL_UL擖椡僨乕僞惗惉僣乕儖.xls").Activate
        Application.Run "L1DL_UL擖椡僨乕僞惗惉僣乕儖.xls!Button2_Proc"
        Application.Run "L1DL_UL擖椡僨乕僞惗惉僣乕儖.xls!Button3_Proc"
        Workbooks("L1DL_UL擖椡僨乕僞惗惉僣乕儖.xls").Close

   End Sub

20   不同sheet间操作
   Sub clear()
   Dim rng As Range, i, ar
   Dim sh As Worksheet
   Application.EnableEvents = False                                                                                                                                                       让程序只执行一次
       ar = Array("UE0", "UE1", "UE2", "UE3", "UE4", "UE5", "UE6", "UE7", "UE8", "UE9", "UE10", "UE11", "UE12", "UE13", "UE14", "UE15")                                                                                                                                                       不同的sheet名
       For n = 0 To UBound(ar)
           Sheets(ar(n)).Range("C5:BJ6").ClearContents
       Next
   Application.EnableEvents = True                                                                                                                                                       程序执行完后，要还原
   End Sub

21   判断内存中已经打开的excel
   Sub compare()
        Dim wb
        Dim name_excel1
        Dim flag_open_excel1

        name_excel1 = "excel1.xlsx"

        flag_open_excel1 = False

        For Each wb In Application.Workbooks
            If wb.Name = name_excel1 Then
                flag_open_excel1 = True
                Exit For
            End If
        Next

        If Not flag_open_excel1 Then
            MsgBox name_excel1 & " not open!"
        End If
   End Sub

22   获取单元格内容
   name_excel1 = Sheets(1).Range("c3").Value                                                                   Sheets里也可以直接写sheet名
   name_excel1 = Sheets("TOOL").Range("c2").Text

23   三目运算符
   x=iif(y>0,"positive","negative")

24   sheet复制
   Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")                                                                   此示例复制工作表 Sheet1，并将其放置在工作表 Sheet3 之后
   sheet_excel1.Copy After:=ActiveWorkbook.Sheets("TOOL")

25   获取当前活动状态单元格
   Set active_sheet = Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name)

26   单元格赋值
   active_sheet.Cells(cell_result_row, cell_result_col) = arr_sheets_excel1(count_sheet_excel1)

27   sheet个数
   Workbooks(name_excel1).Sheets.Count

28   sheet名称
   Workbooks(name_excel1).Sheets(count_sheet).Name

25   sheet删除
   sh.Delete

26   计算总行数
   count_rows = active_sheet.UsedRange.Rows.Count                                                                                       得到使用的行数，比实际行号小1
   count_cols = active_sheet.UsedRange.Columns.Count

   ActiveSheet.Range("A65535").End(xlUp).Row
   ActiveSheet.Range("IV1").End(xlToLeft).Column

27   回到某个sheet
   ActiveWorkbook.Sheets("TOOL").Activate

28   将数字转为abc
   ' change dec to abc
   Public Function DecToABC(ByVal var As Integer) As String
        Dim res As String
        Dim remainder As Integer
        Dim quotient As Integer

        remainder = var Mod 26

        If remainder = 0 Then
            var = var - 26
            remainder = 26
        End If

        quotient = var \ 26

        If quotient <> 0 Then
            res = DecToABC(quotient)
        End If

        DecToABC = res & Chr(remainder + 65 - 1)
   End Function

29   语句太长换行
   Dim MyPath As String, MyName As String, _                                                               如果是语句可以直接在要换行的位加一个空格一个下划
   x = "ABCDEFG" & _                                                               如果是字符串可以加以加一个空格一个&和一个空格加一个下划线

30   参数传递
   Call COMPARE_CTL_COMMON(sheet_excel1, sheet_excel1)
   Public Sub COMPARE_CTL_COMMON(sheet_excel1, sheet_excel2)

31   InStr
   If InStr(arr_sheets_both(count_sheet), STRING_FLAG_CTL_SHEET) Then
   inStr(3,"x5ydscdsx","d",1)=4                                                                                       从字符串"x5ydscdsx"第3个位置找字符串"d"第一次出现的位置

32   单元格设置
   Cells(count_row, count_col).Interior.Color = RGB(255, 255, 255)                                                                               背景色
   Cells(count_row, count_col).Font.Color = RGB(0, 255, 0)                                                                               字体颜色
   Range("A1:B11").Borders.LineStyle=1                                                                               有边框
   Range("A1:B11").Borders.LineStyle=0                                                                               无边框
   Cells(cell_result_row, cell_result_col + 1).Borders.LineStyle = 0                                                                               同上

33   工作表选择
   ActiveWorkbook.Sheets("夵斉棜楌").Select
   Workbooks(name_excel1).Sheets(Range("C" & row).Value).Activate                                                                                       调用不同工作表

34   sheet拷贝
   sheet_excel1.Copy After:=ActiveWorkbook.Sheets(NAME_SHEET_TOOL)

35   新建一个sheet
   Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheet_excel1.Name

36   写入文件
   Open (managefilepath) For Output As #FileNo1                                                                                               mode是必需的，指定文件方式，有Append、Binary、Input、Output、或Random方式。如果未指定方式，则以Random访问方式打开文件。
   Print #FileNo1, "#################################################"
   Close #FileNo1

37   变量名前的符号代表数据类型
   '@ Currency
   '# Double
   '& Long
   '! Single
   '% Integer
   '$ String