additem performance

[Mark]

I have a combobox that is being populated from the contents of a
different worksheet in the same workbook. The problem is that it is
taking over 10 seconds to run - it only runs once during the
workbook_open event.

Here is the code:
==========================================================
Private Sub GeoComboBoxInit(GeoCombo As ComboBox)

Dim i As Integer
Dim ws As Worksheet
Dim wb As Workbook

Set wb = Workbooks(1)

Set ws = wb.Sheets("GeoTier")

For i = 2 To 24183
GeoCombo.AddItem ws.Cells(i, 3)
Next i

End Sub
===============================================================

In a future release I will get the data from a local MS Access
database. How can I speed up the current loading of this combobox?

TIA
mark
markm-at-visionsw.com

 

[Charles Williams]

Try getting the data into a variant first

Private Sub GeoComboBoxInit(GeoCombo As ComboBox)

Dim i As long
Dim vArr as variant


vArr= Workbooks(1).Sheets("GeoTier").range("C2:C24183")

For i = 2 To 24183
GeoCombo.AddItem vArr(i-1,1)
Next i

End Sub


--
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

===============================================================

 

[Tom Ogilvy]

Another possibility is to just assign it in one step:

Private Sub GeoComboBoxInit(GeoCombo As ComboBox)

Dim i As long
Dim vArr as variant

GeoCombo.List = Workbooks(1).Sheets("GeoTier") _
.range("C2:C24183").Value

End Sub