ArrayToList

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal _
hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long
Private Declare Function LockWindowUpdate Lib "user32" Alias "LockWindowUpdate" _
(ByVal hwndLock As Long) As Long

Private Const LB_ADDSTRING = &H180
Private Const LB_RESETCONTENT = &H184
Private Const CB_ADDSTRING = &H143
Private Const CB_RESETCONTENT = &H14B

' Add an array of string to a listbox or combo box

' using API functions, and optionally clearing it first

' This procedure is faster than the one based on AddItem methods,

' especially with ComboBox controls, and reduces flickering

'

' FIRST and LAST indicate which portion of the array

' should be considered; they default to the first

' and last element, respectively


Sub ArrayToListBox(ctrl As Object, arr As Variant, Optional clearIt As Boolean, _
Optional ByVal First As Variant, Optional ByVal Last As Variant)
Dim msgReset As Long
Dim msgAdd As Long
Dim hWnd As Long
Dim index As Long

If TypeOf ctrl Is ListBox Then
msgReset = LB_RESETCONTENT
msgAdd = LB_ADDSTRING
ElseIf TypeOf ctrl Is ComboBox Then
msgReset = CB_RESETCONTENT
msgAdd = CB_ADDSTRING
Else
' none of the above, exit

Exit Sub
End If

If IsMissing(First) Then First = LBound(arr)
If IsMissing(Last) Then Last = UBound(arr)

' disable redrawing

hWnd = ctrl.hWnd
LockWindowUpdate hWnd

' clear control if requested

If clearIt Then
SendMessage hWnd, msgReset, 0, 0
End If

' add all items in the array

For index = First To Last
SendMessage hWnd, msgAdd, 0, ByVal CStr(arr(index))
Next
' re-enable redrawing

LockWindowUpdate 0
End Sub