VBA- read and retain data in spreadsheet

[kevin chan]

Hello. Thank you for looking at this problem of mine.
The help I am requesting relates to the speed of a VBA user defined function.
I have a function which works correctly, as below (relevant parts):

Function placeholdername (arg1, arg2, etc) As Double
    Dim data_table As Range
  …..Other code…..
Location = some logic
   
    Set data_table = Worksheets(DATASHEET).Range( Location & 1).CurrentRegion
cost = cost + data_table (x, y) * something_else
many other reads of data_table ( x , y)

Now this DATASHEET is a functionally read only document. Changes happen to it no more than monthly.
It contains many tables separated by empty columns. My intent is to read a set of data into a VBA memory once, then work off the array so VBA isn’t accessing the workbook all the time. These tables have variable rows and columns. There is other code which decides where in these tables it looks later.

The problem is the speed. This function is called 10,000s of times with every calculate, so if I can save milliseconds, I’ll take it.

My first question is:
With the way I have written this, assuming later in the code I make many references to data_table (x ,y) after changing x and y, does excel read from the workbook only once? Or does this way of working make excel read from the workbook with every call?

If it makes many reads from the workbook. What is the best way to load data into the memory? I currently have code that can find the location (column) of the first cell in the correct table.
There are no other calls to ranges or cells.

My second question is only relevant if the answer to my first question is ‘only one read’
Is there a way for VBA to read these data tables into memory and keep it within scope even when there is no VBA code executing, as long as the workbook is opened, and say make the DATASHEET update the VBA memory only on saving? That way the program only needs to interact with the workbook from being given the arguments and returning the output.

I don't really know how to dynamically create these arrays, name them dynamically or keep them in scope.

I already have the macro set up for speed with automatic calculation and screen updating etc.