lundi 28 août 2017

Why are public variables lost after an error?

I have developed the following two subs which create and remove a collection of checkboxes next to a listobject. Each distinct ID in the listobject gets a checkbox. Like this I can approve the listobject entries.

The code is the follwing:

Public CBcollection As Collection
Public CTRLcollection As Collection

Sub create_chbx()
If Approval.CBcollection Is Nothing Then
Dim i As Integer
Dim tbl As ListObject
Dim CTRL As Excel.OLEObject
Dim CB As MSForms.CheckBox
Dim sht As Worksheet
Dim L As Double, T As Double, H As Double, W As Double
Dim rng As Range
Dim ID As Long, oldID As Long

Set CBcollection = New Collection
Set CTRLcollection = New Collection
Set sht = ActiveSheet
Set tbl = sht.ListObjects("ApprovalTBL")
Set rng = tbl.Range(2, 1).Offset(0, -1)
      W = 10
      H = 10
      L = rng.Left + rng.Width / 2 - W / 2
      T = rng.Top + rng.Height / 2 - H / 2

For i = 1 To tbl.ListRows.count
      ID = tbl.Range(i + 1, 1).Value
      If Not (ID = oldID) Then
            Set CTRL = sht.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, DisplayAsIcon:=False, Left:=L, Top:=T, Width:=W, Height:=H)
            Set CB = CTRL.Object
            CBcollection.Add Item:=CB
            CTRLcollection.Add Item:=CTRL
      End If

      Set rng = rng.Offset(1, 0)
      T = rng.Top + rng.Height / 2 - H / 2
      oldID = ID
Next i
End If
End Sub


Sub remove_chbx()
If Not Approval.CBcollection Is Nothing Then
With Approval.CBcollection ' Approval is the module name
      While .count > 0
            .Remove (.count)
      Wend
End With
With Approval.CTRLcollection
      While .count > 0
            .Item(.count).Delete
            .Remove (.count)
      Wend
End With
Set Approval.CBcollection = Nothing
Set Approval.CTRLcollection = Nothing
End If
End Sub

This all works pretty well. No double checkboxes and no errors if there are no checkboxes. I am developing an approval scheme were I need to develop and test other modules. If I now run this sub:

Sub IdoStupidStuff()
Dim i As Integer
Dim Im As Image

i = 1
Set Im = i
End Sub

It will give me an error. If I then try to run one of my checkbox subs they will not work properly anymore. The collection is deleted by the error and I am no longer able to access the collections. Why does this happen and am I able to counter act this other then just not causing errors? Is there a better way to implement such a system were loss of collections is not an issue?




Aucun commentaire:

Enregistrer un commentaire