Hi,
I am new to the forum and hoping that you guys can shed some light, I have been searching forums for a few days with no luck. I need to create an array within VBA to store calculated values
[Option Explicit]
[Public calc_array (1 to 450, 1 to 9) as Double]
Would be the right syntax. Each row of the array will hold 9 values and be calculated via a call to a function which is complicated but still ultimately calculates 9 rational numbers, current size of the spreadsheet is only 450 rows and this will grow by one row per month, something like
[Function fill_calc_array ( rowvar as Integer, var_1 as Double, var_2 as Double, var_3 as Double,........) as String]
[ calc_array(rowvar, 1) = var_1 * var_2 / var_3]
[ calc_array(rowvar, 1) = var_2 * var_3 / var_1]
[ calc_array(rowvar, 1) = var_3 * var_1 / var_2]
[ fill_calc_array = "Complete"]
[End Function]
I want to be able to access the calc_array from within the VBA function but also be able to access the calc_array from within a specific cell, again like the following is what I would like to use:
Cell J5 is the month value between 1 and 450 , in cell K5 "=calc_array(j5,1)", L5 = "=calc_array(j5,2)", M5 = "=calc_array(j5,3)" etc
Try as I might I cant reference the global array variable from within a cell, so I wrote a function to access the array
[Function access_global_array(rowvar as Integer, colvar as Integer) as Double]
[ access_global_array = calc_array(rowvar, colvar)]
[End Function]
But this made the spreadsheet very slow (15 seconds to recalculate on a very fast machine).
Any ideas? Thanks Gil64
Bookmarks