VBA-TG第4节|深入理解函数

作者: 努力奋斗的durian | 来源:发表于2019-04-30 08:20 被阅读0次

VBA-TG第4节|深入理解函数
javascript函数，以及闭包的理解
栈帧相关-深入理解函数调用
VBA-TG第3节|深入理解单元格
3-Python 函数(2)
2018-08-14 fork
深入理解js的立即执行函数（引用）
深入理解javascript中的立即执行函数(function(
ES5和ES6中的this问题及注意事项（附带少许作用域链查找知
深入理解激活函数

最近更新:'2019-04-29'

1.函数的可选参数
2.使用括号和值传递
3.处理交替变化，怎样生成任意随机数

主要内容介绍
1.msgbox的可选参数
2.括号规则、值传递与引用传递
3.SET、模运算以及随机数

1.函数的可选参数

案例1:Range.Merge

将B2:D4合并为一个单元格以及将B2:D4合并为三个个单元格

Sub mergeDemo()

    '将B2:D4合并为一个单元格
    Range("B2:D4").Merge

    '将B2:D4合并为三个个单元格
    Range("B6:D8").Merge True
    
End Sub

代码显示的最终结果为:

案例2:按行或按列求积再加在一起的函数

这是之前的一个案例,自定义一个函数按行求积再加在一起.

现在对之前的那个案例增加新的要求,自定义一个函数按行或按列求积再加在一起,又应该怎么操作?

怎么样可以实现这个功能呢?可以在参数上增加一个是否的可选参数.

Function mySumProduct(r As Range, useColumn As Boolean)
    Dim i&, j&, k&, s&
    s = 0
    '如果 useColumn为True则按列求积，否则按行求积
    If useColumn Then
        '逐列扫描，将每行各单元格相乘，再将乘积加总到s上
        For j = 1 To r.Columns.Count
            k = 1
            For i = 1 To r.Rows.Count
                k = k * r.Cells(i, j)
            Next i
            s = s + k
        Next j
        
    Else
            '逐行扫描，将每列各单元格相乘，再将乘积加总到s上
        For i = 1 To r.Rows.Count
            k = 1
            For j = 1 To r.Columns.Count
                k = k * r.Cells(i, j)
            Next j
            s = s + k
        Next i
    End If
    mySumProduct = s
End Function

代码显示的最终结果如下:

每次这个函数都要写true或者false,很麻烦,那么有没有更加简洁的方式呢?

可以在参数前面加上optional,这样参数是可选项,允许忽略.

Function mySumProduct(r As Range, Optional useColumn As Boolean = False)
    Dim i&, j&, k&, s&
    s = 0
    '如果 useColumn为True则按列求积，否则按行求积
    If useColumn Then
        '逐列扫描，将每行各单元格相乘，再将乘积加总到s上
        For j = 1 To r.Columns.Count
            k = 1
            For i = 1 To r.Rows.Count
                k = k * r.Cells(i, j)
            Next i
            s = s + k
        Next j
        
    Else
            '逐行扫描，将每列各单元格相乘，再将乘积加总到s上
        For i = 1 To r.Rows.Count
            k = 1
            For j = 1 To r.Columns.Count
                k = k * r.Cells(i, j)
            Next j
            s = s + k
        Next i
    End If
    mySumProduct = s
End Function

案例3:IsMissing()

以下这个自定义函数很难用默认值的方式去实现.

因此需要利用到IsMissing()这个函数

Sub callDemo()
    Dim x, y
    x = myFunction(1)
    y = myFunction(1, 3)
    MsgBox "x是" & x & ",y是" & y
End Sub

Function myFunction(a, Optional b)

    If IsMissing(b) Then
        myFunction = a * 2
    Else
        myFunction = (a + b) * b
    End If
    
End Function

代码显示的最终结果为:

注意事项:

1.假设可选参数不是变体类型,用IsMissing()这个函数的最终结果是false.b是Integer,返回结果是false,则VBA默认为0,所以最终计算结果是为0

Sub callDemo()

    Dim x, y
    
    x = myFunction(1)
    
    y = myFunction(1, 3)
    
    MsgBox "x是" & x & ",y是" & y
    
End Sub

Function myFunction(a, Optional b As Integer)

    If IsMissing(b) Then
    
        myFunction = a * 2
        
    Else
    
        myFunction = (a + b) * b
        
    End If
    
End Function

代码显示的最终结果为:

2.假设可选参数不是变体类型,用IsMissing()这个函数的最终结果是false.b=2,返回false,则VBA默认为b=2,所以最终计算结果是为12

Sub callDemo()

    Dim x, y
    
    x = myFunction(1)
    
    y = myFunction(1, 3)
    
    MsgBox "x是" & x & ",y是" & y
    
End Sub

Function myFunction(a, Optional b = 2)

    If IsMissing(b) Then
    
        myFunction = a * 2
        
    Else
    
        myFunction = (a + b) * b
        
    End If
    
End Function

代码显示的最终结果为:

案例4:参数有三个的情况下

Sub callDemo1()

    Dim x
    
    x = myFun(3)       'x=3,即3+0-0
    
    x = myFun(3, 4)    'x=7,即3+4-0
    
    x = myFun(3, 4, 5) 'x=2,即3+4-5
    
    x = myFun(3, , 5)  'x=-2,即3+0-5
    
End Sub

Function myFun(a, Optional b = 0, Optional c = 0)
    
        myFun = a + b - c
    
End Function

案例5:使用:=更改参数传递顺序

Sub callDemo1()

    Dim x
    
    x = myFun(3)       'x=3,即3+0-0
    
    x = myFun(3, 4)    'x=7,即3+4-0
    
    x = myFun(3, 4, 5) 'x=2,即3+4-5
    
    x = myFun(a:=3, c:=5) 'x=-2,即3+0-5
    
End Sub

Function myFun(a, Optional b = 0, Optional c = 0)
    
        myFun = a + b - c
    
End Function

以上的代码还可以进行如下的更改:

  x = myFun(a:=3, c:=5) 'x=-2,即3+0-5

'还可以改成,效果是一样的

  x = myFun(c:=5, a:=3) 'x=-2,即3+0-5

案例6:msgbox可选参数

1.msgbox可选参数是64的时候

Sub msgboxDemo()


    MsgBox "注意,有情况! ", 64
    
End Sub

2.msgbox可选参数是48的时候


Sub msgboxDemo()


    MsgBox "注意,有情况! ", 48
    
End Sub

3.msgbox第3个参数

4.msgbox返回值代码

Sub msgboxDemo()

    Dim i
    
    i = MsgBox("请选择一个按钮", vbYesNo)

    MsgBox "刚才的msgbox返回值是 " & i
    
End Sub

代码的最终结果为:
当选择是yes的时候,则返回6

当选择是no的时候,则返回7

案例7:VBA颜色常量

案例8:删除选中的行(MsgBox可选参数)

Sub rmDemo()

    Dim i, r

    '将r赋值为用户选中区域的左上角行号，然后删除该行

    r = Selection.Row

    i = MsgBox("删除第" & r & "行数据,是否确定? ", vbYesNo)

    '可以用系统常量vbyes代替6，使代码更易读
    If i = 6 Then

        Rows(r).Delete

    End If

End Sub

代码显示的最终结果为:

image.png

2.使用括号和值传递

上次案例msgbox一个有使用(),另外一个没有使用(),使用()到底有什么样的区别呢?

2.1过程调用中的括号书写规则与原因

2.1.1没有参数:不写

()是用来装参数的箱子,既然没有参数,就可以不写().

2.1.2有参数,调用语句处于一行代码中间:写

2.1.3有参数,调用语句独占一行代码:不写

在vba中,()不仅仅是装箱子,还有特殊含义,比如求值运算.

()会有计算强迫症,因为要计算,而参数之间又是逗号无法运算,最后的计算结果就会出错.

调用语句只有一行,参数只有一个的时候,用(),也能运行.比如案例中的参数3,因为有(),就会强迫运算,就会3=3,再把=3的值传递给函数.一般不建议这样操作,不仅影响效率,而且很容易出错.

以下这个案例就是容易出错的例子.

如果调用函数没写(),调用函数的过程没有任何的问题.

Option Explicit

Sub rangeDemo()

    Dim r As Range
    
    Set r = Range("b2")
    
    setRedColor r
    
End Sub

'本过程接收一个Range对象
'并将其背景色染红

Sub setRedColor(rng As Range)

    rng.Interior.Color = vbRed
    
End Sub

代码显示的最终结果为:

如果将以上代码进行如下修改,在调用函数时增加()就会出错.

 setRedColor r
改成
 setRedColor (r)

出错的原因是,程序运行到setRedColor (r),计算机会强制性对r进行运算,而r是个单元格对象.而单元格的对象是默认的属性值value,因此会对单元格的默认属性5作为运算结果.因此会把5作为函数的参数.此外又因为子过程明确定义参数是range对象,因此类型不匹配,因此就会报错.

在调用函数参数的过程中,建议按以下方式执行

除了上述的方式,还可以使用call的方式调用函数.

2.2过程/函数的参数传递方式

2.2.1引用传递

具体可查看以下案例,在调用函数的过程中,msgbox中的x的值最终是多少?

运行结果是2

为什么会这样呢?因为是传递的过程将x以及1整个都交给了子过程,子过程给x以及1单独取了一个名字叫a.因此房子就有了两个名字,一个x,一个a,指向都是同一栋房子.在子过程操作a的时候,相当于把这个房子的内容乘以了2.回到主过程,用x找到这个房子,把内容取出来就是乘以2倍新的内容.这种方式就成为引用传递.这也是vba默认的传递方式.

2.2.2值传递

如果在参数定义的前面,加上一个关键字byval,这个参数就会变成按值传递.

VBA在传递的时候,不会把x交给新的变量.而是把内容数字1交给新的变量.子过程得到数字1后,会单独新建一个房子,名字叫做a,再把1装进去.接下来的过程对a的操作都是发生在新房子里面.而老房子没有任何的变化,

管好子过程，不要让他们随便修改你的参数。还有函數。

3.处理交替变化，怎样生成任意随机数

3.1 set的理解

如果想把一个对象赋值给一个变量,就必须用到set,而给数值变量则就不需要.

为什么会有这样奇怪的规定呢?就是因为默认属性的设定.

在如下截图案例中,假设没有set r,vba就会犯糊涂

因此就必须要用set这个关键字,是把range对象赋值给r.如果没有set,则把默认属性赋值给r.

3.2mod的用法

案例1:红黄蓝绿顺序交替染色

对以下截图这个工作表,严格按红黄蓝绿顺序交替染色.

Option Explicit
Sub colorize()
    Dim myColor(3) As Long, i As Integer, k As Integer
    myColor(0) = vbRed: myColor(1) = vbYellow
    myColor(2) = vbBlue: myColor(3) = vbGreen
    k = 0
    i = 1
    Do While Cells(i, 1) <> ""
        Cells(i, 1).Resize(1, 4).Interior.Color = myColor(k)
        k = k + 1
        If k = 4 Then k = 0
        i = i + 1
        Loop
End Sub

代码最终显示的结果如下:

k=0,1,2,3这种周期性变化的数字,完全可以考虑用模运算实现.

因此可以对之前案例代码进行修改

Option Explicit
Sub colorize()
    Dim myColor(3) As Long, i As Integer
    myColor(0) = vbRed: myColor(1) = vbYellow
    myColor(2) = vbBlue: myColor(3) = vbGreen
    i = 1
    Do While Cells(i, 1) <> ""
        Cells(i, 1).Resize(1, 4).Interior.Color = myColor(i Mod 4)
        i = i + 1
        Loop
End Sub

但是运行结果是第一行的颜色是黄色而非红色.

因此对代码需要再次进行修改,如下:

Cells(i, 1).Resize(1, 4).Interior.Color = myColor(i Mod 4)
更改为
Cells(i, 1).Resize(1, 4).Interior.Color = myColor((i-1) Mod 4)

最终的结果是正确的.

案例2:判断奇数偶数

Sub 判断奇偶数()
    Dim i As Integer
    i = 7
    If i Mod 2 = 1 Then
        MsgBox "奇数"
        
    Else
        MsgBox "偶数"
    End If
    
    
End Sub

3.2rand()随机函数

案例1:随机数

Sub randDemo()
    Dim a
    a = Rnd()
    MsgBox a
End Sub

每次结果都是不同的小数.

案例2:随机染色

Sub colorize()
    Dim myColor(3) As Long, i As Integer
    myColor(0) = vbRed: myColor(1) = vbYellow
    myColor(2) = vbBlue: myColor(3) = vbGreen
    i = 1
    Do While Cells(i, 1) <> ""
        Cells(i, 1).Resize(1, 4).Interior.Color = myColor(Int(Rnd(i) * 4))
        i = i + 1
        Loop
End Sub

代码显示的最终结果为: