Advertisement
sinancetinkaya

Cell value(number) increase/decrease counter for P.Action

Oct 7th, 2017
92
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VB.NET 2.49 KB | None | 0 0
  1. Public Col As New Collection
  2.  
  3. Function Counter(CELL As Range, direction As String)
  4.     Dim cell_address As String
  5.     cell_address = CELL.Address
  6.     new_value = CELL.Value
  7.    
  8.     If cHas(Col, cell_address) Then
  9.         arr = cGet(Col, cell_address)
  10.         old_value = arr(0)
  11.         sum_counter = arr(1)
  12.         minus_counter = arr(2)
  13.        
  14.         If new_value > old_value And direction = "+" Then
  15.             sum_counter = sum_counter + 1
  16.             cSet Col, cell_address, Array(new_value, sum_counter, minus_counter)
  17.             Counter = sum_counter
  18.         ElseIf new_value < old_value And direction = "-" Then
  19.             minus_counter = minus_counter + 1
  20.             cSet Col, cell_address, Array(new_value, sum_counter, minus_counter)
  21.             Counter = minus_counter
  22.         Else
  23.             If direction = "+" Then Counter = sum_counter
  24.             If direction = "-" Then Counter = minus_counter
  25.         End If
  26.     Else
  27.         cSet Col, cell_address, Array(new_value, 0, 0)
  28.         Counter = 0
  29.     End If
  30. End Function
  31.  
  32. Private Function cGet(ByRef Col As Collection, Key As String) As Variant
  33.     If Not cHas(Col, Key) Then Exit Function
  34.     On Error Resume Next
  35.         Err.Clear
  36.         Set cGet = Col(Key)(1)
  37.         If Err.Number = 13 Then
  38.             Err.Clear
  39.             cGet = Col(Key)(1)
  40.         End If
  41.     On Error GoTo 0
  42.     If Err.Number <> 0 Then Call Err.Raise(Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext)
  43. End Function
  44.  
  45. Private Sub cSet(ByRef Col As Collection, Key As String, Item As Variant)
  46.     If (cHas(Col, Key)) Then Col.Remove Key
  47.     Col.Add Array(Key, Item), Key
  48. End Sub
  49.  
  50. Public Function cHas(Col As Collection, Key As String) As Boolean
  51.     cHas = True
  52.     On Error Resume Next
  53.         Err.Clear
  54.         Col (Key)
  55.         If Err.Number <> 0 Then
  56.             cHas = False
  57.             Err.Clear
  58.         End If
  59.     On Error GoTo 0
  60. End Function
  61. Private Sub cRemove(ByRef Col As Collection, Key As String)
  62.     If cHas(Col, Key) Then Col.Remove Key
  63. End Sub
  64. Private Function cKeys(ByRef Col As Collection) As String()
  65.     Dim Initialized As Boolean
  66.     Dim Keys() As String
  67.  
  68.     For Each Item In Col
  69.         If Not Initialized Then
  70.             ReDim Preserve Keys(0)
  71.             Keys(UBound(Keys)) = Item(0)
  72.             Initialized = True
  73.         Else
  74.             ReDim Preserve Keys(UBound(Keys) + 1)
  75.             Keys(UBound(Keys)) = Item(0)
  76.         End If
  77.     Next Item
  78.  
  79.     cKeys = Keys
  80. End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement