接受范围作为数组参数 [英] Accepting a range as an array parameter

问题描述

我有一个函数需要一个数组并输出另一个数组。它的内部比下面的玩具示例更复杂。

 公共功能divide_by_2_5（ByRef coeffs（）As Double）As Double 
 Dim Columns As Integer 
列= UBound（coeffs，2） -  LBound（coeffs，2）+ 1 
 Dim output（）As Double 
 ReDim输出（1到1， 1到列）
 Dim i As Integer 
对于i = 1到列
输出（1，i）= coeffs（1，i）/ 2.5 
下一个i 
 divide_by_2_5 =输出
结束函数
  

这是我看到的：

我希望第二行代替包含该函数的输出。在这种情况下，这将是 0.4，0.4，0.4，0.4 。

不幸的是，我得到一个 #VALUE！错误，我不知道如何调试这个。

有些澄清：显然有可能相同的函数返回数组或将其写入电子表格（使用 Ctrl - Shift - 输入）。以类似的方式，输入是否可以来自范围或数组？

解决方案

 公共功能divide_by_2_5（coeffs As Variant）As Double（）
 Dim v（）As Variant 
如果TypeName（coeffs）=Range然后
v = coeffs.Value 
 Else 
v = coeffs 
结束If 
 Dim output（）As Double 
 ReDim输出（LBound（v，1）To UBound（v，1），LBound ，2）到UBound（v，2））
 Dim r As Long 
 Dim c As Long 
对于r = LBound（v，1）To UBound（v，1）
对于c = LBound（v，2）到UBound（v，2）
输出（r，c）= v（r，c）/ 2.5 
下一个
下一个
 divide_by_2_5 =输出
结束函数
  

将其称为UDF的示例是： / p>

  {= divide_by_2_5（C2：F2）} 
  

使用范围从VBA调用这个例子可能是：

  Dim v As Variant 
v = divide_by_2_5（Worksheets（Sheet1）。Range（C2：F2））
  

使用数组从VBA调用这个例子可能是：

  Sub test（）
 Dim x（1，4）As Variant 
 Dim v As Variant 
x（1，1）= 6 
x（1，2）= 7 
x（1，3）= 8 
x（1，4）= 9 
v = divide_by_2_5（x）
 MsgBox v（1，3）
 End Sub 
  

I have a function that takes an array and outputs another array. Its internals are more complicated than the toy example below.

Public Function divide_by_2_5(ByRef coeffs() As Double) As Double()
    Dim Columns As Integer
    Columns = UBound(coeffs, 2) - LBound(coeffs, 2) + 1
    Dim output() As Double
    ReDim output(1 To 1, 1 To Columns)
    Dim i As Integer
    For i = 1 To Columns
        output(1, i) = coeffs(1, i) / 2.5
    Next i
    divide_by_2_5 = output
End Function

Here's what I see:

I would like the second row to instead contain the function's output. In this case, that would be 0.4, 0.4, 0.4, 0.4.

Unfortunately, I get a #VALUE! error and I don't know how to debug this.

Some clarification: clearly it is possible to have the same function return an array or have it write to the spreadsheet (with Ctrl-Shift-Enter). In an analogous fashion, is it possible for the input to come from either a range or an array?

解决方案

Public Function divide_by_2_5(coeffs As Variant) As Double()
    Dim v() As Variant
    If TypeName(coeffs) = "Range" Then
        v = coeffs.Value
    Else
        v = coeffs
    End If
    Dim output() As Double
    ReDim output(LBound(v, 1) To UBound(v, 1), LBound(v, 2) To UBound(v, 2))
    Dim r As Long
    Dim c As Long
    For r = LBound(v, 1) To UBound(v, 1)
        For c = LBound(v, 2) To UBound(v, 2)
            output(r, c) = v(r, c) / 2.5
        Next
    Next
    divide_by_2_5 = output
End Function

An example of calling this as a UDF would be:

{=divide_by_2_5(C2:F2)}

An example of calling this from VBA using a Range might be:

Dim v As Variant
v = divide_by_2_5(Worksheets("Sheet1").Range("C2:F2"))

An example of calling this from VBA using an array might be:

Sub test()
   Dim x(1, 4) As Variant
   Dim v As Variant
   x(1, 1) = 6
   x(1, 2) = 7
   x(1, 3) = 8
   x(1, 4) = 9
   v = divide_by_2_5(x)
   MsgBox v(1, 3)
End Sub

这篇关于接受范围作为数组参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！