Help me understand Dante's code

[kweaver]

Either Dante or someone else...I'm trying to work through this perfect macro Dante wrote for me but there are many things I don't understand in my novice VBA.

The main thing is the scripting dictionary. I've been trying to read about this but it's not sinking in.

I don't understand, for one, how the "c" is created in the first FOR block, and where does ".Item" come from?

If someone has time to help me understand this, I'm really appreciative.

Sub Test()
  Dim sh As Worksheet, c As Range, ky As Variant, wb As Workbook, wPath As String, lr As Long
  
  Application.SheetsInNewWorkbook = 1
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set sh = Sheets("Reformatted")
  wPath = "C:\trabajo\books\"
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  lr = sh.Range("P" & Rows.Count).End(xlUp).Row
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("P2:P" & lr)
      .Item(c.Value) = Empty
    Next
    For Each ky In .Keys
      sh.Range("A1").AutoFilter 16, ky
      Set wb = Workbooks.Add
      sh.AutoFilter.Range.Range("A2:O" & lr).Copy Range("A1")  'Change 2 to 1 if you also want to copy the header.
      wb.SaveAs wPath & ky
      wb.Close False
    Next
  End With
  sh.ShowAllData
End Sub

 

[mumps]

The "c" is a range variable that is used to loop through the data in column P. The loop adds that value of "c" (item) to the dictionary if it doesn't already exist. This approach is used to add unique values in column P to the dictionary.

 

[kweaver]

OK. Thanks. Where actually is the dictionary? Just sitting in memory or within the macro somewhere/somehow? Where are those unique values stored?

 

[mumps]

The line of code:

With CreateObject("scripting.dictionary")

creates a dictionary which is a temporary storage area in memory where the unique values are stored.

 

[Rick Rothstein]

Either Dante or someone else...I'm trying to work through this perfect macro Dante wrote for me but there are many things I don't understand in my novice VBA.

The main thing is the scripting dictionary. I've been trying to read about this but it's not sinking in.

I don't understand, for one, how the "c" is created in the first FOR block, and where does ".Item" come from?

If someone has time to help me understand this, I'm really appreciative.

Sub Test()
  Dim sh As Worksheet, c As Range, ky As Variant, wb As Workbook, wPath As String, lr As Long
  
  Application.SheetsInNewWorkbook = 1
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set sh = Sheets("Reformatted")
  wPath = "C:\trabajo\books\"
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  lr = sh.Range("P" & Rows.Count).End(xlUp).Row
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("P2:P" & lr)
      .Item(c.Value) = Empty
    Next
    For Each ky In .Keys
      sh.Range("A1").AutoFilter 16, ky
      Set wb = Workbooks.Add
      sh.AutoFilter.Range.Range("A2:O" & lr).Copy Range("A1")  'Change 2 to 1 if you also want to copy the header.
      wb.SaveAs wPath & ky
      wb.Close False
    Next
  End With
  sh.ShowAllData
End Sub

The For..Each statement highlighted in red will iterate each cell in sh.Range(P2:P & lr) one cell at a time. The 'c' variable is declared as a Range variable and will reference each of those cells in the range, one at a time, with each iteration of the loop. As for the .Item... that leading dot says that there is a With statement above it somewhere whose object it will reference. The immediate With statement is the one highlighted in blue... its object is the Dictionary object that the CreateObject function creates... the .Item is one of the properties of a Dictionary object. A .Item property takes a text string argument (which becomes one of the Keys that is in, or gets created in, the Dictionary if it does not currently exist) and gets assigned a value (in this case, the Empty keyword).

 

[kweaver]

Great explanations, mumps & Rick. Thank you very much.

 

[DanteAmor]

Thanks mumps and Rick.

Just add, c is declared as Range, then it takes the properties of a range, from the mentioned range.

Dim [COLOR=#0000ff]c [/COLOR]As Range
    For Each [COLOR=#0000ff]c[/COLOR] In sh.Range("P2:P" & lr)

c.value contains the data of P2, after P3, P4 until P & lr

Then c.value is stored in the dictionary as key.

The value of the dictionary item is empty. In this case it is not necessary.

More about dictionary.
https://excelmacromastery.com/vba-dictionary/