0

I'm attempting to use an array created from a user-defined function in another user-defined function. For this exercise, I have two separate functions.

The first function will create an array from a range input and sum the values (yes I know there's an excel built-in sum function but this is just for exercise).

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.

            Function first_funct(list_1 As range) As Double

                Dim extent As Integer
                extent = list_1.rows.Count

                Dim main_array() As Double
                ReDim main_array(1 To extent)
                Dim i As Integer

                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

            End Function

            Function second_funct(list_2 As range) As Double

                Dim extent As Integer
                extent = list_2.rows.Count
                'Assume the extent of list_2 is equal to extent of list_1 in first function.

                Dim i As Integer

                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

My question is: how do I use the main_array created in the first function so that I can use it for the second.

In reality, I have hundreds of cells that need to be calculated and creating the same array for each one is inefficient. I would like to create an array one time (in this case function #1) and use a simpler function (function #2) that will call upon the saved array, saving me many lines of code and time.

Thanks in advance.

Soto
  • 75
  • 2
  • 9
  • A global variable is a variable used in many functions and subs. You probably want to make your array global. – A.S.H Jan 03 '17 at 04:28
  • to add on to @A.S.H, just move the line `Dim main_array() As Double` on top of both the functions. Should work. – nightcrawler23 Jan 03 '17 at 04:39
  • You pass the array as a parameter or if only one and the main point of the program, declare it as a global. Globals, used in moderation, speed up programs by making things simple. –  Jan 03 '17 at 04:46

1 Answers1

1

Storing the array in a global variable is the way to go. But a problem with this approach is that you have introduced a dependency into second-func that Excel cannot see. Fixing this problem is more complicated than just making the array global.

So you have to somehow ensure that first_func has been calculated in this Excel session before second-func and also that when first-func is recalculated (for instance because list_1 has changed) that all the second-Funcs recalculate.

So you have to do something like this:

  • In your Workbook_Open event you need to recalculate first-func (to handle the case when you re-open the workbook and the global array is empty and needs recreating)
  • Second_func needs to have an additional parameter that references either the cell that contains first-Func (so that Excel recalculates it whenever list_1 changes and when func-1 recalcs)
Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • the global variable works and you're right the second_funct is giving me issues now because the dependency. Now my goal is to avoid that approach because of the problems that arise. Would you mind taking a look at my new code? See link here:http://stackoverflow.com/questions/41477553/pass-array-created-in-first-function-to-second-function for some reason the code is giving me trouble. My goal here was to make sure that everything will calculate automatically. – Soto Jan 05 '17 at 05:00