Redim An Array To Load Combo Box
For complete information on arrays, see Arrays
Many programs will process a variable number if items to load into a form's combo box. Using the Redim command, an array can be "max sized", and then based on the actual number of items encountered in the data, it can be redimensioned to the exact number of items in the array. This redimensioning is necessary to load a combo box using an efficient "one line" load statement.
A combo box can also be loaded "one item at a time", but bulk loading using a redimensioned array is the fastest method and requires less code.
Program Code
Option Explicit ' *************************************************************** ' Use Option Base 1 Since I Start Indexing at 1 and not 0 ' *************************************************************** Option Base 1 ' *************************************************************** ' Define The Array With No Value For the Number Of Entries ' *************************************************************** Public strProductID() As String Public Sub DemoRedimArray() ' *************************************************************** ' Before the Array is Used, It Must Be Dimensioned ' To A Maximum Value For The Number Of Entries ' That Could Be Loaded ' *************************************************************** ReDim strProductID(200) ' *************************************************************** ' Dynamically Load Values - Usually Done With A Counter ' But For Demo Purposes, Hard Coded Values Are Used ' *************************************************************** strProductID(1) = "AAAAA" strProductID(2) = "BBBBB" strProductID(3) = "CCCCC" strProductID(4) = "DDDDD" strProductID(5) = "EEEEE" ' *************************************************************** ' When The Number of ACTUAL Entries Loaded in the Array ' Has Been Determined, REDIM The Array with a Preserve ' *************************************************************** ReDim Preserve strProductID(5) ' *************************************************************** ' Load A Form's Combo Box With The Exact Number Of Array Entries ' *************************************************************** frmProducts.cmbProductID.List = strProductID ' *************************************************************** ' Display The Form And Its Entries ' *************************************************************** frmProducts.Show End Sub