My Biggest Frustration - The Range Object & Syntax 2

[MattGreer]

The biggest frustration I have whenever doing VBA programming is referring to other cells, rows, etc. using the Range object in VBA. Every time I need to perform some operation based off of a certain cell, or part of a worksheet. I have to google and search and beat my head against the wall to use Range correctly in VBA. I usually end up using some form of OFFSET and moving the active cell around to get what I need done. I just feel like there's probably a much more efficient, foolproof, and elegant way of doing what I do. What I do is usually make custom analysis spreadsheets in support of my project work, whether it's Project Development, tracking and analyzing costs, engineering calculations, etc. Just making templates for repeatability, consistency, accuracy.

So I have two requests for help here:

[ol 1]
[li]Where is the best tutorial out there, paid class online, book, etc. where I could learn in-depth how to use Range?[/li]
[li]If you have the time, would you be able to type up a list of challenges using the Range object, which would help me learn and/or provide for myself a list of examples to refer to in the future?[/li]
[/ol]




Thanks!!


Matt

 

[combo]

In good old days excel vba offline help was sufficient to learn excel object model and how to work with it. Things changed.
Search for "excel vba range object", you will find a lot of useful info and references. One of first finds:

https://powerspreadsheets.com/excel-vba-range-object/

If possible, avoid selecting and activating, this slows down code and does not look nice (however, you can turn off screen updating).
If you need to process a piece of data in VBA and return the result to excel, use fast exchange of data between excel and 2D variant array (works in both directions) with: VariantArray=RangeObjectReference.

combo

 

[SkipVought]

I second combo's notion to avoid selecting and activating ranges. Of course you can't and shouldn't absolutely not Select or Activate since you may want your display result to end up on the right ActiveSheet and displaying the correct ActiveCell.

Where I might differ in practice is that in most cases, where speed and efficiency is not essential, I'm gonna loop thru a range of cells and return the results relative to the loop object, rather than "use fast exchange of data between excel and 2D variant array (works in both directions) with: VariantArray=RangeObjectReference" which IS faster and more efficient, but makes it harder, for me that is, to debug, especially like you question, finding the best method of returning data to the correct cell or range.

In most cases I'm using a For Each Object...Next. The Object is a single cell range. Relative to that cell in the same table you could...
1) use Offset relative to the column or row in which the Object resides (not my particular favorite)
2) use EntireRow (or Entire Column) in conjunction with the a RangeName for a column or row within you table in a statement like this...

   sSomeTextValue = Intersect(rCell.EntireRow, [City].EntireColumn).Value

...where the Object variable is rCell. This is somewhat self documenting as well.

I'm an avid user of Range Names (using the table header names) and Structured Tables (which ALSO employs table header names a bit differently) and leverage these in my code. Most of my coding techniques hinge on Excel version 2007.

Matt, if you were to list a few of the common frequent range referencing question you might have, then you might get a few examples addressing those situations. A lot has to do with how conversant you are with various Excel features and VBA. Of course, this forum is Forum68, while VBA issues would be better addressed in Forum707. Don't ignore the FAQs that have been contributed in both forums. Might take several evenings of bedtime reading with your Excel handy for example trials. I've often done that. It all depends how "hungry" you are. You might not need a feature or technique at the moment, but having a mental reference to a possible approach or solution, could be invaluable in the future.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

SKIP! You didn't retire! Glad you're still around. And combo too of course. Both y'all have helped me on many occasions.

Sorry I missed the VBA forum! I'm usually better at choosing where to post but in this case I goofed and went to what appears to be the defacto Excel forum. I'm curious, have you ever thought about starting an Excel forum here?

Thanks!!


Matt