Ifs, Selects & Loops Error Statements

Ifs, Selects & Loops Error Statements

Post by RFM » Wed, 24 May 2006 21:48:01

Currently, if you have a macro which contains a number of "closed"
statements, such as IF, SELECT CASE, DO...LOOP etc, the VBA error-detection
system tends to be a little random in its identification of where the error
lies if one or more of the statements aren't closed. e.g. if you have three
"Select Case" statements within an "IF", and one of the "Select Case" isn't
closed with an "End Select" - VBA will frequently tell you that there is an
"Block If without End If", rather than a "Block Select without End Select"
which would make things easier to locate the bug in question. Especially as,
when you hit "debug" to the error, the highlighted area is the mistaken
location rather than the actual error area.


Sub Whatever()

hrng = 1

If 1 > 0 Then

Select Case hrng
Case 1
MsgBox "Yo!"
Case 2
MsgBox "uh-huh"
Case 3
MsgBox "Whoa!"

End If

End Sub

In this instance, the Select is not closed. When attempting to run, however,
the error message received is an "End If Without Block If" Compile Error, and
the "End If" is higlighted, rather than the "Select" which is not closed.

Obviously, the example above is very basic, but this would prove very useful
when there are hundreds upon hundreds of lines of code containing multiple
closed statements, and the current process reduces me to a wreck trawling
through line by line looking for an unclosed statement!

or I could just learn to always close, but that would be far too easy.... =:-)

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.


1. New IFS interface (jfs.ifs, udf.ifs for example)

2. Howto embed select statements into other select statements


I want to calculate the fraction of two counts and I have created the
following select statement:

(select table1.co/table2.co
from (select count(1) co, id from t_table1 where seal>=1000 group
by id) table1,
(select count(1) co, id from t_table2 group by id) table2
where table1.id=j.id and table2.id=j.id),
from t_table3 j

This works as long as there are corresponding entries in t_table1 and
t_table2, otherwise the fraction becomes null, which might make sense
when there is nothing in t_table2 but not when t_table1 has no
matching entries.

Instead I have tried:

(select table1.co/table2.co
from (select count(1) co, id from t_table1 where seal>=1000 and
id=j.id) table1,
(select count(1) co, id from t_table2 where id=j.id) table2),
from t_table3 j

But then the database (Oracle 10) complains that it can not find j.id.
How can I write this select correctly?

Best regards
Niels Dybdahl

3. Use results from a select statement in a follow-up select statement in a SP

4. Need help w/ SELECT statement within a SELECT statement

5. Correct syntax for SELECT statement within a SELECT statement.

6. select statement into if statement in an event code (syntax error

7. Loop though select statement

8. Loop through SELECT Statement

9. select and update statements in a while loop is not working proper

10. Does a cursor re-execute the select statement everytime it loops?

11. How to use do while or any loop in select statement ?

12. Loop a select statement?

13. changing database for select statement inside a while loop

14. Loop an SQL SELECT statement

15. reading field from select statement into variable (loop)