java - Excel VBA : Custom functions that return a range of data

Question

我想在 Excel VBA 中编写一个可用作 Excel 公式的用户定义函数，但它不是将值返回到调用它的单元格，而是将一系列值返回到其他单元格的范围。这在许多数据库插件中很常见，例如 Bloomberg、CapIQ、Morningstar。我尝试了不同的方法，但找不到实现它的方法。

例如，如果我将此代码放入单元格 A1 中，它将向单元格 A1 返回 25。

Function AreaOfSquare(length)
  AreaOfSquare = length * length
End Function

<小时/>

我想要的是这样的:

    A     B      C      D      E   
1   *   Eqt1   Eqt1   Eqt2   Eqt2  
2      Price    vol  Price    vol 

在单元格 A1 中，我输入 =DBLOOKUP(B1:F1,B2:E2, "One Year") ，它会生成如下结果:

    A     B      C      D      E   
1   *   Eqt1   Eqt1   Eqt2   Eqt2  
2      Price    vol  Price    vol 
3 07/14   13    100     12    200
4 06/14   14    120     13    210
5 05/14   15    140     14    220

<小时/>

以下是我尝试过的方法列表:

• 从数据库Excel插件中以公式的形式获取原始数据。例如，我可以输入 =Rawdata(B1:E1, B2:E2) 并获取原始数据。问题是我不知道如何在 VBA 中调用这个函数，以便我可以操作这些数据以进行输出，因为这个函数不是内置的，而是带有数据库插件。或者，我可以先使用公式获取原始数据，然后再对这些数据进行操作；问题是这需要两步操作，因为我需要输入公式两次。

• 从Java获取数据并以某种方式导入数据。这很麻烦，因为我需要在老板的计算机环境上配置java应用程序。

有什么建议吗？

Answer 1

这就是我解决这个问题的方法:首先，找到该函数正在调用的单元格。然后你就可以移动它了。

在下面的示例中，它将单元格列表的值相加，直到达到所需的 bucksize。

<小时/>

Function bars(bucksize, columnsgap)

Application.Volatile

'this function is to count backward number of bars need to fill up the bucket
'bucksize is the volume of the bucket
'columnsgap= how many rows the volume column away from where this function use

BB = 0

j = 0

'Find the location where this function call

xx = Application.Caller.Column

yy = Application.Caller.Row

x1 = xx - columnsgap

'Add up the volume of bars until it excess or equal to the bucksize

Do

rowshift = j

y1 = yy - rowshift

BB = BB + Cells(y1, x1).Value

j = j + 1

Loop While BB < bucksize

bars = j

End Function