Constants

[Roger]

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

 

[Joel]

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

 

[Jacob Skaria]

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

 

[Roger]

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

 

[Jacob Skaria]

You cannot.

If this post helps click Yes

 

[Tim Williams]

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

 

[Patrick Molloy]

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