Posted By

lolrenx on 01/04/13


Tagged

excel dictionary vba


Versions (?)

Dictionary loader


 / Published in: Visual Basic
 

short loop to load a dictionary

  1. Public Sub Load_CC_Dico(tgtDico As Dictionary, tgtSht As Worksheet, _
  2. KeyStr As String, H1 As String, Optional H2 As String, Optional H3 As String, _
  3. Optional H4 As String, Optional H5 As String)
  4.  
  5. Dim KeyCol As Long, ColH1 As Long, ColH2 As Long, ColH3 As Long, ColH4 As Long, ColH5 As Long
  6. Dim TempArr As Variant: TempArr = Omit_Header(tgtSht.UsedRange).Value
  7. KeyCol = MatchCol(KeyStr, tgtSht.Rows(1))
  8. ColH1 = MatchCol(H1, tgtSht.Rows(1))
  9. If Len(H2) > 0 Then ColH2 = MatchCol(H2, tgtSht.Rows(1))
  10. If Len(H3) > 0 Then ColH3 = MatchCol(H3, tgtSht.Rows(1))
  11. If Len(H4) > 0 Then ColH4 = MatchCol(H4, tgtSht.Rows(1))
  12. If Len(H5) > 0 Then ColH5 = MatchCol(H5, tgtSht.Rows(1))
  13.  
  14. Dim Temp_Dico As Dictionary
  15.  
  16. Dim i As Long
  17. For i = LBound(TempArr, 1) To UBound(TempArr, 1)
  18. Application.StatusBar = "Loading Dictionary, " & Calc_Advance(i, UBound(TempArr, 1)) & " % done"
  19. If Not tgtDico.Exists(TempArr(i, KeyCol)) Then
  20. Set Temp_Dico = New Dictionary
  21. With Temp_Dico
  22. .Add H1, TempArr(i, ColH1)
  23. If Len(H2) > 0 Then .Add H2, TempArr(i, ColH2)
  24. If Len(H3) > 0 Then .Add H3, TempArr(i, ColH3)
  25. If Len(H4) > 0 Then .Add H4, TempArr(i, ColH4)
  26. If Len(H5) > 0 Then .Add H5, TempArr(i, ColH5)
  27. End With
  28. tgtDico.Add TempArr(i, KeyCol), Temp_Dico
  29. Set Temp_Dico = Nothing
  30. End If
  31. Next i
  32.  
  33. Application.StatusBar = False
  34. Set Temp_Dico = Nothing
  35.  
  36. Exit Sub
  37.  
  38. ErrFound:
  39.  
  40. MsgBox ("Error")
  41.  
  42. Resume
  43.  
  44. End Sub

Report this snippet  

You need to login to post a comment.