2

I have a big sheet Sheet1 in my Excel, using CSE I filtered it and generate an array in a range named filtered_result in Sheet2; filtered_result is afterwards used by many queries in Sheet3 till Sheet10, mainly by VLookUp etc, everything works fine.

filtered_result in Sheet2 is big and its computation is already slow, as an intermediate result it speeds up the computation in Sheet3 till Sheet10.

Now, I'd like to o remove Sheet2, to avoid visualizing the intermediate result. This is still possible as I could modify the formula in Sheet3 till Sheet10, but then the Excel becomes quite slow.

Is there any way to somehow store the original intermediate result filtered_result in Sheet2 like something in the memory, and later queries will still be able to retrieve it?

9
  • You might use a VBA global array - see this post.
    – harrymc
    Jul 26 '17 at 6:27
  • @harrymc but VBA global array can't be used in formula right?
    – athos
    Jul 26 '17 at 7:03
  • 1
    The array can be assigned to a range via Range.FormulaArray, or maybe used in a formula via a defined user function call. If arrays are unsuitable and your problem is in just not visualizing sheet2, then just hide it.
    – harrymc
    Jul 26 '17 at 8:36
  • The first thing coming to mind is simply hiding Sheet2, if your goal is just "avoid visualizing the intermediate result".
    – simlev
    Jul 26 '17 at 12:51
  • Hiding is a pretence...
    – athos
    Jul 26 '17 at 12:52
3
+100

Don't use array formulas, use regular formulas.

For example, let's say you have a formula that adds column B if the value in column A is greater than 10. Don't create an array formula with a conditional to do this. Instead make a new column C with the formula:

=If( $A > 10, $B, 0 )

So the third column contains either B's value if the condition is met or 0 if it is not met. Now just sum column C. This method is usually a lot faster than an array formula. It is called a "helper column".

If it is still slow to calculate, use macros or VBA to compute the values; that way you only have to compute them once, not recaculate them if things don't change. So, in the above example, instead of using the conditional formula, we can use a macro (or VBA code) to compute the If-formula and put the result in the cell. Then run the macro whenever you need to compute the table and generate values. With no conditionals (If-statements) in the spreadsheet itself, it will calculate much faster.

How to Do Everything in Memory

If you want to get rid of the sheet entirely, you can do everything in memory using static variables in a VBA code module:

Dim MyArray(1000, 2000) As Double

Sub computeMyArray()
   ... compute all the values of MyArray
End sub

Function GetValueFromMyArray( Dim x as Integer, Dim y as Integer )
   GetValueFromMyArray = MyArray( x, y )
End Function

You can then use this function in any cell of your workbook to get values out of the array you created which is memory. For example, a cell in your workbook could have the formula:

=GetValueFromMyArray( 5, 6 )

This would retrieve the 5th row, 6th of the array that is in memory.

1
  • yes i want to do everything in memory, thx.
    – athos
    Jul 27 '17 at 22:45
1

The easiest and dumbest solution is just to hide the worksheet Sheet2 (link).

While this solution is not sophisticated, it does not require you to change anything in your application and it will solve the slow display problem.

0

This snipet should work without to much editing. Long story short, you are better of making a sub to make the array, and it should stay in memory as long as you call the sub again.

Public Sub MakeArrayGlobal(byRef Range as Range)
    dim MyPublicArray() as Variant
    with Range
        counter = 0
        For each cell in range
            MyPublicArray(counter) = cell.value #Or whatever you need
            counter = counter + 1
        Next
    End with
End sub
2
  • this is not good as every time have to call the sub again but the real computation is heavy.
    – athos
    Jul 27 '17 at 22:43
  • @athos still is answering the question on the title. Also is faster that leaving volite formulas in the sheets.
    – dmb
    Jul 28 '17 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.