none
3-Dimensional Array Usage RRS feed

  • Question

  • I am currently using a couple of 2-dimensional arrays to compare 2 worksheets (code below). Is it possible to replace the 2 2-dimensional arrays with a single 3-dimensional array to eliminate the If...Then...Else setup for SheetNum variable in the code below?

    Sub RowMatch()
    '
    ' Example of Current Array Usage - 2 2-Dimensional Arrays
    ' - Comparing 2 Worksheets Named "Sheet1" & "Sheet2" looking for Missing Rows
    '
    Dim TotemRows(2) As Long, CurRow As Long, NxtRow As Long, SrchRow As Long
    Dim SrchTot As Long, MissRow As Long, MissTot As Long
    Dim ColWENum As Integer, ColNum As Integer, SheetNum As Integer, Ndx As Integer
    Dim ShtArray1() As Variant, ShtArray2() As Variant
    '
    ' Count # of Columns in 1st Worksheet, both Worksheets will contain same # of Columns
    '
        ColWENum = Sheets("Sheet1").Range("A1").CurrentRegion.Columns.Count
    '
    ' Count # of Rows in Each Worksheet
    '
        TotemRows(1) = Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
        TotemRows(2) = Sheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
    '
    ' Setup the 2 Worksheet Arrays
    '
        ReDim ShtArray1(TotemRows(1), ColWENum)
        ShtArray1 = Range("A1", Cells(TotemRows(1), ColWENum)).Value
        ReDim ShtArray2(TotemRows(2), ColWENum)
        ShtArray2 = Range("A1", Cells(TotemRows(2), ColWENum)).Value
    '
    ' Beginning of Missing Rows Check
    '
        For SheetNum = 1 To 2
        ' Setup Worksheet References
            MissTot = TotemRows(SheetNum)
            If SheetNum = 1 Then
                SrchTot = TotemRows(2)
            Else
                SrchTot = TotemRows(1)
            End If
        ' Initialize Row Counter and 1st Row #
            CurRow = 0
            NxtRow = 1
            If SheetNum = 1 Then
            ' Use Row from 1st Worksheet and Compare to ALL Rows in 2nd Worksheet
                For MissRow = NxtRow To MissTot
                    Missing = True
                    SrchRow = NxtRow
                ' Search down 2nd Worksheet and Compare ALL Columns in Match & Search Rows
                    For SrchRow = NxtRow To SrchTot
                    ' Match ALL Columns in 1st Worksheet Row to all Columns in 2nd Worksheet Row
                        For ColNum = 1 To ColWENum
                            If ShtArray1(MissRow, ColNum) <> ShtArray2(SrchRow, ColNum) Then
                                If ShtArray1(MissRow, ColNum) < ShtArray2(SrchRow, ColNum) Then GoTo Miss1
                                Exit For
                            Else
                                If ColNum = ColWENum Then
                                    Missing = False
                                    NxtRow = SrchRow + 1
                                    SrchRow = SrchTot
                                End If
                            End If
                        Next ColNum
                    Next SrchRow
    Miss1:
                ' If Row is Missing in Alternate Worksheet, List It
                    If Missing Then Call ListIt(SheetNum, MissRow)
                ' Update Row Counter
                    CurRow = CurRow + 1
                Next MissRow
            Else
            ' Use Row from 2nd Worksheet and Compare to ALL Rows in 1st Worksheet
                For MissRow = NxtRow To MissTot
                    Missing = True
                    SrchRow = NxtRow
                ' Search down 1st Worksheet and Compare ALL Columns in Match & Search Rows
                    For SrchRow = NxtRow To SrchTot
                    ' Match ALL Columns in 2nd Worksheet Row to all Columns in 1st Worksheet Row
                        For ColNum = 1 To ColWENum
                            If ShtArray2(MissRow, ColNum) <> ShtArray1(SrchRow, ColNum) Then
                                If ShtArray2(MissRow, ColNum) < ShtArray1(SrchRow, ColNum) Then GoTo Miss2
                                Exit For
                            Else
                                If ColNum = ColWENum Then
                                    Missing = False
                                    NxtRow = SrchRow + 1
                                    SrchRow = SrchTot
                                End If
                            End If
                        Next ColNum
                    Next SrchRow
    Miss2:
                ' If Row is Missing in Alternate Worksheet, List It
                    If Missing Then Call ListIt(SheetNum, MissRow)  ' Routine to post missing row on an error worksheet
                ' Update Row Counter
                    CurRow = CurRow + 1
                Next MissRow
            End If
            ErrorRow = ErrorRow + 1
        Next SheetNum
    End Sub

    Monday, December 28, 2015 5:50 PM

Answers

  • Is it possible to replace the 2 2-dimensional arrays with a single 3-dimensional array to eliminate the If...Then...Else setup for SheetNum variable in the code below?


    a) No.

    b) Even if, the code will not be smaller or faster.

    Andreas.

    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:57 AM
    Wednesday, December 30, 2015 10:37 AM
  • >>> Is it possible to replace the 2 2-dimensional arrays with a single 3-dimensional array to eliminate the If...Then...Else setup for SheetNum variable in the code below?

    According to your description, there's not much you can do directly with a 3-D array in Excel. However, VBA Variants are pretty flexible. You could get what you want by using a 1-D array that contains 2-D arrays instead of a 3-D array.

    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:57 AM
    Friday, January 1, 2016 3:10 AM

All replies

  • Hello,

    That looks more like MS Excel/VBA; so what's the relation to SQL Server Reporting Services, the topic of this forum?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, December 29, 2015 8:50 AM
  • Hi JimThib,

    This forum is SQL Server Reporting Services, Power View forum, since the issue is related to Excel developer. I will move this thread to Excel for Developer forum. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Wednesday, December 30, 2015 2:09 AM
  • Is it possible to replace the 2 2-dimensional arrays with a single 3-dimensional array to eliminate the If...Then...Else setup for SheetNum variable in the code below?


    a) No.

    b) Even if, the code will not be smaller or faster.

    Andreas.

    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:57 AM
    Wednesday, December 30, 2015 10:37 AM
  • >>> Is it possible to replace the 2 2-dimensional arrays with a single 3-dimensional array to eliminate the If...Then...Else setup for SheetNum variable in the code below?

    According to your description, there's not much you can do directly with a 3-D array in Excel. However, VBA Variants are pretty flexible. You could get what you want by using a 1-D array that contains 2-D arrays instead of a 3-D array.

    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:57 AM
    Friday, January 1, 2016 3:10 AM