Simple code lines failing; error 9 confusion, For without Next...with next

[mikecox39]

I am testing; in the Immediate pane of the VBE, some of the example code in a book I am studying; in order to learn VBA.

Here are a couple that fail every time.

[/URL][/IMG]

I checked the Help option for error 9 but I didn’t understand it.

I’ve double checked the code; to make sure I entered it exactly as it is in the text, but it fails every time.

[/URL][/IMG]

This one has a "for" so why am I getting this error? Again, I double checked the example in the text and I have it written exactly as shown in the text.

Others, like “…comments.count” and “…Parent.address” work just fine.

 

[mikecox39]

In cell A2, I inserted comment "Test" then ran the following:

Sub TestComment()

Dim cmt As Comment

For Each cmt In ActiveSheet.Comments
MsgBox cmt.Text
Next cmt

End Sub

No, the text example didn't show that; or I missed it somehow.

Here it the text: am I missing something?

[/URL][/IMG]



I see from your post what code tags are; and now I realize that "tags" are simply the bracketed text; like Quote with the "/": end.

So here is the code I typed in, exactly as shown in the text; which I entered into the Immediate pane in the VBE.

For Each cmt in Activesheet.comments
    msgbox cmt.text
Next cmt

Have you declared your variable cmt as type Comments and not Comment or something else?

When I saw cmt I thought it was code for Comment. But I wasn't thinking about declaring a variable; because I haven't gotten to that option in the text; yet.

So I didn't change the code presented in the text because I assumed it was correct as shown.

If that is not the case then the example in the text is not correct; which means the reference is not a reliable resource. That is what I want to establish here! I can't keep getting hung up in this effort with examples that don't work and which I have to stop and work out why.

ps Are the video adds that keep popping up on my screen part of this forum or there because of my browser? They are driving me crazy! I have to mute the sound when I am here, to avoid being startled by these loud, distracting, adds.

 

[mikecox39]

mikecox39,

Training / Books / Sites as of 1/30/2014

Where do I find this path to the sites; and your list?

 

[hiker95]

mikecox39,

Where do I find this path to the sites; and your list?

There are links below some of the displayed text in my reply #4, like this (just click on the link):

There are over 1800 Excel videos/tutorials here:
http://www.mrexcel.com/articles.shtml

 

[mikecox39]

mikecox39,

There are links below some of the displayed text in my reply #4, like this (just click on the link):

Yes, I didn't realize they were links until I rolled over them; and I've already ordered a book and dl'd bonus files

Thanks for posting all these, I have bookmarked the reply for future reference.

There are over 1800 Excel videos/tutorials here:
Excel Tutorial | Excel Tips | Excel Articles

And thanks for this one too!

 

[Norie]

mike

There are some things you can't do in the immediate window.

 

[mikecox39]

mike

There are some things you can't do in the immediate window.

That would be kind of axiomatic.
Is the code I am having trouble with one of them?

 

[Norie]

Well I suppose the obvious one is the multi-line For/Next code.

 

[shg]

In the Immediate window:

For Each cmt in Activesheet.comments: msgbox cmt.parent.address & " " & cmt.text: Next cmt

 

[mikecox39]

Well I suppose the obvious one is the multi-line For/Next code.

So... all that to say I should always put the code in the Immediate pane on a single line?

It would have been more efficient if you had just said that in the first place; instead of just stating the obvious.

I really don't mean to get all snarky here; because I really appreciate the help, but why not be clear from the outset?

 

[Norie]

I thought I was being clear, you don't use the Immediate windows for some things.

You can use it for multiline code blocks if you use the method shg posted, but I don't really see the point.

Why not stick the code in a temp sub?

Then you can loop through it (F8), examine the variables (Watch/Locals windows), set breakpoints, compile the code...

The way I see the Immediate Window is kind of like using a command prompt.

PS 'all that'? My posts weren't exactly verbose.