How to store/retrieve arrays within a single cell?

[Kelvin Stott]

Hello,

I have created a number of complex functions that use or create 1-dimensional numeric arrays in VBA, and I would like to store and retrieve some of these arrays within single cells of a worksheet so that I can use them in dependent functions without having to store and display the entire array with one element per cell, and without having to recalculate the same intermediate array multiple times within VBA.

Are there any existing worksheet or VBA functions (or is it possible to create two functions) that can convert and store an entire numeric array within a single cell of a worksheet (e.g., as text), and then convert this back into a form which can be read and recognized as a numeric array by another function?

Or is there any other way to avoid filling my worksheet with arrays, or having to recalculate them each time within VBA?

Any help would be much appreciated, thanks!

Best regards,
Kelvin

 

[bertie]

I would suggest using a global array.
See here OFF97: How to Create and Use a Global Array in VBA Procedures

As per your specific request try pressing F8 to step through the Test() procedure.

[COLOR=darkblue]Sub[/COLOR] test()
   [COLOR=darkblue]Dim[/COLOR] myArray [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
      
   myArray = Array(1, 2, 3)
   
   PutArray myArray
   
   myArray = GetArray(myArray)
   
   Stop  [COLOR=green]'click View=>Locals Window and expand arr[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]




[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PutArray([COLOR=darkblue]ByVal[/COLOR] arr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR])
   [COLOR=darkblue]Dim[/COLOR] txt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](arr) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](arr)
      [COLOR=green]'build up the output string[/COLOR]
      txt = txt & arr(i) & ","
   [COLOR=darkblue]Next[/COLOR] i
   
   Sheets("Sheet1").Range("A1").Value = txt
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Function[/COLOR] GetArray([COLOR=darkblue]ByRef[/COLOR] arr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] txt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   
   txt = Sheets("Sheet1").Range("A1").Value
   GetArray = Split(txt, ",")
   
   
   Stop  [COLOR=green]'click View=>Locals Window and expand arr[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[Kelvin Stott]

Super-brilliant, thank you!

I had no idea about global arrays, or about the split function.

 

[kindlin]

Super-brilliant, thank you!

I had no idea about global arrays, or about the split function.

In general, every programming language has built in split/merge, local/global variables (arrays) etc. You could easily write your own split function as well, just loop through the string and search for the splitting character, appending each newly created result at the end of a list.

Besides syntax differences, most languages have very similar commands available and can all accomplish tasks with a very similar written code (though, normally, each language has a 'best', 'easiest' or 'most efficient' way of doing one thing or another).