Reading a Range to an Array

[Kevin H. Stecyk]

Hi,

I am doing something wrong in trying to read a range from a spreadsheet into
an array. Below are the relevant portions of my code. I have omitted the
extraneous stuff.

Sub UpdateSheet()
'\Three string names
Dim sSpreadShtNameRng As String '\Range Name1
Dim sPwdNameRng As String '\Range Name2
Dim sWriteResPwdNameRng As String '\Range Name3

Dim vaSpreadShtName As Variant
Dim vaPwd As Variant
Dim vaWriteResPwd As Variant

Dim iCounter As Integer

sHiddenFileName = "Hidden"

'\ 3 range name are on the sheet "Hidden"
'\ All ranges are local..but I tried global
'\too and it failed.

sSpreadShtNameRng = "xrnSpreadShtNameCC" '\Range Name1
sPwdNameRng = "xrnPwd1CC" ' \Range Name2
sWriteResPwdNameRng = "xrnPwd2CC" '\Range Name3


'\Activate Hidden sheet.
Worksheets(sHiddenFileName).Activate

'\ I think below is where I am going wrong...

vaSpreadShtName = Range(sSpreadShtNameRng).Value
vaPwd = Range(sPwdNameRng).Value
vaWriteResPwd = Range(sPwdNameRng).Value

'\ Below I get iCounter=1, but the next step yields an error message
'\ Run-time error: 9, subscript out of range.

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter

End Sub


Can you see my error(s)? And if so, can you please shed some light on this
matter for me.

Thank you.

Regards,
Kevin

 

[Lonnie M.]

Keven, try one of the two examples, be sure to at least use parenthese
when declaring the array:

Dim sSpreadShtNameRng() As String '\Range Name1
Dim sPwdNameRng() As String '\Range Name2
Dim sWriteResPwdNameRng() As String '\Range Name3

ReDim Preserve vaSpreadShtName(1 To 5) 'for dynamic (1 To
VarriableHere)
ReDim Preserve vaPwd (1 To 5)
ReDim Preserve vaWriteResPwd(1 To 5)

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter



OR...

Dim sSpreadShtNameRng(5) As String '\Range Name1
Dim sPwdNameRng(5) As String '\Range Name2
Dim sWriteResPwdNameRng(5) As String '\Range Name3


HTH--Lonnie M.

 

[Paolo De Laurentiis]

Lonnie, Kevin,

I tried to follow your indications with following code, but I've got the
same runtime error 9: subscript out of range.

Dim arr() As Variant
Dim i As Integer
ReDim Preserve arr(1 To 30)
arr() = ActiveSheet.Range("A1:C30").Value
For i = 1 To UBound(arr)
Debug.Print arr(i)
Next i

Moreover, if the range actually contains less or more than 30 cells (let's
say 2) then the array changes its dimensions (LBound and Ubound correctly
say respectively 1 and 2), but I cannot access any of the two items and got
the error.

Any other clue?

Paolo



--
Paolo
Milan, Italy


NOTE: remove QUESTONO from my email address for direct emailing

 

[Kevin H. Stecyk]

Lonnie M. wrote...

Keven, try one of the two examples, be sure to at least use parenthese
when declaring the array:

Dim sSpreadShtNameRng() As String '\Range Name1
Dim sPwdNameRng() As String '\Range Name2
Dim sWriteResPwdNameRng() As String '\Range Name3

No, I don't think I should not be putting() because these are simply
strings. They are not an array It is a string, which is the name of the
range on Hidden spreadsheet which contains 5 values per range.

ReDim Preserve vaSpreadShtName(1 To 5) 'for dynamic (1 To
VarriableHere)
ReDim Preserve vaPwd (1 To 5)
ReDim Preserve vaWriteResPwd(1 To 5)

I tried this but I got the same error message.

Run-time error 9, subscript out of range. I wished it worked. Then my
problem would be solved.

OR...

Dim sSpreadShtNameRng(5) As String '\Range Name1
Dim sPwdNameRng(5) As String '\Range Name2
Dim sWriteResPwdNameRng(5) As String '\Range Name3

There is only one sSpreadshtNameRng, which is xrnSpreadShtNameCC. There are
not five of them. So I don't think this is my trouble spot here in that I
need to create an array.

I must be doing something that is wrong, but I am not sure what it is.

Thank you for trying.

Regards,
Kevin

 

[Lonnie M.]

Hi, Tom Ogilvy posted a similar example to what you are trying to do:

Dim vArr as Variant
vArr = Range(Cells(1,1),Cells(1,1).end(xldown))
for i = lbound(varr,1) to ubound(varr,1)
debug.print i, varr(i,1)
Next


You must pick up a range as a variant variable, which will then contain
the
array.


The array is always two dimensional, even for a single column.


for a single row it would be


Sub Tester5()
Dim vArr As Variant
vArr = Range(Cells(1, 1), Cells(1, 256).End(xlToLeft))
For i = LBound(vArr, 2) To UBound(vArr, 2)
Debug.Print i, vArr(1, i)
Next
End Sub

HTH--Lonnie M.

 

[Paolo De Laurentiis]

I've got it!

I've written a sample sub here below, you can re-adapt it for every
situation.
The fact is that when you assign the range to the array, no matter the
declaration, VBA create a bi-dimensional array ..... very simple:
Arr(rownumber, colnumber)
It doesn't seem to create a third dimension for multi area ranges, although,
but that is easily solved with a loop.

Sub ProvaArray()

Dim arr() As Variant
Dim i As Long
Dim k As Long
Dim a As Long

For a = 1 To Selection.Areas.Count
arr() = Selection.Areas(a).Value

For k = 1 To UBound(arr, 2) 'columns count
For i = 1 To UBound(arr, 1) 'rows count
Debug.Print arr(i, k)
Next i
Next k
Next a
End Sub

Let me know if it works also in your case.

Paolo

 

[Lonnie M.]

Dim sSpreadShtNameRng() As String '\Range Name1

Dim sPwdNameRng() As String '\Range Name2
Dim sWriteResPwdNameRng() As String '\Range Name3

My appologies, I grabbed the wrong variables, copy paste thing...

Dim vaSpreadShtName() As Variant
Dim vaPwd() As Variant
Dim vaWriteResPwd() As Variant

ReDim Preserve vaSpreadShtName(1 To 5)
'for dynamic (1 To VarriableHere)
ReDim Preserve vaPwd (1 To 5)
ReDim Preserve vaWriteResPwd(1 To 5)

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter

OR...
'using 5 based on the loop to load the array
Dim vaSpreadShtName(5) As Variant
Dim vaPwd(5) As Variant
Dim vaWriteResPwd(5) As Variant

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter

HTH--Lonnie M.

 

[Kevin H. Stecyk]

Paolo De Laurentiis wrote...

I've got it!

I've written a sample sub here below, you can re-adapt it for every
situation.
The fact is that when you assign the range to the array, no matter the
declaration, VBA create a bi-dimensional array ..... very simple:
Arr(rownumber, colnumber)

Bingo!!! You got the correct answer. Once I put the second argument in,
everything was solved. It requires a two dimensional array.

Thank you so very much!!

Best regards,
Kevin

 

[Kevin H. Stecyk]

Hi Lonnie,

Please see Paolo's message. I tried your solution to no avail. It requires
a two dimensional array, even if the array I am picking up is only one
dimension.

Thank you for your efforts. We all learned something.

Best regards,
Kevin

 

[Bob Phillips]

Kevin,

You are picking up a 2D array, so you need to index both dimensions.
Assuming it is a columned range, you would use


For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter,1)
Debug.Print vaPwd(iCounter,1)
Debug.Print vaWriteResPwd(iCounter,1)
Next iCounter


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Kevin H. Stecyk]

Bob Phillips wrote ...

Kevin,

You are picking up a 2D array, so you need to index both dimensions.
Assuming it is a columned range, you would use


For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter,1)
Debug.Print vaPwd(iCounter,1)
Debug.Print vaWriteResPwd(iCounter,1)
Next iCounter


--

Bob,

Thank you so very much. Once I saw the answer, it made obvious sense. But
I needed to see the answer first.

Best regards,
Kevin