VBA学习笔记31:自定义函数2

4 年前 · 来自专栏 VBA学习笔记（完结）

夏西蓝

不用关注我哈，不会再更VBA的内容了

学习资源：《Excel VBA从入门到进阶》第31集 by兰色幻想

这节课讲自定义函数的一些特殊设定。

一、返回数组的自定义函数

在这里首先要说一下excel里的数组公式。

数组就是单元的集合或是一组处理的值集合。一个以数组为参数的公式，即数组公式，就能通过这个单一的公式，执行多个输入的操作并产生多个结果——每个结果显示在一个单元中。

例子：求下表合计金额。

如果直接写公式的话，是=B2*C2+B3*C3+B4*C4+B5*C5+B6*C6，现在数据少还能写，如果数据量多就很麻烦，这时就该使用数组公式，{=SUM(B2:B6*C2:C6)}。

在写下=SUM(B2:B6*C2:C6)后，按[Shift] +[Ctrl] + [Enter]键，公式就会变成数组公式。

那我们该如果在VBE中写下数组公式？

课例：写一个返回特定范围的随机数组公式。

产生随机数，在EXCEL中可以使用Rand 或者 Randbetween。

rand()函数是Excel中产生随机数的一个随机函数。返回的随机数是大于等于 0 及小于 1 的均匀分布随机实数，rand()函数每次计算工作表时都将返回一个新的随机实数。
Randbetween()函数返回位于两个指定数之间的一个随机整数。每次计算工作表时都将返回一个新的随机整数。

以下是老师写的代码：

Function suiji(maxnum, geshu) 
'maxnum是区间最大的数,geshu是返回多少个不重复的数
Dim d As New Dictionary
Dim num
Application.Volatile = True
    num = Int(Rnd() * maxnum + 1)
    d(num) = ""
Loop Until d.Count = geshu
suiji = Application.Transpose(d.Keys)
End Function

代码讲解：

Application.Volatile = True

既然写的是产生随机数的函数，如果工作簿发生了变化，自然应该发生变化。

然而在使用自定义函数时,当涉及修改工作表标签名称、插入工作表等不会引起工作表重算的操作时，自定义函数也不会进行重算，而自定义函数恰要得到重算后的结果。为了解决这个问题，可以使用application.violatile 方法把自定义函数标识为易失性函数，如果工作簿发生任何变化，都会强制对自定义函数进行重算。

但是，使用Application.Volatile也会严重影响到计算性能和某些工作表事件的处理，所以如非必要请不要使用这种方法。

2. Rnd()函数

Rnd 函数返回小于 1 但大于或等于 0 的值。

Int(Rnd() * maxnum + 1) 生成1到最大值之间的随机整数。

3. 这里还是使用了数组和字典，利用了字典key的唯一性储存生成的随机数。

使用该数组公式，因为这是要返回一个数组的，所以在我们在写下公式后按下[Shift] +[Ctrl] + [Enter]键，让它变成数组公式。

比如，我们写下=suiji(88,10) 最大值设定为88，随机生成10个数。

这个自定义函数感觉是还原了randbetween的功能，但在设定的个数里不会产生重复数。

二、参数不定的自定义函数

之前所讲的自定义函数的参数都是固定的。像上列的suiji()函数，要固定输入最大值和个数，两个参数。但在Excel函数中，比如sum()函数，可以有1到无数个参数。

SUM(number1,[number2],...)

课例：模仿sum函数写一个求和的自定义函数。

Function cheng(ParamArray n())
Dim num, k
k = 0
For Each num In n
   k = k + num
Next num
cheng = k
End Function

使用cheng()函数：

三、参数值默认和参数缺省

有些函数是有默认和参数缺省设置的，比如left()函数。

LEFT( string, n ) string是必要函数，而n是可选参数，不写的话默认n=1.

课例：写随机取奇偶数数组的自定义函数。

Function suiji1(maxnum, geshu, Optional qo As Integer)
'随机取奇偶数数组
Dim d As New Dictionary
Dim num
Application.Volatile
    num = Int(Rnd() * maxnum + 1)
    If qo = 0 Then
        d(num) = ""
    ElseIf qo = 2 Then
        If num Mod 2 = 0 Then d(num) = ""
        'num除以2的余数等于零，则为偶数，放入数组
    ElseIf qo = 1 Then
        If Not num Mod 2 = 0 Then d(num) = ""
    End If