MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Retain Array Variable data

  Asked By: Fujita    Date: Nov 18    Category: MS Office    Views: 1618

I need to copy & save the initial data in a 9x9 matrix.
If the users calculations are correct, the mvArray data will be
discarded, or another sub will be run to reset the range with the
initial data.

Sub fillArray
Dim mvarray As Variant
mvarray = Range("A1:I9").Value
End Sub

My question is:
I have experimented with various settings for Public or Private but
cannot get it to save the data once the sub has finished running.

How can I retain the mvArray data?



4 Answers Found

Answer #1    Answered By: Julia Hughes     Answered On: Nov 18

If you dim mvarray in the sub it is destroyed when the sub closes. If you dim
it outside the sub in a module (not in a worksheet or workbook) it lasts until
you close the workbook the module belongs to.

Answer #2    Answered By: Scarlett Hughes     Answered On: Nov 18

try thisworkbook.save..................

Answer #3    Answered By: Marina Smith     Answered On: Nov 18

To create a variable  with longer life, put it outside the subroutine (i.e.
at the top of the module). It will then live as long as the module does -
which for worksheet and normal modules is until you close the workbook.

Public and private  only control the visibility of the item, not its life.

Answer #4    Answered By: Verner Fischer     Answered On: Nov 18

With your help & guidance I understand the answers that you both gave and
successfully resolved the issue.

Didn't find what you were looking for? Find more on Retain Array Variable data Or get search suggestion and latest updates.