?
Solved

What is the proper way to use a single array

Posted on 2014-03-04
12
Medium Priority
?
121 Views
Last Modified: 2014-03-09
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)
..........................................
............................................
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 800 total points
ID: 39905638
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
 

Author Comment

by:upobDaPlaya
ID: 39906150
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
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 39906163
Hi,

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

That's what my code does

Regards
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Accepted Solution

by:
gowflow earned 1200 total points
ID: 39909565
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
 

Author Comment

by:upobDaPlaya
ID: 39914093
Rgonzo1971, if i do not loop per your suggestion, then to grab the first value then it would just be varnames(b10) ?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39914290
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
 

Author Comment

by:upobDaPlaya
ID: 39915387
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39915487
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39915495
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
 

Author Comment

by:upobDaPlaya
ID: 39915837
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39915920
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
 

Author Closing Comment

by:upobDaPlaya
ID: 39916593
Thanks, great post..I hope I was fair with the points...I wish I could give both of you 500 :)
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question