需要帮助为excel 2003制作自定义功能 [英] Need help on making a custom function for excel 2003

问题描述

大家好，

今天我要求你的帮助。

我需要在VBA6 for Excel 2003中构建自定义功能。

我的问题是，我需要传递2个与参数3和4相同大小的列表。我有这个代码：

Hi guys,
Today I am asking for your help.
I need to build a custom function in VBA6 for Excel 2003.
My problem, I need to pass 2 lists of same size as parameters 3 and 4. I have this code:

function MyFunc(P1, P2, P3 As Range, P4 As Range) As Long
    ...

来自一个Cell，我可以称之为 = MyFunc（Alpha，123，A1：A3，{1,2,3}），到目前为止，非常好。



问题是我还需要列表为 {2，B1 * 2，C1} ，然后我被卡住了。我的列表需要混合使用单元格引用，数字常量和公式。我不能使用单元格将列表构建为单元格区域。



你会怎样？吗？

我应该使用ParamArray吗？



我尝试过：



谷歌搜索很多，但没有发现任何有趣的东西。

From a Cell, I can call it like =MyFunc("Alpha", 123, A1:A3, {1,2,3}), so far, so good.

The problem is that I also need the lists as {2, B1*2, C1}, and there I am stuck. My lists need to be a mix of cell references, numeric constants and formulas.I can't use cells to build the lists as ranges of cells.

How would you do ?
Should I use the ParamArray ?

What I have tried:

Googled a lot but did not found anything interesting.

推荐答案

excel中的计算是基于设置/单元格的，所以你需要思考在集合/单元格中，即将计算分解为步骤，然后将列作为中间值放入列中。



因此，对于您的示例，列中的某些单元格需要乘以，用乘法计算创建一个新列，并使用公式范围内那列的值。



简而言之：创建更多列作为中间值。

Computations in excel are set/cell based so you need to think in sets/cells i.e. break your computations into steps and put then in columns as intermediary values.

So for your example where some of your cells in the column need to be multiplied, create a new column with the computation for the multiplication and use the values of that column in the range for your formula.

In short : create more columns as intermediary values.

至于 ParamArray ...是的，这个是使用的解决问题的好方法 ParamArray 当未知的参数数量时。请注意，您只能在函数中使用一个 ParamArray 参数。因此，在您的情况下，您将失去传递两个列表的能力。



如果两个列表上的元素数始终相同，则可以通过这种方式定义函数：

As to the ParamArray... Yes, this is good way to resolve issue by using ParamArray when the number of paramaters in unknown. Note, that you can use only one ParamArray parameter in the function. So, in your case, you lose the ability to pass two lists.

If the number of elements on both lists is always the same, you can define function this way:

Function MyFunc(ByVal a As String, ByVal b As Long, _
       ByVal x1 As Range, ByVal y1 As Range, ByVal z1 As Range, _
       ByVal x2 As Range, ByVal y2 As Range, ByVal z2 As Range) As Long
    'body of function
End Function

这是常见做法。请查看 LINEST功能 [ ^ ]在MS Excel中。



不幸的是，没有方式（据我所知）将用户定义类型（UDT）传递给工作表函数：

This is common practice. Take a look at LINEST function[^] in MS Excel.

Unfortunately, there's no way (as per my knowledge) to pass user defined type (UDT) to worksheet function:

Function MyFunc(ByVal a As String, ByVal b As Long, _
       ByVal x1 As MyPoint, ByVal x2 As MyPoint) As Long
    'body of function
End Function

其中 MyPoint 是UDT（结构，类等）

where MyPoint is UDT (structure, class, etc.)

这篇关于需要帮助为excel 2003制作自定义功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！