Nooby
08-18-2020, 11:21 PM
Hello, I need some help in understanding what precisely the attached code is doing. A very kind person created/gifted this code to me after I posted here:
https://www.excelforum.com/excel-programming-vba-macros/1323642-match-two-fields-in-userform.html#post5374623
I've heavily commented the code myself (my understanding/interpretation may not be 100%!). I've been learning as much VBA as I can via various online tutorials and now understand the theory of variables and arrays as well as being increasingly familiar with Userforms. I've even made some loops myself in another project.
There seems to be an issue surrounding the initialize event that means when I try to add this code in to my project that it refuses to work and throws an error.
Option Explicit'Colletions - a bit like arrays but you don't need to know how big it needs to be before you add variables in to it.
Dim ary1 As New Collection, ay1 '
Dim arr2 As New Collection, a2 '
Dim arr3 As New Collection, a3 '
'un-dimensioned arrays
Dim Aary() As Variant '
Dim Bary() As Variant 'Contains the values to populate into combobox1?
Dim Cary() As Variant '
Dim TEMP1ary() As Variant 'Contains all of the data in A2:C THEN the FOR IF replaces all values with ""
Dim TEMP2ary() As Variant 'Contains a subset of TEMP1ary based on what has been selected in combobox1
Dim TEMP3ary() As Variant
'Variables
Dim RowCount As Integer 'Count of the number of rows in Column A
Dim CounterRow As Integer 'Counter for the number of rows in the loop
Dim CounterColumn As Integer 'Counter for the number of columns in the loop
Dim RowContainsValue As Integer 'Count of the number of rows that contain a value (are not null)
Dim RowCountTEMP2ary As Integer 'Count of the number of rows in TEMP2ary
Private Sub CboOrder() '_Change()
'empty the second cbo
UserForm1.Cbo_Part.Clear
'count the number of rows in sheet 1 column A
RowCount = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' this array contains all of the data that has been 'copied' from the sheet - excludes header row
TEMP1ary = Worksheets("Sheet1").Range("A2:C" & RowCount).Value
RowContainsValue = 1
For CounterRow = LBound(TEMP1ary) To UBound(TEMP1ary)
'LBound & UBound of TEMP1ary are now controlling the number of times to loop the first dimension(ROWS)
If TEMP1ary(CounterRow, 1) <> UserForm1.Cbo_Order.Value Then ' IF first row (first dimension) of TEMP1ary is not what is in the combobox THEN**
For CounterColumn = 1 To 3
TEMP1ary(CounterRow, CounterColumn) = "" 'loop through columns and populate with ""
Next CounterColumn
'** put a "" in the Array for each column
Else:
RowContainsValue = RowContainsValue + 1 'IF first row (first dimension) of TEMP1ary IS (=) what is in the combobox THEN _
increase variable named RowContainsValue by 1
End If
Next CounterRow 'cycle through all rows and columns in the array populating the rows/columns either the value that is _
in the combobox or ""
ReDim TEMP2ary(1 To RowContainsValue, 1 To CounterColumn) 'sets the size of TEMP2ary based on _
the number of rows (containing the values that match to the selection in the combobox) and the number of columns (which is hardcoded as 3)
'BASICALLY TEMP2ary is a copy of TEMP1ary but contains only those values that match to what has been selected in combobox1
RowCountTEMP2ary = 1
For CounterRow = LBound(TEMP1ary) To UBound(TEMP1ary)
'LBound & UBound of TEMP1ary are now controlling the number of times to loop the first dimension(ROWS)
If TEMP1ary(CounterRow, 1) <> "" Then 'IF 'X' row (first dimension) of TEMP1ary is not "" THEN***
For CounterColumn = 1 To 3
TEMP2ary(RowCountTEMP2ary, CounterColumn) = TEMP1ary(CounterRow, CounterColumn) 'loop through columns and populate data from TEMP1ary _
into TEMP2ary IF TEMP1ary contains values
Next CounterColumn
'BASICALLY TEMP2ary is populated with the values that 'match' to the selection in combobox1 that were 'loaded' into TEMP1ary
RowCountTEMP2ary = RowCountTEMP2ary + 1 ' if has a value (i.e. <> "") then add 1 to RowCountTEMP2ary
End If
Next CounterRow
ReDim Bary(1 To RowCountTEMP2ary, 1 To 1) 'sets the size of Bary based on _
the number of rows (containing the values that match to the selection in the combobox) that are in TEMP2ary but only in column 1
'BASICALLY Bary is a copy of TEMP2ary but contains only those values that match to what has been selected in combobox1 AND _
is only the first column of TEMP2ary
For CounterRow = LBound(TEMP2ary) To UBound(TEMP2ary) 'use upper and lower boundaries of TEMP2ary to control the loop
Bary(CounterRow, 1) = TEMP2ary(CounterRow, 2) 'Load the second dimension of TEMP2ary (Column) into Bary
Next CounterRow
On Error Resume Next
'''''''''''''''''REALLY STUCK ON WHAT IS GOING ON BELOW - HAVE HAD A STAB AT IT THOUGH!!!!!
Set arr2 = Nothing ' severs all links to and from arr2 (collection not an array)
For Each a2 In Bary ' for each SOMETHING that is In Bary?
arr2.Add a2, a2 'add the value of what into where/what?
Next ' loop based on what?
With UserForm1.Cbo_Part
For CounterRow = 1 To arr2.Count 'loop from 1 to (count of the rows in arr2)
.AddItem arr2(CounterRow) ' additem in to arr2
Next CounterRow
End With
UserForm1.Cbo_Part.Value = ""
UserForm1.Cbo_Part.SetFocus
End Sub
Private Sub Cbo_Part() '2_Change()
UserForm1.Cbo_qty.Clear
RowCount = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
TEMP2ary = Worksheets("Sheet1").Range("A2:C" & RowCount).Value
k = 1
For i = LBound(TEMP2ary) To UBound(TEMP2ary)
If TEMP2ary(i, 2) <> UserForm1.Cbo_Part.Value Then
For j = 1 To 3
TEMP2ary(i, j) = ""
Next j
Else:
k = k + 1
End If
Next i
ReDim TEMP3ary(1 To k, 1 To j)
z = 1
For i = LBound(TEMP2ary) To UBound(TEMP2ary)
If TEMP2ary(i, 2) <> "" Then
For j = 1 To 3
TEMP3ary(z, j) = TEMP2ary(i, j)
Next j
z = z + 1
End If
Next i
ReDim Cary(1 To z, 1 To 1)
For i = LBound(TEMP3ary) To UBound(TEMP3ary)
Cary(i, 1) = TEMP3ary(i, 3)
Next i
On Error Resume Next
Set arr3 = Nothing
For Each a3 In Cary
arr3.Add a3, a3
Next
With UserForm1.Cbo_qty
For i = 1 To arr3.Count
.AddItem arr3(i)
Next i
End With
UserForm1.Cbo_qty.Value = ""
UserForm1.Cbo_qty.cbo3.SetFocus
End Sub
Private Sub UserForm_Initialize()
RowCount = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
ReDim Aary(1 To RowCount)
Aary = Worksheets("Sheet1").Range("A2:A" & RowCount).Value
On Error Resume Next
For Each ay1 In Aary
ary1.Add ay1, ay1
Next
With UserForm1.Cbo_Order ' this populates combobox1?
For CounterRow = 1 To ary1.Count
.AddItem ary1(CounterRow)
Next CounterRow
End With
End Sub
I just really need some help with understanding the bit of the code that I haven't commented so that I can understand and use it. Hopefully in the many projects to come.
Many thanks in advance for any and all assistance.
Nooby
https://www.excelforum.com/excel-programming-vba-macros/1323642-match-two-fields-in-userform.html#post5374623
I've heavily commented the code myself (my understanding/interpretation may not be 100%!). I've been learning as much VBA as I can via various online tutorials and now understand the theory of variables and arrays as well as being increasingly familiar with Userforms. I've even made some loops myself in another project.
There seems to be an issue surrounding the initialize event that means when I try to add this code in to my project that it refuses to work and throws an error.
Option Explicit'Colletions - a bit like arrays but you don't need to know how big it needs to be before you add variables in to it.
Dim ary1 As New Collection, ay1 '
Dim arr2 As New Collection, a2 '
Dim arr3 As New Collection, a3 '
'un-dimensioned arrays
Dim Aary() As Variant '
Dim Bary() As Variant 'Contains the values to populate into combobox1?
Dim Cary() As Variant '
Dim TEMP1ary() As Variant 'Contains all of the data in A2:C THEN the FOR IF replaces all values with ""
Dim TEMP2ary() As Variant 'Contains a subset of TEMP1ary based on what has been selected in combobox1
Dim TEMP3ary() As Variant
'Variables
Dim RowCount As Integer 'Count of the number of rows in Column A
Dim CounterRow As Integer 'Counter for the number of rows in the loop
Dim CounterColumn As Integer 'Counter for the number of columns in the loop
Dim RowContainsValue As Integer 'Count of the number of rows that contain a value (are not null)
Dim RowCountTEMP2ary As Integer 'Count of the number of rows in TEMP2ary
Private Sub CboOrder() '_Change()
'empty the second cbo
UserForm1.Cbo_Part.Clear
'count the number of rows in sheet 1 column A
RowCount = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' this array contains all of the data that has been 'copied' from the sheet - excludes header row
TEMP1ary = Worksheets("Sheet1").Range("A2:C" & RowCount).Value
RowContainsValue = 1
For CounterRow = LBound(TEMP1ary) To UBound(TEMP1ary)
'LBound & UBound of TEMP1ary are now controlling the number of times to loop the first dimension(ROWS)
If TEMP1ary(CounterRow, 1) <> UserForm1.Cbo_Order.Value Then ' IF first row (first dimension) of TEMP1ary is not what is in the combobox THEN**
For CounterColumn = 1 To 3
TEMP1ary(CounterRow, CounterColumn) = "" 'loop through columns and populate with ""
Next CounterColumn
'** put a "" in the Array for each column
Else:
RowContainsValue = RowContainsValue + 1 'IF first row (first dimension) of TEMP1ary IS (=) what is in the combobox THEN _
increase variable named RowContainsValue by 1
End If
Next CounterRow 'cycle through all rows and columns in the array populating the rows/columns either the value that is _
in the combobox or ""
ReDim TEMP2ary(1 To RowContainsValue, 1 To CounterColumn) 'sets the size of TEMP2ary based on _
the number of rows (containing the values that match to the selection in the combobox) and the number of columns (which is hardcoded as 3)
'BASICALLY TEMP2ary is a copy of TEMP1ary but contains only those values that match to what has been selected in combobox1
RowCountTEMP2ary = 1
For CounterRow = LBound(TEMP1ary) To UBound(TEMP1ary)
'LBound & UBound of TEMP1ary are now controlling the number of times to loop the first dimension(ROWS)
If TEMP1ary(CounterRow, 1) <> "" Then 'IF 'X' row (first dimension) of TEMP1ary is not "" THEN***
For CounterColumn = 1 To 3
TEMP2ary(RowCountTEMP2ary, CounterColumn) = TEMP1ary(CounterRow, CounterColumn) 'loop through columns and populate data from TEMP1ary _
into TEMP2ary IF TEMP1ary contains values
Next CounterColumn
'BASICALLY TEMP2ary is populated with the values that 'match' to the selection in combobox1 that were 'loaded' into TEMP1ary
RowCountTEMP2ary = RowCountTEMP2ary + 1 ' if has a value (i.e. <> "") then add 1 to RowCountTEMP2ary
End If
Next CounterRow
ReDim Bary(1 To RowCountTEMP2ary, 1 To 1) 'sets the size of Bary based on _
the number of rows (containing the values that match to the selection in the combobox) that are in TEMP2ary but only in column 1
'BASICALLY Bary is a copy of TEMP2ary but contains only those values that match to what has been selected in combobox1 AND _
is only the first column of TEMP2ary
For CounterRow = LBound(TEMP2ary) To UBound(TEMP2ary) 'use upper and lower boundaries of TEMP2ary to control the loop
Bary(CounterRow, 1) = TEMP2ary(CounterRow, 2) 'Load the second dimension of TEMP2ary (Column) into Bary
Next CounterRow
On Error Resume Next
'''''''''''''''''REALLY STUCK ON WHAT IS GOING ON BELOW - HAVE HAD A STAB AT IT THOUGH!!!!!
Set arr2 = Nothing ' severs all links to and from arr2 (collection not an array)
For Each a2 In Bary ' for each SOMETHING that is In Bary?
arr2.Add a2, a2 'add the value of what into where/what?
Next ' loop based on what?
With UserForm1.Cbo_Part
For CounterRow = 1 To arr2.Count 'loop from 1 to (count of the rows in arr2)
.AddItem arr2(CounterRow) ' additem in to arr2
Next CounterRow
End With
UserForm1.Cbo_Part.Value = ""
UserForm1.Cbo_Part.SetFocus
End Sub
Private Sub Cbo_Part() '2_Change()
UserForm1.Cbo_qty.Clear
RowCount = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
TEMP2ary = Worksheets("Sheet1").Range("A2:C" & RowCount).Value
k = 1
For i = LBound(TEMP2ary) To UBound(TEMP2ary)
If TEMP2ary(i, 2) <> UserForm1.Cbo_Part.Value Then
For j = 1 To 3
TEMP2ary(i, j) = ""
Next j
Else:
k = k + 1
End If
Next i
ReDim TEMP3ary(1 To k, 1 To j)
z = 1
For i = LBound(TEMP2ary) To UBound(TEMP2ary)
If TEMP2ary(i, 2) <> "" Then
For j = 1 To 3
TEMP3ary(z, j) = TEMP2ary(i, j)
Next j
z = z + 1
End If
Next i
ReDim Cary(1 To z, 1 To 1)
For i = LBound(TEMP3ary) To UBound(TEMP3ary)
Cary(i, 1) = TEMP3ary(i, 3)
Next i
On Error Resume Next
Set arr3 = Nothing
For Each a3 In Cary
arr3.Add a3, a3
Next
With UserForm1.Cbo_qty
For i = 1 To arr3.Count
.AddItem arr3(i)
Next i
End With
UserForm1.Cbo_qty.Value = ""
UserForm1.Cbo_qty.cbo3.SetFocus
End Sub
Private Sub UserForm_Initialize()
RowCount = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
ReDim Aary(1 To RowCount)
Aary = Worksheets("Sheet1").Range("A2:A" & RowCount).Value
On Error Resume Next
For Each ay1 In Aary
ary1.Add ay1, ay1
Next
With UserForm1.Cbo_Order ' this populates combobox1?
For CounterRow = 1 To ary1.Count
.AddItem ary1(CounterRow)
Next CounterRow
End With
End Sub
I just really need some help with understanding the bit of the code that I haven't commented so that I can understand and use it. Hopefully in the many projects to come.
Many thanks in advance for any and all assistance.
Nooby