While ... Wend
 

I greet all

I have problem.

I would like that my code acted as follows:

If user wrote some Text (but only in the odd columns) and if this Text is
incorrect with conditions, display MsgBox and written text becomes
deleted.... but my loop acted to many times....

where is mistake?

Pozdrawiam,

Andrzej



Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As String
Dim k As Integer

x = Target.Value
k = 1
' blokada wpisu niew³a¶ciwej nazwy cechy do zak³adki
While k < 254
If Not Application.Intersect(Columns(k), Target) Is Nothing Then

If x <= "NGD" Or x = "NGN" Then
MsgBox " Incorrect data !!! " & Chr(10) & _
Chr(10) & " Try again "
Target.Value = ""
End If
End If

k = k + 2
Wend

End Sub

While ... Wend
 

I did not read all the code, but it seems there's a first (big)
problem: when you change something with this line:
Target.Value = ""

then, this line change the value of one cell,
so (it's wanted) the CHANGE event is called again !

Because your tests delete some cells, and an empty cell does not change
it anymore the second time, the loop is not infinite. But this kind of
loop may be infinite sometimes.

YOU MUST stop events while changing cells in the change_events macro

Private Sub Worksheet_Change(ByVal Target As Range)
application.enableevents = false ' STOP EVENTS
Dim x As String
Dim k As Integer

x = Target.Value
k = 1
' blokada wpisu niew³a¶ciwej nazwy cechy do zak³adki
While k < 254
If Not Application.Intersect(Columns(k), Target) Is Nothing Then

If x <= "NGD" Or x = "NGN" Then
MsgBox " Incorrect data !!! " & Chr(10) & _
Chr(10) & " Try again "
Target.Value = ""
End If
End If

k = k + 2
Wend
application.enableevents = true 'REACTIVATE EVENTS
End Sub

While ... Wend
 

also take care with this line
x <= "NGD" Or x = "NGN"

may be interpreted as
( ( (x <= "NGD") Or x) = "NGN")

so with this in VBA it's better to write
((x <= "NGD") Or (x = "NGN"))

While ... Wend
 

Thanks

Pozdrawiam,

Andrzej

Uzytkownik "abcd" napisal w wiadomosci
...
also take care with this line
x <= "NGD" Or x = "NGN"

may be interpreted as
( ( (x <= "NGD") Or x) = "NGN")

so with this in VBA it's better to write
((x <= "NGD") Or (x = "NGN"))

While ... Wend
 

No. That line is completely unambiguous. From VBA Help:

Operator Precedence

When expressions contain operators from more than one category,
arithmetic operators are evaluated first, comparison operators are
evaluated next, and logical operators are evaluated last.

So the comparisons will always occur before the Or.

For readability, it may be helpful to use parens, but not for how it's
interpreted by VBA.



In article ,
abcd wrote:

also take care with this line
x <= "NGD" Or x = "NGN"

may be interpreted as
( ( (x <= "NGD") Or x) = "NGN")

so with this in VBA it's better to write
((x <= "NGD") Or (x = "NGN"))

While ... Wend
 

I'm saying in some cases it's better to use it this way
(do you really check the precedence each time ?
for + and * we know
for or and and only few people think about it each time

using () is readable and also sure

say what you want: I had this kind of surprise once ! So the advice is
(I think) still a very good one !

While ... Wend
 

OK.

Personally, I don't check precedence each time because VB's precedence
is both clear and pretty standard, so I don't need to.

I interpreted your post as a warning that the code would be variably
interpreted by the compiler, which is clearly false.

I certainly can't disagree that your advice would be useful for new or
infrequent coders who don't know the language.


In article ,
abcd wrote:

I'm saying in some cases it's better to use it this way
(do you really check the precedence each time ?
for + and * we know
for or and and only few people think about it each time

using () is readable and also sure

say what you want: I had this kind of surprise once ! So the advice is
(I think) still a very good one !

While ... Wend
 

I interpreted your post as a warning that the code would be variably
interpreted by the compiler, which is clearly false.

it's not what I whish to say.

you already said it: "I do not check it each time... because I know it
very well the precedence" - that's the point. To check it or to be
confident with it. That is _not_ the question: because we also have a
"don't care" way.

I do not remember what was my mistake the day I had this problem. I know
the precedence, but it still brings me to a mistake. I'm saying that
habits (good ones) gives this "don't care" way.

A mistake _may_ happend... to be ion the habit of unsing more "()" than
needed make me use my "thinking time" on needed things only.
(not on the syntax).

My example was maybe bad to your eyes: sorry
you think I said "<" comes before "and"
I know: it's what i wrote.

but my thinking was "sometimes you may have some surprises" because 1-
not every one knows the precedence (me, i do. Not every one) 2- a
mistake happend, even when you know precedences.

So my advice was: "using () is a better habbit" it's better using it

i hope this way of saying is better. Now, each one will choose to
follow or not this advice. Once again, this way you will never have
problem, or you will identify it quickly. That's my purpose: not wasting
time with syntax.