What is the proper way to use a single array

If I have a single array is the below correct ?  Do you use a redim in a single array..What is considered best practice for dimming a single array variable ?

Dim strNames () as String
Dim intR

LastRow = Sheets("Roster").Range("A" & Rows.Count).end(xlUp).row
ReDim strNames(LastRow)

For intR = 10 to LastRow
strNames(intR) = Range("B" & intR)
..........................................
............................................
upobDaPlayaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

You  could do it that way but why not using the first 10 items of the array?

or you could do it that way without loop

Dim varNames() As Variant
lastrow = Range("A" & Rows.Count).End(xlUp).Row
varNames = Range("B10:B" & lastrow).Value

Open in new window


Regards
0
upobDaPlayaAuthor Commented:
I do not understand...if the last row is at row 100 and the first row is at 10 then why would i set the array at 10
0
Rgonzo1971Commented:
Hi,

In your example you get in your array the values of B10 through B&lastRow

That's what my code does

Regards
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

gowflowCommented:
I read your post and to answer:


Do you use a redim in a single array. What is considered best practice for dimming a single array variable ?

The answer simply is what do you want ? If you have data that is variable you can use Redim to either increase or decrease the array to the extent that you want.

The advantage of Redim especially for dynamic data is that you are always guaranteed to have the Array extending to the limit of your data.

To go back to your code the answer is YES your code is correct and as long as you need the array to extend always to the last row encountered then your Redim is correct that way.

If I did not address your issue then please clarify what you need.
gowflow
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
upobDaPlayaAuthor Commented:
Rgonzo1971, if i do not loop per your suggestion, then to grab the first value then it would just be varnames(b10) ?
0
gowflowCommented:
Whhen you do this:

LastRow = Sheets("Roster").Range("A" & Rows.Count).end(xlUp).row
ReDim strNames(LastRow)


then LastRow contains the row reference of the last row in Column A. Say this value is 20 therefore by doing
Redim strNames(LastRow)

it will re dimension the Array strNames to 20

Hope clarifies.
gowflow
0
upobDaPlayaAuthor Commented:
But if I instead use the below, how do I retrieve the first value in B10 ?  Do just do varnames(b10) = ...............

Dim varNames() As Variant
lastrow = Range("A" & Rows.Count).End(xlUp).Row
varNames = Range("B10:B" & lastrow).Value
0
gowflowCommented:
ok here it is:

I corrected your lastrow to refer to Col B as if you have nothing in Col A and all the data in Col B lastrow will show 1.

Then I have a loop to show you all the data that you affected in your array it will print in your immediate window

Sub test()
Dim varNames() As Variant
lastrow = Range("B" & Rows.Count).End(xlUp).Row
varNames = Range("B10:B" & lastrow).Value

For I = LBound(varNames) To UBound(varNames)
    Debug.Print varNames(I, 1)
Next I
End Sub

Open in new window


Now to answer your question as you are affecting 1 dimentional array to varNames then from
varNames(x,y)
the x part will always be the variable part
and y = 1

So to answer specifically your question the first value you need to get from your array in this specific case B10 will be the first item therefore will be
varNames(1,1)

Hope this answers your querry.
gowflow
0
gowflowCommented:
Further to above and to be more specific you can do array manipulations with Ranges this way. Now this is the reverse to affect a Range with data from an array. If we take the previous example after you load your array say you had data in from B10 to B20 in varNames then you can get the following:

'---> Will get as much as the array contains
'ex this will only get 1 item the first in your case B10 and put it in D1
Range("D1") = varNames

'ex this will get 3 items from the first to the third B10 to B12 and put in D1 to D3
Range("D1:D3") = varNames

'ex this will get all the items
'but if the array is less than 100 then the rest of the items will show as #N/A
Range("D1:D100") = varNames

gowflow
0
upobDaPlayaAuthor Commented:
goflow..very helpful..thanks for he well explained mechanics of the array..final question..whiy do we need to declare the array as a varinat..
0
gowflowCommented:
Dim varNames
or
Dim varNames as Variant is the same

you mean why not declare it
Dim varNames() as string ?
you could do that but then you cannot affect the Range to varNames

to affect it then you need to have it as variant.

gowflow
0
upobDaPlayaAuthor Commented:
Thanks, great post..I hope I was fair with the points...I wish I could give both of you 500 :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.