将第一个函数创建的数组传递给第二个函数 [英] Pass array created in first function to second function

问题描述

这个问题是根据我最后一个问题构建的，主要是因为我想避免使用全局变量，因为它的局限性。查看答案链接：如何做我调用一个由不同函数创建的数组？

我试图在另一个用户定义的函数中使用从用户定义的函数创建的数组功能。我想避免将数组设置为 Global ，因为第二个函数不会自动重新计算。对于这个练习，我有两个单独的函数。

第一个函数将根据范围输入创建一个数组，并将这些值相加。

第二个函数将调用在第一个函数中创建的数组，并将值与第二个范围输入相加。

pre $ Option Explicit
函数first_funct（list_1 As range）As Double
Dim extent As Integer，i As Integer
extent = list_1.rows.Count
Dim main_array（）As Variant
ReDim main_array（1 To extent）As Variant
'main_array（）was changed从双倍到变体，以避免潜在的问题。
first_funct = 0

对于i = 1扩展
main_array（i）= list_1（i）.Value
'main_array将在第二个函数
first_funct = first_funct + main_array（i）
下一个我

调用second_funct（main_array）

结束函数

函数second_funct （list_2 As range，ByRef main_array（）As Variant）As Double
Dim extent As Integer，i As Integer
extent = list_2.rows.Count
'假设list_2的范围等于第一个函数中list_1的范围。
Dim main_array（）As Variant
ReDim main_main_array（1 To extent）As Variant
second_funct = 0

For i = 1扩展
second_funct = second_funct + main_array（i）+ list_2（i）.Value
'如何在第一个函数中调用从list_1创建的main_array？
下一个我

结束函数


第一个函数给我错误ByRef参数类型不匹配。我的想法是，调用语句会将数组传递给第二个函数，而ByRef语句会将其选中。我也不确定现在第二个函数是否正确，因为第一个函数给了我错误。

预先感谢。

<解决方案

你的数组都是用强类型声明的，并且你正以正确的方式传递它们。你的问题不在于数组的类型，而在于命令，或者是为第二个函数省略参数。

你的 second_funct 函数需要2个参数 list_2作为Range，ByRef main_array（）As Double ，但您只提供一个参数：

 调用second_funct（main_array）
  

假设你的意思是传递一个范围 AND 数组，请尝试将其更改为：

  Call second_funct（list_1，main_array）
  

或者更好的是，移除 Call 语句，只需使用：

  second_funct list_1，main_array 
  

This question is sort of built from my last question mainly because I want to avoid using Global variables because of its limitations. See answer to link here: How do I call upon an array created by a different function?

I'm attempting to use an array created from a user-defined function in another user-defined function. I want to avoid setting the array as Global because the second function won't automatically recalculate. For this exercise, I have two separate functions.

The first function will create an array from a range input and sum the values.

The second function will call upon the array created in the first function and sum the values with a second range input. See the following code.

            Option Explicit
            Function first_funct(list_1 As range) As Double
                Dim extent As Integer, i As Integer
                extent = list_1.rows.Count
                Dim main_array() As Variant
                ReDim main_array(1 To extent) As Variant
                '   main_array() was changed from double to variant to avoid potential problems.
                first_funct = 0

                For i = 1 To extent
                    main_array(i) = list_1(i).Value
                    '   main_array will be used again in second function
                    first_funct = first_funct + main_array(i)
                Next i

                Call second_funct(main_array)

            End Function

            Function second_funct(list_2 As range, ByRef main_array() As Variant) As Double
                Dim extent As Integer, i As Integer
                extent = list_2.rows.Count
                '   Assume the extent of list_2 is equal to extent of list_1 in first function.
                Dim main_array() As Variant
                ReDim main_main_array(1 To extent) As Variant
                second_funct = 0

                For i = 1 To extent
                    second_funct = second_funct + main_array(i) + list_2(i).Value
                    '   How do I call upon main_array created from list_1 in the first function?
                Next i

            End Function

The first function gives me the error "ByRef argument type mismatch". My thinking was, the call statement would pass the array onto the second function and the ByRef statement will pick it up. I'm also not sure now if second function is even correct because the first one gives me the error.

Thanks in advance.

解决方案

Both of your arrays are declared with strong types, and you're passing them in the right way. Your problem is not with the type of the arrays, but rather with the order, or rather omission of arguments to the second function.

Your second_funct function is expecting 2 arguments list_2 As Range, ByRef main_array() As Double, but you're only providing a single argument:

Call second_funct(main_array)

Assuming you mean to pass a range AND an array, try changing that to:

Call second_funct(list_1, main_array)

Or better still, remove the Call statement, and just use:

second_funct list_1, main_array

这篇关于将第一个函数创建的数组传递给第二个函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！