IT'S POSSIBLE???

[galileogali]

My question:

It is possible to assign an Array to a CustomDocumentProperties?
HOW?

GALILEOGALI

 

[Damon Ostrander]

Hi GALILEOGALI,

I don't believe it is possible. I think the only place where you can permanently store a large array in an Excel workbook is in a worksheet range. Perhaps you could use a protected hidden (xlVeryHidden) sheet if you want to save the array invisibly. You could also write it to a binary file using VBA and have Excel read the binary file into an array using VBA.

Damon

 

[galileogali]

You could also write it to a binary file using VBA and have Excel read the binary file into an array using VBA.

how?

Thanks Damon

 

[galileogali]

I discard the other proposals, that already the tapeworm in account, simply because my question is net theoretical.

GALILEOGALI

 

[Damon Ostrander]

Hi again galileogali,

Here is an example of how one might write an array of data to a data file (in this case ArrayData.dat in the working folder, then read it in later to restore it. In this example I start by reading the data (I assumed numbers) from column B and loading it into the array A. The procedure that subsequently reads the data back into the array (also named A) then write the array to column D so it can be easily verified that the data was read and written correctly. In the reading procedure I dimension the array 100 x 1 so that I don't have to use a loop to restore it to column D--if I wasn't going to write it to a column of cells I would have just dimensioned it 100.

Writing the binary file:

Option Base 1

Sub WriteArray()
   Dim A(100)     As Double
   Dim i          As Long
   
   'Load data into array
   For i = 1 To 100
      A(i) = Cells(i, "B")
   Next i
   
   'Write data to binary file
   Open "ArrayData.dat" For Binary Access Write As #1
   For i = 1 To 100
      Put #1, , A(i)
   Next i
   
   Close 1
End Sub

Reading the binary file:

Option Base 1

Sub ReadArray()
   Dim A(100, 1)    As Double
   Dim i          As Long
   
   'Read data from binary file
   Open "ArrayData.dat" For Binary Access Read As #2
   For i = 1 To 100
      Get #2, , A(i, 1)
   Next i
   
   'Write data to column D of active worksheet
   Range("D1:D100") = A
   
   Close 2
End Sub

Note also that I used Option Base 1 at the header of each module to indicate that all arrays should start with index 1 rather than 0 (for convenience only).

Keep Excelling.

Damon

 

[galileogali]

THANKS DAMON!!!
Excellent your answer....

B U T..........


IF I have
numbers in “B”,
strings in “C”
and Booleans in “A”

In this case: What I must modify in the procedure?

GALILEOGALI

 

[Damon Ostrander]

Hi again galileogali,

You can write and read any or a mix of these data types. The main problem arises with regard to text strings when you don't know the length of the string being written--or when they are varying in length. This is because you must read variables that are fixed length data types. So you can write and read a string that is declared String*20, but not one that is declared just String.

Similiarly with numbers. All numbers in Excel cells are Double, so if you want to read numbers from a spreadsheet range to an array without any loss of precision you must use Double. But if you are starting with an array of Integer, Long, Currency, Boolean or Date data types, you must read the values back into arrays of exactly the same data types.

Damon

 

[galileogali]

Thanks Damon. I believe that the best Option is to keep the data in xlheryhidden Worksheet.


GALI