array problem

[natanz]

i am missing something. I have a function that takes in a multiselect
listbox and creates a one dimensional array populated with the indices
of the selected items.

Public function LB2Array() as integer

Dim i As Integer
Dim ArrayCount As Integer

ArrayCount = 1

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
ReDim Preserve LBarray(1 To ArrayCount)
LBarray(ArrayCount) = i
ArrayCount = ArrayCount + 1
End If
Next i
End With
LB2Array = LBarray
End Sub

I am now trying to use that array to do something on my spreadsheet,
for example i have this code in my OK button click sub.

'snippet1
....
For i = 0 To UBound(LB2array)
OV = (LB2array(i) + 4)
.Offset(1, OV).Value = "X"
Next i


when i run this, i get a compile error that says "Expected Array" at
the first line of snippet1 above.

Can anyone help me, put these x's in the right cells?

thanks in advance.

[Tim Williams]

Typo?

LBarray vs. LB2array

Tim


"natanz" <natan.zimand@gmail.com> wrote in message
news:1138682555.412703.222550@o13g2000cwo.googlegroups.com...
>i am missing something. I have a function that takes in a multiselect
> listbox and creates a one dimensional array populated with the indices
> of the selected items.
>
> Public function LB2Array() as integer
>
> Dim i As Integer
> Dim ArrayCount As Integer
>
> ArrayCount = 1
>
> With Me.ListBox1
> For i = 0 To .ListCount - 1
> If .Selected(i) Then
> ReDim Preserve LBarray(1 To ArrayCount)
> LBarray(ArrayCount) = i
> ArrayCount = ArrayCount + 1
> End If
> Next i
> End With
> LB2Array = LBarray
> End Sub
>
> I am now trying to use that array to do something on my spreadsheet,
> for example i have this code in my OK button click sub.
>
> 'snippet1
> ...
> For i = 0 To UBound(LB2array)
> OV = (LB2array(i) + 4)
> .Offset(1, OV).Value = "X"
> Next i
>
>
> when i run this, i get a compile error that says "Expected Array" at
> the first line of snippet1 above.
>
> Can anyone help me, put these x's in the right cells?
>
> thanks in advance.
>

[natanz]

thanks but no, i tried that. I tried defining LBarray globally at the
top of the module, (public lbarray () as integer, gave me one error;
public lbarray as integer gave me a different error) and i still had
problems.
i am thinking now about passing an array variable into the 1st sub,
which will redefine the variable, and then using that passed variable
in the second snippet. the second snippet is the main part of the
module where everything is happening.

[Kris]

First.
add option explict to your module and declare all variables.

2.
LBarray is not define inside function. I hate not defined variables
define it as
dim LBarraay () as integer

3.
"Public function LB2Array() as integer" is defined to return single
integer, not an array, so you can't expect that Ubound (LB2Array) will
return anything.

declare is as
Public function LB2Array() as variant.


4.

> For i = 0 To UBound(LB2array)
> OV = (LB2array(i) + 4)

LB2Array is a function without parameters so what LB2array(i) menas? It
tries to call LB2array function


replace it by

Dim LBArray2 as variant
LBArray2 = LB2Array()
For i = 0 To UBound(LBarray2)
OV = (LBarray2(i) + 4)


----
Too many errors to continue.



natanz wrote:
> i am missing something. I have a function that takes in a multiselect
> listbox and creates a one dimensional array populated with the indices
> of the selected items.
>
> Public function LB2Array() as integer
>
> Dim i As Integer
> Dim ArrayCount As Integer
>
> ArrayCount = 1
>
> With Me.ListBox1
> For i = 0 To .ListCount - 1
> If .Selected(i) Then
> ReDim Preserve LBarray(1 To ArrayCount)
> LBarray(ArrayCount) = i
> ArrayCount = ArrayCount + 1
> End If
> Next i
> End With
> LB2Array = LBarray
> End Sub
>
> I am now trying to use that array to do something on my spreadsheet,
> for example i have this code in my OK button click sub.
>
> 'snippet1
> ...
> For i = 0 To UBound(LB2array)
> OV = (LB2array(i) + 4)
> .Offset(1, OV).Value = "X"
> Next i
>
>
> when i run this, i get a compile error that says "Expected Array" at
> the first line of snippet1 above.
>
> Can anyone help me, put these x's in the right cells?
>
> thanks in advance.
>

[Kris]

natanz wrote:
> thanks but no, i tried that. I tried defining LBarray globally at the
> top of the module, (public lbarray () as integer, gave me one error;
> public lbarray as integer gave me a different error) and i still had
> problems.
> i am thinking now about passing an array variable into the 1st sub,
> which will redefine the variable, and then using that passed variable
> in the second snippet. the second snippet is the main part of the
> module where everything is happening.
>

I can;t have LBArray as variable and lbarray as a function.
It's not C++. VBA is case unsensitive.

[natanz]

thank you for your help.

your last line was gratuitously discouraging and unhelpful. I'll just
chalk it up to cultural differences. I am learning this on my own, and
have no formal background. I appreciate the help of strangers such as
yourself, but your editorializing doesn't do me any good.