possible problem with referencing an array

[StrawS]

Hi,
I have a listbox which I am trying to populate with an array.

the line of code I am using to do this is

listbox1.Column() = d()

My sub is stalling on this line though, with error message

Run-time error 91: Object variable or With block variable not set

This seems like an odd error message to me since

• a - it's not in a With block
  b - I have dimensioned the array, and have filled it.

am I missing anything here? Perhaps I'm referencing the array wrong. When I rest my cursor over that line in debug mode the comment that pops up shows that the error refers to the array reference.

I'm sure it's possible the problem may lie in another part of my code but I thought I'd try this first

Any help would be much appreciated.
Thanks for your time.

Straws

 

[sunnyland]

hello,
Try using the list property as follow

Sub fillit()
Dim vari As Variant
vari = Array("Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi")
ListBox1.List = vari

End Sub

 

[StrawS]

Thanks for taking the time to reply sunnyland

I had tried that before.... but with the same result.

I wanted to use the .column since it transposes the array... and in this case I need that.

Still a bit baffled

Straws

 

[sunnyland]

Hello again,

I tried with a 2 dimension array and it works for me: check the dimension of your array may be:

Dim myarray(0 To 5, 0 To 3) As String

myarray(0, 0) = "Name"
myarray(1, 0) = "Surname"
myarray(2, 0) = "Age"
myarray(3, 0) = "Address"
myarray(4, 0) = "Town"
myarray(5, 0) = "Country"

myarray(0, 1) = "Jeff"
myarray(1, 1) = "Proud"
myarray(2, 1) = "22"
myarray(3, 1) = "Rue des Soupirs"
myarray(4, 1) = "Beziers"
myarray(5, 1) = "France"


myarray(0, 2) = "Jwen"
myarray(1, 2) = "Lion"
myarray(2, 2) = "36"
myarray(3, 2) = "Road to nowhere"
myarray(4, 2) = "London"
myarray(5, 2) = "England"

myarray(0, 3) = "Jwennie"
myarray(1, 3) = "Lionne"
myarray(2, 3) = "16"
myarray(3, 3) = "Road to where"
myarray(4, 3) = "London"
myarray(5, 3) = "England"

ListBox1.Column() = myarray
End Sub


Same result can be done easier using a range let's say using a named range called mydat corresponding to the data :

Book1
	B	C	D	E	F
19	Name	Surname	Age	Address	Town
20	Jeff	Proud	22	RuedesSoupirs	Roadtonowhere
21	Jwen	Lion	36	Beziers	London
22	Jwennie	Lionne	16	Roadtowhere	London
Sheet1

now to populate your listbox one sentence is enough:

ListBox1.Column() = WorksheetFunction.Transpose(Range("mydat"))

and you get the same result as prior way.

If you don't want the user to see or modify the listbox source then put them on a different worksheet and set property to not visible

 

[sunnyland]

Hello again :

in fact using

ListBox1.List() = WorksheetFunction.Transpose(Range("mydat"))

and

ListBox1.column() = WorksheetFunction.Transpose(Range("mydat"))

allows to joggle between the transposition

 

[sunnyland]

Hello may be went a bit too fast for the answer in fact using:

this 2 subs will transpose your listboxsource

Sub columnheader_To_rowheader()
ListBox1.Column() = WorksheetFunction.Transpose(Range("mydat"))
End Sub

Sub rowheader_To_columnheader()
ListBox1.List() = WorksheetFunction.Transpose(Range("mydat"))
End Sub

Sorry for my explanation before.

Try this out having :

Book1
	B	C	D	E	F
19	Name	Surname	Age	Address	Town
20	Jeff	Proud	22	RuedesSoupirs	Roadtonowhere
21	Jwen	Lion	36	Beziers	London
22	Jwennie	Lionne	16	Roadtowhere	London
Sheet1

something like that
and run each one after naming the range mydat .

You will see that the list box change transposition

 

[StrawS]

Hi sunnyland.
sorry I haven't replied for a while - been away from this project at work.

Thanks for your replies, in comparing your code to mine I think it's possible there's something wrong with the dimensioning of my listbox... namely cause I you haven't dimensioned the listbox in your code...

at the moment I have

Dim listbox1 As MSForms.ListBox

could this be interferring with things?

 

[StrawS]

Hello again!

It's fixed!
I hadn't fully grasped a couple of basic concepts and hadn't made a userform with a listbox1 on it
err. yes so... I thought I could just do it from inside the macro... by dimensioning it and a few other things, but actually I needed to make a whole new form.

It all makes sense now. Sorry to drag you along with that wild goose chase sunnyland.

Thanks for trying to help all the same!

Straws