Assignment Vs Condition

[Aussiebear]

I recently came across this explanation and was wondering if and when you would use the 6th statement b=6=5?

Using Equals	Statement Type	Meaning
Loop until X = 5	Condition	Is X equal to 5
Do Until X = 5	Condition	Is X equal to 5
If X = 5 Then	Condition	Is X equal to 5
For X = 1 to 5	Assignment	Set value of X to 1, then 2 etc.
X = 5	Assignment	Set the value of X to 5
b = 6 = 5	Assignment & Condition	Assign b the result of Condition 6 = 5
X = MyFunc(5,6)	Assignment	Assign X to the value Returned from the Function

Can someone give a good example of when you would use this statement?

[georgiboy]

That will return a Boolean, in the example above that would return FALSE as 6 does not equal 5.

You would use this for doing a comparison and returning TRUE/ FALSE.

Take the below for example, in the first part of the code: 6 and 5 don't match so you get a FALSE
In the second part 5 and 5 do match so you get a TRUE:

Sub test()
    Dim b As Boolean
    
    b = 6 = 5
    Debug.Print b
    
    b = 6 = 6
    Debug.Print b
End Sub

[Aflatoon]

Really it's just shorter than doing something like this:

if 6 = 5 then
   b = True
Else
   b = False
end if

[Paul_Hossler]

I recently came across this explanation and was wondering if and when you would use the 6th statement b=6=5?

Can someone give a good example of when you would use this statement?

A 'Good Example'? .. No

While the other posters are correct about how the computer's evaluation order would handle it, I don't think that as written it's clear to the reader

At a bare minimum, parens would make it a little clearer

b = (6=5) giving b = False

[Aussiebear]

I've had to go back to the site a number of times to re read this section

The last entry in the above table shows a statement with two equals. The first equals sign is the assignment and any following equals signs are conditions.This might seem confusing at first but think of it like this. Any statement that starts with a variable and an equals is in the following format

[variable] [=] [evaluate this part]
So whatever is on the right of the equals sign is evaluated and the result is placed in the variable. Taking the last three assignments again, you could look at them like this
[x] [=] [5]
[b] [=] [6 = 5]
[x] [=] [MyFunc(5,6)]

Which is in agreement with Paul and Aflatoon. Surely there's a simpler way of saying the B is False?

[Paul_Hossler]

Well, FWIW ... I think the table in #1 was only intended to show differences between "Conditions" and "Assignment" and the examples are unrealistic so I wouldn't spend too much time trying to figure out

If the macro didn't need to reuse the 5=6 condition, I'd just

If 5=6 then
DoFalse
Else
DoTrue
End If


If the macro did need to reuse the 5=6 condition later on, I'd just

b=(5=6) or more realistic

b=(sCurrMonth = "Jan")
.....
.....
.....

If b then
DoTrue
Else
DoFalse
End If

Of course a lot of this is just personal style

[Aussiebear]

Thank you Paul.

[georgiboy]

Surely there's a simpler way of saying the B is False?

Not that I can think of, you could use NOT but that doesn't really make it simpler, it just removes the use of = to get the same result. For completeness below are a few other ways to get the same result (not that I would use them)

Sub test()
    Dim b As Boolean
    
    b = Not 6 <> 5
    b = Application.XLookup(6, 5, 1, 0, 0)
    b = Evaluate("EXACT(" & 6 & "," & 5 & ")")
End Sub

[Aflatoon]

I don't particularly like IIf but you could use it here:

b = IIf(6 = 5, True, False)

[Paul_Hossler]

I don't particularly like IIf but you could use it here:

b = IIf(6 = 5, True, False)

At least in this simple example. that seems like a more complicted way of just

b = (6=5)

I use (like ) IIf for things like

FinalCost = IIf (IsTaxable, BasicCost * TaxRate, BasicCost)

which is a one line If / Then / Else

Again, personal style / choice since I think the same number of CPU cycles are required

[Aflatoon]

which is a one line If / Then / Else

Not exactly - IIf always evaluates all the expressions, unlike an If..Then. It's more like the IFS function in Excel.

I only suggested it because Ted seemed to want to get away from the 6=5 construction.

[Aussiebear]

...seemed to want to get away from the 6=5 construction.

Only because I'm struggling with the concept that 6 = 5

[Paul_Hossler]

Not exactly - IIf always evaluates all the expressions, unlike an If..Then. It's more like the IFS function in Excel.

Interesting, could you expand?

To me

FinalCost = IIf (IsTaxable, BasicCost * TaxRate, BasicCost)

Is equivalent to

If IsTaxable Then
    FinalCost = BasicCost * TaxRate
Else
    FinalCost = BasicCost

since there is a only a single test and single True/False return

IIf(expr, truepart, falsepart)

whereas I'd think the IIF worksheet function

Simple syntax

Generally, the syntax for the IFS function is:


=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

would be more like a string of If / Then / ElseIf / Then / ..... / ElseIf / THen / End If

Admittedly not important in the overall scheme of things, but when you're retired you have too much time on your hands

[Aflatoon]

What I mean is that everything gets evaluated regardless of whether the condition evaluates to True or False. In other words this will produce an error:

someval = IIf(True, 1, 1/0)

even though the 1/0 is in the False part, whereas this would not, because the 1/0 would never be evaluated:

If true then
  someval = 1
else
   someval = 1/0
end if

[Paul_Hossler]

[Bob Phillips]

I use (like ) IIf for things like

FinalCost = IIf (IsTaxable, BasicCost * TaxRate, BasicCost)

I like IIf as well, I really detest putting a simple test like that in a If ... Else ... Endif, but as Rory says, you have to be careful, there is no short-circuiting in VBA.

But I like b = 6 = 5 even more, I use that structure all of the time.

As an aside, on something Rory said, I never, or more probably rarely use <> I use Not ... =, for example

If Not myvariable = somevalue Then

for some reason I decided some time ago that the condition is equality, so only test for equal and use Not if you want to know when it isn't.

[georgiboy]

As an aside, on something Rory said, I never, or more probably rarely use <>

If I'm not mistaken, I may have mentioned that and I have to say, I (not don't) agree with you. I was looking for alternatives to the b = 6 = 5, like you I happen to like the simplicity of it and find it succinct.

[Paul_Hossler]

p

If Not myvariable = somevalue Then

for some reason I decided some time ago that the condition is equality, so only test for equal and use Not if you want to know when it isn't.

(Very) obviously pesonal style/choice, but I usually opt for readability (probably my COBOL background)

So I'd probably do something like this since it's (to me) more readable even if it's longer

n = -100
GiveUp = False

While Not ReadyToGo  And Not GiveUp Then
    Call GetReadyToGo
Loop

....

....

....

Function ReadyToGo() as Boolean

ReadyToGo = False

if n < 1000 then
    GiveUp = True
    Exit Function
ElseIf n < 1 Then
    n = n+1
Else
    ReadyToGo = True
End If

End Function