Best way to code a repeatedly used array?

Hi all

I am trying to code a function that needs to compute a data array specified by the user to calculate the output. Lets called the function func().

This function will be used about 1000 times in a spreadsheet and the data array will need to be quite large so i would like to minimise the number of times the data array needs to be calculated to make the spreadsheet as quick as possible.

so if the user inputs func("array 1", ....). I play to write a sub function to turn "array 1" into the required data array. Ideally i would like to store this data array as a global array called "array 1" so that if another cell needs "array 1" it will be able to just use it rather than have to recalculate it.

Is this all possible to have an unknown number of global arrays stored with unknown names (Specified by the user)?

Thanks

Hi, and welcome to the forum.

Can we double check whether you really need to use a VBA function? As soon as you invoke VBA there is a time overhead each time you use an Excel function to call a VBA function. In other words even if you have a global array held in the VBE, it sound like your XL functions will be calling it a lot. The overhead is not necessarily in the size of the array but the number of times you refer to it.

Is a standard data table held in the XL application not a viable option? It would certainly process things far quicker.

Can you describe in a little more detail what you are trying to compute>

Rgds

Thanks for the welcome - what information do i need to specify?

My VBA isn't great but essentially if i input the text "array 1" into a cell, can i create a function that would create a 10x10 array say called "array 1" that would be stored globally for all functions to see?

Thanks also for the welcome Richard.

I need to create an excel add in that can be be saved onto any computer.

The time intensive part of my current function is to take an (nx1) array and multiply it by a (nxm) array to produce an (nxm) array (with some modifications depending on the user's other inputs)

The data arrays are all saved on excel sheets in the add-in. At the moment the function i have computes the (nxm) array each time the function calls which is hugely inefficient.

My idea was to create global arrays that all the functions could see and cut down the speed of the 1000 functions in my spreadsheet.

Hope this helps?