String array initialisation

[major_yash]

Hi,


I am facing a problem in intialising a string array in VBA. I need to
intialise an array of 10 elements from data in a range. This range has
text values. I tried to use following.

Dim VarNames() As String
.....
VarNames = ActiveSheet.Range("K22:K32").Value
.....

But I am getting Type Mismatch error. I don't want to use any loop to
populate the array. Filling each value is cumbersome.

Can anybody please suggest some way around.

Thanks,

Mhr

 

[Piotr Lipski]

Dnia 29 Jun 2006 12:21:48 -0700, (e-mail address removed) napisa³(a):

Dim VarNames

dim r as excel.range, strtmp as string
for each r in range("k22:k32").cells 'btw, that's 11 cells, not 10
strtmp = strtmp & switch(strtmp="", "", true, ";")
next r
VarNames = split(strtmp, ";")

There is probably someting better, but I always use this.

 

[Tim Williams]

declare your VarNames as a variant and drop the ()