How to understand each line of VBA code

[Anbuselvam K]

Hi

The attached sheet has a below VBA code to calculate the expected results. Here I want to understand each line of the code what it does and where it is looking the data.

Because I want to rewrite the code according to my other requirements which are the similar worksheet or nearby same worksheet.

If I post that worksheet here, then someone can modify easily. but, I want to understand each line of codes to write my own in the future.

Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D5:F5")) Is Nothing Then
        If Target.Count > 3 Then Exit Sub
        Range("A8:E19").Value = ""
        If Range("D5").Value = "" Or Range("F5").Value = "" Then Exit Sub
        '
        pName = Range("D5").Value
        nOrig = Range("F5").Value
        existe = False
        k = 8
        Set h = Sheets("Formulation")
        Set r = h.Columns("A")
        Set b = r.Find(pName, LookAt:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            celda = b.Address
            Do
                'detalle
                If LCase(h.Cells(b.Row, "B").Value) = LCase(nOrig) Then
                    existe = True
                    uc = h.Cells(4, Columns.Count).End(xlToLeft).Column
                    For j = 3 To uc
                        If h.Cells(b.Row, j).Value <> "" Then
                            Cells(k, "A").Value = h.Cells(4, j).Value
                            Cells(k, "B").Value = h.Cells(5, j).Value
                            Cells(k, "E").Value = h.Cells(b.Row, j).Value
                            k = k + 1
                        End If
                    Next
                    Exit Do
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> celda
        Else
            MsgBox "Prodcut not exists"
        End If
        If existe = False Then
            MsgBox "Relation Prodcut - Origin not exists"
        End If
    End If
End Sub

 

[YasserKhalil]

You can run the code line by line using F8 key and notice the changes after each line. Display the Locals window (that will help you too)

 

[Anbuselvam K]

Could anyone explain the below codes?

Line 3, If Target.Count > 3 Then Exit Sub (What is count 3 mean by)

Line 10, K = 7 (What is 7 mean by)

Line 15, celda = b.Address (What is Address)

Line 18 to 26 (Totally I did not Understand what it does)

If you are explaining the above lines, I can rewrite the code for my other requirements on my own.

Expecting your positive reply.

Thanks in advance.

 

[Stevie]

Hi Anbuselvam,

If Target.Count > 3 Then Exit Sub

This prevents the code from running if the user's selection is more than 3 cells.

K = 7

In the code you pasted above, it is k=8, but either way, k is a variable used to store the row where data is inserted later on in the code: lines 23-26
In this instance it marks the start row and is then incremented in the loop on line 26.

celda = b.Address

The address here is the cell address (such as "A1") where a value is found.
The value which is searched for in your code is stored in the pName variable which is set to the value of the cell 'D5' on line 7.

Lines 18-26 are the start of a loop within an if statement.
The if statement checks to see if the contents of the cell 'F5' when put into lower case (no capital letters) matches the value of the cell 'BX' where X is the row in which the match mentioned above was found.
If this is the case, the loop code is initiated, which places values from various cells in columns A B and E into other cells based on the value of the variable uc, starting from 'C4'.
Continued below:

 

[Stevie]

Continued:

Further than this I cannot tell from the code, and should my explanation be insufficient, I would suggest that perhaps this code is too advanced for you and you would benefit from doing some learning on VBA and programming in general. Each of the functions and attributes which you don't understand can be looked up on MSDN (google it). For example, searching for range.address will give a good explanation of what .address means and is used for.

I hope this helped, if so, please click 'Like!' in the bottom right.

Stevie

 

[Anbuselvam K]

Continued:

Further than this I cannot tell from the code, and should my explanation be insufficient, I would suggest that perhaps this code is too advanced for you and you would benefit from doing some learning on VBA and programming in general. Each of the functions and attributes which you don't understand can be looked up on MSDN (google it). For example, searching for range.address will give a good explanation of what .address means and is used for.

I hope this helped, if so, please click 'Like!' in the bottom right.

Stevie

Dear Stevie

Thanks a lot for your explanation.

 

[Stevie]

No worries.