In this post, we are going to go over a simple example that will demonstrate how subroutine and arrays can work together to achieve a specific purpose. What we want to do is for our subroutine to be able to take a number from the user and determine if when we divide each number in the array y the user number if the answer has a remainder of 1 or greater.

Below is the array as it would be found in Excel

Notice how the cells are selected. This is necessary for the code that we will use.

Next, we need to set up our code. We will begin by declaring our subroutine and variables as shown below.

Option Base 1
Sub remainderOne()
Dim i as Integer, j as Integer, n as Integer
Dim nr As Integer, nc As Integer, c As Integer
nr=  Selection.Rows.Count
nc = Selection.Columns.Count
n = InputBox("Enter a number")

The variables above will be used to store data we need to achieve our goal. I and j are for the rows and columns, the n variable will store data from the user, nr and nc are for counting the rows and columns, lastly, c is a variable that will count how many times our criteria is met.

We now need to include the nested for loop with the output

For i = 1 To nr
	For j = 1 to nc
		If Selection.Cells(i,j) Mod n >= 1 Then
			c = c+1
		End If
	Next J
Next i
MsgBox("There were " & c & " numbers with remainder of one when divivded by " & n & "."
End Sub

The nested for loop goes through each row and column seeing if the number inputted by the user results in a remainder of 1 or greater. The details of for loops have been explained in a prior post.

The code was explained in parts but here is how it should look in the VBA developer

Option Base 1
Sub remainderOne()
Dim i as Integer, j as Integer, n as Integer
Dim nr As Integer, nc As Integer, c As Integer
nr=  Selection.Rows.Count
nc = Selection.Columns.Count
n = InputBox("Enter a number")
For i = 1 To nr
	For j = 1 to nc
		If Selection.Cells(i,j) Mod n >= 1 Then
			c = c+1
		End If
	Next J
Next i
MsgBox("There were " & c & " numbers with remainder of one when divivded by " & n & "."
End Sub

Execute

We will now run the code.

The textbox asks for a number and we put the number. Below are the results

The message box tells you there are two numbers that have a remainder of 1 or more. Doing some basic math you can see that those two numbers are 41 and 14.

Conclusion

With a little bit of work, it is possible to use arrays with subroutines to do powerful things inside VBA. This example is one of many of how this can be done.