Thank you very much for your answer. That's certainly a solution using
Transpose function.
Regar
Jan


"Dana DeLouis" skrev i melding
...
Hi. See if using the Dictionary or Collection would work for you.
I prefer the Dictionary.

You might be interested in looking at the following:
The Dictionary object helps to avoid such code.

Sub Demo()
Dim v()
ReDim v(1 To 3, 1 To 2)
v(1, 1) = 11
v(1, 2) = "Joe"
v(2, 1) = 12
v(2, 2) = "Bill"
v(3, 1) = 13
v(3, 2) = "Sue"

'Hmmm. Need to add 1 Record

With WorksheetFunction
v = .Transpose(v)
ReDim Preserve v(1 To 2, 1 To 4)
v = .Transpose(v)
End With
v(4, 1) = 14
v(4, 2) = "New Guy"
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2007


"Jan T." wrote in message
...
Okey, I might have found a solution. What I do is I don't dimention the
array
before I know number of records. Then I dimention the array.

Here's my code; (I get the data from a .mdb database file).

Function varEmplNames() As Variant
Dim varTemp() As Variant
Dim i As Integer
Dim oCnn As Object
Dim oRst As Object
Set oCnn = CreateObject("ADODB.Connection")
Set oRst = CreateObject("ADODB.Recordset")
i = 0
' open the connection
With oCnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open gstrOvertidDBfil ' global variabel holding the file path...
End With
' Define the Recordset
oRst.CursorLocation = adUseServer
' open the table
oRst.Open Source:="EmployeeNames", _
ActiveConnection:=oCnn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
' Check for number of items in db record.
Do
i = i + 1
oRst.MoveNext
Loop While Not oRst.EOF ' Get number of records to dimention the
array...
oRst.MoveFirst
ReDim varTemp(i, 1)
i = 0
Do
varTemp(i, 0) = oRst("EmplId")
varTemp(i, 1) = oRst("FirstName") & " " & oRst("LastName")
i = i + 1
oRst.MoveNext
Loop While Not oRst.EOF
' Close
oRst.Close
oCnn.Close
varEmplNames = varTemp
End Function

------------------------
Well, I wish there was an easier way to count number of records, like
oRst.Count
or something. Anyway, this function seem to works.

If anybody have moore sufficiant code, I will be glad to know.

Thanks, again.

Regards
Jan






"Norman Jones" skrev i melding
...
Hi Jan,

'-----------------
Well, now I want to do like your example but I want first
to fill the Array with values from a db. I dont know how
many items it will contain. When using the Redim Preserve,
I am only allowed to change the Ubound of my Array wich
seems to just give me more Columns and not more Rows.
How do I solve this? Any idea?
'-----------------

Perhaps, try something like:

'=============
Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("Book1") '<<=== CHANGE
Set SH = WB.Sheets("Sheet3") ' <<=== CHANGE
Set Rng = SH.Range("A1:D100") '<<=== CHANGE

With Me.ComboBox1
.ColumnCount = Rng.Columns.Count
.List = Rng.Value
End With
End Sub
'<<=============


---
Regards,
Norman