CODEWITHSUNDEEP
arraysexcelvba
Tornado168
Tornado168

Reputation: 465

vba - Declare variables from elements of a dynamic array

I need to declare variant variables from the elements of a dynamic array.

I have an array (arrWS) which contains the names of specific sheets which is populated manually, and I want to declare "N" range objects where "N" is the number of worksheets.

Sequence: 1- Declare an array containing some worksheet names ==> varAAA = Array("Sheet1", "Sheet3", "Sheet4", ...)

2- Get the number of elements in this array

3- Then like that if possible:

Dim varBBB(1 To N)
For i = 1 to N
    varBBB(i) = Sheets(varAAA(i)).UsedRange
Next N

Thank you so much in advance.

Upvotes: 0

Views: 225

Answers (3)

Rory
Rory

Reputation: 34075

You could do this:

Dim varBBB()
Redim varBBB(LBound(varAAA) to UBound(varAAA))
For i = LBound(varAAA) to UBound(varAAA)
    varBBB(i) = Sheets(varAAA(i)).UsedRange
Next i

Upvotes: 0

YowE3K
YowE3K

Reputation: 23994

Your main problem is that you must Set object variables:

'1- Declare an array containing some worksheet names
' ==> varAAA = Array("Sheet1", "Sheet3", "Sheet4", ...)
Dim VarAAAA
varAAA = Array("Sheet1", "Sheet3", "Sheet4")

'2- Get the number of elements in this array
Dim N As Long
N = UBound(varAAA) - LBound(varAAA) + 1

'3- Then like that if possible:
'Dim varBBB(1 To N)
Dim varBBB() As Range
ReDim varBBB(1 To N)
'For i = 1 to N
For i = 1 To N
'varBBB(i) = Sheets(varAAA(i)).UsedRange
    Set varBBB(i) = Sheets(varAAA(i - 1 + LBound(varAAA))).UsedRange
    'Confirm that ranges have been correctly set
    Debug.Print i, varAAA(i - 1 + LBound(varAAA)), varBBB(i).Address
'Next N
Next

Upvotes: 1

Romcel Geluz
Romcel Geluz

Reputation: 603

Maybe you are looking for something like,

Dim arrWS() As String
Dim iLoop As Integer

With ThisWorkbook
  ReDim arrWS(1 To .Sheets.Count)
  For iLoop = 1 To .Sheets.Count
    arrWS(iLoop) = .Sheets(iLoop).Name
  Next
End With

then you can use Ubound(arrWWS) to get the last index of the array.

You can now use this code to store each usedrange in the sheet,

Dim arrBB() As Variant
Dim arrHolder() As Variant

  ReDim arrBB(1 To UBound(arrWS))
  For iLoop = LBound(arrWS) To UBound(arrWS)
    arrHolder() = Sheets(arrWS(iLoop)).UsedRange.Value
    arrBB(iLoop) = arrHolder()
  Next
End Sub

Upvotes: 0

Related Questions