如何对电子表格组件使用自定义函数

最新推荐文章于 2022-06-18 20:41:27 发布
最新推荐文章于 2022-06-18 20:41:27 发布
阅读量4.5k 收藏
点赞数
文章标签： microsoft spreadsheet function excel object vbscript
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xqdd/article/details/1642380
版权

根据msdn修改( http://support.microsoft.com/kb/248822/zh-cn )

概要

<script type="text/javascript">loadTOCNode(1, 'summary');</script>
可以在 COM 对象（或 ActiveX DLL）中创建您自己的自定义函数，并将这些函数公开给 Microsoft Office 电子表格组件，以便在单元格公式中使用。本文介绍如何创建、部署以及与电子表格组件一起使用 COM 对象
本文中讨论的示例 COM 对象公开了两个公用函数： GetTicks() 和 CustomTrend()。
•GetTicks

此函数返回一个值，该值代表自系统启动之后所经过的毫秒数。它返回的值是从对 GetTickCounts API 函数的调用中获得的。
•CustomTrend

电子表格组件不支持在单元格中使用数组公式。因此，它不支持返回值数组的任何 Excel 工作表函数。返回值数组并且在电子表格组件中不受支持的常用 Excel 工作表函数有 LINEST、LOGEST、TREND 和 TRANSPOSE。

CustomTrend 函数演示如何使 Excel 自动调用这些不受电子表格组件支持的函数中的其中一个：TREND 函数。利用自动化功能，Excel TREND 函数会向 COM 对象返回一个值数组；然后，CustomTrend 会根据函数的其中一个参数返回该数组中的一个元素。由于 COM 对象函数无法向电子表格组件返回一个值数组，因此您可以根据需要多次调用 CustomTrend 函数，以便从产生的数组中检索所有元素。

注意：由于示例 COM 对象会实现 Microsoft Excel 自动化，因此使用此示例 COM 对象的客户端上必须安装 Microsoft Excel。
创建 COM 对象
<script type="text/javascript">loadTOCNode(3, 'moreinformation');</script>
1.启动 Microsoft Visual Basic，并创建一个新的 ActiveX DLL 项目。
2.将该项目命名为 OWCAddin。
3.将类命名为 MyFunctions。
4.在“项目”菜单上，单击“组件”，然后选择“Microsoft Office Web Components 11.0”。
5.将以下代码添加到 MyFunctions 类的代码模块中： 
Private Declare Function GetTickCount Lib "kernel32" () As Long

Dim oExcel As Object

Public Function GetTicks() As Long
    GetTicks = GetTickCount()
End Function

Private Function GetValues(xRange As IXRangeEnum) As Variant()
    Dim nCols As Long
    Dim nRows As Long
    Dim objRange As Object
    
    ' QI for IDispatch interface
    Set objRange = xRange
    
    ' Get unsigned longs and assign it to signed longs
    ' This is not always a good idea. You can use it here
    ' because the number of rows or columns cannot be greater
    ' than maximum value of a signed long
    nCols = objRange.ColCount
    nRows = objRange.RowCount
    
    ' Get values in an array of variants
    ReDim vVals((nRows * nCols) - 1) As Variant
    objRange.Next nRows * nCols, vVals(0), vbNull
    
    ' Return the array
    GetValues = vVals
    
End Function

Public Function CustomTrend(ByVal KnownY As IXRangeEnum, ByVal KnownX As IXRangeEnum, _
    ByVal NewX As IXRangeEnum, ByVal Idx As Variant) As Variant

    Dim XVals() As Variant, YVals() As Variant
    Dim NewXVals() As Variant, NewYVals() As Variant

    On Error GoTo ErrHandler
    
    'Get the values of Range in an array
    YVals = GetValues(KnownY)
    XVals = GetValues(KnownX)
    NewXVals = GetValues(NewX)
    
    'Now automate Excel to get an array of new Y Values using the TREND function
    NewYVals = oExcel.WorksheetFunction.Trend(YVals, XVals, NewXVals, True)
    
    'Return the requested index (Idx)
    CustomTrend = NewYVals(Idx)
    
    Exit Function
    
ErrHandler:
    CustomTrend = "#VALUE!"
    
End Function

Private Sub Class_Initialize()
    Set oExcel = CreateObject("Excel.Application")
End Sub

Private Sub Class_Terminate()
    oExcel.Quit
    Set oExcel = Nothing
End Sub
6.在文件菜单上，选择生成 OWCAddin.dll 以生成 DLL。
7.将项目保存为 OWCAddin.vbp。
将 COM 对象打包
<script type="text/javascript">loadTOCNode(3, 'moreinformation');</script>
1.启动打包和部署向导。开始菜单中Microsoft Visual Studio 6.0 Tools－>Package & Deployment Wizard
2.选择 OWCAddIn.vbp 并单击打包。
3.对于程序包类型，请选择 Internet 程序包，并单击下一步。
4.选择一个程序包文件夹并单击下一步。
5.收到说明 MSOWC.dll 缺少依赖项信息的消息后，单击确定。

注意：在 Microsoft Office 2003 中，当您收到说明 OWC11.dll 缺少依赖项信息的消息时，请单击确定。
6.在包含文件的列表中，选择 OWC11.dll，然后单击下一步。
7.单击下一步以接受文件来源。
8.对于安全设置，请为可安全执行脚本和可安全执行初始化选择是，然后单击下一步。
9.单击完成以生成 CAB 文件。
使用电子表格组件（该组件在 COM 对象中使用自定义函数）创建 HTML 页面
<script type="text/javascript">loadTOCNode(3, 'moreinformation');</script>
1.使用记事本创建一个包含以下代码的新文本文件： 
<HTML>
<HEAD>

<! --- COMMENT BEGIN ------------------------------------->
<! --- Modify the codebase and clsid items below --------->

<OBJECT classid = clsid:FFB16550-E40D-11D3-BB97-00C04FAEB609 
codebase="http://MyServer/OWCAddin.CAB" id=OWCAddin></OBJECT>

<! --- COMMENT END --------------------------------------->

</HEAD>

<BODY>

<OBJECT classid=clsid:0002E559-0000-0000-C000-000000000046 height="50%" 
id=Spreadsheet1 width="80%"></OBJECT>

<SCRIPT Language=VBScript>

Function Window_OnLoad()
   'Reference the COM object so that its functions can be called from
   'formulas in cells on the Spreadsheet
   Spreadsheet1.AddIn OWCAddin.Object

   'Populate the Spreadsheet with data
   With SpreadSheet1
      .Range("A1:D1").Value = Array("Known X-Values", "Known Y-Values", _
                                    "New X-Values", "New Y-Values")
      .Range("A2:A13").Value = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
      .Range("B2:B13").Value = Array (133890, 135000, 135790, 137300, _
                                      138130, 139100, 139900, 141120, _
                                      141890, 143230, 144000, 145290)
      .Range("C2:C5").Value = Array(13, 14, 15, 16)
      .Range("A1:D13").AutoFitColumns
      .Range("D2:D5").NumberFormat = "0.00"
   End With
End Function

</SCRIPT>

</BODY>
</HTML>
注意：您需要修改 OWCAddin 对象的 <OBJECT> 标记，以使它包含 clsid 和 codebase 的正确值。您可以通过在注册表编辑器 (Regedit.exe) 中检查注册表项 HKEY_CLASSES_ROOT/OWCAddin.MyFunctions/Clsid 来确定 COM 对象的类 ID。codebase 标记应该包含您使用打包和部署向导创建的 CAB 文件的位置。也可以再*.CAB目录下找到一个OWCAddin.HTM中找到classID

2.将该文本文件另存为 CustomFunction.htm。
3.在 Windows 资源管理器中双击 CustomFunction.htm 以便在浏览器中查看它。
4.在单元格 D2 至 D5 中分别输入下列公式： 
D2：=CustomTrend(B2:B13, A2:A13, C2:C5, 1)
D3：=CustomTrend(B2:B13, A2:A13, C2:C5, 2)
D4：=CustomTrend(B2:B13, A2:A13, C2:C5, 3)
D5：=CustomTrend(B2:B13, A2:A13, C2:C5, 4)
这些公式返回： 
D2：     146171.52
D3：     147189.70
D4：     148207.88
D5：     149226.06
5.在单元格 F1 中，输入公式： 
=GetTicks()
该公式返回一个值，该值代表自系统启动之后所经过的毫秒数。
码农丁丁
关注
EXCEL如何自定义函数（下次直接使用）
qq_39783601的博客
07-23 1万+
在EXCEL中我们是可以设置自定义函数来进行运算数据的，方便我们下次直接使用我们已经定义好的函数，让我们开始吧： 1、首先打开EXCEL表格 2、然后点击开发工具，选择Visual Basic 3、右击VBAPorject选择插入，再点击模块 4、在窗口输入 Function 三项合并计算(a, b, c) 三项合并计算 = (a / b + c) End Function 5、然后在EXCEL单元格中输入=三项合并计算(9, 3, 6) 6、回车出结果 我们多测试几个看看： 这个环比的例
EXCEL自定义函数
37度2
05-27 1995
在工作中有时会遇到在编写公式时，找不到适合的EXCEL内置函数，或者虽然可以使用内置函数，但会造成公式复杂不易理解，这时就可以考虑使用自定义函数了。 编写自定义函数需要一定的VBA基础，但完成后，就可以像使用内置函数一样方便了，任何人都可以使用。 工具/原料 excel 步骤/方法 下面通过一个例子来学习简单
python基础之函数
桃子猫的博客
10-05 606
python基础之函数 函数 #函数定义必须以def开头,+函数名,参数,+冒号 #从函数的定义行的下一行开始,必须有缩进 def sumdata(): print('Hello') pass #占位符,没有实际意义,只是避免语法错误 print(sumdata()) #函数的返回值 return 语句不是必须的,但是如果没有return,函数就没有返回值 def sumdata(a,b): return a+b #return之后的语句,称之为不可达语句 def sumdata(a
Excel - 自定义函数
yuxuac的专栏
12-12 1万+
1. 问题： 假设我们在Excel中有以下需求： 你可以嵌套使用IF函数，判断每一个分数属于哪一个区间。 但是，4层IF会不会很容易出错；如果是5个区间，甚至是10个区间呢？嵌套使用10个IF不太现实，也很容易出错吧。 其实在Excel中，我们可以自定义自己的函数，代码使用VB实现；下面的内容就介绍如何在Excel中添加自定义函数。 2. 在Excel中自定义函数 请按照以下步
152_Power Query 快速合并文件夹中表格之自定义函数 TableXlsxCsv
焦棚子的博客
05-21 1155
152-技巧-Power Query 快速合并文件夹中表格之自定义函数 TableXlsxCsv **附件下载地址：**https://jiaopengzi.com/2602.html 一、背景 在我们使用 Power BI 或者 Power Pivot 做数据分析模型时，使用 Power Query 做数导入，经常会遇到如下场景： 同一文件夹下多个表格的合并。 同一个 Workbook 中有多个相同的字段的 WorkSheet 。 Xlsx, Xls, Csv 并存。 字段的类型更改每次都要打开 Pow
react-React的电子表格组件
08-15
本篇将深入探讨React中的电子表格组件，以及如何使用名为`sou-react-table`的开源库来创建自定义的数据网格。 React是一个由Facebook维护的、用于构建用户界面的JavaScript库，它以其组件化思维著称。在React应用中...
易语言高级表格自定义计算公式
07-22
1. **易语言表格组件**：在易语言中，表格组件通常用于展示和管理二维数据，类似于电子表格。它提供了行列结构，可以进行数据的增删改查，同时支持数据排序、过滤等功能。表格组件对于数据分析和展示具有极大的便利...
简易电子表格项目.zip
01-27
- 文件系统接口：保存和加载电子表格文件，可能使用自定义的文件格式。 通过这个项目，学生不仅可以巩固数据结构的知识，还能学习到软件工程实践，如模块化编程、错误处理和文件I/O等技能。这样的课程设计对于提升...
WPS 2016表格使用最新个人所得税函数（2019年版）补丁.zip
10-31
WPS Office是一款由金山软件开发的办公软件套件，包含了文字处理、电子表格和演示文稿等组件。WPS 2016表格作为其中的一部分，提供了类似于Microsoft Excel的功能，允许用户进行复杂的计算和数据分析。 个人所得税...
FlexCell电子表格控件1.0版(FLEXCELL.npk)-易语言
06-13
FlexCell电子表格控件1.0版是一款专为易语言设计的组件，它是一个基于ActiveX技术的控件模块，允许开发者在Windows操作系统上轻松集成电子表格功能到他们的应用程序中。这个控件提供了丰富的功能，使得开发人员可以...
excel自定义函数
07-23
excel自定义函数
Excel自定义函数大全
01-03
强人编写的Excel函数，可以实现如显示公式、Wlooup、人民币大小写转换、显示汉字笔画、拼音等，插入后可以像系统函数一样直接调用
Excel的自定义函数.rar
09-26
Excel的自定义函数.rar,自定义函数就是用户创建的用于满足特定需求的函数，是对于Excel内置工作表函数的一个补充。本示例文件内容包括：对比自定义函数的效率、两种参数传递方式示例、返回数组的函数示例和自定义函数的自动计算。
Excel VBA自定义常用函数
12-14
包含8个自定义函数： 1.提取中文,支持多个中英文交替2.赋值3.Copy首行，给其他行赋值4.获得链接5.截字符串中首尾两个-之间的部分6.提取中文前面的英文7.截取字符串中最后一次出现要str之后的字符串
Excel 自定义函数
djph26741的博客
06-01 239
打开excel，在菜单栏选择开发工具，点击Visual Basic(或按Alt+F11)，打开 Visual Basic 编辑窗口。 2 在编辑窗口中菜单栏选择插入－模块命令，插入一个新的模块1。 3 在右边的模块1窗口中输入如下代码： Function V(a, b, C) V = a * (b + c) ...
Excel自定义函数
seizeF的专栏
06-18 1638
Excel中如何自定义函数
在 Excel 中编写自定义函数——基础篇
热门推荐
唐诗宋词的专栏
07-08 2万+
工作中经常在 Excel 中使用公式，很多问题不是一个函数可以解决的，往往是很多函数嵌套在一起。熟练以后，输入很长很长的公式倒也不是什么难事，就当是一次脑筋锻炼。但是阅读上到底有点不方便，尤其是隔了很长时间再去看这个公式，又是一次脑筋锻炼，不如把工作中解决实际问题常用到的函数组合包装成单一的自定义函数来用。下面就总结一下如何在 Excel 中自定义函数。我这里说的自定义函数(User De
vue3 univerjs 电子表格自定义右键菜单
最新发布
09-10
Vue3结合univerjs来实现电子表格并自定义右键菜单，通常会涉及到以下几个步骤： 1. **设置Vue项目环境**：首先，你需要创建一个Vue3项目。可以使用Vue CLI或者Vite等工具来搭建项目框架。 2. **集成univerjs电子...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

码农丁丁

你的认可是我创作最大的动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值