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.