VBA EXCEL - Public Array losing its value at each new code execution -

i developing code in vba excel.

this code generates 3 dimensional array based on userinputs in worksheets.

the idea re-run code every time userinput changed.

but due heavy calculation time, want keep calculated data in array , amend array rows / columns have received new user input.

my original idea therefore to:

1. declare array public and
2. use "redim preserve" each new code execution (i.e. each new update)

however, realise @ every new code execution, array comes in empty , full calculation re-run...

could solve issue?

thanks in advance al

public function usetestvar()     static testvar(10) integer      debug.print testvar(1)      testvar(1) = testvar(1) + 1 end function  sub test3()     usetestvar end sub 

every execution time testvar(1) value incremented.

edit;

so yes, need have function "hold" "global" variable, , use function stuff it... or return it, or... without knowing code must leave figure out working implementation.