Excel 2010 VBA Tutorial 22 - Looping through workbook objects

[rajsoni]

great collection of tutorials...easy to follow and understand...thanks to putting it together.

[zandre]

great videos!!! you get to the core of programing language- no beating around the bush.
really looking forward to next 80 videos :)

BTW I started watching vbaisfun channel before this one and I quit- too chaotic- got really annoyed LOL

[YourprogrammingnetworkCoUk]

You can put it with or without, it is really only for your own benefit if you have a lot of nested for loops.

[YourprogrammingnetworkCoUk]

VBA will automatically know to create the variable when you use the enhanced for loop. You should however for good practice declare it beforehand, Option Explicit at the top of the code would enforce this if you wanted to be strict on yourself. Really you should for larger programs as it will make them more memory efficient.

[MafteiA]

Now you put again the name of the variable after next. How do I decide when I leave Next alone and when I add the name of the variable?
Thanks!
Great videos:)

[ranjitparmar]

Sir your tutorials are very helpful, thank you. I have a question,
I try to work in my VBA as follow
for each ws in thisworkbook.worksheets
ws.name = Range(5, 2).value
however this does not work. If i use Activeworkbook instead of Thisworkbook than it does work. I have got 42 ws which should be named. This the case for another code which i have created as well. Your quick response will be appreciated

[johnng]

Hi there, great videos! However, I don't quite understand about the wb thing? Can you elaborate more on that? Thank you

[thomasprevarin]

1. When you choose to loop through all opened workbooks, you're telling the system to save them all. Could you give an example of another action that can be applied to all opened workbooks?

2. Also, you gave example of worksheets,  workbooks and cells to be used as objects in the "looping" function, how can I find out about other objects which could be potentially used here?

By the way,  I'm off work at the moment and I'm looking to strengthen my IT skills before I start looking for the next challenge, your video tutorials are good stuff, thanks Matty!!

[liu]

Hi,
in the case of For Each Wb In...
is wb and ws just variable or does it reference to the workbook and work sheet?

[martinhenderson]

Great video, I have an excel spreadsheet that has concatenated hyperlinks to solidworks drawings. At present if I click the hyperlink in excel it opens the drawing the runs a macro to convert the drawing format to dwg. Is there a way of adding a loop to the macro so it runs through all of the hyperlinks in the column?

Any help would be greatly appreciated

[namski]

Hello, why did the counter not take the zero value initially?

[mcccalle]

Hi. If I write the code EXACTLY as you do, my Excel version complains that the variable ws is not defined.
How come you don´t get compile error about this? (I´m using Excel 2013 by the way)

[NIEBE]

Hi,

I have watched all VBA tutorials and I have to say that they are really very good.

I am trying to loop 81 combo boxes in a spreadsheet but I do not how to do it. You have used "ws" and "cl" for looping aroun worksheet and cells but I do not know how to do it for Combo boxes (activex).

Is there any way to create a combobox (activex) matrix (9, 9)? I tried to copy and pase the first but it is created with different name.

Many thanks in advance.
 

[刘思家]

Thanks! It was great! But would you please speak a bit slowly, especially during the explanation.

[asharua]

this last vba code is working but it gives all zeroes as an output. kindly help

[abhishekprasad]

Thanks for the amazing set of tutorials. Quick question on the below. I get two different results based on cl as "object" or "variant". Any reason as to why is that. My code:

Option Explicit
Sub forloopsobject3()
Dim cl As Variant
Dim counter As Integer
counter = 5

For Each cl In Selection.Cells
cl.Value = cl.Row * cl.Column
Next cl

End Sub

[abhirupsensarma]

When I am typing "cl" it is giving me a error that i need to declare cl. It will be great if you can explain this

[asharua]

Sub nlsn()
Dim counter As Integer
counter = 2
For Each cl In Selection.Cells
cl.Value = cl.Row * cl.Columns
Next cl
End Sub

[Reduce_Scan]

I need a code to do this
Assume that we want to place the cursor inside the cell manually and repeat this in the range of a1: a1000
What I want is a code that does this

[markb]

great video lots of learning achieved. thank you. for the saving of application code you did not need the counter Dim and it's various declarations. the below code is all you need.

Sub Matty()

For Each wb In Application.Workbooks
wb.Save
Next

End Sub