## 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.

Show source

## Answers to Pass array created in first function to second function ( 2 )

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
``````
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)