Displaying a 1d Array in a spreadsheet and a list-box

[Guest]

Hello

I have an array ListSubFldrs that is a result of a macro that searches for
sub-directory names within a folder. It can vary in size depending on how
many sub-directories there are.

eg. copied from Local Window...

ListSubFldrs : ",Cat,Barclay Mowlem,Allflex,AWU,Burdekin Shire
Council,Business Success Group,Cash Sales,Colorado,Flight Centre,Foot Locker"
: Variant/String

Any suggestions for code that would result in a vertical list in the .xls
starting at A1.

eg.

Cat
Barclay Mowlem
Allflex
AWU
Burdekin Shire Council
Business Success Group
Cash Sales
Colorado
Flight Centre
Foot Locker

How do I then get this list into a list box within a form? Do I refer to the
..xls or the Array 'ListSubFldrs'?

Thanks,

Andrew Heath

 

[Bob Phillips]

Add this function,

Public Function ReturnArray() As Variant
ReturnArray = Array("Cat", "Barclay Mowlem", "Allflex", _
"AWU", "Burdekin Shire Council", _
"Business Success Group", "Cash Sales", _
"Colorado", "Flight Centre", "Foot Locker")
End Function

then on the worksheet, select the target cell, and enter
=TRANSPOSE(ReturnhArray()) as an array, Ctrl-SHift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

If you want to use the result as a function (as in Bob's response), you
will need to select as many (or more) cells as you expect in your
response. One possible (uncompiled) way to code this would be:

function myFunction(...)
...
If Application.Caller.Columns.Count>1 then
myFunction="This can be used only on a single column range"
elseif application.Caller.Rows.Count<ArraySz(myArray) then
myFunction="You need at least " & ArraySz(myArray) _
& " cells for this result"
else
myFunction=application.worksheetfunction.transpose(myArray)
end if
end function

If this code is in a subroutine, one way to code would be:

myDest.resize(ArraySz
(myArray),1).value=application.worksheetfunction.transpose(myArray)

as in

Function ArraySz(anArr)
ArraySz = UBound(anArr) - LBound(anArr) + 1
End Function
Sub testIt2()
Dim myArray, myDest As Range
myArray = Array("Cat", "Barclay Mowlem", "Allflex", _
"AWU", "Burdekin Shire Council", _
"Business Success Group", "Cash Sales", _
"Colorado", "Flight Centre", "Foot Locker")
Set myDest = ActiveCell.Resize(ArraySz(myArray), 1)
If Application.WorksheetFunction.CountA(myDest) = 0 Then
myDest.Value = _
Application.WorksheetFunction.Transpose(myArray)
Else
MsgBox "Insufficient empty cells in column starting with " _
& ActiveCell.Address & " to return result of " _
& ArraySz(myArray) & " elements"
End If
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

Oh, and as far as using the result in a listbox goes, either of the
following is simple enough. {g}

Userform1.ListBox1.RowSource = "Sheet1!L15:L24"

or

UserForm1.ListBox1.List = myArray

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

Option Explicit
Sub AA()
Dim LstSubFldrs As Variant
Dim lngth As Long
LstSubFldrs = Array("Cat", "Barclay Mowlem", "Allflex", _
"AWU", "Burdekin Shire Council", _
"Business Success Group", "Cash Sales", _
"Colorado", "Flight Centre", "Foot Locker")

UserForm1.ListBox1.List = LstSubFldrs
UserForm1.Show

lngth = UBound(LstSubFldrs) - LBound(LstSubFldrs) + 1
Range("A1").Resize(lngth, 1).Value = Application.Transpose(LstSubFldrs)
End Sub