Are all longs equal?

[gmaxey]

If I have a collection of lng variables, what is a good way to verify that all are equal value. This works, but I was wondering if there is a more general way. This method would be very difficult if my collection was 300 lngs instead of just 3. Thanks.

Sub Test()
Dim a As Long, b As Long, c As Long, d As Long
a = 8
b = 8
c = 8
If a <> b Or b <> c Or a <> c Then
MsgBox "a, b and c are not equal"
End If
End Sub

[macropod]

If you have a lot of variables to test, it'd probably be better to store them in an array, rather than as separate variables. You could then use a nested For ... Next loop for testing:
Sub Test1()
Dim Arr() As Long, i As Long, j As Long, bErr As Boolean
bErr = False
For i = 1 To 10
ReDim Preserve Arr(i)
Arr(i) = 8
Next
Arr(5) = 7
For i = 1 To UBound(Arr) - 1
For j = 2 To UBound(Arr)
If Arr(i) <> Arr(j) Then
bErr = True
MsgBox "The array elements are not equal." & vbCr & _
"The first difference occurs at:" & vbCr & _
"Elements: " & i & " (" & Arr(i) & ") & " & j & " (" & Arr(j) & ")"
Exit For
End If
Next
If bErr = True Then Exit For
Next
End Sub

[gmaxey]

Paul,

Actually I only have three. I was hoping for somthing Like

If a = b = c Then
'There all equal
Else
'One or more is not equal
End If

Thanks.

[macropod]

Well, you did mention having maybe 300 ...

Also, since you've only got 3 values, you only need two tests:
If a <> b Or a <> c Then
After all, if a is not equal to both b and c, b can't possibly be equal to both a and c.

[gmaxey]

Paul,

300 was just a number off the top of my head. It could be 300,000 and the goal would still be the same. A simple way to detect one or more values that isn't equal to the rest. It was very late when I kluged out the method I used and I agree with your logic (as usual).

Thanks.

[Frosty]

It seems like this would be an opportunity to give a snarky reply like "some longs are longer than others"

But if I were being serious, I would tend to agree with Paul... utilize an array, somehow. That will make it more scalable. You could also use a collection, and simply iterate through each item in the correction, and then escape if any value doesn't match the previous value... but with an array you could sort the whole thing, then just test the first value against the last value-- and if they are the same, you know everything in between is too.

Lots of different ways to approach... but in terms of the logic for the 3 variables, you only need the two tests. I tend to prefer positive logic, so I would do something like...

Dim bAllSame As Boolean
If a = b AND a = c then
bAllSame = True
End if

But that's because combining OR operators with NOT EQUALS gives me headaches.

[Paul_Hossler]

Not very flexible, only tested with Longs, but I'd use an array if I had more than a few


Option Explicit
Sub AreTheyEqual()
Dim bEqual As Boolean
Dim a As Long, b As Long, c As Long


a = 8
b = 8
c = 8


bEqual = (b And c) = a
MsgBox bEqual

a = 7
bEqual = (b And c) = a
MsgBox bEqual

End Sub


Paul

[gmaxey]

Gents all,

Thanks. Answer the to question that I didn't clearly ask, appears to be that while:

8 = 8 = 8 = 8 = True in the real world (or maybe just my limited view of the world) doesn't equate to a = b = c = d = true if a, b, c, and d = 8 in VBA. I was hoping that it did or some simple equivent rather than goig off and doing something like you suggested.

While I only had 3, I often ponder what I would do if I had 300 or 3 million.

Thanks again.

[Frosty]

Well, you could get creative and use arithmetic, if they were longs...something like
If (a + b + c) / 3 = a...

basically, you could add all of your longs together, divide by the number of longs and then test to see if the result equals one of your longs... but that doesn't seem scalable either, unless you dump that stuff into an array. And then I don't know which operation would give you more efficiency in terms of CPU. I *think* arithmetic functions are typically faster than comparison functions, but it seems like a pretty esoteric problem.

But VBA seems to fail on the 8 = 8 = 8 in that it returns False. However, 8 = 8 <> 8 returns True. But so does 8 = 8 <> 7.

I'm not sure what it's actually doing there, but since it doesn't return an error... it's clearly doing something.

However, my inquisitiveness led me to a couple of logical operators I didn't know existed in VBA....
http://msdn.microsoft.com/en-us/library/wz3k228a(v=vs.80).aspx
I've encountered Xor before, but not AndAlso or OrElse. So that was interesting.

[Paul_Hossler]

Well, you could get creative and use arithmetic, if they were longs...something like


Actually, I don't think you can rely on arithmetic either; dividing might cause a loss of precision sometimes


Option Explicit
Sub One()
Dim bEqual As Boolean
Dim a As Long, b As Long, c As Long
a = 1
b = 1
c = 1
'false - lack of decimal precision
MsgBox (a + b + c) / a = a
'false - lack of decimal precision
MsgBox (a + b + c) \ a = a
'false
MsgBox a = b = c
'false - a=b --> True, but True <> 8
MsgBox ((a = b) = c)
'true
MsgBox (a = b) And (b = c)

'false
bEqual = a = b = c
MsgBox bEqual
'false
bEqual = (a = b) = c
MsgBox bEqual
'true -- ((True) = (True)) = True
bEqual = (a = b) = (a = c)
MsgBox bEqual
End Sub






However, my inquisitiveness led me to a couple of logical operators I didn't know existed in VBA....


Thanks for that. Always learning something new

Paul

[Frosty]

Paul,I don't think that's lack of decimal precision. I think that's just bad math.

1 + 1 + 1 / 1 = 1

Would be wrong regardless, because of operator precedence and because it's wrong. You need parens around the addition functions, or the division goes first, and you can't use the variable for the divisor... you need the total number of variables.

(1 + 1 + 1) / 3 = 1

So you should never need decimal precision.

[Paul_Hossler]

Paul,I don't think that's lack of decimal precision. I think that's just bad math.


Especially embarrasing for an old math major :blush

Paul