Solved

For Each ArrayStr In RangeToSum - how does "For" know ArrayStr is each cell?

Posted on 2012-03-11
22
319 Views
Last Modified: 2012-03-11
Novice question:

I am perplexed about how it is that the arbitrarily (meaning not apparently connected to anything at all) dimensioned "Dim ArrayStr As Range" is recognised by the "For Each" loop as being each following cell in RangeToSum.

How does VBA know this?
Clearly there is some assumed logic derived from how VBA itself is written in the underlying language; but what is it?

Public Function SumVlookups(ByVal Lookupvalue As Single, ByVal RangeToSum As Range) As Single

    Dim ArrayStr As Range               
       
    For Each ArrayStr In RangeToSum
       
       If Not IsError(Application.VLookup(Lookupvalue, Evaluate(ArrayStr.Value), 1, False)) Then SumVlookups = SumVlookups + Application.VLookup(Lookupvalue, Evaluate(ArrayStr.Value), 2, False)
              
      Next ArrayStr
 
End Function

Open in new window


I can't help wondering if the "For Each" line is read right to left (backwards), we have a range that contains a series of cells (being the definition of a range) and these cells are named whatever name had been given after the words "For Each", and from there we work further backwards to dimension it with a datatype - or in this case an expected datatype.. kinda, as it's a range and that's not a datatype is it? It's an object is it not?

Anthony
0
Comment
Question by:Anthony Mellor
  • 12
  • 10
22 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37707944
The range class has an enumeration sub built into it that allows the For Each.  If you create your own class, you would have to add that enumeration routine as well to allow for For Each enumeration through a collection.

There is not need to WONDER what direction the For Each traverses.  Try it out and see for yourself:

Sub testEnumeration()
Dim r As Range


    For Each r In Range("A1:B30")
        Debug.Print r.Address
    Next r
End Sub

Open in new window

0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37707953
what's an enumeration thinghy?

ok so it's a class, which is not an object and not a datatype. I remember now, Dim can dimension a number of things.

I meant reading it as a human, not so much as a computer.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37707961
See online definition:  to Enumerate means to list or catalog.  

Here's an example of a sub in a class I created that allows enumeration through the collection that's part of that class:

Function NewEnum() As IUnknown
    ' Enables enumeration of the clsParent parent collection, i.e.:
    '
    ' For Each Child In Parent...Next
   
    Set NewEnum = oCol.[_NewEnum]

End Function

PS - you are not always guaranteed to get out of an enumeration data in the same order that you put it in.

I think with the Range object, its always by row, then left to right, however, when order is important (e.g., processing bottom up, or right to left or whatever) , I usually use indexes and directly address the ranges I want.  Most uses of enumeration that I've done/witnessed, order was not important - just getting the data out and dealing with it.

Dave
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37707972
yes I found this while you were typing:

An enumeration is a series of constant integers that each has a specific position in the list and can be recognized by a meaningful name.
To create an enumeration, you use the Enum keyword, followed by the name of the enumeration, press Enter, and end with End Enum
:

So where the above talks about writing it, it is included in the range class as standard (with numbers not names presumably)
So that's why no counter is needed, or that's the counter used by For Each, which presumably is derived from the property of the range? The rows property? or maybe the number of cells property if there is one..

PS - you are not always guaranteed to get out of an enumeration data in the same order that you put it in.

my guess is that holds true when the range is not a simple column of cells, because a different shaped range might have its integers assigned differently compared to the order we feed in? In fact I think you just said that.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37707984
>>So where the above talks about writing it, it is included in the range class as standard (with numbers not names presumably)

Yes, the Enum capability is part of the range class.  If you didn't have this you'd have to iterate by using indexes.

>>So that's why no counter is needed, or that's the counter used by For Each, which presumably is derived from the property of the range? The rows property? or maybe the number of cells property if there is one..
If there's a counter (likely) its not exposed to you for your use when you use the For Each.

>>my guess is that holds true when the range is not a simple column of cells, because a different shaped range might have its integers assigned differently compared to the order we feed in?

I just stated it as a general warning.  Some collections you may have exposed to you may sort themselves as you put them in, others, like dictionary objects (are a lot like collections) certainly aren't guaranteed to come out as you put them in due to their seek technique to find an object in a dictionary.

I think you're safe with the Range class - by row, then by column within row (left to right) but wanted to get that disclaimer out there for you to check before just assuming ;)

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37707986
Why don't you type the word "each" in VBA Editor and then click on the word and hit F1?

Dave
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37707987
so is the For Each looking for a local range from which to use the enumerated integers and so the name used must have been dimensioned, or is it implied that the characters used are a Range class? What if the name is dimensioned as something else? crunch?

edit: just seen above..
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37707993
regrettably my f1 key deletes the word each - Mac, have to find the relevant key... can't just see it, have to search. Can't be far away.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37707994
Dim r as range
Dim rng as range 'could be Object or Variant, but well defined as Range

  Set rng = Range("A1:B500")

  For Each r in rng
     'do something
  next r

Open in new window

>>so is the For Each looking for a local range from which to use the enumerated integers and so the name used must have been dimensioned, or is it implied that the characters used are a Range class? What if the name is dimensioned as something else? crunch?

THIS IS NOT LOOKING AT ENUMERATED INTEGERS.  It is looking at an Enumerated Range - from A1 to B500 (above example).

the r in the for each is a Range object.

if you want to assess integers in the range A1:B500, then you might use:
Dim r as range
Dim rng as range 'could be Object or Variant, but well defined as Range

  Set rng = Range("A1:B500")

  For Each r in rng
     'do something with r.value 'not r, but r.value (however, FYI .Value is the default property)
  next r

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
ID: 37707996
Think of a collection as a list, catalog or bucket of something.  The reason you use For Each is to go through that list and generally do something to every item in that list.  The order then is not important.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37708000
For each DUMMY in myCollection
  'do something with DUMMY
Next DUMMY

The focus here is myCollection.  I want to get one from the "bucket" and do something with it.  

DUMMY has no meaning until it pulls from the "bucket"

make sense?

Dave
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37708007
Sure ok, so where is the link between r and rng ? I can't see where it says rng is full of r's
r cound be full of rngs for all that vba appears to have been told.

edit:immediate above just seen
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37708016
No, its very similar to a regular integer indexing loop.

e.g.,

Dim i as integer
   for i = 0 to 100

dim r as range

  for each r in Range("A1:B500")

in the first part i is assigned 0, then 1, then all the way to 100

in the second, r is set to Range("A1"), then Range("B1"), then Range("A2"), etc.

Dave
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37708027
yes I understand, the counter is hidden, but based on the number of cells in the range and implemented .. actually as the "names" mentioned in the definition I found above, in so far as the cell references can be viewed as names since they are clearly not integers.

so same question again, (a bit dim here), you do this:

dim r as range

how does For Each know that all those r s are in Range("A1:B500")?

Anthony
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37708029
Your original question:

>>I can't help wondering if the "For Each" line is read right to left (backwards), we have a range that contains a series of cells (being the definition of a range) and these cells are named whatever name had been given after the words "For Each", and from there we work further backwards to dimension it with a datatype - or in this case an expected datatype.. kinda, as it's a range and that's not a datatype is it? It's an object is it not?

Ok - kind of.  The last term in the For Each is the focus/bucket/collection

Those cells are NOT named, but the For Each rObj in Range().  The rObj is assigned (or in this case, SET) to an item in the Range()

The datatype in this example of rObj must be an object of type Range.  It can be dimensioned properly as Range, or Object (which is generic) or variant (which is inefficient but works, too)

Range IS a datatype.

From help on Dim (dimension/declaration):
Optional. Data type of the variable; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, a user-defined type, or an object type. Use a separate As type clause for each variable you declare.

Range is an object type.  The word Range lets Excel know what type of object you've declared and as a result, you have intellisense support for it.

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37708033
>>dim r as range

>>how does For Each know that all those r s are in Range("A1:B500")?

r is a dummy varaible.  You're just asking Excel to give you all the items, one by one (hence, For EACH) that are in Range("A1:B500").  it gives them to you by setting r to each one as it goes through the loop.

Just as in

for i = 0 to 100

excel sets i (small case set;) to each of 0 thru 100

Dave
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37708047
I seem to think i have read a warning that a range can be both an object and a ... a class i think and causes confusion with beginners (hi!) . Hence you "kinda" after quoting my para 1.
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37708053
aha,... so we set up r and dim it.

then we use FOR each as the messenger to tel the collection to use the name we set aside for the purpose, for each of the items as they are processed.

so to go again: we set aside a variable as... as what (a range yes) an object or a class?

and then we allocate that variable to the collection for use as a name as it loops through, allowing us to use that name as much as we like in our programming of the loop, because without that name it would be all done internally and we would have no means of referring to it.

any closer?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37708056
that's pretty good.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37708057
A class defines how an object behaves.

here's a good discussion  I think that may shed some light:

http://forums.techarena.in/software-development/1131896.htm

Dave
0
 
LVL 9

Author Closing Comment

by:Anthony Mellor
ID: 37708059
bull's eye.

QUESTION:
I am perplexed about how it is that the arbitrarily (meaning not apparently connected to anything at all) dimensioned "Dim r As Range" is recognised by the "For Each" loop as being each following cell in Range("A1:B500").

ANSWER
r is a dummy variable.  You're just asking Excel to give you all the items, one by one (hence, For EACH) that are in Range("A1:B500").  it gives them to you by setting r to each one as it goes through the loop.
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37708087
An object is an instance of a class.

nuff said! -)
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Multiple Open Excel Spreadsheets 12 39
Data in Rows to be converted into single row 9 39
Calculate number of nights between two dates 5 18
If help 9 41
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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