Consulting

Results 1 to 18 of 18

Thread: Assignment Vs Condition

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location

    Assignment Vs Condition

    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    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
    Last edited by georgiboy; 05-07-2024 at 05:23 AM. Reason: Simpler example
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  3. #3
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    Really it's just shorter than doing something like this:

    if 6 = 5 then
       b = True
    Else
       b = False
    end if
    Be as you wish to seem

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,744
    Location
    Quote Originally Posted by Aussiebear View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    Quote Originally Posted by Aussiebear View Post
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,744
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Thank you Paul.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    I don't particularly like IIf but you could use it here:

    b = IIf(6 = 5, True, False)
    Be as you wish to seem

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,744
    Location
    Quote Originally Posted by Aflatoon View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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.
    Be as you wish to seem

  12. #12
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Quote Originally Posted by Aflatoon View Post
    ...seemed to want to get away from the 6=5 construction.
    Only because I'm struggling with the concept that 6 = 5
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,744
    Location
    Quote Originally Posted by Aflatoon View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,445
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    Quote Originally Posted by Bob Phillips View Post
    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.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  16. #16
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,744
    Location
    p
    Quote Originally Posted by Bob Phillips View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  17. #17
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    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
    Be as you wish to seem

  18. #18
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,744
    Location
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •