Constants
 

I store some values in "Names"
EG Names "Site1" to "Site10" store 10 locations such as "Site1" = Bolton
"Site2" = Chorley Etc
I can retrieve them like this

Sub SetNames()
Dim Ctr as Byte
For Ctr = 1 to 10
Range("A" & Ctr) = Application.Evaluate("Site" & Ctr)
Next
End Sub

I have tried storing the values in Constants but I cannot retrieve them in
the same way as shown above. I have to write a seperate line of code for each
Constant.
Please could anyone show me a way to use a group of 10 Constants in the same
way as I have used the 10 values stored in Names.

Thankyou in anticipation
--
Roger

Constants
 

Constants are Constant. There are not meant to be changed by the software.

"Roger" wrote:

I store some values in "Names"
EG Names "Site1" to "Site10" store 10 locations such as "Site1" = Bolton
"Site2" = Chorley Etc
I can retrieve them like this

Sub SetNames()
Dim Ctr as Byte
For Ctr = 1 to 10
Range("A" & Ctr) = Application.Evaluate("Site" & Ctr)
Next
End Sub

I have tried storing the values in Constants but I cannot retrieve them in
the same way as shown above. I have to write a seperate line of code for each
Constant.
Please could anyone show me a way to use a group of 10 Constants in the same
way as I have used the 10 values stored in Names.

Thankyou in anticipation
--
Roger

Constants
 

Hi Roger

Do you mean something like the below. Insert a module and copy the code and
try. You can append more data to the string variable with a comma separator


Public Const strLocation As String = "loc1,loc2,loc3,loc4,loc5"

Sub Macro()
Dim arrLocation As Variant
arrLocation = Split(strLocation, ",")
MsgBox arrLocation(0) 'To access location1
MsgBox arrLocation(1) 'To access location2

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Roger" wrote:

I store some values in "Names"
EG Names "Site1" to "Site10" store 10 locations such as "Site1" = Bolton
"Site2" = Chorley Etc
I can retrieve them like this

Sub SetNames()
Dim Ctr as Byte
For Ctr = 1 to 10
Range("A" & Ctr) = Application.Evaluate("Site" & Ctr)
Next
End Sub

I have tried storing the values in Constants but I cannot retrieve them in
the same way as shown above. I have to write a seperate line of code for each
Constant.
Please could anyone show me a way to use a group of 10 Constants in the same
way as I have used the 10 values stored in Names.

Thankyou in anticipation
--
Roger

Constants
 

Jacob - thanks but......

if I had:-

Public Const Site1 as String = "Bolton"
Public Const Site2 as String = "Grimsby"
Public Const Site3 as String = "Chorley"
Public Const Site4 as String = "Wigan"
Public Const Site5 as String = "Bury"

How would I need to change the code below to make it work

Sub SetSite()
Dim Ctr as Byte
For Ctr = 1 to 4
Range("A" & Ctr) = "Site" & Ctr
Next
End Sub

Thanks for taking an intrest - Rog






--
Roger


"Roger" wrote:

I store some values in "Names"
EG Names "Site1" to "Site10" store 10 locations such as "Site1" = Bolton
"Site2" = Chorley Etc
I can retrieve them like this

Sub SetNames()
Dim Ctr as Byte
For Ctr = 1 to 10
Range("A" & Ctr) = Application.Evaluate("Site" & Ctr)
Next
End Sub

I have tried storing the values in Constants but I cannot retrieve them in
the same way as shown above. I have to write a seperate line of code for each
Constant.
Please could anyone show me a way to use a group of 10 Constants in the same
way as I have used the 10 values stored in Names.

Thankyou in anticipation
--
Roger

Constants
 

You cannot.

If this post helps click Yes
---------------
Jacob Skaria


"Roger" wrote:

Jacob - thanks but......

if I had:-

Public Const Site1 as String = "Bolton"
Public Const Site2 as String = "Grimsby"
Public Const Site3 as String = "Chorley"
Public Const Site4 as String = "Wigan"
Public Const Site5 as String = "Bury"

How would I need to change the code below to make it work

Sub SetSite()
Dim Ctr as Byte
For Ctr = 1 to 4
Range("A" & Ctr) = "Site" & Ctr
Next
End Sub

Thanks for taking an intrest - Rog






--
Roger


"Roger" wrote:

I store some values in "Names"
EG Names "Site1" to "Site10" store 10 locations such as "Site1" = Bolton
"Site2" = Chorley Etc
I can retrieve them like this

Sub SetNames()
Dim Ctr as Byte
For Ctr = 1 to 10
Range("A" & Ctr) = Application.Evaluate("Site" & Ctr)
Next
End Sub

I have tried storing the values in Constants but I cannot retrieve them in
the same way as shown above. I have to write a seperate line of code for each
Constant.
Please could anyone show me a way to use a group of 10 Constants in the same
way as I have used the 10 values stored in Names.

Thankyou in anticipation
--
Roger

Constants
 

Function GetSite(i as Integer)
Const SITES as string = "Bolton|Grimsby|Chorely|Wigan|Bury"
GetSite = Split(SITES,"|")(i-1)
End Function

Sub SetSite()
Dim Ctr as Integer
For Ctr = 1 to 4
Range("A" & Ctr).Value = GetSite(Ctr)
Next
End Sub

Tim

"Roger" wrote in message
...
Jacob - thanks but......

if I had:-

Public Const Site1 as String = "Bolton"
Public Const Site2 as String = "Grimsby"
Public Const Site3 as String = "Chorley"
Public Const Site4 as String = "Wigan"
Public Const Site5 as String = "Bury"

How would I need to change the code below to make it work


Thanks for taking an intrest - Rog






--
Roger


"Roger" wrote:

I store some values in "Names"
EG Names "Site1" to "Site10" store 10 locations such as "Site1" = Bolton
"Site2" = Chorley Etc
I can retrieve them like this

Sub SetNames()
Dim Ctr as Byte
For Ctr = 1 to 10
Range("A" & Ctr) = Application.Evaluate("Site" & Ctr)
Next
End Sub

I have tried storing the values in Constants but I cannot retrieve them
in
the same way as shown above. I have to write a seperate line of code for
each
Constant.
Please could anyone show me a way to use a group of 10 Constants in the
same
way as I have used the 10 values stored in Names.

Thankyou in anticipation
--
Roger

Constants
 

the only things I could think of were the same as Jacob's answer.
Reality is that this is Excel .... and if you have a table of names, then
put in into a spreadsheet. It becomes far more manageable, maintainable and
scalable.

Sub SetNames()
Dim cell as Range
dim index as long
For Each cell in Range("MySiteTable").Cells
index = index +1
Range("A" & index) = cell.Value
Next
End Sub



"Roger" wrote in message
...
I store some values in "Names"
EG Names "Site1" to "Site10" store 10 locations such as "Site1" = Bolton
"Site2" = Chorley Etc
I can retrieve them like this

Sub SetNames()
Dim Ctr as Byte
For Ctr = 1 to 10
Range("A" & Ctr) = Application.Evaluate("Site" & Ctr)
Next
End Sub

I have tried storing the values in Constants but I cannot retrieve them in
the same way as shown above. I have to write a seperate line of code for
each
Constant.
Please could anyone show me a way to use a group of 10 Constants in the
same
way as I have used the 10 values stored in Names.

Thankyou in anticipation
--
Roger