Large Conditioning Statements in VBA comparing variables

[Peteor]

I have a situation where I would like to compare variables 27 different times, and output the results to a MsgBox. Even a simple true/False would work.

Test 1 would have variables A & B
Test 2 would have variables A2 & B2
Test 3 would have variables A3 & B3
....
Test 27 would have variables A27 & B27

I would like to output the results of each test to one large MsgBox, but don't want to code every possible outcome. Is there a way to shortcut this?

 

[Joe4]

Here is a simple example that shows you how you can run through a loop 27 times, and write things to a continuous string, and return one long Message Box at the end.

Sub MyTest()

    Dim myString As String
    Dim i As Long
    
'   Loop through 27 times
    For i = 1 To 27
'       Build string of addresses from column A and B
        myString = myString & Cells(i, "A").Address & "-" & Cells(i, "B").Address & vbCrLf
    Next i
    
'   Return single result to message box
    MsgBox myString
    
End Sub

Hopefully, that shows you the methodology you need to use.

 

[Peteor]

I am still a bit confused. For a small example,

If A=B Then
MsgBox("They are equal")
Else
MsgBox("They are not equal")
End If

Easy for one situation, but when I have to do it 27 times with unique variables, it gets problematic. I see you tried to explain it, but I am having trouble seeing it. I don't want to MsgBox 27 times, rather MsgBox 1 time with all of the data. I am very weak with lists and arrays. could you explain it to me as simply as possible?

All of my data is in variable form, and would not reference any cells or worksheets at this point.

What Statement would you use for your loop?
How do I define the iterations?
How do I create the list?
How do I compare them?

 

[Joe4]

Did you try running my code?
If you did, you would see that you only get one message box.
Take a few minutes to test it out and investigate it.

You iterate through a loop 27 times using the the For loop I wrote:

For i = 1 To 27

Note what this string is doing within my loop:

[COLOR=#574123]myString = myString & Cells(i, "A").Address & "-" & Cells(i, "B").Address & vbCrLf[/COLOR]

It is taking the string, adding the values from my current iteration, and then adding a soft carriage return at the end ("vbCrLf")
So, it is building one long string on 27 lines, and not displaying anything until after the loop is complete.

If you need more help than this, you are going to need to provide more information, such as, where are these 27 values coming from?
Are they are on a sheet? If so, in which cells?
Are you hard-coding them into your VBA code?
How do you know which variables you want to compare to what (if you have 27 different variables, you probably will have much more than 27 comparisons)?
What exactly do you want the output to look like?

 

[Peteor]

The variables are defined and written to at this point. They are Longs. Now, all I need to do is the comparison. For instance, 2 variables I would like to compare are "D04C_Kit_Count" and "D04C_Kit_Count2". There are 27 sets of these types of variables. Sorry, I thought it was pseudocode, I will go try it now. A-lot of this code has been hard-coded, and all my needed values have already been extracted from the sheets.

 

[DanteAmor]

I apologize, it was not in this post
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">I apologize, it was not in this post</body>

 

[Peteor]

Dante,
I think you may have tried to help, but something may not have come through.

 

[Joe4]

OK, here is a second example that may be more similar to what you are trying to do. I am assuming that we have two set of exactly three values, already stored in variables. I then store them in arrays, and compare them to each other, and return one long string at the end. Give this one a test:

Sub MyTest2()

    Dim A1 As Long, A2 As Long, A3 As Long
    Dim B1 As Long, B2 As Long, B3 As Long
    Dim arrA, arrB
    Dim i As Long
    Dim chk As String
    Dim str As String
        
'   Set/get values of first set
    A1 = 1
    A2 = 2
    A3 = 3
    
'   Store in an array
    arrA = Array(A1, A2, A3)
    
'   Set/get values of second set
    B1 = 11
    B2 = 2
    B3 = 33
    
'   Store in an array
    arrB = Array(B1, B2, B3)
    
'   Iterate through array (assume arrays A and B are the exact same size) and compare each set
    For i = LBound(arrA) To UBound(arrA)
'       Check to see if A matches B
        If arrA(i) = arrB(i) Then
            chk = "They are equal"
        Else
            chk = "They are not equal"
        End If
'       Build string
        str = str & "Test " & i + 1 & ": " & chk & vbCrLf
    Next i
    
    MsgBox str
    
End Sub

 

[Peteor]

Joe,

You are amazing! Nailed it! My whole code works now. would it be a simple task to name the tests individually instead of leave them as "test 1, "test 2", "etc...", "test 27"?

 

[Joe4]

Sure, just create a third array where you store all the test names.
The key thing with the arrays is to make sure that:
- they all have the EXACT same number of elements (27)
- they are in the correct order, so that the first element in first array corresponds to the first element in the second array and that corresponds to the first element in the third array, etc.

So the code would look something like this:

Sub MyTest2()

    Dim A1 As Long, A2 As Long, A3 As Long
    Dim B1 As Long, B2 As Long, B3 As Long
    Dim arrA, arrB, arrT
    Dim i As Long
    Dim chk As String
    Dim str As String
        
'   Set/get values of first set
    A1 = 1
    A2 = 2
    A3 = 3
    
'   Store in an array
    arrA = Array(A1, A2, A3)
    
'   Set/get values of second set
    B1 = 11
    B2 = 2
    B3 = 33
    
'   Store in an array
    arrB = Array(B1, B2, B3)
    
'   Store test names in array
    arrT = Array("Test 1", "Test 2", "Test 3")

'   Iterate through array (assume arrays A and B are the exact same size) and compare each set
    For i = LBound(arrA) To UBound(arrA)
'       Check to see if A matches B
        If arrA(i) = arrB(i) Then
            chk = "They are equal"
        Else
            chk = "They are not equal"
        End If
'       Build string
        str = str & arrT(i) & ": " & chk & vbCrLf
    Next i
    
    MsgBox str
    
End Sub