Solved

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

Posted on 2012-03-11
22
300 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:anthonymellorfca
  • 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:anthonymellorfca
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
 
LVL 9

Author Comment

by:anthonymellorfca
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:anthonymellorfca
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:anthonymellorfca
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Author Comment

by:anthonymellorfca
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:anthonymellorfca
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:anthonymellorfca
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:anthonymellorfca
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:anthonymellorfca
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:anthonymellorfca
ID: 37708087
An object is an instance of a class.

nuff said! -)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now