Problems using if-then in direct window

[chamster]

I'm executing
for each ws in worksheets: if (right(ws.name, 3)="(12)") then Debug.Print ws.name: next ws
in the direct window but the stupid computer claims that i'm using next without for. If i skip it, it complains that i have for without next.

I'm fairly sure that my if-statement makes the computer confised but i haven't figured out how to enter such a statement. Suggestions?

[Bob Phillips]

The If throws it, so you have to be smarter, maybe something like



For Each ws In Worksheets: Debug.Print ws.Name, Right(ws.Name, 3) = "(12)": Next ws

[chamster]

No, i need an if-statement. The above was an example only. Am i to understand that it's not doable in direct window to use if-statements?

[Bob Phillips]

I've never been able to do it, but can't say I have had that much need to try too hard.

[Charlize]

for each ws in worksheets: select case right(ws.name, 3): case "(12)": Debug.Print ws.name: end select: next ws

[chamster]

Ah, nice!

It didn't work at first but then i realized that the code has a logical error (absurdity, rather). Other than that, it was of great help. Thanks!

[rory]

You can use If statements in the Immediate window, but it seems they don't work within other loops - at least based on my testing. Haven't found any official confirmation yet.

[chamster]

I'm getting the impression of being a problam catalysator. :)

I've been working with VB for quite a while and every week or so i run into something heavy that nobody else thinks about or needs.

[rory]

You don't really need to enter multi-line statements in the Immediate window - use a test routine! :)

[Bob Phillips]

I don't think it is a question of running into something heavy, you just seem unwilling to accept the constraints of the language to me, or adapt to them.

[chamster]

I don't think it is a question of running into something heavy, you just seem unwilling to accept the constraints of the language to me, or adapt to them.

That stung. I'm under impression that i've been nothing but willing to accept a different way to accomplish things. The only thing i refuse to accept is that certain things can't be done in a language. Now, the fact that if-then-clauses aren't accepted in the direct window and i'm asking about it - that's hardly unwillingness. It's a bug report, i'd say.

For instance - the memory issue i'm having. I'm certain that there's a way to go around it but i myself have no clue how to do that. That's why i'm asking. It's hardly me being unwilling to accept/adapt.

Am i getting a little touch of prejudice here? "Since he's of C++ origin, he'll be nagging about how sucky VB is and how much freedom C++ offers... Sight..."-sort of thing? I'm nothing like that so i hope it's just a wrong impression i've had. :(

[Bob Phillips]

No prejudice here, if any I would say it is the other way around. I would direct you at the thread on the declaration and initialisation of variables. You complained that VBA doesn't do it like C++, should do it that way, and was wrong to do it the way it does. That rings of prejudice to me, and many of your posts since then are what I would describe as a complaining nature about VBA.

I have to ask, why are you using VBA, you clearly don't like it?

Personally, I come from an Algol/Assembler/Pascal/Cobol/VB/VBA/C# programming path, so I have changed many times, and just accepted they are different. I haven't used C++ as I found it too obtuse when I tried, so it was simpler to employ people who knew it already and were used to it. Maybe you should do the same with VBA.

[johnske]

it's because you're trying to do it on one line, the : for line end shouldn't be used in this case, try
Sub cc()
Dim Ws As Worksheet
For Each Ws In Worksheets
If (Right(Ws.Name, 4) = "(12)") Then Debug.Print Ws.Name
Next Ws
End Sub
or
Sub ccc()
Dim Ws As Worksheet
For Each Ws In Worksheets
If Right(Ws.Name, 4) = "(12)" Then
Debug.Print Ws.Name
End If
Next
End Sub

[Steiner]

Just to add to the above problem: according to the help file, the : in a single line if-then (= without endif) allows you, to execute more than 1 command.
So what happens is, that the next is understood as part of the then-statement and not as part of the surrounding for-loop.
So what you do here is to start a for-loop then start a if-then and try to finish the for-loop without finishing the if-then-junction before, and that's what the interpreter does not like.

Daniel

[johnske]

i might add that the colon ( : ) notation to signify the end of a code line is not conventional notation for visual basic and you're better off using standard (conventional) notation until you fully understand the limitations of any unconventional notations...

[chamster]

No prejudice here, if any I would say it is the other way around. I would direct you at the thread on the declaration and initialisation of variables. You complained that VBA doesn't do it like C++, should do it that way, and was wrong to do it the way it does.

Incorrect. I argued that the declaration part was confusing to me. I'd expect either error message (cause by redeclaration). That applies to any language - try it out in Pascal and C# too, i think.

As to other parts, i don't see my questions as complaining about VBA. That's your impression (right, wrong, i'm not discussing). However, when the stupid computer doesn't do what i wish, i will be asking why. Once an issue is solved, i'm happy and have learned something.

Besides, coming to vbax.com to complain and bitch about VBA is like going to KuKluxKlan and telling that Jesus was black. No matter who's wrong, you'll the dumbass creating the comotion. I'm sorry if anybody got that impression about me.


I have to ask, why are you using VBA, you clearly don't like it?

Shortly - because that happened to be the job i got. I'm a in statistical math and that's the tool my employer uses. And it's good thing they do so, because Excel makes the task easier.

I'd like to stress very strongly that i do not dislike VBA. Having said that, i don't like it either. It's just a tool - terribly buggy in some parts, causing excruciating pains and frustration as well as madly effective and life saving. All depending on what the issue of the day happens to be.

In developmnet time, the work i do would probably take a longer time if i used C++/Java or MatLab. So, i'm not saying it's a tool of evil. I'm just not praising it.