Excel自定义函数开发手记

目录

• 本文使用的版本：Excel 2013
• 1、打开脚本编辑框
• 2、插入模块，编写代码
• 3、测试所写代码是否正确
• 4、给Excel单元插入自定义函数
• 5、给函数增加自定义说明
• 6、设置该自定义函数在Excel上通用
• 附录：
• 脚本语言：VBScript
  • 变量声明
  • 变量数组变量
  • 子程序
  • 函数程序
  • 条件语句
  • 循环语句
• 注意事项
• 参考

今天编辑部用Excel弄月季度奖励的时候，涉及到一些复杂的业务逻辑，比如不同数据，奖金不一样，等等，这时候就要用到自定义函数了，谁叫我是开发部门的，不是我们做，谁做，小研究一下，特此记录。

本文使用的版本：Excel 2013

其他版本的同学请自行百度使用方法，流程步骤类似。

1、打开脚本编辑框

2013版本的开发工具貌似默认不会显示出来，需要自行设置，还好我们有快捷键ALT+F11

2、插入模块，编写代码

用户窗体：写过c#窗体的同学你懂的
模块：类似全局变量与函数，不包含对象或属性设置而只包含可在代码窗口中显示和编辑的代码。
类模块：需要实例化对象才能调用，包含代码又包含数据，可视为没有物理表示的控件。
一般的自定义函数我们只用到了模块~

然后帖上代码即可

'1个阅读奖励0.01元，去掉个、十百位，如10000阅读量即奖励100元，12552阅读量奖励120元；2万阅读即奖励200元，以此类推，500元封顶；10万+文章再奖励200元。
Function GetMoney(readcount As Long) As Long
    GetMoney = readcount * 0.001
    GetMoney = GetMoney * 10
    If GetMoney > 500 Then
    GetMoney = 500
    End If
    If readcount > 100000 Then
    GetMoney = GetMoney + 200
    End If
End Function

3、测试所写代码是否正确

在函数下方写一个子过程即可，在需要调试的代码处加入断点，选中该子过程代码，按F5运行子过程调试即可

Sub test()
    GetMoney (1234567)
End Sub

4、给Excel单元插入自定义函数

返回Excel表，选中需要显示结果的单元，插入函数

结果完全符合预期~

5、给函数增加自定义说明

只需在函数下写一个子过程，按F5运行子过程即可，如果有多个函数自行循环调用Application.MacroOptions。

Sub RegUDF()
   Dim FuncName As String      '函数名称
   Dim FuncDesc As String       '函数描述
   Dim Category As String        '函数类别
   Dim ArgDesc(0) As String     '函数参数描述数组
 
   FuncName = "GetMoney"
   FuncDesc = "获取阅读量的奖金"
   Category = "编辑部函数"
   ArgDesc(0) = "阅读量，整型"

   Call Application.MacroOptions(Macro:=FuncName, Description:=FuncDesc, Category:=Category, ArgumentDescriptions:=ArgDesc)
End Sub

返回单元格，再次插入函数，看看是否有说明了？

6、设置该自定义函数在Excel上通用

另保存为宏文件
为了让我们宏文件有名称显示，记得修改一下【文件】-》【信息】-》【属性】

Excel97-03的格式为.xla
后面的版本格式为.xlam

设置加载项
【文件】-》【选项】-》【加载项】

确定之后，任何一个文档，都会自动加载该宏，获取我们的自定义函数
新建一个Excel，Alt+F11调出代码编辑器，你会发现一家自动加载了xla

整个过程就是这样~

附录：

脚本语言：VBScript

记录一下快速入门吧~大概了解语法即可。

变量声明

dim name
name=some value

变量数组变量

Dim names(2)
names(0)="Tove"
names(1)="Jani"
names(2)="Stale"

子程序

• 是一系列的语句，被封装在 Sub 和 End Sub 语句内。
• 可执行某些操作，但不会返回值。
• 可带有通过程序调用来向子程序传递参数。
• 如果没有，必须带有空的圆括号

Sub mysub()
some statements
End Sub

Sub mysub(argument1,argument2)
some statements
End Sub

函数程序

• 是一系列的语句，被封装在 Function 和 End Function 语句内
• 可执行某些操作，并会返回值
• 可带有通过程序调用来向其传递的参数。
• 如果没有参数，必须带有空的圆括号 ()
• 通过向函数程序名赋值的方式，可使其返回值

Function myfunction()
some statements
myfunction=some value
End Function
或者
Function myfunction(argument1,argument2)
some statements
myfunction=some value
End Function

条件语句

• If...Then...假如您希望在条件为 true 时执行一系列的代码，可以使用这个语句
• If...Then...Else 语句 - 假如您希望执行两套代码其中之一，可以使用这个语句
• If...Then...ElseIf 语句 - 假如您希望选择多套代码之一来执行，可以使用这个语句
• Select Case 语句 - 假如您希望选择多套代码之一来执行，可以使用这个语句

循环语句

• For...Next 语句 - 运行一段代码指定的次数
• For Each...Next 语句 - 针对集合中的每个项目或者数组中的每个元素来运行某段代码
• Do...Loop 语句 - 运行循环，当条件为 true 或者直到条件为 true 时
• While...Wend 语句 - 不要使用这个语句 - 请使用 Do...Loop 语句代替它

注意事项

1. VALUE属性获取是单元格的真实值，即实际结果。
   TEXT属性获取的是单元格的显示值，就是单元格里显示出来的值。
   比如你在单元格A1中输入1，而单元格A1的自定义格式为"第"0"项"，单元格A1显示出来的结果就是”第1项“，如果用一个变量(比如X)获取单元格A1的显示值，即X=RANGE("A1").TEXT，那么X的值就是”第1项"，如果是X=RANGE("A1").VALUE,那么X的值就是1；
   再比如你在单元格A2输入一个日期(如2012-1-1)，而单元格A2的自定义格式为"yyyy-mm-dd",如果列宽不够时会显示一串"#"号(比如显示的是5个#号)，用变量(比如Y)获取A2单元格的显示值时，即Y=RANGE("A2").TEXT，那么Y的值就是"#####"，如果列宽足够时(A2能显示完整数据)，那么Y的值就是"2012-1-1"。
   再比如你在单元格A3中输入一个错误的公式(如=1/0)，A3单元格显示的是"#DIV/0!"，用变量(比如Z)获取A3单元格的显示值时，即Z=RANGE("A3").TEXT，那么Z的值是"#DIV/0!"，而用Z获取A3单元格的真实值即Y=RANGE("A3").VALUE，就会出现一个错误。等等
2. 在Excel VBA中还可以用range对象来表示，例如cells（1,1）单元格可以用range("A1")来表示

参考

VBScript教程： http://www.runoob.com/vbscript/vbscript-tutorial.html
函数参考：https://msdn.microsoft.com/zh-cn/library/office/jj692818.aspx