Using an Array in Environment Variable

  • Thread starter Thread starter Jack Gillis
  • Start date Start date
J

Jack Gillis

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.
 
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
 
Thank you, Dana

I will explore that.


Dana DeLouis said:
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
Click to expand...
 
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.
Click to expand...
....

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.
 
Back
Top