Migrating from Global Variables for Forms

[Robynsveil]

Hi. I've been struggling with the concept described in the title for a bit.

What I had =>
I have this little Excel-based 3-form application to manage assets in my department. The initial form displays a listbox of asset items (equipment) and their status (in-service, in-repair, etc.). The equipment is segregated (grouped) by which sort of equipment it is, whether diagnostic, antithrombotic, med-delivery, etc with a 2-character code associated with each type... what displays in the listbox is driven by a "EquipmentType" combobox at the top of the form. At this point - my understanding of arrays being at the extreme-neophyte stage: I'm a nurse, not a programmer, yet - I would populate my combobox in a udf thus:

Dim aList(0 To 12) As String
  
  aList(0) = "Anaesthetic"
  aList(1) = "Appliances"
  aList(2) = "Antithrombotic"
  aList(3) = "Diagnostic"
  aList(4) = "Gases-Medical"
  aList(5) = "Other"
  aList(6) = "Medication Delivery"
  aList(7) = "Monitoring"
  aList(8) = "Patient Warming"
  aList(9) = "Resusitation"
  aList(10) = "Suction"
  aList(11) = "Trolleys-Stands"
  
  With myForm.CboEqType
    .Clear
    For i = 0 To 11
      .AddItem (aList(i))
    Next i
    .ListIndex = 0
  End With
  EquipTypeList = aList(0)

Then, call this from the combobox:

Public Function GetEquipAbbr(TypeName As String) As String
  Select Case TypeName
    Case "Anaesthetic"
      GetEquipAbbr = "AN"
    Case "Appliances"
      GetEquipAbbr = "AP"
    Case "Antithrombotic"
      GetEquipAbbr = "AT"
    Case "Diagnostic"
      GetEquipAbbr = "DI"
    Case "Gases-Medical"
      GetEquipAbbr = "GM"
    Case "Other"
...
  End Select
End Function

... to get the appropriate code for the type of equipment. I know - very kludgy.

Progress so far =>
So, what I've been doing is converting all my global variables - ones that can be seen and manipulated from sub-forms - to properties of the form with Let / Get property statements in the form, like this...

In the variable declaration section where the globals were formerly defined:

Private pWkSht, pSrchType ...etc... As String

and then the Let / Gets:

Public Property Get WkSht() As String
  WkSht = pWkSht
End Property
Public Property Let WkSht(lWkSht As String)
  pWkSht = lWkSht
End Property

Public Property Get SrchType() As String
  SrchType = pSrchType
End Property
Public Property Let SrchType(lSrchType As String)
  pSrchType = lSrchType
End Property

And yay, all this works: what were global variables are now properties exposed to my whole app, which I wanted. But.

What I want to do=>
I sort-of want to fix my combobox behaviour to make a 2-dimentional array where the array itself is a property of the form (and thus, accessible anywhere I need it) and populate my combobox with the first element of:

With myForm
  .gcArrET(0, 0) = "Anaesthetic"
  .gcArrET(0, 1) = "AN"
  .gcArrET(1, 0) = "Appliances"
  .gcArrET(1, 1) = "AP"
  .gcArrET(2, 0) = "Antithrombotic"
  .gcArrET(2, 1) = "AT"
  .gcArrET(3, 0) = "Diagnostic"
  .gcArrET(3, 1) = "DI"
  .gcArrET(4, 0) = "Gases-Medical"
  .gcArrET(0, 0) = "GM"
  .gcArrET(5, 1) = "Other"
  .gcArrET(0, 1) = "OT"
...
  .CboEqType.Clear
  For i = 0 To 11
    .CboEqType.AddItem (.gcArrET(i))
  Next i
  ' Forces showing of the first item in the cboBox,
  ' which also runs PopInvList()
  .CboEqType.ListIndex = 0
End With

I did try the rowsource property and controlsource property for the combobox but didn't like how Excel 2000 worked and how it looked.

Thank you to any and all who take the time to consider my little conundrum and even more thanks to those who respond. I've seen a lot of different solutions on Chip Pearson's site involving setting up a class module, but they seem more like solutions for collections for dynamic arrays: mine is a finite list and I had trouble adapting what I read to my problem.

Thanks again!

 

[xenou]

Hi,
to have an array as a property is no different than another other property really - just use the same procedure. (Example below).
I would probably however prefer to keep the property in the form and a "Get" property only, which is to say read-only from outside of the form. Then the data is "protected" and their is only one place where it can be updated, making the project easier to maintain, since if you need to add to the list you know where to go, and you know that once you make the change it will propagate to the entire application. Alternatively, you could do the reverse - populate the list outside of the userform and force the userform to "call" it to get the list.

Below is an example:

[COLOR="Navy"]Private[/COLOR] gcArrET(0 [COLOR="Navy"]To[/COLOR] 5, 0 [COLOR="Navy"]To[/COLOR] 1) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    
    gcArrET(0, 0) = "Anaesthetic"
    gcArrET(0, 1) = "AN"
    gcArrET(1, 0) = "Appliances"
    gcArrET(1, 1) = "AP"
    gcArrET(2, 0) = "Antithrombotic"
    gcArrET(2, 1) = "AT"
    gcArrET(3, 0) = "Diagnostic"
    gcArrET(3, 1) = "DI"
    gcArrET(4, 0) = "Gases-Medical"
    gcArrET(4, 1) = "GM"
    gcArrET(5, 1) = "Other"
    gcArrET(5, 1) = "OT"
    
    [COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] UBound(gcArrET) [COLOR="SeaGreen"]'//Better than a hard coded upper bound[/COLOR]
        Me.CboEqType.AddItem (gcArrET(i, 0))
    [COLOR="Navy"]Next[/COLOR] i
    Me.CboEqType.ListIndex = 0

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Property[/COLOR] [COLOR="Navy"]Get[/COLOR] ArrET()
    ArrET = gcArrET
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Property[/COLOR]

 

[Robynsveil]

Thank you so much for your help, XeNou. This works a treat.

So, if let's say I didn't want the array to be populated in the Form_Initialise - I tend to do all this sort of thing in functions - I can always send a UDF an array which I would then generate my final gcArrET(x,x) from?

ETA: I ask this because if I do try this in a separate UDF straight away, since gcArrET is private (to the form and to ArrET Get property) I'd have to do a bit of managing... like, passing an array, perhaps?

Actually, that last question is purely academic... this works a treat as is, and thanks HUGEly for the help. I've been struggling with this for *ages*... finally a solution that works! Thanks again!

 

[xenou]

Hi,
Glad it's working. There's several ways you could go about this so in answer to your question it's certainly possible. You *usually* want to populate userform comboboxes in the initialize event, if they are being populated programmatically, but the manner of getting the data is up to you: private property, function, subroutine, local variables, properties or public variables, even ranges on worksheets, etc. etc. etc.

Cheers,
ξ

 

[Robynsveil]

Thanks again... I think I'll just leave as is - in the Form_Initialize - since it is working well. Thanks so much for taking the time! Hope your weekend's good!