Getting all items with VBA

[excelos]

Hello

I am getting started with VBA and what I find a bit rudimentary is that for almost everything you need to write loops.

1) Is there any better way to get all within a specific object etc, i.e. all the fields of a Pivot or all the formulas of a range without having to loop?
2) If you always need to loop, what is the best practice looping in terms of efficiency and error handling etc? I think there are while loops and for loops etc, can you provide a general structure that can be used in most cases to get all the values of an object?

Thanks!

 

[6StringJazzer]

With regard to loops, virtually every programming language is like this. Yes, you need to write loops.

1. It depends on what you mean by "get". Fields of a pivot are part of a collection so you can get the whole collection easily. But then what do you want to do with it? Most of the time you will need a loop to be able to do anything with each individual value. Similar reasoning goes for formulas in a range. Range("A1:A10").Formula will give you a bunch of formulas with no loop, but you will need a loop for most things you would want to do with those formulas.

2. If you have a collection, the most efficient way is to declare a variable (let's call it V) and use it like this

For Each V In Range("A1:A10")

However, the difference in efficiency is usually not noticeable until you are dealing with many thousands of items.
For other structures, like an array, it's typical to loop it with an index.

For i = LBound(ExampleArray) To UBound(ExampleArray)

While and Until loops are for testing a condition. A For loop is better if you know before the loop starts how many times you need to loop. Use While and Until if you need to determine this dynamically.

Your question is pretty generic. I could give more specific responses if you gave a concrete example of what you would want to do.