IT'S POSSIBLE???

galileogali

Well-known Member
Joined
Oct 14, 2005
Messages
748
My question:

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

GALILEOGALI
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Well-known Member
Joined
Oct 14, 2005
Messages
748
I discard the other proposals, that already the tapeworm in account, simply because my question is net theoretical.

GALILEOGALI
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239

ADVERTISEMENT

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:

Code:
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:
Code:
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

Well-known Member
Joined
Oct 14, 2005
Messages
748
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Well-known Member
Joined
Oct 14, 2005
Messages
748
Thanks Damon. I believe that the best Option is to keep the data in xlheryhidden Worksheet.


GALI
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,727
Members
410,702
Latest member
clizama18
Top