vba基础 个人学习详细笔记 知识点梳理 -- 大白话篇

---

宏是什么

宏就是录像机功能、可以录制一些基本的操作。
vba 是编辑宏的语言，可以把宏实现拓展。

1.带有宏的工作表保存

1.带有宏的工作表要保存为 xlsm 格式，保存的时候会提示。
  xlsx 保存的只是普通的 excel(excel安全机制导致的)

2.相对引用与未使用相对引用

未使用相对引用录制的宏是 固定单元格的 （Range）
使用相对引用录制的宏是   活动单元格的（ActiveCell）

3.宏不能撤销–只能删除

宏不能撤销--只能删除（右键--格式--清除格式）

4.表单控件运行宏

  插入--按钮--托选区域--选择要运行的宏  (wps 的不在开发工具处)
  右键点击按钮进入编辑状态--左键可以移动按钮

5.自定义工具栏添加宏

 文件--选项--快速访问工具栏--宏（wps 中没有）

6.录制新宏

     保存到
     			（新工作薄、当前工作薄）时，将宏保存到单一的工作薄中
      			 别的Excel 工作薄也想使用这个宏，需要将保存有宏的工作薄打开才能使用。
     保存到 
         	    个人工作薄时候（wps没有）相当创建了一个文件  personal.xlsb 工作薄
          		视图--隐藏  personal.xlsb可以隐藏掉   
         		视图--取消隐藏  personal.xlsb可以显示出来

vba开发

7.vba 开发环境

    项目
      -- EXCEL 对象 
           --   sheet1 (工作表1)
           --   sheet2 (工作表2)
           --   ThisWorkbook (工作薄)
      -- 模块
           --  模块1（录制的宏对应的代码的地方）
           
     模块可以导入也可以导出   格式为 xx.bas

8.语法

   Sub  子程序代码名字（）
   	MsgBox "这是我的第一个VBA代码"
   End Sub
   
  '  -- 注释符号
  Sub 表示一个子程序，可以理解为一串代码的集合
  MsgBox 弹窗
  每句代码结束不用加 分号

  注意： 一个 模块中可以有 多个  Sub 子程序代码块

9.运行

    1.选中对应 sub 代码 --直接 点运行
    2.视图--立即窗口--输入
         Call 子程序代码名字
       --回车

10. 变量声明

   ①变量声明

    格式：
    	Dim  变量名 As  变量类型
    	变量名 = 值

      如：Dim score As Integer
          score =100

      · 变量不赋值有默认值

     ②多变量声明
      Dim  变量名1 As  变量类型1，变量名2 As  变量类型2


     ③变量可以不指定类型
      Dim  变量名
      变量名 = 值
      
     ·变量可以不指定类型（variant:可变数据类型），不会报错，但占据空间不确定

      
     ④防止变量不声明使用

       1.Sub 上面写 Option Explicit
       2. 工具--选项--编辑器--要求变量声明

11. 数据类型

      Byte   	1(字节)
      Integer	2(字节)         默认值 0
      Long    	4(字节)
      Currency  8(字节)

      Single  4(字节)
      Double  8(字节)

      Boolean  2   True/Fale

      Date   8
      String 

   日期格式定义：
        Dim birthDay As Date
        birthDay = #1991/12/12#
  
    ·日期前后加要加 # 号，而且会自动格式化
    ·显示日期格式会跟随电脑系统的显示格式

12.变量

      局部变量
         Sub 里面 声明的变量，当前的sub 能使用

      全局变量
      	第一个Sub 上面 声明的变量，当前整个模块的sub 都能使用该变量。
        
        Dim score As Integer
        Sub helloWorld()
   		 	xxx
	 	End Sub
        Sub helloWorld()
   		 	xxx
	 	End Sub
     
      超全局变量 
      	第一个Sub 上面 声明的变量 将 Dim 改为 public,别的模块的 sub 也能使用该变量。  
        public score As Integer

13. 变量 的 static 的用法

      Sub helloWorld()
      	Static count As Integer
        count = count + 1  
        MsgBox "您已经运行了:"  & count &"次" 
      End Sub
  
      static 修饰的变量会声明超出子程序的范围，但是在第二个子程序无法引用
     （类似该程序的 static 修饰的变量 加了循环，运行一次，修改一下变量）

      想停止 count  变量 需要
                         ·关闭工作薄，停止程序
                         ·点停止按钮，重新设置

14. With语句

       避免每次都要重复写对象

       with 对象

		.属性1=属性值

		.属性2=属性值

		……

		.属性N=属性值

      end with

15.常量

  Const pi As Double = 3.14159

   ·定义常量用 Const 而不是 Dim
   ·定义常量的时候要直接赋值
   ·常量的值不允许被修改
      

    局部常量
         Sub 里面 声明的常量，当前的sub 能使用

    全局常量
      	第一个Sub 上面 声明的常量，当前整个模块的sub 都能使用该常量。
        
         Const pi As Double = 3.14159

         Sub helloWorld()
   			 xxx
	 	 End Sub
         Sub helloWorld()
   		 	xxx
	    End Sub
     
     超全局常量 
        public Const pi As Double = 3.14159
        第一个Sub 上面

16.算数运算符

     +、-、*、/、^(幂运算)、Mod（求模）
     

17.比较运算符

     > 、>=、 <、 <=、 =（等于，区别赋值号 可以把比较的变量加上小括号） 、
     <> （不等于）
    Like (字符串匹配)	

18.逻辑运算符

  And（与）  、OR（或）、  Not（非）  
  XOR(逻辑排除) : 如果表达式中只有一个表达式的值为True，则结果为True。

19.连接操作符

        & : 拼接两个值
            1 & 2 = 12
            "1" & "2" = 12

        + : 将两个值添加为变量，其值是数字
            1 + 2 = 3
            "1" + "2" = 12

20.命名规范

    数字、字母、_ 下划线；数字不能做开头

21.if 结构

   ①If  判断条件 Then
     	代码语句
      End If
       
   ②If  判断条件 Then
     	代码语句
     Else
        代码语句
     End If

   ③If  判断条件 Then
     	代码语句
     ElseIf  判断条件 Then
        代码语句
     End If

22. select 结构（类似 switch）

    Select Case 变量
           Case Is  表达式（这里不用写变量）
                表达式
	   Case Is  表达式（这里不用写变量）
                表达式
           Case Else
                表达式
    End Select

    Select Case score
   	 Case Is > 90
    	MsgBox "优秀"
    	Case Is > 70
    	MsgBox "良好"
    	Case Else
    	MsgBox "差"
    End Select

23. For Next 语句

      For 循环变量初始赋值 to 循环变量最大值   
          执行语句   
      Next 循环变量 (修改循环变量)

     Dim num As Integer
     For num = 1 To 5
        MsgBox num
     Next num

24.step的应用

   
     For 循环变量初始赋值 to 循环变量最大值 step 数字      
          执行语句    
     Next 循环变量 (修改循环变量，带自增效果)
     
    ·step  循环变量的循环间隔

25.中途退出 for Next 循环

     ...
      Exit For (退出 for循环 ，类似 break)
     ...

26. Do while 循环 （满足条件继续循环）

   Do while 循环条件  
      执行语句    
      修改循环变量
   loop

27.Do Until 循环 （循环直到不满足条件）

   Do Until 循环条件  
      执行语句  
      修改循环变量
   loop

28.无参数子过程

       子过程（子函数、子程序）
        Sub test()
            ...
        End Sub  

    有参数子过程
         Sub test(num1 As Integer,num2 As Integer)
            ...
         End Sub

    调用：无参数       test()
          有一个参数   test(100)
          有多个参数   test 2,100    (不带括号)

     Exit Sub (中途退出子程序)

29. 自定义函数

自定义函数:
        自定义的函数可以在 Excel 表格单元格中直接调用

     函数过程语法：
      Function 函数名（参数1，参数2） AS 数据类型      （是返回值的数据类型）         
         函数名 = 值
      End Function 

     调用：
        Sub test()
           函数名（参数1，参数2）   
        End Sub
                 
      ·函数过程有返回值，子过程没有返回值
      ·函数名作为函数返回变量
      ·函数调用 函数名（） 带括号

30.一维数组

     声明定义一维数组
     	①Dim  数组名（开始 To 结束） As Integer    
          Dim  MyArray（1 To 5） As Integer

        ②Dim  数组名（值） As Integer           
            ·默认开始下标为 0
            ·在第一个sub最上面 添加  option Base 1，下标是从1开始

        ③使用Array函数创建常量数组：
            一维常量数组：Array("A",1,"C") 
      
     数组赋值
           数组名（下标）= 值   
           MyArray（1） =10

     访问数组
          数组名（下标）
          MyArray（1）
   
     遍历
       Dim Index As integer
       For index = LBound(数组名) To UBound(数组名)
           MsgBox MyArray(Index)
       Next index

     数组方法：
       LBound(数组名) :数组的最小角标
       UBound(数组名) :数组的最大角标
       Split(String,char)：返回一个数组，其中包含基于分隔符分割的特定数量的值。
          Split("Red $ Blue $ Yellow","$")

       Join(array,char) :函数返回一个包含数组中指定数量的子串的字符串。

       Filter(array,char,是否包含):函数返回一个基于零的数组，
                     其中包含基于特定过滤条件的字符串数组的子集。
   
                        是否包含 true: 筛选所有含char的数值组成一个新数组
                        是否包含 false:筛选所有不含char的数值组成一个新数组

       IsArray(数组名):函数返回一个布尔值，指示指定的输入变量是否是数组变量。

       Erase 数组名  :用于重置固定大小数组的值并释放动态数组的内存。 它的行为取决于数组的类型

31.二维数组

   Dim  数组名（行开始 To 行结束，列开始 To 列结束） As Integer

32.动态数组（可以动态修改下标大小）

 
     定义一维动态数组
     	Dim  数组名（） As Integer

     明确数组的下标
        ReDim 数组名（开始 To 结束）

        ReDim  动态调整数组时，会将之前的赋值都自动抹除，
         添加 Preserve 将保留历史数据      
         ReDim Preserve  数组名（开始 To 结束）

33.对象的层级框架

 
      Applocation（Excel）--Workbook(工作薄)  --     Worksheet(工作表)
                                                 |          |         |
                                               shapes   Range 单元格 Charts
     
      Applocation.Workbooks("xxx").worksheets("xxx").Range("xxx"): 
      层级使用时要用集合  带S的，表示 很多xx中的某一个具体的
                                                               
     注意：集合也是对象： Workbooks、worksheets、sheets、cells

     使用： 如   属性  Range("A1").value
                      Range("A1").value =100
                方法  Range("A1").Clear

34.Range()、cells()、 Offset

  一、单元格的引用
  	  1.单元格的引用 Range()
    	 ①引用某个单元格
    	 Range("单元格坐标").Value = 值
       		Range("A1").Value = 100

    	 ②引用多个单元格
    	 Range("单元格坐标1,单元格坐标2,..").Value = 值
       		Range("A1,c2,b5").Value = 100

    	 ③引用某个单元格区域
     	 Range("区域左上单元格坐标：区域右下单元格坐标").Value = 值
       		 Range("A1:C5").Value = 100

     	④引用不连续的单元格区域
    	 Range("区域左上单元格坐标1：区域右下单元格坐标1,
     	  区域左上单元格坐标2：区域右下单元格坐标2").Value = 值

        ⑤引用单元格区域名称
         Excel 中选取单元格区域--在单元格坐标处修改名字
         Range("区域名字").Value = 值
       
         赋值时，.value可以省略掉

    2.单元格的引用 []
       [A1]        '表示A1单元格
       [A1:A9]     '表示A1到A9单元格    

    3.单元格的引用 []
       Cells 属性（好处是行号列标都是数字）
        
   	   引用工作表中的某一个单元格
   		Worksheets("工作表名").cells(行号数字，列标数字)
   			Worksheets("sheet1").cells(2,3).Value =100

   		引用单元格区域
   		Range(Cells(行号数字，列标数字)，Cells(行号数字，列标数字))

   		引用单元格中的某个单元格
   		Range("坐标开始，坐标结束").Cells(行号数字，列标数字)

  二、行和列的引用
    
    ①引用单行单列
     Range("行号:行号").value =100
    	Range("10:10").value =100

     Range("列标:列标")
    	Range("B:B")

    ②引用多行（可不相连）
     Range("行号1:行号1,行号2:行号2..")
         Range("10:10,13:13")
      
    ③引用多列（可不相连）
      Range("列标1:列标1,列标2:列标2..")
         Range("A:A,F:F")

    ④引用相连的行或列
      Range("行号开始:行号结束")
      Range("列标开始:列标结束")

      另外一种写法：
      Rows("行号开始:行号结束")
      Columns("列标开始:列标结束")

  三、Offset属性  
     offset属性（偏移）
        Range("坐标").Offset(行号数字，列标数字)
```javascript
35.VBA对单元格的常见操作
    1.单元格的值获取和赋值   x.Value
        Range("A1").Value
        Range("A1").Value = 值  

    2.设置单元格的填充色  x.Interior.ColorIndex = 值
        Range("B3").Interior.ColorIndex = 3   '值有1-56，代表56种颜色

    3.单元格的复制  x.copy y
        Range("B3").copy Range("C3")   '将B3的值，复制到C3单元格

    4.单元格的剪切  x.cut y
        Range("A1").cut Range("B3")  '将A1的值，剪切到B3单元格

36.Select 与 Activate 方法

     Range("坐标").Select  选中单元格
     Range("坐标").Activate  在选中区域中选中活动的单元格

          注意：Activate  选中活动单元格
          Range("B26:F31").Select
          Range("B27").Activate

     引用选择的单元格/单元格区域
      Selection
       Selection.Value=100

     引用活动的单元格
      ActiveCell
         ActiveCell.Value=100

37.End （有空格单元格会选不上）

  	End 属性 模拟Ctrl+ 方向键   在数据区域中选取的效果 
        End(常量)   常量:xlDown、xlUp、xlToRight、 xlToLeft
                         
        选中一个单元格
        Range("坐标").End(xlToRight).Select

        选中一个区域 (End 可以连用)
        Range("坐标",Range("坐标").End(xlDown).End(xlToRight)).Select

38.UsedRange

      它是worksheet 的一个属性，代表指定工作表上的 所用区域 
      
       工作表.UsedRange.方法或者属性
       Sheet3.UsedRange.Select

39.CurrentRegion （有空格单元格也可以选上）

      它是单元格 的一个属性，代表指定单元格所在的区域    

      单元格.CurrentRegion.方法或者属性
      Range("a5").CurrentRegion.Select

40.字符串函数

    instr(从第几个开始,字符串1,字符串2)   返回 0 表示未找到
    	'从规定的位置开始查找，返回字符串2在字符串1中的位置，从左到右搜索。
    	 MsgBox ("Line 1 : " & InStr(1, “safdfasdf”, "s")) 
      
    InStrRev(string1,string2[,start,[compare]])
    	'从规定的位置开始查找，返回字符串2在字符串1中的位置，搜索从右到左
   	 msgbox("Line 1 : " & InStrRev("asdfasdf","s",Len("string1")))

          Compare - 一个可选参数。 指定要使用的字符串比较。它可以采用以下值。
          	 0 = vbBinaryCompare - 执行二进制比较(默认)
          	 1 = vbTextCompare - 执行文本比较

    Lcase(String)/UCase(String)
    	小写转大写/大写转小写
 
    Left(String, Length)
   	'从字符串的左侧返回指定数量的字符
    
    Right(String, Length)
        '从字符串的右侧返回指定数量的字符
    
    Mid(字符串，从第几个开始，长度)
        '返回给定输入字符串中指定数量的字符
    
    Ltrim(String) /RTrim(String)/Trim(String)
   	'删除左侧的空格/删除右侧的空格/删除两头的空格
    
    Len(String)
    '返回给定输入字符串的长度，包括空格
    
    space(number)
      '用特定数量的空格填充字符串
       msgbox("aaa" & Space(2)& "bbb")

    StrComp(string1,string2[,compare])
      '比较两个给定字符串后,返回一个整数值。

       如果 String1 < String2, 那么 StrComp 函数返回 -1
       如果 String1 = String2, 那么 StrComp 函数返回 0
       如果 String1 > String2, 那么 StrComp 函数返回 1

       msgbox("Line 1 :" & StrComp("Microsoft","Microsoft"))

    Replace ( string1, find, replacement, [start, [count, [compare]]] )
      '返回一个字符串，该字符串中指定的子字符串已被替换成另一子字符串，并且替换发生的开始位置和次数也是指定的。
       msgbox("Line 1 :" & Replace("alphabet", "a", "e", 1, 1))

    String(number,character)
    	'填充[number]个[character] 
        msgbox("Line 1 :" & String(3,"$"))

    StrReverse(string)
    	'反转指定的字符串

41.日期函数

    Date()
        '返回当前的系统日期
       
    CDate(date)
        '将有效的日期和时间表达式转换为系统类型日期
        MsgBox ("The Value of a : " & CDate("Jan 01 2020"))

    DateAdd(interval,number,date)
        '将有效的日期和时间表达式转换为类型日期
        msgbox("Line 1 : " &DateAdd("h",1,"01-Jan-2013 12:00:00"))

        interval - 一个必需的参数。 它可以采用以下值。
              d - 一年中的一天
              m - 一年中的月份
              y - 一年中的年份
              yyyy - 年份
              w - 工作日
              ww - 星期
              q - 季度
              h - 小时
              m - 分钟
              s - 秒钟
        
        Number - 必需的参数。它可以采用正值和负值的参数
 

    DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])
        '返回两个指定的时间间隔之间的差值。
        msgbox("Line 2 : " &DateDiff("q","01-Jan-09 00:00:00","01-Jan-10 23:59:00"))

    DatePart(interval,date[,firstdayofweek[,firstweekofyear]])
        '返回给定日期的特定部分
        msgbox("Line 4 : " & DatePart("m","2013-01-15"))

    Day(date)
        '返回1到31之间的数字，表示指定日期的一天
        msgbox(Day("2018-06-30"))

    DateSerial(year,month,day)
        '返回指定的日期，月份和年份参数的日期
         msgbox(DateSerial(2018,5,10))

    FormatDateTime(date,format)
        '格式化并返回有效的日期和时间表达式
         msgbox("Line 5 : " & FormatDateTime("2018-08-15 20:25",4))

        Format  - 一个可选参数。指定要使用的日期或时间格式的值。
        它可以采取以下值 - 
           0 = vbGeneralDate - 默认值         15/08/2018 8:25:00 PM 
           1 = vbLongDate - 返回长日期        Thursday, 15 August 2018
           2 = vbShortDate - 返回短日期       15/08/2018
           3 = vbLongTime - 返回长时间        8:25:00 PM
           4 = vbShortTime - 返回短时间       20:25


    IsDate(expression)
        '判断是否为日期，它都会返回一个布尔值
           msgbox("Line 1 : " & IsDate("Nov 03, 1950"))

    Month(date)
        '返回1到12之间的数字，表示指定日期的月份。
        msgbox("当前的月份的值是："&Month("2018-06-30"))

    Year(date)
        '返回一个表示指定日期的年份的整数
        msgbox(Year("2018-06-30"))

    MonthName(month[,toabbreviate])
        '回指定日期的月份名称。
        msgbox("Line 1 : " & MonthName(01,True))    1月
        msgbox("Line 2 : " & MonthName(01,false))   一月

    Weekday(date[,firstdayofweek])
        '返回一个从1到7的整数，表示指定日期的星期几。
        msgbox("Line 1: " & Weekday("2013-05-16",1))

    WeekdayName(weekday[,abbreviate[,firstdayofweek]])
        '返回指定日期的工作日名称
        msgbox("Line 2 : " &WeekdayName(2,True))
        msgbox("Line 3 : " &WeekdayName(1,False))
        msgbox("Line 4 : " &WeekdayName(2,True,0))
        msgbox("Line 5 : " &WeekdayName(1,False,1))

42.时间函数

      Now()
        返回当前的系统日期和时间。  yyyy/mm/dd hh:mm:ss

      Hour(time)
        返回0到23之间的数字，表示指定时间戳的一天中的小时。

      Minute(time)
        返回0到59之间的数字，表示指定时间戳的分钟数值。

      econd(time)
        返回0到59之间的数字，表示指定时间戳的秒钟数值。

      Time()
        返回当前的系统时间。 hh:mm:ss

      Timer()
        返回自凌晨00:00起的秒数和毫秒数。

      TimeSerial(hour,minute,second)
        返回指定小时，分钟和秒值的时间。
        msgbox(TimeSerial(7*2,60/3,15+3))   14:20:18

      TimeValue(StringTime)
        将给定的输入字符串转换为有效的时间。
        MsgBox (TimeValue("5:15"))  5:15:00
        

43. VBA消息框

     MsgBox(prompt[,buttons][,title][,helpfile,context])
    
       Prompt 显示在对话框中的消息的字符串
       buttons 可选的参数。数值表达式
       Title  对话框的标题栏文字

44.VBA 的 VBA输入框

     InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])


    Default - 一个可选的参数。显示在文本框中的默认文本
    XPos - 一个可选的参数。X轴的位置
    YPos - 一个可选的参数。Y轴的位置

45.正则

    引用外部:工具--引用--勾选 Microsoft VBScript Regular Expression 5.5

    创建正则对象
      ① Dim reg As New RegExp                       new 对象

      ② Set reg = CreateObject("VBScript.RegExp")   创建对象

46.错误处理

   ①On Error Resume Next 
   '当代码运行错误时忽略，继续向下运行


   ② GoTo  str

      执行代码
 
      str: 

47. dir(“目录”) 获取文件名

    dir("D:\DEST\hello\")  ：路径结束要加 \

48.超链接

   sheet1.HyperLinks.Add 超链接放置在Excel中的位置,超链接地址, _
   子地址,光标悬浮提示，将超链接显示为字符

49. 一句代码过长，折行时 加空格 再加 _

   Sheet4.Hyperlinks.Add Range("A1"), _
   "D:\Users\Desktop\记录.xls\", "sheet1!a1", "超链接", "点这里"
   

50.Dim 与 Set

   Dim obj                             ‘定义变量obj
   Set obj=createobject("........")     '将对象 赋值给obj变量
     或者 = new xxx

51.字典

    字典不是Excel 自带功能，需要引用后才能使用属性和方法

    ① 前期绑定（有代码提示,方便程序员调用方法属性）
       工具--引用--浏览--找到 scrrun.dll
       引用-- Microsoft Scripting Runtime

       如：Dim dict As New Dictionary
           dict.Add "jia", "学代码"


    ② 后期绑定，利用使用(通过CreateObject函数创建使用)
       绑定语句：Set o = CreateObject("scripting.dictionary")


     字典方法和属性
        方法: Add Items Keys Remove RemoveAll Exits
        属性：Item Key Count CompareMode

       object.Add(Key,item)
          添加一条相对应的关键字和条目到字典
          key唯一，item 可以为空 或者 nothing

       object.items
          一维数组的形式返回字典中所有的条目

       object.keys
          一维数组的形式返回字典中所有的key

       object.Exits(key)
          判断字典中是否包含key
 
       object.Remove(key)
          删除字典中关键字对应的条目

       object.RemoveAll
          删除字典中所有的key和item