Using an Array in Environment Variable

I want to store the interior color of each cell in row 1 of a worksheet
in an environment variable so that they can be restored later on using
the Environ function. I know I can do it one by one by setting a
environment variable for each cell but that seems cumbersome. Is there
a way to have a environment variable act as an array so that I can loop
through the storing process with some sort on index?

Thank you very much.

Jack,

Why not save them as a comma separated string ("16,3,5" etc.) and then when
you read the environment variable, use Split to turn it into an array. Seems
simple to me.

--
HTH

Bob Phillips

"Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message
news:11aoiackvoqu81f@corp.supernews.com...
> I want to store the interior color of each cell in row 1 of a worksheet
> in an environment variable so that they can be restored later on using
> the Environ function. I know I can do it one by one by setting a
> environment variable for each cell but that seems cumbersome. Is there
> a way to have a environment variable act as an array so that I can loop
> through the storing process with some sort on index?
>
> Thank you very much.
>
>

Yes! Great idea. Now why didn't I think of that.

Thank you.

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:eO$ry91bFHA.2124@TK2MSFTNGP14.phx.gbl...
> Jack,
>
> Why not save them as a comma separated string ("16,3,5" etc.) and then
> when
> you read the environment variable, use Split to turn it into an array.
> Seems
> simple to me.
>
> --
> HTH
>
> Bob Phillips
>
> "Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message
> news:11aoiackvoqu81f@corp.supernews.com...
>> I want to store the interior color of each cell in row 1 of a
>> worksheet
>> in an environment variable so that they can be restored later on
>> using
>> the Environ function. I know I can do it one by one by setting a
>> environment variable for each cell but that seems cumbersome. Is
>> there
>> a way to have a environment variable act as an array so that I can
>> loop
>> through the storing process with some sort on index?
>>
>> Thank you very much.
>>
>>
>
>

Because you're too close to it <vbg>

Regards

Bob

"Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message
news:11aosoamcaj1s42@corp.supernews.com...
> Yes! Great idea. Now why didn't I think of that.
>
> Thank you.
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:eO$ry91bFHA.2124@TK2MSFTNGP14.phx.gbl...
> > Jack,
> >
> > Why not save them as a comma separated string ("16,3,5" etc.) and then
> > when
> > you read the environment variable, use Split to turn it into an array.
> > Seems
> > simple to me.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message
> > news:11aoiackvoqu81f@corp.supernews.com...
> >> I want to store the interior color of each cell in row 1 of a
> >> worksheet
> >> in an environment variable so that they can be restored later on
> >> using
> >> the Environ function. I know I can do it one by one by setting a
> >> environment variable for each cell but that seems cumbersome. Is
> >> there
> >> a way to have a environment variable act as an array so that I can
> >> loop
> >> through the storing process with some sort on index?
> >>
> >> Thank you very much.
> >>
> >>
> >
> >
>
>

Don't know if this would be an option., but a different idea might be to
store them in the Windows Registry.

Sub SaveRow1()
Dim C As Long ' Column #
Const MyApp As String = "MyApp"
Const Row1 As String = "Row1"

For C = 1 To 256
SaveSetting MyApp, Row1, CStr(C), Cells(1, C).Interior.ColorIndex
Next
End Sub

And at some later time, you can recall them. I 'reset' the colorindex of
Row 2 for testing.

Sub GetRow1()
Dim C As Long ' Column #
Dim v As Variant

Const MyApp As String = "MyApp"
Const Row1 As String = "Row1"

v = GetAllSettings(MyApp, Row1)
For C = 1 To 256
Cells(2, C).Interior.ColorIndex = Val(v(C - 1, 1))
Next C

'// When you no longer want them stored...
'DeleteSetting MyApp, Row1
End Sub


--
Dana DeLouis
Win XP & Office 2003


"Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message
news:11aoiackvoqu81f@corp.supernews.com...
>I want to store the interior color of each cell in row 1 of a worksheet in
>an environment variable so that they can be restored later on using the
>Environ function. I know I can do it one by one by setting a environment
>variable for each cell but that seems cumbersome. Is there a way to have a
>environment variable act as an array so that I can loop through the storing
>process with some sort on index?
>
> Thank you very much.
>

Thank you, Dana

I will explore that.


"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:unRMhONcFHA.3712@TK2MSFTNGP09.phx.gbl...
> Don't know if this would be an option., but a different idea might be
> to store them in the Windows Registry.
>
> Sub SaveRow1()
> Dim C As Long ' Column #
> Const MyApp As String = "MyApp"
> Const Row1 As String = "Row1"
>
> For C = 1 To 256
> SaveSetting MyApp, Row1, CStr(C), Cells(1,
> C).Interior.ColorIndex
> Next
> End Sub
>
> And at some later time, you can recall them. I 'reset' the colorindex
> of Row 2 for testing.
>
> Sub GetRow1()
> Dim C As Long ' Column #
> Dim v As Variant
>
> Const MyApp As String = "MyApp"
> Const Row1 As String = "Row1"
>
> v = GetAllSettings(MyApp, Row1)
> For C = 1 To 256
> Cells(2, C).Interior.ColorIndex = Val(v(C - 1, 1))
> Next C
>
> '// When you no longer want them stored...
> 'DeleteSetting MyApp, Row1
> End Sub
>
>
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message
> news:11aoiackvoqu81f@corp.supernews.com...
>>I want to store the interior color of each cell in row 1 of a
>>worksheet in an environment variable so that they can be restored
>>later on using the Environ function. I know I can do it one by one by
>>setting a environment variable for each cell but that seems
>>cumbersome. Is there a way to have a environment variable act as an
>>array so that I can loop through the storing process with some sort on
>>index?
>>
>> Thank you very much.
>>
>
>

Dana DeLouis wrote...
>Don't know if this would be an option., but a different idea might be to
>store them in the Windows Registry.
....

The danger of this approach is filling the Registry with nonpersistent
settings persistently. Eventually you'll slow down the system and
affect its stability if lots of such settings are added to the
Registry.

An alternative is writing setting to file in the user's HOME directory
(which is the Unix-like approach to this sort of thing), then reading
from it as needed. Much easier to delete common files in HOME
directories, and they generate no drag on the system other than using
disk storage.