Excel 2010 VBA Tutorial 21 - For Loops with Arrays

How to use for loops to loop through arrays.

[peteheckman]

I had the same question as Andreea. It seems to work just fine either way, even for cases where you have multiple loops going on. I haven't watched Tutorial 23 yet, but maybe the answer is there. At this point I can't seem to figure out a reason you'd even need to declare which loop variable is being 'nexted'.

[MafteiA]

In tutorial 20 you put the name of the variable after next (Next x) but in this one you did not. Why? could I have left it out in tutorial 20 too?
Thank you. These videos are very helpful.

[BeanieBroadcast]

I'm confused.

Once the array has taken the values, how does the second loop relay it? What bit of code actually communicates with the array?

[markb]

thanks for the tutorial. its very helpful. curious why are we showing two "For" statements when one is enough as per below code?


Sub Matty ()

Dim myarray(15) As Integer
Dim x As Integer

For x = 0 To UBound(myarray)

myarray(x) = x
+ 1, 1).Value = myarray(x)

Next

End Sub

[markonikof]

This is weird. Declaring an array i(10) initializes an array from i(0) to i(10) instead of i(0) to i(9)? i.e. an array i(10) has 11 elements?

[anirbanduttagupta]

hi folks,

I tried to use the for loop in the array but during compilation, i am getting an error msg stating subscript out of range.My code is as follows:

Sub forArray()
Dim myArray(9) As Integer
Dim i As Integer
For i = 0 To 9
myArray(i) = i
'ThisWorkbook.Sheets("sheet 4").Cells(i + 1, 1).Value = myArray(i)
Next i
 Dim x As Integer
 For x = 0 To 9
 ThisWorkbook.Sheets("sheet 4").Cells(x + 1, 1).Value = myArray(x)
 Next x
 End Sub


Could someone advise plz?

[krish]

Hello,

In (Excel 2010 VBA Tutorial 21 - For Loops with Arrays) video vba code declared "Array'" but i tried without array variable. I got the output same. May i know why you are used "array" function in this code.


Thanks in advance

[atiskazaferi]

Option Explicit
Option Base 1
Public Matrix() As Double
Dim M As Long, N As Long

Sub FillMatrix()
Dim i As Long, j As Long
Dim LowerBound As Long, UpperBound As Long

LowerBound = 10
UpperBound = 15

M = Int((UpperBound - LowerBound) * Rnd) + LowerBound
N = Int((UpperBound - LowerBound) * Rnd) + LowerBound

ReDim Matrix(1 To M, 1 To N) As Double

For i = 1 To M
For j = 1 To N
Matrix(i, j) = 9 * Rnd + 1
Next j
Next i

End Sub

Sub WriteMatrix()
Dim r As Long, c As Long

Call FillMatrix
For r = 1 To M
For c = 1 To N
Cells(r, c) = Matrix(r, c)
Next c
Next r

End Sub

Function SumLargerThan(AValue As Double) As Long

Dim Sum As Long
Dim r As Long, c As Long

Call FillMatrix
Call WriteMatrix

Sum = 0
For r = 1 To M
For c = 1 To N
If Matrix(r, c) > AValue Then
Sum = Sum + Matrix(r, c).Value
End If
Next c
Next r
SumLargerThan = Sum
End Function

Function As Double, ARange As Range) As Long

Dim Sum As Long
Dim r As Long, c As Long

Sum = 0
For r = 1 To ARange.Rows.Count
For c = 1 To ARange.Columsn.Count
If ARange(r, c) > AValue Then
Sum = Sum + ARange(r, c).Value
End If
Next c
Next r

SumRangeElementsLargerThan = Sum


End Function

Sub
Call SumRangeElementsLargerThan(2, "A1:G10") ''Insert Range
End Sub

Sub SortRow(RowNr As Long)
Dim i As Long, j As Long
Dim dummy As Double

For i = 1 To N - 1
For j = i + 1 To N
'if item i<item j then we swap
If Matrix(RowNr, i) < Matrix(RowNr, j) Then
dummy = Matrix(RowNr, i)
Matrix(RowNr, i) = Matrix(RowNr, j)
Matrix(RowNr, j) = dummy
End If
Next j
Next i

End Sub
Sub TestSortRows()
''Call FillMatrix
Call SortRow(5)
Call WriteMatrix
End Sub

Sub DrawTwoDifferentNumbers(ByRef Number1 As Long, Number2 As Long)

Call FillMatrix

Number1 = Int(Rnd * M) + 1
Number2 = Int(Rnd * M) + 1

Do While (Number1 = Number2)
Number2 = Int(Rnd * M) + 1
Loop

End Sub


Sub SwapRandomRows()
'draw two random row numbers that are
Dim r1 As Long, r2 As Long
Dim c As Long, dummy As Long

r1 = Int(Rnd * M) + 1
r2 = Int(Rnd * M) + 1

Do While (r1 = r2)
r2 = Int(Rnd * M) + 1
Loop

'swap rows
For c = 1 To N
dummy = Matrix(r1, c)
Matrix(r1, c) = Matrix(r2, c)
Matrix(r2, c) = dummy
Next c
Call WriteMatrix
End Sub