概念了解

一、什么是函数

一个函数就是预定的一个计算公式,可以快速地完成一个特定的计算。如:sum,index etc。

二、自定义函数

自定义函数是用户自己编写的函数;一个自定义函数就是一个function过程;编写了一个function过程就相当于编写了一个自定义函数。

三、为什么要创建自定义函数

1.简化工作,解决需要较多辅助列或使用较长公式才能完成的计算问题,比如后面的提取工作簿名称的应用。

2.解决不能用现有的工作簿函数完成的计算问题,如统计指定背景颜色的单元格个数。

 

小试牛刀自定义函数

目的:创建一个返回当前工作簿名称的自定义函数

操作过程:

1.插入一个标准模块

2.在模块中插入一个空的function过程

3.在function和end function 之间写入代码

Pulic Function WbName()

WbName = ThisWorkbook.name

End Function

4.自定义函数也可以在Sub过程中使用如:

Sub test()

MsgBox”当前工作薄名称为:” &wbName ()

End Sub

进一步说明:如何为自定义函数添加说明和快捷键

>如果需要,可以通过宏对话框为自定义的函数添加快捷键设置和函数说明

 

注意:一定要把程序运行的结果返回给函数名,要不会出错。

 

创建Function过程的步骤

1.新插入一个标准模块

方法一:执行“插入”->“模块”菜单命令

方法二:在工程资源管理器中,单击右键,执行:“插入”->“模块”右键菜单命令

2.插入一空的Function过程

方法一:执行“插入”->“函数”菜单命令,通过“添加过程”对话框完成。

方法二:在代码窗口手动输入完成。

3.在Function与End Function之间写入相应的代码。

4.将计算结果返回给过程名。

5.如果需要,可以通过宏对话框为自定义函数添加函数说明。

 

统计指定背景颜色的单元格个数

1. 可以使用宏表函数 get.cell(63, a1)来提取单元格的背景颜色索引值; 也可以通过录制宏来实现颜色索引值到提取。

2. 使用以上的操作步骤来插入自定义函数countcolor,代码如下:

Function countcolor(Rng As Rang, cel As Range) ‘ 第一个参数是区域,第二个参数是单元格的颜色

Dim Cindex as Integer 

Cindex = Cel.Interior.ColorIndex‘将单元格的背景颜色索引值返回给Cindex

Dim n As Range

For Each n In Rng

If n.interior.ColorIndex = Cindex then

Countcolor = countcolor+1

End If

Next

End Function

3. 自定义函数不会自动刷新,但可以利用易失性函数的特点(当工作表自动计算时,函数也跟着计算)把自定义函数定义为易失性函数

Applicaion volatile 将自定义函数调设置为易失性函数

Function myrnd()

Application.Volatile'将自定义函数设置为易失性函数

myrnd = Rnd()

End Function

 

关于自定义函数的更多信息

1.声明函数的语法:

[Public | Private][Static] Function 函数名 ([函数]) [As 数据类型(函数返回值的数据类型)]

[语句块]

[函数名=过程结果]

[Exit Function]

[语名块]

[函数名=过程结果]‘最后必须把计算结果返回给过程名

End Function

2.可以在工作表中使用自定义函数,也可以在过程中使用自定义函数

3.自定义函数有自己的作用域,可以给自定义函数设置参数

4.可以通过Application的volatile的方法设置自定义为易失性函数

 

怎样存储自定义函数

1.将自定义函数工作簿保存成Excel加载宏

2.加载这个加载宏,这个这个自定义函数就可以在这个机器的excel上使用。如果需要在其他机器上使用该自定义函数,可以选择copy加载宏,然后加载。

课后补充

自定义函数使用规则

 

1、编写自定义函数时,必须在VBA模块中,而不是在与ThisWorkbook、工作表和用户窗体关联的代码模块中。

2、在自定义函数前加上Private关键字进行声明,该函数将不会出现在Excel的“粘贴函数”对话框中,但仍然可以在公式中运用它们。如果是专门为其他的VBA过程开发的自定义函数,则应该使用Private关键字进行声明。

3、 通常,用户自定义函数后,在“粘贴函数”对话框中将会出现在“用户定义”类别中。如果希望自定义函数出现在其它的类别中,必须编写和执行VBA代码为自定 义函数指定类别。如运行Application.MacroOptions Macro:=”SumPro”,Category:=4语句后,将自定义的SumPro函数指定给“统计函数”类别。

4、可以用MacroOptions方法:运行Application.MacroOptions Macro:="宏名",Description:="参数说明"

或者从菜单-工具-宏-宏,输入自定义函数名,选择“选项”,在其中输入备注,退出即可。

5、与Sub过程不同,自 定义函数Function过程并不出现在宏对话框中;当执行VBE编辑器中的“运行——运行子过程/用户窗体”命令时,如果光标位于某Function过 程中,就不能获取宏对话框并从中选择要运行的宏。因此,在开发过程的时候,必须采取其它方式对自定义函数进行测试,可以设置调用该函数的过程;如果该函数是用在工作表公式中的,可以在工作表中输入简单的公式进行测试。

6、如果在公式中使用了自定义函数,但返回值“VALUE!”,表明函数中有错误。错误的原因可能是代码中的逻辑错误、可能给函数传递了不正确的参数、可能执行了禁用的动作如试图更改单元格的格式。

7、在自定义函数中使用参数时,可选参数必须在任何必需的参数之后。在参数名称前加上关键字Optional即可指定一个可选的参数。

如果必须确定某可选的参数是否传递给了某函数,应将该参数声明为Variant类型,然后在过程代码中使用IsMissing函数来进行判断。

要创建包含不定数量参数的自定义函数,使用一个数组作为最后一个(或惟一的)参数,并在数组前面加上关键字ParamArray。关键字ParamArray只能用于参数列表中的最后一个参数,其数据类型总是Variant并且是一个可选的参数。

8、在自定义函数的程序代码中,应确保至少有一次把合适的值赋给了函数的名称。

9、函数的名称与变量名称一样必须遵循一定的规则。最好不要采取与单元格地址相同的形式,并且还要避免使用与Excel内置函数名称相同的名称,否则,Excel会使用内置函数。10、通常情况下,VBA中对文本进行比较等操作是区分大小写的,若将语句Option Compare Text添加到模块的顶部,则程序执行时不会区分大小写。

 

Excel 中自定义函数的相关限制说明

 

由工作表单元格中的公式调用的用户定义函数不能更改 Microsoft Excel 的环境。这意味着此类函数不能执行以下任何操作: 

?在电子表格中插入、删除单元格或设置单元格格式。 

?更改其他单元格中的值。 

?在工作簿中移动、重命名、删除或添加工作表。 

?更改任何环境选项,例如计算模式或屏幕视图。 

?向工作簿中添加名称。 

?设置属性或执行大多数方法。

 

用户定义函数旨在允许用户创建不包括在 Microsoft Excel 随附函数中的自定义函数。Microsoft Excel 中包括的函数也不能更改环境。函数可以执行计算以在输入它们的单元格中返回某个值或文本。任何环境更改都应通过使用 Visual Basic 子例程来完成。

 

计算过程中,Excel 会检查包含用户定义函数的单元格的引用单元格。如果未计算所有引用单元格,Excel 会向函数传递空单元格。然后,Excel 将确保针对要计算的所有引用单元格进行了足够的计算传递。在进行最后的计算传递过程中,会向用户定义函数传递单元格的当前值。这会导致比预期更频繁地调用用户定义函数。因此,用户定义函数可能返回意外值。

 

要进行正确的计算,计算中使用的所有区域都应该作为自变量传递给函数。如果不以自变量形式传递计算区域,而是引用函数的 VBA 代码中的区域,Excel 将无法在计算引擎中考虑这些区域。因此,Excel 可能无法对工作簿进行适当地计算,来确保在计算用户定义函数之前计算所有引用单元格。