How to reference a public array (declared in module) from a proced

I can reference a listbox in a Worksheet using the following

Public Sub Procedure1(worksheetName As String, listBoxName As String)
Dim listSize As Integer
listSize = Worksheets(worksheetName).OLEObjects(listBoxName).Object.ListCount
......
end sub

How do I do the same for a public array (declared in a module) with the
following procedure??

Public Sub setSelections(arrayName as String)
????.....
end sub

Thanks!

Assuming arrayname is a single dimension array of type string.

Public Sub setSelections(arrayName as String)
for i = lbound(arrayname) to ubound(arrayname)
sStr = msg & arrayname(i) & ", "
if i mod 5 = 0 then
msgbox msg
sStr = ""
end if
Next
end sub

--
Regards,
Tom Ogilvy


"Amzee" <Amzee@discussions.microsoft.com> wrote in message
news:FAE35677-7C3F-4B75-B9FB-E2B05FC1B61C@microsoft.com...
> I can reference a listbox in a Worksheet using the following
>
> Public Sub Procedure1(worksheetName As String, listBoxName As String)
> Dim listSize As Integer
> listSize =
Worksheets(worksheetName).OLEObjects(listBoxName).Object.ListCount
> .....
> end sub
>
> How do I do the same for a public array (declared in a module) with the
> following procedure??
>
> Public Sub setSelections(arrayName as String)
> ????.....
> end sub
>
> Thanks!

Hi Tom,

It worked fine when I used Public Sub setSelections(arrayName() as String)

Regards.

"Amzee" wrote:

> I can reference a listbox in a Worksheet using the following
>
> Public Sub Procedure1(worksheetName As String, listBoxName As String)
> Dim listSize As Integer
> listSize = Worksheets(worksheetName).OLEObjects(listBoxName).Object.ListCount
> .....
> end sub
>
> How do I do the same for a public array (declared in a module) with the
> following procedure??
>
> Public Sub setSelections(arrayName as String)
> ????.....
> end sub
>
> Thanks!

There were a couple of typos/mistakes. For completeness here is a cleaned
up version (although it sounds like you got what you needed).

Sub Main()
Dim myArr(1 To 10) As String
For i = 1 To 10
myArr(i) = Chr(i + 64) & Chr(i + 65) & Chr(i + 66)
Next
setSelections myArr


End Sub

Public Sub setSelections(ArrayName() As String)
For i = LBound(ArrayName) To UBound(ArrayName)
sStr = sStr & ArrayName(i) & ", "
If i Mod 5 = 0 Then
MsgBox sStr
sStr = ""
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Amzee" <Amzee@discussions.microsoft.com> wrote in message
news:80E7F3AD-A2C6-4F01-B542-B85B5A87D0C4@microsoft.com...
> Hi Tom,
>
> It worked fine when I used Public Sub setSelections(arrayName() as String)
>
> Regards.
>
> "Amzee" wrote:
>
> > I can reference a listbox in a Worksheet using the following
> >
> > Public Sub Procedure1(worksheetName As String, listBoxName As String)
> > Dim listSize As Integer
> > listSize =
Worksheets(worksheetName).OLEObjects(listBoxName).Object.ListCount
> > .....
> > end sub
> >
> > How do I do the same for a public array (declared in a module) with the
> > following procedure??
> >
> > Public Sub setSelections(arrayName as String)
> > ????.....
> > end sub
> >
> > Thanks!

Thanks Tom. It's working fine for me now.


"Tom Ogilvy" wrote:

> There were a couple of typos/mistakes. For completeness here is a cleaned
> up version (although it sounds like you got what you needed).
>
> Sub Main()
> Dim myArr(1 To 10) As String
> For i = 1 To 10
> myArr(i) = Chr(i + 64) & Chr(i + 65) & Chr(i + 66)
> Next
> setSelections myArr
>
>
> End Sub
>
> Public Sub setSelections(ArrayName() As String)
> For i = LBound(ArrayName) To UBound(ArrayName)
> sStr = sStr & ArrayName(i) & ", "
> If i Mod 5 = 0 Then
> MsgBox sStr
> sStr = ""
> End If
> Next
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Amzee" <Amzee@discussions.microsoft.com> wrote in message
> news:80E7F3AD-A2C6-4F01-B542-B85B5A87D0C4@microsoft.com...
> > Hi Tom,
> >
> > It worked fine when I used Public Sub setSelections(arrayName() as String)
> >
> > Regards.
> >
> > "Amzee" wrote:
> >
> > > I can reference a listbox in a Worksheet using the following
> > >
> > > Public Sub Procedure1(worksheetName As String, listBoxName As String)
> > > Dim listSize As Integer
> > > listSize =
> Worksheets(worksheetName).OLEObjects(listBoxName).Object.ListCount
> > > .....
> > > end sub
> > >
> > > How do I do the same for a public array (declared in a module) with the
> > > following procedure??
> > >
> > > Public Sub setSelections(arrayName as String)
> > > ????.....
> > > end sub
> > >
> > > Thanks!
>
>
>