自定义函数如何返回多个值，并赋值给excel表

VBA万岁

VBA万岁 发表于 2014-5-16 14:12
我觉得增加两个参数ad、sd也可实现是取和值，还是取差值：
Public Function cal(a As Integer, b As Integ ...

附件：
Book1.rar (11.25 KB, 下载次数: 36)

2014-5-16 14:15 上传

点击文件名下载附件

kof369

多谢版主！

zhaogang1960

VBA万岁 发表于 2014-5-16 14:12
我觉得增加两个参数ad、sd也可实现是取和值，还是取差值：
Public Function cal(a As Integer, b As Integ ...

楼主要的是数组，cal一定要赋值一个数组

VBA万岁

zhaogang1960 发表于 2014-5-16 18:11
楼主要的是数组，cal一定要赋值一个数组

赵老师，那这样可以吗？

Public Function cal(a As Integer, b As Integer, ad As Integer, sd As Integer) As Variant
    Dim c(1 To 2) As Integer
    If ad = 1 And sd = 0 Then
        c(1) = a + b
    ElseIf ad = 0 And sd = 1 Then
        c(2) = a - b
    End If
    cal = c
End Function
Book1.zip (11.87 KB, 下载次数: 20)

2014-5-16 21:22 上传

点击文件名下载附件

zhaogang1960

VBA万岁 发表于 2014-5-16 21:23
赵老师，那这样可以吗？

Public Function cal(a As Integer, b As Integer, ad As Integer, sd As Int ...

从定义上看应该正确，返回数组的自定义函数，最终需要把一个数组赋给函数名

VBA万岁

zhaogang1960 发表于 2014-5-16 21:45
从定义上看应该正确，返回数组的自定义函数，最终需要把一个数组赋给函数名

明白了，多谢赵老师指点。

kof369

zhaogang1960 发表于 2014-5-16 13:47

Sub Macro1()
    Dim arr
    arr = cal(2, 1, 1, 2)
    Range("a1").Resize(, UBound(arr) - LBound(arr) + 1) = "=cal(2, 1, 1, 2)"
End Sub
Public Function cal(a As Integer, b As Integer, x As Integer, y As Integer) As Variant
    Dim c(1 To 4) As Integer
    Dim z() As Integer
    c(1) = a + b
    c(2) = a - b
    c(3) = a * b
    c(4) = a / b
    ReDim z(x To y)
    For i = x To y
        z(i) = c(i)
    Next
    cal = z
End Function

为什么不行呢？

zhaogang1960

kof369 发表于 2014-5-17 18:26
Sub Macro1()
    Dim arr
    arr = cal(2, 1, 1, 2)

向区域写数组公式：

1. Sub Macro1()
   
2.     'Dim arr
   
3.     'arr = cal(2, 1, 1, 2)
   
4. '    Range("a1").Resize(, UBound(arr) - LBound(arr) + 1) = "=cal(2, 1, 1, 2)"
   
5.     Range("a1").Resize(, 2).FormulaArray = "=cal(2, 1, 1, 2)"
   
6. End Sub
   
7. Public Function cal(a As Integer, b As Integer, x As Integer, y As Integer) As Variant
   
8.     Dim c(1 To 4) As Integer
   
9.     Dim z() As Integer
   
10.     c(1) = a + b
    
11.     c(2) = a - b
    
12.     c(3) = a * b
    
13.     c(4) = a / b
    
14.     ReDim z(x To y)
    
15.     For i = x To y
    
16.         z(i) = c(i)
    
17.     Next
    
18.     cal = z
    
19. End Function
    

复制代码

kof369

zhaogang1960 发表于 2014-5-17 18:53
向区域写数组公式：

Public Function cal(a As Integer, b As Integer, x As Integer, y As Integer) As Variant

    Dim c(1 To 4) As Integer

    Dim z() As Integer

    c(1) = a + b

    c(2) = a - b

    c(3) = a * b

    c(4) = a / b

    ReDim z(x To y)

    For i = x To y

        z(i) = c(i)

    Next

    cal = z

End Function

1.老师，在这个函数当中如果我只想区c（1）和c（4）的值，及X=1，y=4；中间的2，3不要，返回函数是2维数组的不是4维的怎么处理？
1.另外函数的参数里面能不能有数组，那样能实现我的另一个目的，如下；

Public Function cal(a As Integer, b As Integer, x（）as integer, ) As Variant
‘X是一个数组
    Dim c(1 To 4) As Integer
   
   c(1)= a + b
   c(2)= a - b
    c(3) = a * b
    c(4) = a / b
   x(1)=c(1)
   x(2)=c(2)  
x(3)=c(4)
    cal =x
End Function

数组X（）的维数不确定，如果输入两个数，则返回两个，如果输入3个则返回三个

zhaogang1960

kof369 发表于 2014-5-19 19:38
Public Function cal(a As Integer, b As Integer, x As Integer, y As Integer) As Variant

    Dim  ...

1. Public Function cal(a As Integer, b As Integer, x As Integer, y As Integer) As Variant
   
2.     Dim c(1 To 4) As Integer
   
3.     Dim z(1 To 2) As Integer
   
4.     c(1) = a + b
   
5.     c(2) = a - b
   
6.     c(3) = a * b
   
7.     c(4) = a / b
   
8.     z(1) = c(x)
   
9.     z(2) = c(y)
   
10.     cal = z
    
11. End Function
    

复制代码