Documenting VBA Code

' Begin by declaring your variables. In this case, they are all strings, used to hold alphanumeric/text values.
Public Category As String
Public CustRef As String
Public FirstName As String
Public LastName As String
Public Exec As String
Public UniqueID As String
Public xCategory As String
Public xCustRef As String
Public xFirstName As String
Public xLastName As String
Public xExec As String
Public xUniqueID As String
 
' Public means make this a function that can be seen by all other subroutines and functions.
' Function declares that it is a function, like a subroutine that can be called, but that returns a value.
' ConvertColumn is the name of the function that is called.
' ColumnNumber is a parameter value passed in to the function that it uses to perform its, uh, function 
Public Function ConvertColumn(ColumnNumber As Integer)
    ' ConvertColumn here is the name of the function. Assigning a value to that variable/function name means that value is what the function returns.
    ' WorksheetFunction is an object that allows you to use VB to apply functions to data as though that function had been invoked directly in the worksheet rather than using the VB code.
    ' .Substitute is the function that it's applying. (See the Excel help for that, if you like.) Essentially, it's replacing one value with a different value. It's often used to replace a substring of text.
    ' In this case, it's targeting the cell returned by Cells(1, ColumnNumber).AddressLocal(RowAbsolute = True, ColumnAbsolute = True).
    ' Cells(1, ColumnNumber) returns a cell object, in this case, row A, in the column passed in by the ColumnNumber parameter passed in to the function. Once you have that cell object, .AddressLocal gets the actual address.
    ' So, "Cells(1, 1).AddressLocal(RowAbsolute = True, ColumnAbsolute = True)" returns "A1"
    ' And, "Cells(1, 7).AddressLocal(RowAbsolute = True, ColumnAbsolute = True)" returns "G1"
    ' Once it has that cell, it's replacing 1 in that cell with "" (nothing), thanks to the Substitute function.
    ' Since it started as, for example, "$A$1" what this gives back is "$A$"
    ' Note that I have corrected a typo in this line, as "ColumnAbsolute" was shown as "ColumnAbslute"
    ConvertColumn = WorksheetFunction.Substitute(Cells(1, ColumnNumber).AddressLocal(RowAbsolute = True, ColumnAbsolute = True), 1, "")
    ' Because all of that was assigned to the "ConvertColumn" variable, that value is returned to the calling subroutine when the function ends.
End Function
 
' Private means this subroutine can only be seen by other subroutines in this module.
' Sub means we're defining a subroutine
' InsertEntry is the name of the subroutine.
' Category, CustRef, FirstName, LastName, Exec, and UniqueID are all string parameters passed in to the subroutine.
' RowN is an integer parameter passed in.
Private Sub InsertEntry(Category As String, CustRef As String, FirstName As String, LastName As String, Exec As String, RowN As Integer, UniqueID As String)
    ' WorksheetFunction (again) allows you to use a worksheet function here in the VB code
    ' The CountIf function tells how many cells in a specified range meet the given criteria.
    ' In this case, it looks at the worksheet whose name is in the Category value passed in.
    ' Within that worksheet, it's looking in the entire X column, as the whole column is the specified range.
    ' Essentially, it checks to see if any of the cells in the X column contain the "UniqueID" value passed in as a parameter.
    ' If any of them do, then don't bother processing, and leave the subroutine ("Exit Sub").
    ' If any of the cells in the range DO contain UniqueID, it falls through to the code below.
    If WorksheetFunction.CountIf(Sheets(Category).Range("X:X"), UniqueID) > 0 Then Exit Sub
    ' Go to the first column of the specified row and insert an entire new row
    Sheets(Category).Range("A" & RowN).EntireRow.Insert
    ' In this new row, put in CustRef, FirstName, LastName and UniqueID values (passed in as parameters) into the A, E, F and X columns
    Sheets(Category).Range("A" & RowN) = CustRef
    Sheets(Category).Range("E" & RowN) = FirstName
    Sheets(Category).Range("F" & RowN) = LastName
    Sheets(Category).Range("X" & RowN) = UniqueID
    ' Having done that, we exit the subroutine
End Sub
 
' Private, again, means only let this be visible to other code in this module.
' Sub means define a subroutine
' SendData is the name of the subroutine
Private Sub SendData()
    ' Into the xCategory variable, insert the value returned by the ConvertColumn function when we send it a column number.
    ' WorksheetFunction we've talked about already. In this case, it's the Match function.
    ' Match looks up a value in a list of values, and returns the closest value to it from the list. (Or, no value, or exact values, depending on how you use it.)
    ' Match returns the location in that list for where the value was found, if it was.
    ' Here, it searches for the word "Category" in the entire first row. If it finds it, it returns the column number.
    ' Once the Match function returns the column number, that column number gets passed as a parameter to the ConvertColumn function.
    ' The ConvertColumn function then replaces any 1 in the first row of the column with nothing.
    ' That text, with the 1 removed, is assigned to the variable at the beginning of the line.
    ' So, for example, if "Category" is in cell D1, "Match("Category", Range("1:1"), 0)" would return a 4, for the fourth column.
    ' The ConvertColumn function then runs with 4 as the parameter being passed in. That would give you "$D$" as the value.
    ' This is done six times: Once for each variable listed here:
    xCategory = ConvertColumn(WorksheetFunction.Match("Category", Range("1:1"), 0))
    xCustRef = ConvertColumn(WorksheetFunction.Match("Customer Reference Value", Range("1:1"), 0))
    xFirstName = ConvertColumn(WorksheetFunction.Match("First Name", Range("1:1"), 0))
    xLastName = ConvertColumn(WorksheetFunction.Match("Last Name", Range("1:1"), 0))
    xExec = ConvertColumn(WorksheetFunction.Match("Sr. Exec.", Range("1:1"), 0))
    xUniqueID = ConvertColumn(WorksheetFunction.Match("Unique ID", Range("1:1"), 0))
    ' WorksheetFunction again. CountA finds how many cells in the specified range are not empty.
    ' So, we're looking for how many cells in column B have something in them. And we're then looping through them, starting with row 2.
    For i = 2 To WorksheetFunction.CountA(Range("B:B"))
        ' For each of the following, assign a range of cells to the variables.
        ' Remember that "xCategory" for example, will contain an absolute pointer to the column, as set by the ConvertColumn function.
        ' The & operator concatenates strings.
        ' So, xCategory could have "$D$" in it, which would make Category be the range "$D$1", if i is 1, or "$D$2" if i is 2.
        ' Essentially, the For loop means, "Do all this for rows 1 and 2" in this case.
        Category = Range(xCategory & i)
        CustRef = Range(xCustRef & i)
        FirstName = Range(xFirstName & i)
        LastName = Range(xLastName & i)
        Exec = Range(xExec & i)
        UniqueID = Range(xUniqueID & i)
        ' The On Error line means if there's an invalid response, keep going.
        On Error Resume Next
        ' Determine your next action based on the value of Category. This is like an If-Then-Else construction.
        Select Case Category
            Case "WC"
            ' If Category is "WC" don't do anything
            Case "Corp"
            ' If Category is "Corp" then run that InsertEntry subroutine we did a while ago.
            ' That adds a row to the specified worksheet with the specified information, if that worksheet doesn't already have that UniqueID value in it.
            ' The value is added at the line determined here by the Match function -- if the value in the Exec variable is found in the I column on the Corp sheet
                InsertEntry Category, CustRef, FirstName, LastName, Exec, WorksheetFunction.Match(Exec, Sheets(Category).Range("I:I"), 0), UniqueID
            Case Else
            ' This isn't the WC category or the Corp category.
            ' Use that same InsertEntry subroutine to add a line to the specified worksheet with the specified data, at row 7.
                InsertEntry Category, CustRef, FirstName, LastName, Exec, 7, UniqueID
        End Select
    ' Next i means, "Well, you've done all this for the situation where i=1, so now set i=2 and try it again"
    ' This continues up to the ending value declared up there in the "For i =" loop.
    Next i
' And we're done with this subroutine.
End Sub
 
 
Private Sub CommandButton1_Click()
' This subroutine is what is invoked by the button click. All it does is call the SendData subroutine.
SendData
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119: