Excel VBA to Sort Multidimensional Array: 2 Methods

Code Breakdown:

• We declared a two-dimensional array called arr where we put 5 rows and 3 columns and put some data in it.
• We have used nested loops in each column of the array. The outer loop iterates over each column, and the two inner loops iterate through each row.
• The innermost loops specifically compare the current element with the next element. If the current element is greater than the next element, then the two elements are interchanged.
• This is the sorting process. After all this sorting, the output is displayed in the Immediate window.

The code is designed to sort a two-dimensional array in ascending order. We discourage you from using it as it is not very efficient for large arrays. It is simple and understandable to implement in 2D arrays.

Code Breakdown:

• A sub-procedure called “Sub BubbleSortMultiDimensionalArray()” is declared in the first line of the code.
• The second line declares the “arrData” variable, a 2-dimensional integer array with 5 rows and 3
• The program declares three long integer variables, “i” “j,” and “k,” as well as a temporary variable, “temp“, for use in the sorting operation.
• The “For” loop beginning on line 7 uses the “Int(Rnd() * 100)” function to fill the array with random integer values.
• The code uses nested “For” loops and the “Print” instruction to show the unsorted array in the current window.
• The 2-dimensional array is then sorted using the bubble sort method. The outer “For” loop, beginning on line 16, regulates how many times the array is passed through (i.e., it will iterate for a total of “n-1” times for an array of “n” elements).
• Beginning on line 17, the first nested “For” loop iterates over the array and compares nearby entries to see if they should be switched.
• Beginning on line 18, the second iteration of the “For” loop compares nearby entries as it traverses the array’s columns.
• The code switches the items using a temporary variable called “temp” if the comparison decides they need to be switched.
• The “Print” command and nested For Loops are used to display the sorted array in the current window when sorting is finished.
• The sub-procedure is ended using the “End Sub” command.

Code Breakdown:

• “Sub SortSelectedRangeAscending()” is a sub-procedure that is declared in the first line of the code.
• The user-selected range will be stored in a Range variable called “dataRange“, which is declared in the second line.
• The “On Error Resume Next” statement instructs VBA to forgo any potential runtime errors that may arise while the next code is being executed.
• Using an input box, the “Set” statement asks the user to choose a range, then adds that range to the “dataRange” variable. The “Type:=8” argument instructs the input box to only accept input in the form of ranges.
• Any faults that happen after the “On Error GoTo 0” statement will be captured by VBA and shown to the user since it disables error handling at this point.
• By determining if the “dataRange” variable is empty, the “If” statement determines whether the user closed the input box (i.e., equal to “Nothing”). A message box informing the user that the range selection was abandoned will be shown if the variable is empty, and the subprocedure will end.
• A method or property may be applied to a given object without repeatedly referencing the same object using the “With” statement. The “With” statement is applied to the “dataRange” variable in this instance, which implies that any statements that follow it in the “With” block will work with the chosen range.
• Based on the values in the first column, the “.Sort” method is used to sort the chosen range in ascending order. The sort key should be the first cell in the range, as specified by the “Key1:=.Cells(1,1)” argument, and the sort order should be ascending as specified by the “Order1:=xlAscending” parameter. The range includes a header row that should also be sorted, according to the “Header:=xlYes” argument.
• Lastly, the End Sub statement finishes the sub-procedure.

Code Breakdown:

• A sub-procedure with the name “Sub SortData()” is declared in the first line of the code.
• The range to be sorted will be stored in a Range variable called “rngSort“, which is declared in the second line.
• The “Set” statement is used to assign the range “B5:F17” to the “rngSort” variable.
• By using the “With” statement, you may affect the “rngSort” range without referencing it more than once.
• The chosen range is sorted into two keys using the “.Sort” function. The first sort key should be the third column (column C) in the range, according to the “Key1:=.Columns(3)” option, and the “Order1:=xlAscending” parameter specifies that the sort order for this key should be ascending.
• The second sort key should be the fourth column (column D) in the range, according to the “Key2:=.Columns(4)” argument, and the “Order2:=xlDescending” option specifies that the sort order for this key should be descending.
• The range does not contain a header row that has to be sorted according to the “Header:=xlNo” argument.
• The sort should be done from top to bottom, according to the “Orientation:=xlTopToBottom” argument.
• The “With” block is concluded with the “End With” declaration.
• The sub-procedure is ended using the “End Sub” command.

Code Breakdown:

• Sub SumMultiArrayInWorksheet() – With this line, a new Sub-procedure with the name SumMultiArrayInWorksheet is defined.
• Dim arr As Variant – This line creates a variant variable with the name arr that will be used to hold a range of values.
• Then we define two variables with the names i and j that are of the data type Long. As loop counters, these variables will be employed.
• range = arr(“E5:E17”).Value – This line gives the arr variable the values in the range E5:E17.
• Since i = LBound(arr, 1) The line To UBound(arr, 1) initiates a loop that iterates through the rows of the arr array. The lower and upper boundaries of the array’s first dimension are provided by the LBound and UBound functions, respectively.
• j = LBound for(arr, 2) The line To UBound(arr, 2) initiates a loop that iterates across the columns of the arr array. The lower and upper boundaries of the array’s second dimension are provided by the LBound and UBound functions, respectively.
• sum = sum + arr(i, j) – This statement increases the value of the sum variable by the value of the current element in the arr array.
• Then theSum command calculates the sum and returns it in cell E18.

Code Breakdown:

• We call a sub-procedure named  SortMultiArrayAlphabetically(). Then we declared the variable.
• numRows and numCols are two variables of the data type Long declared in the line Dim numRows As Long, numCols As Long. The number of rows and columns in the arr array will be kept in these variables.
• range = arr(“B5:B17”).Value – This line gives the arr variable the values from B5 to B17.
• The number of rows in the arr array is determined by the formula numRows = UBound(arr, 1).
• This line sets the numCols variable to the number of columns in the arr array using the formula UBound(arr, 2).
• For i = 1 From the first row through the next-to-last row of the arr array, a loop called through numRows – 1 begins with this line.
• For j = i + 1, the loop To numRows iterates over the rows of the arr array, beginning with the row that follows the current row and ending with the final row.
• Rows to swap: arr, i, j, numCols – This line invokes the SwapRows Sub function with the arguments arr array, indices of the two rows to be swapped, and the array’s number of columns.
• The inner loop over the rows ends in the next j lines, and the outer loop across the rows is completed on the next line, i.
• Range(“B5”).Resize(numRows, numCols).Value = arr – Starting in cell B5 and spanning the same number of rows and columns as the original array, this line sends the sorted arr array back to the worksheet.
• End Sub – With this line, the SortMultiArrayAlphabetically Sub-process is defined.