精华内容
下载资源
问答
  • 经过上次文章介绍郑天玑:如何学习Excel VBA?​zhuanlan.zhihu.com本文将开启我们正式的Excel VBA之旅,本文的终点将放在VBA语言本身,我们将编写一个最基本的List链表数据结构类型,带大家学习一边VBA语言的基本...

    经过上次文章介绍

    郑天玑:如何学习Excel VBA?zhuanlan.zhihu.com

    本文将开启我们正式的Excel VBA之旅,本文的终点将放在VBA语言本身,我们将编写一个最基本的List链表数据结构类型,带大家学习一边VBA语言的基本特性。

    1 何谓链表List

    大致图示如下:

    25caa0ad30209c9b6ac8f27913b6337f.png
    • 1 List有点类似数组,可以将一个个数据串联在一起,进行顺序访问,每个数据被包裹在一个名为node的对象中,node也成了List的基本组成单元。
    • 2 node对象由两个基本元素组成,一个是data,用来存放数据,另一个是nextnode指针用来存放下一个node的地址,这样一来我们获取List中的第一个node之后就可以顺藤摸瓜式地按顺序访问挂载在整条List中的所有node中的数据。(链表最后一个node,它的nextnode=nothing)
    • 3 List链表本身包含一个head指针指向链表头,一个tail指针指向链表尾,一个isize变量记录链表的长度

    程序要实现的功能如下:

    Sub test()
        Dim mList As New MSList '定义并创建一个链表对象
        '调用链表对象的Append函数往里面添加数据,这个数据会由一个node对象包裹
        '我们这里会往list中添加整型、浮点型、字符串、日期四种不同类型的数据类型
        mList.Append (3) 
        mList.Append (9999.77)
        mList.Append ("租鸡青一")
        mList.Append (#1/1/2020#)
        mList.PrintList '将整个链表中的数据打印出来
    End Sub

    输出结果如下:

    8640a88b66d56c8c1fd952fd4df55e8a.png

    2 从零开始的List编写生活

    明确了我们的目标,就可以从0开始构建我们的程序了,万里执行始于足下,首先我们新建一张Excel表格,取个名字,然后双击打开,点击到:开发工具-Visual Basic,我们就会进入开发页面

    4c394555074326f1ceea30e02c4e269f.png

    (PS:如果你打开Excel后发现没有开发工具选项卡:请点击文件-选项-自定义功能区-在主选项卡页上,勾选开发工具选项卡

    38a5fe1a1652aa0fdd01bafbc302dc94.png

    然后在信任中心启用宏

    2a41256383300afad7baf97e315477fe.png

    然后我们在右侧的工程栏目中,右键我们的自己的工程,选择插入一个类模块和一个模块:

    我们会在类模块里面封装Node和List的基本功能,然后在模块中使用它们

    bd3fb9db633ac3d22461c2b22c5f90e6.png

    大致是这个样子:

    ac6b15fb790e2a83d275e72cf82d4450.png

    然后我们要把MSList和Node两个类模块的可见性改为:2-PublicNotCreatable,以便我们未来给其他程序调用

    64733445505ba1e2e347e95557d2e5f4.png

    ok,万事俱备,但我们不先急于编写Node和List,我们先打开modTtest,创建一个sub test()的过程函数,来编写我们的第一条代码(以后我们的程序会在这个过程函数中运行)

    ' 英文符号'开头代表此行为注释,在VBA代码编辑器中显示绿色
    ' 便于我们写说明,不会影响程序运行
    ' 我们可以在编程前将思路写在注释中
    Option Explicit '代码检测开关,如果检测到未定义变量则会报错
    Private Const hello_str As String = "Hello World!"
    Sub test()
        Dim s As String
        s = "VBA "
        MsgBox (s + hello_str)
    End Sub

    点击运行,我们会看到一个弹出窗口:

    0cbc8eb33ae6e1ecc67ab0fa06fc9fcd.png

    d2efc9a3ee63a96662cc9ea9365f3ce9.png

    首先我们来明确一下这段小程序结构:

    5adfea4054d3dcbb0fedac1fef69801c.png

    可见上述程序的工作过程为:定义变量->变量赋值->然后使用变量

    这也是大多数程序设计的一个基本逻辑,我们将此牢记于心就能理解大多数程序的运转思路

    然后我们再来看本程序中有两种变量:

    一个叫全局变量,位置在sub test()过程函数上方,不属于任何函数,遗世独立的存在:

    Private Const hello_str As String = "Hello World!"

    另一个叫局部变量,位于sub test()过程函数内部:Dim s As String

    这就牵扯出一个概念叫做变量的作用域:

    • 位于在函数外部的变量我们称之为全局变量,作用域默认为整个当前模块文件,可以被这个模块内所有函数调用,如果我们再加一个sub test2()函数那么hello_str也同样可以在其中使用。
      • 如果我们将hello_strPrivate关键字改为Public那么这个全局变量还可以被其他模块调用。
    • 位于函数内部定义的变量我们称之为局部变量,它的作用域只有在函数体内部,一旦函数运行结束那么它生命周期也随之完结,s变量只能在sub test()中使用,无法在sub test2()中使用。

    有了以上概念之后,我们再来一条条解读代码:

    ' 代码检测开关,如果检测到使用的变量没有事先定义过就会报错
    ' 可写可不写,我们推荐在所有代码文件之上全部写上这句话,可以帮助我们减少编程错误。
    Option Explicit
    ' 定义了一个名为hello_str的全局变量,类型为String,
    ' 并且初始化为“hello world”, 中间的const关键字代表这个变量不能再被修改。
    Private Const hello_str As String = "Hello World!"
    ' Sub函数体,VBA中有两大类函数:
    ' sub过程函函数:sub + 函数名 (形参), sub过程函数没有返回值,代表执行一段任务
    ' function函数: function + 函数名(形参) As 返回值类型,函数需要返回任务的结果
    Sub test()
        ' Dim 变量名 As 变量类型, 这是我们最常用的变量定义模式
        ' 在变量使用前必须先定义变量,相当于先注册用户才能进使用网站功能
        ' 我们这里定义了一个名字为s的String类型变量(其他变量类型请看如下的类型清单)
        Dim s As String
        ' 变量名 = 赋值的内容, 这是变量赋值的过程,我们这里让变量s存储"VBA"这个字符串
        s = "VBA "
        ' MsgBox(显示的内容)是一个弹窗函数,会弹出一个对话框显示我们在()里放的内容
        ' s + hello_str 我们将两个字符串拼接在一起显示,这里的"+"是字符串拼接的方式之一
        ' 只适用于两个字符串之间的拼接,如果字符串需要同其他类型的变量拼接请用"&"
        MsgBox (s + hello_str)
    End Sub

    VBA主要的变量类型如下:

    e4ee4ab1dfd0962cd95e0aa9d24b271b.png

    我们这里需要终点记忆的只有六个:

    • Integer: 整数型变量,取值范围-32,768—32,767,当你一旦发现自己需要的数字超过这个范围时,应该采用Long类型(-2,147,483,648—2,147,483,647
    • Single:实数类型,单精度浮点型,取值范围:负值:-3.402823E38 到 -1.401298E-45; 正值:1.401298E-45 到 3.402823E38)。如果需要使用超出这个范围的数字请使用Double类型
    • String:字符串类型,可以表达一串文字、字符集,赋值时候需要用双引号""括起来
    • Boolen: 布尔类型,只有两个值要么true要么false,我们用它来判断循环和分支
    • Variant:是一个特殊数据类型,是动态数据类型,相当于一种宽泛的约束,你有时候可能不想限制死某个变量的数据类型或者不知道某个传递过来的数据到底时什么类型,那么这时候可以用variant。
    • Object: 对象类型,这里存放的是对象的地址(指针)。我们在实际编程过程中会对多个功能函数和数据变量进行打包,以便对外展现特定的API接口,方便使用者调用,用户可以将我们包裹起来的功能和数据结合视作一种自定义的数据类型,而这种类型的统称为Object类型。而我们接下去要讲的List和Node都是Object类型。

    好了,我们对VBA的最基本背景知识有了个大致了解,我们终于可以开始我们的List链表编程之旅了,从我们开头画的示意图可以看出List和Node的大致结构如下:

    2f20e4e16e8a449726f97684ececcd5a.png

    我们先来构建Node,双击Node类模块,输入以下代码:

    同样来我们来一条条解读:

    ' 开启未定义变量检测
    Option Explicit
    ' 定义两个全局变量,一个是data,它的类型我们设定为Variant,我们这里可以称之为类的内部成员变量
    ' 使Node本身存放接收多种类型的变量
    Private data As Variant
    另一个使next_node, 它的类型为Object用来指向下一个Node对象的地址可以理解为指针
    ' 两个变量都是Private,意味着我们只能在Node类模块中使用他们,不能跨文件使用
    Private next_node As Object
    ' 接下来我们要编写一个class_initialize()函数,也就是我们的类初始化函数
    ' 什么使类初始化?当我们编写好Node对象的时候,我们可能在别处会这样使用它:
    ' Dim newnode As Node    <---- 先定义了一个Node对象
    ' Set newnode = New Node  <---- 创建一个Node实例,这时候会调用Node类模块中的class_initialize()
    ' 以上也可以合并成一条: Dim newnode As new Node
    Public Sub class_initialize()
        ' class_initialize的主要任务是对Node内部的两个变量赋值
        ' 在对象初始化时,我们讲data默认直为0
        data = 0
        ' 将next_node指针置为空,Nothing相当于很多编程语言里面的null或nil是一个空对象
        ' 注意Object对象有个特殊之处就是赋值的时候,在前面一定要加Set关键字
        Set next_node = Nothing
    End Sub
    ' 接下来我们要为data和nextnode设置对外属性
    ' 我们是通过对外属性接口来间接地对Node内部成员变量进行赋值和取值
    ' 我们在外部会这么使用:
    ' ---------------------------------------------------------------
    ' Dim newnode As new Node
    ' 赋值操作,此时调用了Property Let NodeData(ByVal value As Variant) ,属性Let函数
    ' 将newnode中的data赋值为3
    ' newnode.NodeData = 3     
    ' Dim d as Variant
    ' 取值操作,此时调用了类模块中的 Public Property Get NodeData() As Variant 属性Get函数
    ' newnode中data的数据取出来给d
    ' d = newnode.NodeData     
    '-----------------------------------------------------------------
    ' 从以上代码中我们可以看到,我们在外部并不直接使用data和nextnode,
    ' 而是通过一层以Public作为关键字的{Let,Get}属性函数包装,令其在外部可以被赋值、取值调用
    ' Get函数是取值,所以这个函数有指定返回类型:As Variant
    Public Property Get NodeData() As Variant
       ' 在函数体的最后一行:函数名 = xxx,相当于其他语言的return xxx,xxx就是要返回的数据
        NodeData = data
    End Property
    ' Let是赋值函数,所以函数有个名为value类型为Variant的形参,但没有返回类型
    ' ByVal关键字是按值传递的意思,指明了传递到函数内部的value是一个数据拷贝而不是数据引用
    Public Property Let NodeData(ByVal value As Variant)
        ' 对data进行赋值
        data = value
    End Property
    ' 以下是对nextnode的Get和Set函数,原理基本同上,唯一区别在于
    ' 1 Object类型的赋值需要在前面加上Set关键字
    ' 2 Object的形参传递尽量用ByRef,传递一个引用,而不是拷贝
    Public Property Get NextNode() As Object
        Set NextNode = next_node
    End Property
    Public Property Let NextNode(ByRef value As Object)
        Set next_node = value
    End Property

    Node说完了,让我们再来看看List,双击List类模块,赋值如下代码,仍然是逐行解读

    ' 开启未定义变量检测
    Option Explicit
    ' 定义List的三个内部成员变量
    Private isize As Integer ' 链表长度
    Private head As Object ' 头指针
    Private tail As Object ' 尾指针
    ' class_inializ函数原理同Node
    Public Sub class_inialize()
        isize = 0
        Set head = Nothing
        Set tail = Nothing
    End Sub
    ' isize head tail三个成员变量的对外属性接口
    ' List类模块有点特殊,它只需要向外部暴露Get取值属性,不需要向外部暴露其赋值属性
    ' 我们会有单独的函数它们,依照特定规律其进行赋值
    ' 所以三个成员变量只有Public Property Get 函数
    Public Property Get ListSize() As Variant
        ListSize = isize
    End Property
    Public Property Get ListHead() As Node
        Set ListHead = head
    End Property
    Public Property Get ListTail() As Node
        Set ListTail = tail
    End Property
    ' Append函数是我们自定义的一个核心功能函数
    ' 当外部使用者调用类似list.append(123)时候
    ' 链表会自动在整条链表的尾部添加一个新Node,而其data成员变量为123,其nextnode指针为nothing
    ' List中的tral尾指针会更新,指向我们新添加的Node(保存它的地址)
    Public Sub Append(ByVal value As Variant)
       ' 我们新创建一个Node对象,名为tmp
        Dim tmp As New Node
        ' 调用tmp.NodeData属性,将形参传过来的数值赋值给Node中的data成员变量
        tmp.NodeData = value
        ' 接下来是个If条件判断, vba中分支判断的基本格式:
        ' ---------------------------------------------------------------
           'If 条件1 Then
           ' 运行符合条件1的代码
           'ElseIf 条件2 Then
           ' 运行符合条件2的代码
           'Else
           ' 运行其他情况的代码
           'End If
           ' 其中ElseIf 可以有多行,也可以没有
        ' ---------------------------------------------------------------
        ' 我们先判断头部指针是不是为空,如果为空则说明当前List中没有Node元素,链表长度为0
        If head Is Nothing Then
           ' 我们将头部指针和尾部指针指向我们新创建的Node对象tmp
            Set head = tmp
            Set tail = tmp
           ' 链表长度+1
            isize = isize + 1
        Else
            ' 如果头部指针不为空,那么说明当前链表已经有了其他node
            ' 我们需要将新node插入List尾部,并更新tail指针令其重新指向新的尾部
            ' tail指向的node的NextNode赋值为tmp,这样我们相当于在尾部又添加了一个node对象
            tail.NextNode = tmp
            ' 将tail指向tmp,也即是指向新的尾部
            Set tail = tmp
            ' 链表长度+1
            isize = isize + 1
        End If
    End Sub
    ' 打印函数PrintList是一个Sub过程,将从head开始到tail结束,打印我们当前List中保存的数据
    Public Sub PrintList()
        ' 如果isize为0,也就是当前链表中没有元素,那么直接退出
        ' 注意VBA中的=除了赋值还有判断功能
        If isize = 0 Then Exit Sub 
        ' 我们声明一个Node对象并将head赋值给它
        Dim tmp As Node
        Set tmp = head
        ' 我们创建一个Do-Loop循环体
        Do
            ' 一旦遇到tmp为nothing的时候会跳出循环
            If tmp Is Nothing Then Exit Do
            ' 打印当前tmp中的NodeData,Debug.Print是打印到立即窗口
            Debug.Print tmp.NodeData
            ' 将tmp.NextNode指针赋值给当前的tmp,这样重新进入循环体时tmp就平移到了它指向的下一个node
            Set tmp = tmp.NextNode
        Loop
    End Sub

    我们回到modTest模块,编写如下代码,测试一下:

    ' 开启未定义变量检测
    Option Explicit
    ' 这里用Public关键字,方便我们未来外部调用
    Sub test()
        ' 创建一个List对象
        Dim mList As New MSList
        ' 用Append函数向其分别添加整型、浮点型、String类型、日期型四种数据
        mList.Append (3) 
        mList.Append (9999.77)
        mList.Append ("租鸡青一")
        mList.Append (#1/1/2020#)
       ' 调用PrintList打印
        mList.PrintList
    End Sub


    当然为了方便起见,我们还可以把代码写成这样,用一个With - End With块包裹起来,对象的函数调用,这样不必重复输入mlist变量名

    90943848088975752cd0fcd88ee0d283.png

    我们想要看到结果:打开视图-立即窗口

    31d91df2603e7da850deba11e7dd3578.png

    会显示我们的运行结果:

    0711f6328abe4ea4cf36fe17bdcc0120.png

    好了,通过一个List链表的学习我们学习了VBA的主要内容有:

    变量作用域、基本数据类型、变量的声明定义和赋值、类模块的使用方式、函数和过程、分支和循环。我们未来会用到的日常功能大部分都已经涵盖了,具体细节我们会在今后的文章中遇到实际情况进一步讲述,有些概念会再次重述帮助大家加深记忆。

    当然如果你想了解更多细节,微软 @微软大中华区 官方文档是最权威的参考资料,如果你想在Excel VBA上精进,它应该是你最好的朋友。

    Visual Basic 语言参考docs.microsoft.com

    最后我们再来思考一个问题,此时我们要保存表格的话,该保存成什么格式?

    一般而言我们要保存为*.xlsm格式,这是一个带宏命令的电子表格,也就是编写好程序的表格,但这样一来我们的程序和表格就存在紧耦合的概念,事实上我们这段程序并没有操作表格上的任何元素,完全不需要表格可以独立作为一个程序存在,有点类似插件的概念。这时候该怎么办?

    我们可以将其保存为*.xla格式,令其成为可以加载项,默认会保存的地址如下:

    35a147ac7dc34928aa2ff060ce7f9e6b.png

    然后我们新打开一张表格,希望能够引用我们编写好的程序:

    我们仍然点击开发工具选项卡-Excel加载项

    0cd5fd5a555b7cb201bc0d2d9c76f02d.png

    可以看到我们刚才编写的程序已经处于可加载状态了

    aabde0aa089331de5c095f29145bc223.png

    勾选,确定,我们来到代码编辑器页面,可以看到xla程序已经加载进来了

    36b88eb61622735b2bc58c2630315c50.png

    如果我们要在自己的当前的表格上使用它可以先到工具-引用

    ac3e0db372c385597d09e578197d0604.png

    这里将其选进来:

    b4482ec3749362bf8f2f31d1918ffc24.png

    我们的VBA工程下会多了个引用,

    但我们需要修改一下modTest中的代码,编写一个创建MSList对象的函数:

    396aa324e8a11f744917cfe2fc2698a7.png

    然后我们回到当前的表格,创建一个模块,编写一个测试函数,点击运行,在立即窗口就可以查看结果了。这里要注意一点,类模块是虽然我们选了Public可见性,但它是无法在xla程序外部实例化的,我们只能将其当作数据类型进行声明,它的实例化需要我们单独调用modeTest中的createList函数才可以完成。

    419dc1aba9701da29796e5f9fdc9c308.png

    还有一种加载项使用方式,我们会在未来介绍。

    第一期内容就先到这里,建议将代码都自己敲一遍,学以巩固。

    下一期我们会用表格搭建一个非常试用的账务系统,内容可能会分多期,一步步带大家走进VBA该有的应用场景。

    模板文件下载地址:

    链接:https://pan.baidu.com/s/1oXUmArjZVF-uULeeM1rATQ

    提取码:1oc5

    展开全文
  • Function SigTop5(ByVal SessionCode) As Dictionary Set SigTop5 = New Dictionary '.......... End Function Sub a() Dim SessionCode Dim Sig_Top5 As Dictionary Set Sig_Top5 = New ...
  • 学习Excel技术,关注微信公众号:excelperfect在VBA中,InStr函数是一个非常有用的函数,可用于查找某字符串在另一个字符串中第一次出现的位置。InStr函数的语法如下图1所示:图1其中:1.参数Start,可选,指定搜索...

    学习Excel技术,关注微信公众号:

    excelperfect

    在VBA中,InStr函数是一个非常有用的函数,可用于查找某字符串在另一个字符串中第一次出现的位置。

    InStr函数的语法如下图1所示:

    5510d66aa78ff6f5142acab71a92831a.png

    图1

    其中:

    1.参数Start,可选,指定搜索的起始位置。如果省略该参数,则会从String1的第一个字符开始查找。

    2.参数String1,必需,被搜索的字符串。

    3.参数String2,必需,要搜索的字符串。

    4.参数Compare,可选,指定比较模式。默认为vbBinaryCompare模式,即二进制比较,还可以指定为vbTextCompare模式(文本比较,不区分大小写)和vbDatabaseCompare模式(数据库比较,只适用于Access)。如果指定该参数,就要同时指定参数Start。如果省略该参数,比较模式由Option Compare语句值决定。

    简单地说,InStr函数就是查找String2在String1中第一次出现的位置。

    InStr函数的返回值有以下几种情形:

    1.如果String1长度为0,则返回值0。

    2.如果String1为Null,则返回值Null。

    3.如果String2长度为0,则返回参数Start的值。

    4.如果String2为Null,则返回值Null。

    5.如果找不到String2,则返回值0。

    6.如果在String1中找到了String2,则返回String2被找到的位置。

    7.如果参数Start指定的数值大于String2的长度,,则返回值0。

    示例1:获取字符出现的位置

    下面的代码返回一个字符在另一个字符中出现的位置:

    Sub InstrSample1()

        Dim str1 As String

        Dim str2 As String

        Dim iPos As Long

        str1 = "我的微信公众号是完美Excel"

        str2 = "完美Excel"

        iPos = InStr(1, str1,str2)

        Debug.Print str2 &" 出现在 " & str1 & " 的第"& iPos & "个字符."

    End Sub

    运行结果如下图2所示。

    a931f692bc0b3fd3eb744fce33284e03.png

    图2

    示例2:统计字符串中包含某子字符串的数量

    下面的代码统计字符串str1中发现字符串str2的个数:

    Sub InstrSample2()

        Dim str1 As String

        Dim str2 As String

        Dim str As String

        Dim iPos As Long

        Dim iCount As Long

        str1 ="ABCDABEF"

        str2 = "AB"

        str = str1

        iPos = InStr(1, str1,str2)

        Do While (iPos <>0)

            iCount = iCount + 1

            str1 = Mid(str1, iPos+ Len(str2))

            iPos = InStr(1, str1,str2)

        Loop

        Debug.Print"""" & str & """" & "中共有" &iCount & "个" & "字符串""" & str2 & """"

    End Sub

    运行结果如下图3所示。

    f5e50441409da9a7998df9aa3f33a7dd.png

    图3

    示例3:获取字符出现的多个位置

    如果一个字符串在另一个字符串中多次出现,要获取该字符串出现的这些位置值,示例代码如下:

    Sub InstrSample3()

        Dim str1 As String

        Dim str2 As String

        Dim str As String

        Dim iPos As Long

        Dim iPos1 As Long

        Dim iPosAll() As Long

        Dim iCount As Long

        str1 ="ABCDABEFAB"

        str2 = "AB"

        str = str1

        iPos = InStr(1, str1,str2)

        iPos1 = iPos

        Do While (iPos <>0)

            iCount = iCount + 1

            ReDim Preserve iPosAll(1 To iCount)

            iPosAll(iCount) =iPos1

            str1 = Mid(str1, iPos+ Len(str2))

            iPos = InStr(1, str1,str2)

            iPos1 = iPos1 +Len(str2) + iPos - 1

        Loop

        Debug.Print"""" & str2 & """" & "出现在" &"""" & str & """" & "中的位置:"

        For iCount =LBound(iPosAll) To UBound(iPosAll)

            Debug.Print iPosAll(iCount)

        Next iCount

    End Sub

    运行结果如下图4所示。

    83541d3752524db339082dfa1a0eb80b.png

    图4

    可以将上面的代码转换成一个自定义函数,由用户传递相应的参数,该函数返回由字符位置组成的数组:

    Function InstrSample4(str1 As String, str2 As String) As Long()

        Dim iPos As Long

        Dim iPos1 As Long

        Dim iPosAll() As Long

        Dim iCount As Long

        iPos = InStr(1, str1,str2)

        iPos1 = iPos

        Do While (iPos <>0)

            iCount = iCount + 1

            ReDim Preserve iPosAll(1 To iCount)

            iPosAll(iCount) =iPos1

            str1 = Mid(str1, iPos+ Len(str2))

            iPos = InStr(1, str1,str2)

            iPos1 = iPos1 +Len(str2) + iPos - 1

        Loop

        InstrSample4 = iPosAll()

    End Function

    a7c6d2bbcb828f4b368b15a4e6da6d97.png

    展开全文
  • 上一篇回顾:VBA语法基础,语法是语言的基础基本语句1、控制程序流程语句(1) GoTo语句该语句将执行的程序转到指定的标签所在的语句指令,但不能转移到过程之外的指令。例如,在进行错误捕捉时,发生错误后,程序转移...

    上一篇回顾:VBA语法基础,语法是语言的基础

    f0d57fbf648c4d69b614e0202bcc72c8.png

    基本语句

    1、控制程序流程语句

    (1) GoTo语句

    该语句将执行的程序转到指定的标签所在的语句指令,但不能转移到过程之外的指令。例如,在进行错误捕捉时,发生错误后,程序转移至标签所在处执行。

    (2) If…Then语句

    这种类型的语句用于条件判断中,当满足条件时,执行相应的语句;当条件不满足时,执行其它的操作。

    基本语法为:

    If Then

    If … Then语句有几种形式分别用于不同的情况:

    ①当只有一个条件时,可使用下面的结构:

    If Then [Else ]

    其中,Else子句可选。如果该语句不在同一行中,则应在后面加上End If语句,即:

    If Then

    [指令]

    End If

    或:

    If Then

    [指令]

    Else

    [指令]

    End If

    当条件为真时,执行Then后面的语句并结束If…Then语句的执行,否则执行Else后面的语句或结束If…Then语句的执行。

    ②当有两个或多个条件时,可使用嵌套的If … Then 结构:

    If Then

    [指令]

    ElseIf Then

    [指令]

    [Else]

    [指令]

    End If

    上面只是两层嵌套,可以根据情况使用多层嵌套。当条件为真时,执行Then后面的语句并结束If…Then语句的执行,否则判断条件1,当条件1为真时,执行Then后面的语句并结束If…Then语句的执行,否则执行Else后面的指令。

    (3) Select Case语句

    当需要作出三种或三种以上的条件判断时,最后使用Select Case语句。其基本语法为:

    Select Case

    [Case 条件表达式1]

    [指令]

    [Case 条件表达式2]

    [指令]

    ……

    [Case Else]

    [指令]

    End Select

    当某个条件表达式与测试表达式相匹配时,则执行其后的指令,否则执行Else(如果有的话)后的指令,然后结束Select Case块的执行。

    此外,Select Case语句还可以嵌套。

    (有关程序控制语句的进一步介绍和示例请见后面的一系列文章)

    2、循环语句

    循环即重复执行某段代码。在VBA中,有多种可以构成循环的语句结构。

    (1) For … Next 循环

    其语法如下:

    For To [step 步长]

    [指令]

    [Exit For]

    [指令]

    Next [计数器]

    从开始到结束,反复执行For和Next之间的指令块,除非遇到Exit For语句,将提前跳出循环。其中,步长和Exit For语句以及Next后的计数器均为可选项。

    For…Next循环中可以再包含For…Next循环,即For…Next循环可以嵌套使用。

    (2) Do While循环

    只有在满足指定的条件时才执行Do While循环。有两种形式:

    ■ 第一种形式

    Do [While 条件]

    [指令]

    [Exit Do]

    [指令]

    Loop

    当条件满足时执行指令。

    ■ 第二种形式

    Do

    [指令]

    [Exit Do]

    [指令]

    Loop [While 条件]

    先执行指令,然后再判断条件,如果条件满足则再次执行指令。

    其中Exit Do语句表示提前退出指令块。

    (3) Do Until循环

    与Do While循环一样,也有两种形式;

    ■ 第一种形式

    Do [Until 条件]

    [指令]

    [Exit Do]

    [指令]

    Loop

    ■ 第二种形式

    Do

    [指令]

    [Exit Do]

    [指令]

    Loop [Until 条件]

    执行指令,直到条件满足时退出循环。

    (4) While … Wend循环

    其语法为:

    While

    [指令]

    Wend

    当条件满足时,则执行指令。

    e0ae663951311ef9e605ddcc34b9d2f2.png

    过程

    过程由一组完成所要求操作任务的VBA语句组成。子过程不返回值,因此,不能作为参数的组成部分。

    其语法为:

    [Private|Public] [Static] Sub ([参数])

    [指令]

    [Exit Sub]

    [指令]

    End Sub

    说明:

    (1) Private为可选。如果使用Private声明过程,则该过程只能被同一个模块中的其它过程访问。

    (2) Public为可选。如果使用Public声明过程,则表明该过程可以被工作簿中的所有其它过程访问。但是如果用在包含Option Private Module语句的模块中,则该过程只能用于所在工程中的其它过程。

    (3) Static为可选。如果使用Static声明过程,则该过程中的所有变量为静态变量,其值将保存。

    (4) Sub为必需。表示过程开始。

    (5) 为必需。可以使用任意有效的过程名称,其命名规则通常与变量的命名规则相同。

    (6) 参数为可选。代表一系列变量并用逗号分隔,这些变量接受传递到过程中的参数值。如果没有参数,则为空括号。

    (7) Exit Sub为可选。表示在过程结束之前,提前退出过程。

    (8) End Sub为必需。表示过程结束。

    如果在类模块中编写子过程并把它声明为Public,它将成为该类的方法。

    b08adc817d1c11a31a0fa8c816aca75b.png

    函数

    函数(function)是能完成特定任务的相关语句和表达式的集合。当函数执行完毕时,它会向调用它的语句返回一个值。如果不显示指定函数的返回值类型,就返回缺省的数据类型值。

    声明函数的语法为:

    [Private|Public] [Static] Function ([参数]) [As 类型]

    [指令]

    [函数名=表达式]

    [Exit Function]

    [指令]

    [函数名=表达式]

    End Function

    说明:

    (1) Private为可选。如果使用Private声明函数,则该函数只能被同一个模块中的其它过程访问。

    (2) Public为可选。如果使用Public声明函数,则表明该函数可以被所有Excel VBA工程中的所有其它过程访问。不声明函数过程的作用域时,默认的作用域为Public。

    (3) Static为可选。如果使用Static声明函数,则在调用时,该函数过程中的所有变量均保持不变。

    (4) Function为必需。表示函数过程开始。

    (5) 为必需。可以使用任意有效的函数名称,其命名规则与变量的命名规则相同。

    (6) 参数为可选。代表一系列变量并用逗号分隔,这些变量是传递给函数过程的参数值。参数必须用括号括起来。

    (7) 类型为可选。指定函数过程返回的数据类型。

    (8) Exit Function为可选。表示在函数过程结束之前,提前退出过程。

    (9) End Function为必需。表示函数过程结束。

    通常,在函数过程执行结束前给函数名赋值。

    函数可以作为参数的组成部分。但是,函数只返回一个值,它不能执行与对象有关的动作。

    如果在类模块中编写自定义函数并将该函数的作用域声明为Public,这个函数将成为该类的方法。

    事件处理过程

    要对一个控件事件编写事件处理程序,应先打开窗体的代码窗口并从可用对象的下拉列表中选择所需的控件。然后,从该控件的可用事件下拉列表中选择所用的事件。此时,对事件处理程序的定义语句就会自动出现在代码窗口中,就可以直接编写事件处理程序了。

    在Excel中,有下面几类事件,即Excel应用程序事件、工作簿事件、工作表事件、图表事件、用户窗体事件等。

    类模块

    类模块是存放共享变量以及共享代码的存储库。创建一个类模块,实际上也是在创建一个COM(组件对象模型)接口。因此,类模块允许通过一个由属性、方法和事件组成的可编程接口向外界描述应用程序,同时保证保留对应用程序的控制权。也就是说,类模块能够让程序实现“封装”,这样,在其它工程中可以直接使用某类模块而不需要访问源代码。此外,可以使用类来创建自已的库,如果要使用的话,只需要在任何新的工程中添加一个对该类的引用就行了。并且,如果要改变程序,只需对类模块改动就行了,而不需要在程序的每个部分都作改动。

    属性过程

    属性过程(property procedure)是特殊的过程,用于赋予和获取自定义属性的值。属性过程只能在对象模块如窗体或类模块中使用。

    有三种属性过程:

    Property Let

    给属性赋值

    Property Get

    获取属性的值

    Property Set

    将对象引用赋给属性引用

    调用子过程和函数过程

    子过程可以用下面三种方法调用。第一种使用Call语句:

    Call DoSomething(参数1,参数2,……)

    如果使用Call语句,就必须用小括号将参数列表括起来。

    第二种是直接利用过程名:

    DoSomething 参数1,参数2,……

    此时,不用在参数列表两边加上括号。

    如果不想使用函数的返回值,可以用上述任一种方法调用函数。否则,可以用函数名作为表达式的组成部分,如

    If GetFunctionResult(parameter)=1 Then

    如果用函数调用作为表达式的一部分,参数列表必须放在小括号中。

    第三种是使用Run方法。

    在过程间传递参数

    在很多情况下,需要在子过程或函数中调用另一个自定义函数或子过程,这时,在被调用过程中就要用到在调用过程中使用的某个变量。因此,可把该变量作为参数传递给被调用过程。不管被调用过程是在同一模块、同一工程中的过程,还是在远程服务器上的类中的一个方法,从一个过程向另一个过程传递变量的原理都是一样的。

    被调用过程(而不是调用过程)决定了变量如何从调用过程传递到被调用过程。

    1、VBA允许用两种不同的方式在过程和组件之间传递参数。在子过程或函数的定义部分,可以指定参数列表中的变量的传递方式:ByRef(按引用)或者ByVal(按值)。

    (1) ByRef

    这是VBA中在过程间传递变量的默认方法。ByRef是指按引用传递变量,即传递给被调用过程的是原变量的引用。因此,如果改变了被调用过程中的变量值,其变化就会反映到调用过程中的那个变量,因为它们实际上是同一个变量。

    (2) ByVal

    如果使用ByVal关键字传递变量,被调用过程获得的就是该变量的独立副本。因此,改变被庙用过程中该变量的值不会影响调用过程中该变量原来的值。

    2、Optional参数

    Optional关键字用来指定某个特定的参数并不一定要传递,即为可选参数。但是,该参数必须放在最后。

    3、ParamArray

    使用ParamArray关键字能够使过程按受一组数目可变的参数。ParamArray参数必须是参数列表中的最后一个参数,而且不能在使用了Optional关键字的参数列表中使用ParamArray参数。

    变量(常量)作用域和生存期

    有时需要在工程内的所有过程中使用某个变量,而有时某些变量又只需要在某些特定的过程中用到,变量的这种可见性称为变量作用域。

    变量存在和作用的时间,称为变量的生存期。

    变量或常数在程序中声明的位置决定了变量的作用域和生存期。

    总的说来,在模块的声明部分用Private关键字声明的变量可以被模块中的所有过程使用;在模块的声明部分用Public关键字声明的变量可以被整个工程使用;若某个对象引用指向某类模块,则在该类模块的声明部分用Public关键字声明的变量可以被整个工程使用;在子过程或函数中用Dim语句声明的变量只能被声明这些变量的过程使用。

    (1) 过程级作用域

    在一个过程(即子过程或函数)内声明的变量只能在该过程内使用,其生存期在执行了End Sub或End Function语句后结束。因此,可以在不同的过程中定义具有相同名称的不同变量。声明过程级作用域的变量,在过程中用Dim语句声明变量。

    此外,还有一种具有过程级作用域的特殊变量,称为静态变量。静态变量是在过程中定义的,尽管这种变量也具有过程级作用域,但是它具有模块级的生存期。这意味着只能在定义静态变量的过程内使用这些变量,但是变量的值在两次过程调用之间是保持不变的。用Static关键字声明静态变量:

    Static lngExcuted As Long

    还可以声明一个过程为静态过程,在这种情况下,在过程中声明的所有变量都被认为是静态变量,而且它们的值在两次过程调用之间都会保持不变,如

    Static Procedure MyProcedure()

    Dim iCtr As Integer

    (2) 模块级或私有作用域

    具有模块级作用域的变量可以被某个模块内的所有子过程和函数使用,也可以在模块级生存期内保存在内存中。

    在模块的声明部分(即任何子过程或函数外),用Dim语句或Private语句声明变量来创建一个具有模块级作用域的变量。

    (3) Friend作用域

    Friend关键字只能用于在对象模块(如类模块或窗体模块)中的变量和过程的声明。用Friend声明的变量允许工程中的其他对象模块访问原模块中的变量或方法,但是不需要用Public语句声明这些变量或方法。

    (4) 公共作用域

    在过程外使用Public语句声明的变量可以被当前工程中的所有模块使用。

    展开全文
  • 学习Excel技术,关注微信公众号:excelperfectFunction过程能够让我们自定义可以返回值的函数,减少复杂性,扩展功能,提高效率。在使用VBA编写自定义函数时,了解一些注意事项,掌握一些技术技巧,能够帮助我们顺利...

    学习Excel技术,关注微信公众号:

    excelperfect

    Function过程能够让我们自定义可以返回值的函数,减少复杂性,扩展功能,提高效率。在使用VBA编写自定义函数时,了解一些注意事项,掌握一些技术技巧,能够帮助我们顺利创造自已的函数。

    让自定义函数返回指定类型的数组

    如下图1所示,在消息框中显示了工作表单元格地址及对应的值。

    357c9199cc8ccffef4f5c506819f66ec.png

    1

    这里,使用自定义函数PopulateArray来返回包含单元格地址的数组。

    Function PopulateArray(str AsString) As String()

       Dim strTempArray(1 To 9) As String

       Dim i As Integer

       For i = 1 To 9

            strTempArray(i) = str & CStr(i)

       Next i

       PopulateArray = strTempArray

    End Function

    PopulateArray函数接受所传递的字符串值,并将其与数字合并成一个字符串,然后将合并的字符串值赋值给数组元素,最后将得到的数组赋值给函数名,作为函数过程的返回值。

    下面的test过程调用PopulateArray函数,传递列字母A作为参数,将函数的返回值赋值给动态数组strArray,然后取数组中的值并与相应单元格值组合。

    Sub test()

       Dim i As Integer

       Dim strUnion As String

       Dim strArray() As String

       strArray = PopulateArray("A")

       For i = 1 To UBound(strArray)

            strUnion = strUnion & strArray(i) _

                  & vbTab &Range("A" & i).Value & vbNewLine

       Next i

       MsgBox strUnion

    End Sub

    从上面的例子可以看出:

    • 自定义函数的返回值可以是指定数据类型的数组,本例中为String型数组。

    • 要使自定义函数的返回值是指定数据类型的数组,必须在自定义函数声明时,在数据类型后添加括号。

    • 在调用过程中,接受自定义函数所返回数组的数组应声明为动态数组,其大小未显式定义。

    • 在调用过程中,调用自定义函数所返回的数组必须赋值给相同类型的数组。

    可以从自定义函数返回一个包含数组的Variant型变量。例如下面的代码:

    Sub testVarArray()

       Dim i As Integer

       Dim myArray As Variant

       myArray = varArray()

       For i = LBound(myArray, 1) To UBound(myArray, 1)

            Debug.Print myArray(i, 1)

       Next i

    End Sub

    Function varArray() As Variant

       varArray = Range("A1:A9")

    End Function

    运行testVarArray过程后的结果如下图2所示。

    6395fe000bce1a360589a17dba29814d.png

    2

    注意,将单元格区域赋值组Variant型变量时,该变量包含的是一个二维数组。

    参数值的传递方式

    不要将向自定义函数传递参数值的ByRefByVal方法弄混淆。ByRef是按引用传递,对函数过程中参数变量值的改变将会影响到调用过程中传递给该参数的变量值;ByVal是按值传递,对函数过程中参数变量值的改变不会影响到调用过程中传递给该参数的变量值。如果没有指定参数的传递方式,则默认为ByRef

    使用ByRef方式传递参数,可以让自定义函数不仅通过函数名返回值,也通过参数返回值,如下面的代码:

    Sub testReturnValue()

       Dim i As Integer

       i = 1

       If ReturnValue(i) = True Then

            Debug.Print i

       End If

    End Sub

    Function ReturnValue(ByRef myValueAs Integer) As Boolean

       myValue = myValue + 1

       ReturnValue = True

    End Function

    运行代码后的结果如下图3所示。ReturnValue函数返回值True,同时改变了参数myValue的值,从而影响调用过程中参数i的值相应改变。

    51ac33a9f71b57bfeeef640d93970fcb.png

    3

    使用可选参数增加灵活性

    在编写自定义函数时,可以使用Optional来指定可选参数,并且还可以为可选参数指定默认值,这是非常方便灵活的。

    此外,在使用可选参数时,还可以使用IsMissing函数方便地检测可选参数的值。如果IsMissing函数返回True,则可以知道在调用函数时没有提供可选参数。如下面的代码所示:

    Sub testOptionArgument()

       Dim i As Integer

       Dim iOption As Integer

       i = 1

       iOption = 100

       Debug.Print OptionArgument(i)

    End Sub

    Function OptionArgument(ByRef iValAs Integer, _

            Optional iTwo As Integer)

       If IsMissing(iTwo) Then

            OptionArgument = iVal

       Else

            OptionArgument = iVal + iTwo

       End If

    End Function

    运行代码后的结果如下图4所示。

    4ff0a853754906afdb5b6267d041b3d5.png

    4

    在自定义函数中接受多个参数

    在自定义函数中,使用ParamArray指定的参数可以接受多个值。但是使用ParamArray声明的参数:

    • 必须为Variant类型的数组。

    • 必须在声明的最后部分。

    在调用使用ParamArray指定参数的自定义函数时,调用过程无需显式将数组传递给该参数,传递给该参数的各元素是使用逗号分隔的值或变量。如下面的代码:

    Sub testParamArray()

       Debug.Print ParamArraySample(1, 2, 3, 4, 5)

    End Sub

    Function ParamArraySample(ByRef iAs Integer, _

       ParamArray someArgs() As Variant) As Integer

       Dim iResult As Integer

       Dim varArg As Variant

       For Each varArg In someArgs

            iResult = iResult + varArg

       Next varArg

       ParamArraySample = i + iResult

    End Function

    运行代码后的结果如下图5所示。

    887664e610bfbf07bb4058f5b01796c5.png

    5

    72d63cf6bac056759dfbb73429daa708.png

    展开全文
  • Excel VBA 函数返回值

    千次阅读 2020-02-11 13:29:52
    Excel VBA 函数返回值 Sub 定义一个过程 VB的函数定义格式与C有很大区别: 格式: Sub 过程名(参数列表 ... ) 过程体 End Sub Function 定义一个函数 官方文档 ???? Microsoft Docs | 编写 Function 过程 Function...
  • VBA是利用Office实现自己小型办公自动化的有效手段,我根据自己20多年的VBA实际利用经验,现在推出了五部VBA教程。第一:VBA代码解决方案,是VBA中各个知识点的讲解,覆盖绝大多数的VBA知识点;第二:VBA数据库解决...
  • 用几行简单的VBA语言就能解决这个问题首先打开vba编辑窗口,点击开发工具——visual basic(如果没有这个菜单,点击文件——选项——自定义功能区——右边窗口勾上开发工具)弹出编辑窗口,点击左边vbaproject下的模块...
  • 大家好,今天继续和大家分享VBA编程中常用的简单“积木”,第61-65的过程。简单的过程组合起来就是一个复杂的过程,越简单的事物往往越容易理解,我们要认真掌握这些简单的过程,可以为我们复杂的工程服务。这也是我...
  • 所以更高阶一点的东西,比如VBA,讨论结果是不会有什么热度,因为大部分人觉得说一辈子也用不着吧,就不会点收藏了。这个论点是有道理的,因为我之前连载过罗技LUA脚本系列,看一下数据就知道了。不过这次正好有征文...
  • VBA过程分Sub过程和Function过程。前者是通常意义上的过程,后者经常称之为函数过程。首先我们分析一下两者的特点:1 Sub过程:总是以“sub 过程名()”开头,以“End Sub”结尾,一个过程就是执行某项动作的一套指令...
  • 先将下面的代码复制到模块中: Function 宝哥(I As Integer)Application.Volatile True If I > Sheets.Count Then 宝哥 = "" Else 宝哥 = Sheets(I).Name End IfEnd Function 完成后的效果如下图所示: 这样完成...
  • 学习Excel技术,关注微信公众号:...在Excel中,使用VBA编写用户自定义函数很容易。例如,想要写一个自定义函数,计算单元格区域的平均值,但要排除非数字或小于误差值的单元格。我们可以这样编写代码:Function...
  • private void button1_Click(object sender, EventArgs e) { try { object oMissing = System.Reflection.Missing.Value; Excel.Application oExcel = new Excel....无论VBA中返回什么值都是 -2146826273
  • 学习Excel技术,关注微信公众号:excelperfect在《Excel VBA解读(27):看看VBA的Sub过程和Function过程》中,我们讲解了Function过程的基本形式。在《Excel VBA解读(121):Sub过程详解——枯燥的语法》中,我们详细...
  • VBA function函数

    2012-04-18 02:21:22
    SUB可以使用可选参数,optional 参数 在程序中可以使用isMissing来判断 Sub sub_name(optional val1) function function_name(optional ...function 是可以返回值的过程 function function_name parameter f...
  • excel vba中调用SQLConfigDataSource函数时返回值总是FALSE,是哪里出错了?请高手指教! 代码如下: Private Const ODBC_ADD_SYS_DSN = 4 Private Declare PtrSafe Function SQLConfigDataSource Lib "odbccp32....
  • VBA

    2017-05-24 08:47:00
    在工具栏中将"开发工具"菜单调出来,点击VBA script或者快捷键ALT+F11, 打开编程窗口 下拉列表:选中要处理的单元格>...1.function可以返回值,sub则不可以返回值 2.sub可以直接执行,但function需要调用才可以执...
  • VBA 相关

    2018-01-06 19:04:14
    BLOG VBA 教程 - w3cSchool - - VBA教程 - 易百教程 - 轻量级一些 - - VBA中Option的四种用法 - ? ...Type 内部可以定义sub, function ...如何设置一个Function返回值 - 1 function 和 sub 的区别是什么? -
  • 最近闲来无事,学了点VBA。...function返回值,但是sub没有返回值。 声明变量 sub 笔记() Dim 变量名 as 变量类型 '除了dim,还可以用 private ,public ,static END sub 可以用一个语句同时声明多个变量 Dim
  • Microsoft Access VBA

    2020-03-07 23:05:56
    文章目录模块子过程/函数过程类模块标准模块控件操作窗体文本框VBA与宏语法常量数据类型错误 模块 子过程/函数过程 子过程: 关键字 sub 模块解决大问题,大问题分解为小问题由子过程解决 无返回值 Sub 过程名(参数)...
  • http://lwl0606.cmszs.com/archives/excel-vba-string-function.html 先建好表,然后写带输出参数的存储过程 CREATEORREPLACEPROCEDURE"MES"."MES_GETKEY"(KNameINVARCHAR2,KValueOUTVARCHAR2)...
  • Sub 和 FunctionVBA提供的两种封装体。利用宏录制得到的就是Sub。Sub 定义时无需定义返回值类型,而 Function 一般需要用 “As 数据类型” 定义函数返回值类型。Sub 中没有对过程名赋值的语句,而 Function 中有...
  • VBA教学视频

    千次阅读 2018-11-26 14:53:00
    全民一起VBA视频笔记 基础篇: 视频地址 https://study.163.com/course/courseLearn.htm?courseId=1003088001#/learn/video?lessonId=1003503026&amp;amp;amp;amp;amp;amp;courseId=1003088001 子过程 无...
  • VBA快速读懂入门

    2020-12-23 22:52:44
    这篇文章主要教有编程基础但是基本不懂VBA的人能够快速读懂VBA。 Option Explicit 强制声明变量,出现没有定义过的变量就会报错。 Sub 函数名…End Sub 可直接执行函数,函数内如果写了【函数名=值】代表这是一个有...
  • ExcelVBA之函数

    2020-06-04 16:30:35
    它们以关键字Function开头和关键字EndFunction结束。在本章中,你将创建你的第一个函数过程。函数过程可以从子程序里执行,也可以从工作表里访问,就像Excel的内置函数一样。 技巧: 关于函数名称:函数名称应该...
  • 2 调用其他函数(VBA若只调用函数,并不会直接返回函数返回值) func1 call function() 3 调用其他函数并使用函数返回值 Debug.Print func1(1, 3) a = func1(100, 99) 4 如何关闭其他sub,用传递参数的方法 ...
  • VBA findNext nothing 问题

    2020-04-19 17:59:03
    Function test(cel As Integer) As String Dim functionRetStr As String '方法返回值 Dim currentValue As String '调用方法单元格第一列值 Dim totalRow As Long '总行 Dim rngTemp As Range '搜索 Dim rngA ...

空空如也

空空如也

1 2 3
收藏数 51
精华内容 20
关键字:

functionvba返回值