title: How to preserve variable value between function calls in vba module?
body: <p>I've created module in vba. I have two functions "AddDropDowns" and "RemoveDropDowns". In first function I would like to store a value in a variable that would be accessible in second function at a later time. I declared a variable called "DropDownsCounter" in the same module as these two functions are in, but the variable does not preserve it's value between function calls. My question is why and how to achieve this? Code of this module is below.</p>
  
  <pre><code>    Option Explicit    
          Private DropDownsCounter As Integer
          Public Const QueryAttributes = "Query1:Query2:Query3:Query4:Query5"
  
          Private Const DropDownsWidth = 70
          Private Const DropDownsHeight = 16.5    
          Function AddDropDowns()
              DropDownsCounter = DropDownsCounter + 1
              Dim QueryAttributesArray() As String
              Dim NumberOfDropDowns As Integer
              QueryAttributesArray() = Split(QueryAttributes, ":")
              Application.ScreenUpdating = False
              Dim x As Integer
              For x = 0 To UBound(QueryAttributesArray)
                  Dim Name As String
                  Name = "DropDown_" &amp; (NumberOfDropDowns + x)
                  Dim CmbBox As OLEObject
                  Set CmbBox = Worksheets("Poizvedba").OLEObjects.Add("Forms.ComboBox.1")
                  With CmbBox
                      .Left = GetLastDropDownLeftPos(DN)
                      .Top = Range(DNStartCell).Top + x * DropDownsHeight
                      .Width = DropDownsWidth
                      .Height = DropDownsHeight
                      .Name = Name
                  End With
              Next x
              Application.ScreenUpdating = True
              End Function
  
      Function RemoveDropDowns()
          Dim QueryAttributesArray() As String
          Dim LastDropDown As Integer
          DropDownsCounter = DropDownsCounter - 9
          QueryAttributesArray() = Split(QueryAttributes, ":")
          Dim OleObj As OLEObject
          For Each OleObj In Worksheets("Poizvedba").OLEObjects
              Dim SplittedObjectName() As String
              SplittedObjectName() = Split(OleObj.Name, "_")
              If SplittedObjectName(0) = "DropDown" Then
                  LastDropDown = SplittedObjectName(1)
              End If
          Next OleObj
          Dim StartIndexToRemove As Integer
          Dim EndIndexToRemove As Integer
          StartIndexToRemove = LastDropDown - UBound(QueryAttributesArray)
          EndIndexToRemove = LastDropDown
          Dim Sh As OLEObject
          For Each Sh In Worksheets("Poizvedba").OLEObjects
              Dim x As Integer
              For x = StartIndexToRemove To EndIndexToRemove
                  If Sh.Name = "DropDown_" &amp; x Then
                      Sh.Delete
                  Exit For
                  End If
              Next x
          Next Sh
      End Function
  
  Private Function GetLastDropDownLeftPos(ByVal DropDownCategory As String) As Integer
      Dim pos As Integer
      pos = Range("A4").Width + Range("B4").Width + DropDownsWidth * DropDownsCounter
      GetLastDropDownLeftPos = pos
  End Function
  </code></pre>
  
  <p><strong>New code that still loses variable value</strong></p>
  
  <p><strong>Worksheet code:</strong></p>
  
  <pre><code>Public QueryDropDownsCollection As New Collection
  
  Public Sub CommandButton1_Click()
      Dim NewQuery As QueryDropDown
      Set NewQuery = New QueryDropDown
      QueryDropDownsCollection.Add NewQuery
      Call NewQuery.Initialize(1, 20, 20, 70, 17, 9)
      NewQuery.AddDropDowns
  End Sub
  
  Public Sub CommandButton2_Click()
      QueryDropDownsCollection(QueryDropDownsCollection.Count - 1).RemoveDropDowns
  End Sub
  </code></pre>
  
  <p><strong>Class Code:</strong></p>
  
  <pre><code>Private pID As Integer
  Private pDropDownsWidth As Integer
  Private pDropDownsHeight As Integer
  Private pLeftPos As Integer
  Private pTopPos As Integer
  Private pNumberOfDropDowns As Integer
  Private pDropDownNames() As String
  
  Property Get ID() As Integer
      ID = pID
  End Property
  
  Private Const DropDownsWidth = 70
  Private Const DropDownsHeight = 16.5
  
  Public Sub Initialize(ByVal ID As Integer, ByVal LeftPos As Integer, ByVal TopPos As Integer, ByVal DropDownsWidth As Integer, ByVal DropDownsHeight As Integer, ByVal NumberOfDropDowns As Integer)
      pID = ID
      pLeftPos = LeftPos
      pTopPos = TopPos
      pDropDownsWidth = DropDownsWidth
      pDropDownsHeight = DropDownsHeight
      pNumberOfDropDowns = NumberOfDropDowns
      pSheet = Sheet
  End Sub
  
  Function AddDropDowns()
      For x = 0 To (pNumberOfDropDowns - 1)
          Dim Name As String
          Name = "DropDown_" &amp; pID &amp; "_" &amp; x
          ReDim Preserve pDropDownNames(0 To x)
          pDropDownNames(x) = Name
          With ActiveSheet.OLEObjects.Add("Forms.ComboBox.1")
              .Left = LeftPos
              .Top = pTopPos + x * pDropDownsHeight
              .Width = pDropDownsWidth
              .Height = pDropDownsHeight
              .Name = Name
              With .Object
                  .AddItem "Krneki1"
              End With
          End With
      Next x
  End Function
  
  Function RemoveDropDowns()
      Dim Sh As OLEObject
      For Each Sh In ActiveSheet.OLEObjects
          Dim x As Integer
          For x = 0 To pNumberOfDropDowns
              If Sh.Name = pDropDownNames(x) Then
                  Sh.Delete
              Exit For
              End If
          Next x
      Next Sh
  End Function
  </code></pre>