0

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.

Improve this question

3 Answers 3

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

How can we refer to the cells in an array of range?
@RomcelGeluz However you want to, e.g. varBBB(3).Cells(1,1) or varBBB(x).Range(myRangeVariable) or .... It's no different to using a non-array Range object, except that it is indexed.
Oh yes! Another reason not to use a 3D array, (I used to do this to represent the sheet with 3rdD). Thanks a ton.
0

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
Improve this answer

2 Comments

Thank you for quick response Romcel.. Your code will put all workbook's sheets in the array.. I need to put only specific sheets not all of them.. these sheets are specified in the code itself .. Then I want to declare range variable with the same name varBBB each for one sheet (varBBB(1) = Sheets(First Array Element).UsedRange, varBBB(2 = Sheets(Second Array Element).UsedRange))
Updated the post. You can disregard the first set of code and iterate trough the specific array of sheets using the second set of code. You need the arrHolder() to temporarily hold the array of UsedRange.
0

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
Improve this answer

1 Comment

Thank you Rory.. When I create the sheets array like that varAAA= Array("Sheet1", Sheet2", "Sheet4") your code gives me Subscript out of range error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.