Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
array problem
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
array problem
Typo?
LBarray vs. LB2array Tim "natanz" wrote in message oups.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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
array problem
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
array problem
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
array problem
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
array problem
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array problem | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem? | Excel Programming | |||
Array Problem | Excel Programming |