Pass array created in first function to second function

Question

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.

Answer 1

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

Answer 2

Function and subroutines should be proper in their use. The code

 Call second_funct(main_array)

has an error as you are passing main_array while the definition in second_funct requires a range to be the first parameter provided.

Suggestion#1 Modify first_funct as follows

Function first_funct(list_2 As Range, list_1 As Range) As Double

You would be passing both ranges in the first function.

Suggestion #2 Call second_funct(list_2, main_array)