Help understanding this code

Question

Hi,I'm new in VBA. &nbsp;I've been reading and seeing courses&nbsp;on internet this last three days and triying to understand every single line in this code, but I cant read completely all.Could someone try to explain the reason of the parts with bolt and underline please.&nbsp;Thanks!&nbsp;&nbsp;&nbsp;Public Const C_SEPARATOR = "."

Function IsItem(ByVal varCheckNumber As Variant) As Boolean
 IsItem = (InStr(varCheckNumber, C_SEPARATOR) &gt; 0) And (varCheckNumber &lt;&gt; vbNullString)
End Function

Sub ChangeTopicStatus()
' Called after user double clicked on the status of a topic
' Set all items of this topic to the status of the topic
Dim varData As Variant
Dim varTopic As Variant
Dim strStatus As String
Dim lngRowCount As Long
Dim lngColumns As Long

On Error Resume Next
    ' Initialize
    lngColumns = Range("myCheckList").Columns.Count
    varData = Range("myCheckList")
    varTopic = ActiveCell.Offset(0, -lngColumns + 1).Value (Why put a - before the number of colums?)
    strStatus = ActiveCell.Value
    
    ' Loop through the entire check list
    For lngRowCount = 1 To UBound(varData)
        If IsItem(varData(lngRowCount, 1)) Then
            If varTopic = Left$(varData(lngRowCount, 1), InStr(varData(lngRowCount, 1), C_SEPARATOR) - 1) Then
            ' Current item belongs to the selected topic, i.e. the item receives the status of the topic
                varData(lngRowCount, lngColumns) = strStatus
            End If
        End If
    Next lngRowCount
    
    ' Write the array back to the range
    Range("myCheckList") = varData
    
    ' Clean up
    Set varData = Nothing
    Set varTopic = Nothing
    
End Sub&nbsp;

yury tokarev · Answer

Hi Kamu,&nbsp;Here are some explanations&nbsp;InStr(varCheckNumber, C_SEPARATOR) &gt; 0This function finds the position of the first occurance of "." in the string. If you function referred to A1 (IsItem(A1)), and A1 had abc.123, the InsStr(varCheckNumber, C_SEPARATOR) would retun 4. Since 4 is more than 0, the result of the function would be TRUE&nbsp;&nbsp;-lngColumns + 1&nbsp;The "-" in the offset funciton will shift the active cell reference to the left. Essentially, the formula in the line&nbsp;retrieves a value in the first column of the range "myCheckList" on the same row as active cell&nbsp;&nbsp;For lngRowCount = 1 To UBound(varData)The code starts a loop from first row of the range "MyChecklist" (which was ,techincally speaking, put into the array varData), and continues looping intil the last row of the array is reached (UBound is the function to find the last row number)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If IsItem(varData(lngRowCount, 1)) Then&nbsp; &nbsp; &nbsp; &nbsp; The line evaluates if the first column of the current row contains a text separated by "." (as described above) &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; If varTopic = Left$(varData(lngRowCount, 1), InStr(varData(lngRowCount, 1), C_SEPARATOR) - 1) Then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; This line finds the first characters of the text in the first column, preceeding "." (eg. it will return abc if the text was abc.123) and ocmpares then with the value in the first column of the same row in the range "MyChecklist".&nbsp;At the same time, I cannot see in this code how this line would ever evaluate to TRUE, so that the Status can be updated further on, because the isntances where it can be TRUE are exceluded by the preceeding 'If IsItem' line. Are you able to send me an example table, and how the macro is used on it? &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;Hope this helpsYury&nbsp;

francis e. e. omoruto · Answer

Without opening the worksheet, I see that code as checking whether Target.Column corresponds to the LAST column in your range.

If you have n columns starting at m, then your last column is at (m+n-1).

If you don't subtract one, you would be at the first column AFTER your range.

Hope this helps.

Regards

kamu algeciras pérez · Answer

Thanks, thanks a lot!With this help I can advance now and read more codes!I put attached the excell with this code&nbsp;&nbsp;Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = Range("myCheckList").Column + Range("myCheckList").Columns.Count - 1 ThenOne las question in this line the range Target is the range of columns in "mychecklist"&nbsp;plus the number of columns in this same range - 1. But I don't understand why...&nbsp;Thanks again&nbsp;

rohit chawla · Answer

Will any one please be able to help as I added another section in the checklist attached earlier; the status doesn't update when double clicking and also doesn't update the completion rate.&nbsp;It looks like the the worksheet range is not extendable.&nbsp;I have copied the full vba code below:&nbsp;Option Explicit' Public ConstantsPublic Const C_DONE = "R"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Checked box Wingdings2Public Const C_OPEN = "£"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Empty box Wingdings2Public Const C_MIXED = "©"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Square in box (mixed status) Wingdings2Public Const C_SEPARATOR = "."&nbsp; ' Separator between topic no and item noFunction IsTopic(varCheckNumber As Variant) As Boolean' Number of the checkitem / topic contains no separator, i.e. the user double clicked on a topic&nbsp;&nbsp;&nbsp; IsTopic = (InStr(varCheckNumber, C_SEPARATOR) = 0) And (varCheckNumber &lt;&gt; vbNullString)End FunctionFunction IsItem(ByVal varCheckNumber As Variant) As Boolean' Number of the checkitem / topic contains a separator, i.e. the user double clicked on a check item&nbsp;&nbsp;&nbsp; IsItem = (InStr(varCheckNumber, C_SEPARATOR) &gt; 0) And (varCheckNumber &lt;&gt; vbNullString)End FunctionSub ChangeTopicStatus()' Called after user double clicked on the status of a topic' Set all items of this topic to the status of the topicDim varData As VariantDim varTopic As VariantDim strStatus As StringDim lngRowCount As LongDim lngColumns As Long&nbsp;&nbsp;&nbsp; On Error Resume Next&nbsp;&nbsp;&nbsp; ' Initialize&nbsp;&nbsp;&nbsp; lngColumns = Range("myCheckList").Columns.Count&nbsp;&nbsp;&nbsp; varData = Range("myCheckList")&nbsp;&nbsp;&nbsp; varTopic = ActiveCell.Offset(0, -lngColumns + 1).Value&nbsp;&nbsp;&nbsp; strStatus = ActiveCell.Value&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Loop through the entire check list&nbsp;&nbsp;&nbsp; For lngRowCount = 1 To UBound(varData)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If IsItem(varData(lngRowCount, 1)) Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If varTopic = Left$(varData(lngRowCount, 1), InStr(varData(lngRowCount, 1), C_SEPARATOR) - 1) Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Current item belongs to the selected topic, i.e. the item receives the status of the topic&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; varData(lngRowCount, lngColumns) = strStatus&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp; Next lngRowCount&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Write the array back to the range&nbsp;&nbsp;&nbsp; Range("myCheckList") = varData&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Clean up&nbsp;&nbsp;&nbsp; Set varData = Nothing&nbsp;&nbsp;&nbsp; Set varTopic = Nothing&nbsp;&nbsp; &nbsp;End SubSub AutomaticSetTopicStatus()' Called after the status of an item was changed' Checks how many items of this topics are already checked:'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1. all items checked: topic status is set to done'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2. no item is checked: topic status ist set to open'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3. otherwise topic status is set to mixedDim varData As VariantDim varTopic As VariantDim lngRowCount As LongDim lngColumns As LongDim lngTopicRow As LongDim lngItems As LongDim lngCheckedItems&nbsp; As Long&nbsp;&nbsp;&nbsp; On Error Resume Next&nbsp;&nbsp;&nbsp; ' Initialize&nbsp;&nbsp;&nbsp; lngColumns = Range("myCheckList").Columns.Count&nbsp;&nbsp;&nbsp; varData = Range("myCheckList")&nbsp;&nbsp;&nbsp; varTopic = Left$(ActiveCell.Offset(0, -lngColumns + 1).Value, InStr(ActiveCell.Offset(0, -lngColumns + 1).Value, C_SEPARATOR) - 1)&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Loop through the entire check list, find the position of the topic,&nbsp;&nbsp;&nbsp; ' detect the number of items of this topic and the number of checked items&nbsp;&nbsp;&nbsp; For lngRowCount = 1 To UBound(varData)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If varTopic = CStr(varData(lngRowCount, 1)) Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lngTopicRow = lngRowCount&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Else&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If IsItem(varData(lngRowCount, 1)) Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If varTopic = Left$(varData(lngRowCount, 1), InStr(varData(lngRowCount, 1), C_SEPARATOR) - 1) Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lngItems = lngItems + 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If varData(lngRowCount, lngColumns) = C_DONE Then lngCheckedItems = lngCheckedItems + 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp; Next lngRowCount&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Set the overall status of the topic&nbsp;&nbsp;&nbsp; If lngCheckedItems = 0 Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; varData(lngTopicRow, lngColumns) = C_OPEN&nbsp;&nbsp;&nbsp; ElseIf lngCheckedItems = lngItems Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; varData(lngTopicRow, lngColumns) = C_DONE&nbsp;&nbsp;&nbsp; Else&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; varData(lngTopicRow, lngColumns) = C_MIXED&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Write array back to the range&nbsp;&nbsp;&nbsp; Range("myCheckList") = varData&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Clean up&nbsp;&nbsp;&nbsp; Set varData = Nothing&nbsp;&nbsp;&nbsp; Set varTopic = Nothing&nbsp;&nbsp; &nbsp;End SubSub ExpandCollapseItems()Dim rngCheckList As RangeDim varTopic As VariantDim lngRowCount As Long&nbsp;&nbsp;&nbsp; On Error Resume Next&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Initialize&nbsp;&nbsp;&nbsp; Application.ScreenUpdating = False&nbsp;&nbsp;&nbsp; Set rngCheckList = Range("myCheckList")&nbsp;&nbsp;&nbsp; varTopic = ActiveCell.Offset(0, -(ActiveCell.Column - rngCheckList.Column)).Value&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Loop through the entire check list and hide the rows belonging to the double clicked topic&nbsp;&nbsp;&nbsp; For lngRowCount = 1 To rngCheckList.Rows.Count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If IsItem(rngCheckList(lngRowCount, 1)) Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If (varTopic = Left$(rngCheckList(lngRowCount, 1).Value, InStr(rngCheckList(lngRowCount, 1).Value, C_SEPARATOR) - 1)) Then _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rngCheckList.Cells(lngRowCount, 1).EntireRow.Hidden = Not rngCheckList.Cells(lngRowCount, 1).EntireRow.Hidden&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp; Next lngRowCount&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Clean up&nbsp;&nbsp;&nbsp; Set varTopic = Nothing&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; Application.ScreenUpdating = TrueEnd SubPublic Function CompletionRate(rngCheckList As Range) As Double' User defined function: calculate the actual completion rate = number of checked items / number of itemsDim lngRowCount As LongDim lngCheckedItems As LongDim lngItems As LongDim lngColumns As Long&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; On Error Resume Next&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; ' Initialize&nbsp;&nbsp;&nbsp; lngColumns = rngCheckList.Columns.Count&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; For lngRowCount = 1 To rngCheckList.Rows.Count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If IsItem(rngCheckList(lngRowCount, 1)) Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lngItems = lngItems + 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If rngCheckList(lngRowCount, lngColumns) = C_DONE Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Item is checked&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lngCheckedItems = lngCheckedItems + 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp; Next lngRowCount&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; CompletionRate = lngCheckedItems / lngItems&nbsp;&nbsp; &nbsp;End Function&nbsp;

Forum Discussion

Help understanding this code

Share

Resources