Declaring variables programmatically

  • Thread starter Thread starter Herman
  • Start date Start date
H

Herman

Hello,
In some procedure, I use an array lilke my Array =
(100,160,200,280,290,.........560), some 50 numbers in all.
Furthere down the sub I need to create Ranges like R100, R160, R200,
R280.....R560, based on the numbers in the array.

Is there any smart way to do this without having to write 50 range
declarations and then 50 Set-commands manually?
Thank you very much for any help!
Herman
 
You don't have to use individual variables for the range names. You could use a
second array:

Option Explicit
Sub testme()

Dim myArr As Variant 'array of longs
Dim myRanges() As Range 'array of ranges
Dim iCtr As Long 'looping variables

myArr = Array(100, 160, 200) 'just 3

'make myRanges the same size as myArr
ReDim myRanges(LBound(myArr) To UBound(myArr))

'I'm not sure what you're doing here, though:
For iCtr = LBound(myRanges) To UBound(myRanges)
'Set myRanges(iCtr) = somerangebasedonmyarr
'maybe...
Set myRanges(iCtr) = Worksheets("Sheet1").Range("R" & myArr(iCtr))
Next iCtr

End Sub


=========
Another option would be to look at the Type statement in VBA's help:

Option Explicit
Type myType
myNum As Long
myRng As Range
End Type
Sub testme()

Dim myArr(1 To 3) As myType
Dim iCtr As Long

myArr(1).myNum = 100
myArr(2).myNum = 160
myArr(3).myNum = 200

For iCtr = LBound(myArr) To UBound(myArr)
'still not sure what you're doing here
Set myArr(iCtr).myRng _
= Worksheets("Sheet1").Range("R" & myArr(iCtr).myNum)
Next iCtr

End Sub
 
Back
Top