variables out of context when Click Event in a ListBox

[Gonfer]

Hello Guys, please help

I have this piece of code in my UserForm_Initialize():


r = 0
ReDim MyArray(MyArray_Upto - 1, 5)
Do While Not Rs.EOF
MyArray(r, 0) = Rs.Fields(4)
MyArray(r, 1) = Rs.Fields(5)
MyArray(r, 2) = Rs.Fields(6)
MyArray(r, 3) = Rs.Fields(13)
MyArray(r, 4) = Rs.Fields(14)
MyArray(r, 5) = Rs.Fields(179)

MyArray2(r) = Rs.Fields(4) & " " & Rs.Fields(5) & " " & Rs.Fields(6) & " " & Rs.Fields(13) & " " & Rs.Fields(14) & " " & Rs.Fields(179)
Rs.MoveNext
r = r + 1
Loop

LstPolicies.List() = MyArray2

Rs.Close

I assign to my listbox (LstPolicies) already set in my form and the content from Array2 and I have another Var (MyArray) for keeping each row and column reference from the Record Set. So, I get successfully my listbox filled with MyArray2 and MyArray keeps the matrix by rows and columns for some SELECT.. WHERE queries, so far so good. MY PROBLEM STARTS when I click on my Listbox to choose any option. When the sequence of process start the click event my variables Myarray and MyArray2 set in "Out of Context". However, I have defined both of them as a Public variables in the top of the module showing my form.

I will appreciate if somebody can help me. Any advice or suggestion?

Thanks in advance guys

GONFER

 

[Andrzejek]

Do you have [tt]Option Explicit[/tt] at the top of your code?

Could you show how you declared [tt]MyArray[/tt] and [tt]MyArray2 [/tt]?


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Gonfer]

Thanks Andy for your response

In the Module :

Option Explicit
Public MyArray() As String
Public MyArray2() As String

Sub Button_Retrieve_Click()

UserForm1.Show

End Sub

In my UserForm I have also put "Option Explicit" in the top

GonFer

 

[mintjulep]

Do you need to declare the array's size in the public statement?

Public MyArray(6) as String

 

[Gonfer]

Thanks for the response mintjulep!!

Declaring the arrays with the size was not the solution.

I haven't expertise with VBA. The goofy problem here is originated for a really stupid action in the watch window in VB. The values were really kept in the variables, buy for some "microsoft" reason these get in "out of context" again when the Click event of the listbox happens, however they are in the array yet!!!!.

conclusion "DON"T TRUST ALWAYS IN WHAT THE WATCH WINDOW SHOWS, IF YOU GET AN "OUT OF CONTEXT" VALUE, ADD A NEW WATCH AND SET YOUR VARIABLE AGAIN"

Thank you

 

[combo]

The context of the watch is configurable, right-click the watch in the watch window and set its options.

combo