Setting Array question.

I am gettin an error, and I don't know why. Here is my code:

'this is where I set the values for my array. This is working fine.
   arrCabinetState(1) = myvalue1
   arrCabinetState(2) = myvalue2
   arrCabinetState(3) = myvalue3
   arrCabinetState(4) = myvalue4
   arrCabinetState(5) = myvalue5
   arrCabinetState(6) = myvalue6
   arrCabinetState(7) = myvalue7

'Now I want to add this array to a collection
   colCabinetState.Add Item:=arrCabinetState, key:=CStr(lngRxTrxID)


'Now I want to access this array in the collection in another sub

private sub myFuntion(MyValue7 as String)
Dim index as String
Dim arrCabinetState()

 Set arrCabinetState = (colCabinetState.Item(MyValue7))

Index = arrCabinetState(7)

End Sub

'Any suggestions?

LVL 6
LeeHenryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prasitleeCommented:
Dear LeeHenry,
    What line does your program show the error ? Is it run-time error ?
                                                   Meng
0
appariCommented:
try this
private sub myFuntion(MyValue7 as String)
Dim index as String
Dim arrCabinetState()

arrCabinetState = (colCabinetState.Item(MyValue7))

Index = arrCabinetState(7)

End Sub
0
fantasy1001Commented:
Why do you want to assign back to the array? just query like this:
'this is where I set the values for my array. This is working fine.
   arrCabinetState(1) = myvalue1
   arrCabinetState(2) = myvalue2
   arrCabinetState(3) = myvalue3
   arrCabinetState(4) = myvalue4
   arrCabinetState(5) = myvalue5
   arrCabinetState(6) = myvalue6
   arrCabinetState(7) = myvalue7

'Now I want to add this array to a collection
   colCabinetState.Add arrCabinetState


'Now I want to access this array in the collection in another sub

private sub myFuntion(MyValue7 as String)
Dim index as String
Dim arrCabinetState()

index = colCabinetState.item(1)(7)

End Sub


Thanks & Cheers
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

inthedarkCommented:
See part 2 for a great workarround to this problem.....

Sorry VB collections have some bugs and it just won't work. Also VB has some bugs in its syntax checking which make some legal operations unattainable, example this should work:

Dim V
V = Array(0)
ReDim Preserve V(10) ' this works
V(0) = Array(0)
ReDim Preserve V(0)(10) ' this should works but it fails

So there are a choice of solutions:

1) Save a pointer to an array element in the collection (this works but is not so cute)

Option Explicit

Dim MyArray()
Dim MyIndex As Long

Dim colCabinetState As New Collection

Private Sub Form_Load()
'this is where I set the values for my array. This is working fine.
Dim arrCabinetState
arrCabinetState = Array(0)
ReDim arrCabinetState(7)
arrCabinetState(1) = "a"
arrCabinetState(2) = "B"
arrCabinetState(3) = "B"

MyIndex = MyIndex + 1
If MyIndex = 1 Then
    ReDim MyArray(MyIndex)
Else
    ReDim Preserve MyArray(MyIndex)
End If

ReDim Preserve MyArray(MyIndex)

Dim lngRxTrxID As Long

MyArray(MyIndex) = arrCabinetState

colCabinetState.Add CVar(MyIndex), CStr(lngRxTrxID)
MsgBox MyArray(colCabinetState(CStr(lngRxTrxID)))(1)
ChangeOne CStr(lngRxTrxID)
MsgBox MyArray(colCabinetState(CStr(lngRxTrxID)))(1)

End Sub


Sub ChangeOne(Key)

MyArray(colCabinetState(Key))(1) = "Fred"

End Sub


2) this is cute.......you can place your data in a class module

READ THE COMMENTS IN THE ITEM PROCEDURE

----------------form1.frm  (example form)
Option Explicit

Dim myCollection As New Collection
Sub MySub(MyKey)

myCollection(MyKey)(2) = "this works, "

Dim P As Stuff
Set P = myCollection(MyKey)
P(1) = "and this works too"
Set P = Nothing

End Sub

Private Sub Form_Load()
Dim Stuff As Stuff
' create an object
Set Stuff = New Stuff

' work with the object
Stuff.Redimension 10

Stuff(1) = "Fred"
' add to collection
myCollection.Add Stuff, "AnyKey"

Call MySub("AnyKey")
MsgBox Stuff(2) + Stuff(1)

End Sub

---------------------------Stuff.cls
Option Explicit

Dim MyArray

Public Property Let Item(Element As Long, NewValue)
'Click here, then
' Tools - Procedure attributes
' Advanced - change Procedure ID to default
' otherwise the exmaple won't work.

MyArray(Element) = NewValue

End Property

Public Property Get Item(Element As Long)
Item = MyArray(Element)
End Property

Public Sub Redimension(Elements As Long)
ReDim Preserve MyArray(Elements)
End Sub

Private Sub Class_Initialize()
MyArray = Array(0)
End Sub




0
inthedarkCommented:
PS It works becuase your created your own Array connection object. You can only make pointers to objects which is why it works.
0
Mike TomlinsonMiddle School Assistant TeacherCommented:
Seems to me you are accessing your collection wrong.  Is the point of having a collection to put more than one array into it keyed by lngRxTrxID?

If so, then create a new project, add one command button and paste the code below into the form.

The code creates two different arrays with different keys and then puts them in the collection.  When you hit the button, the results of the function calls are placed directly on the form.  The function allows you to get a particular index out of the array corresponding to the key you passed in.

Hope this helps.

---------------- Begin Code
Dim colCabinetState As New Collection
Dim lngRxTrxID As Long
Dim arrCabinetState(10) As String
Dim lngRxTrxID2 As Long
Dim arrCabinetState2(10) As String

Private Sub Command1_Click()
    ' valid key/index combo's
    Form1.Print "lngRxTrxID = " & lngRxTrxID & " Index 5=" & myFunction(5, lngRxTrxID)
    Form1.Print "lngRxTrxID2 = " & lngRxTrxID2 & " Index 8=" & myFunction(8, lngRxTrxID)
   
    ' bad index
    Form1.Print "lngRxTrxID = " & lngRxTrxID & " Index 99=" & myFunction(99, lngRxTrxID)
   
    ' bad key
    Form1.Print "nosuchid = 99999 Index 8=" & myFunction(8, 99999)
End Sub

Private Sub Form_Load()
    lngRxTrxID = 89235
    arrCabinetState(1) = "a"
    arrCabinetState(2) = "b"
    arrCabinetState(3) = "c"
    arrCabinetState(4) = "d"
    arrCabinetState(5) = "e"
    arrCabinetState(6) = "f"
    arrCabinetState(7) = "g"
    arrCabinetState(8) = "h"
    arrCabinetState(9) = "i"
    arrCabinetState(10) = "j"
   
    lngRxTrxID2 = 47568
    arrCabinetState2(1) = "k"
    arrCabinetState2(2) = "l"
    arrCabinetState2(3) = "m"
    arrCabinetState2(4) = "n"
    arrCabinetState2(5) = "o"
    arrCabinetState2(6) = "p"
    arrCabinetState2(7) = "q"
    arrCabinetState2(8) = "r"
    arrCabinetState2(9) = "s"
    arrCabinetState2(10) = "t"
   
    colCabinetState.Add Item:=arrCabinetState, Key:=CStr(lngRxTrxID)
    colCabinetState.Add Item:=arrCabinetState2, Key:=CStr(lngRxTrxID2)
End Sub

Private Function myFunction(CabinetIndex As Integer, CabinetKey As Long) As String
    Dim localCabinetState As Variant
    On Error GoTo noSuchItem
   
    localCabinetState = colCabinetState.Item(CStr(CabinetKey))
    myFunction = localCabinetState(CabinetIndex)
    Exit Function
   
noSuchItem:
    myFunction = "" ' or use some kind of sentinel value to indicate not found
End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeeHenryAuthor Commented:
inthedark:
I changed it up a bit......

Options Explicit

Dim MyArray()
Dim MyIndex As Long
Dim lngRxTrxID As Long

Dim colCabinetState As New Collection




Sub ChangeOne(Key)

MyArray(colCabinetState(Key))(1) = "Fred"

End Sub



Private Sub Command1_Click()
Dim arrCabinetState
Dim lngRxTrxID As Integer

arrCabinetState = Array(0)
ReDim arrCabinetState(7)

lngRxTrxID = 6 'I am manually changing this value everytime in the code myself. I am
                      'am going to have another function return this value later on. It will
                      'always be a unique number.

arrCabinetState(1) = "a"
arrCabinetState(2) = "B"
arrCabinetState(3) = "C"
arrCabinetState(4) = lngRxTrxID

MyIndex = MyIndex + 1
If MyIndex = 1 Then
    ReDim MyArray(MyIndex)
Else
    ReDim Preserve MyArray(MyIndex)
End If

ReDim Preserve MyArray(MyIndex)

MyArray(MyIndex) = arrCabinetState

colCabinetState.Add Item:=MyArray(MyIndex), Key:=CStr(lngRxTrxID)


End Sub 'See Note Below

//***********************************************************
This code works fine. When its done though, and I clicked 5 times for example. How can I search the collection for the key specified(lngRxTrxID)?
colCabinetState.Add Item:=MyArray(MyIndex), Key:=CStr(lngRxTrxID)

'I could get the count, set a while loop, and search.. But isn't there a faster method.
'Perhaps something like this?

SetNewArray() = colCabinetState(myIndex)(lngRxTrxID)

If (SetNewArray = Nothing) Then
    Exit Sub
End If

'I got an error when trying to set the new array??

Thanks
0
LeeHenryAuthor Commented:
Idle_Mind:

That code works fine also. The only thing is in MyFunction, how could you edit the function to search for the Cabinet Key, but not know the Index?

This doesn't really work, but maybe i'm heading in the right path?


Private Function myFunction(ArrayCount As Integer, CabinetKey As Long) As String
    Dim localCabinetState As Variant
    Dim index as Integer

    On Error GoTo noSuchItem
   
    While Not (index = ArrayCount)    
       localCabinetState = colCabinetState.Item(CStr(CabinetKey))
       myFunction = localCabinetState(CabinetIndex)
       index = index + 1
    Wend

    Exit Function
   
noSuchItem:
    myFunction = "" ' or use some kind of sentinel value to indicate not found
End Function
0
LeeHenryAuthor Commented:
I created a form with a command box that enters the number to put into a collection, and then a command box to check if the number is in that collection. It works, but crashes if you enter more than 8 numbers into the collection.. any suggestions?

Option Explicit

Dim MyArray()
Dim NewArray()
Dim MyIndex As Long
Dim lngRxTrxID As Long

Dim colCabinetState As New Collection




Sub ChangeOne(Key)

MyArray(colCabinetState(Key))(1) = "Fred"

End Sub



Private Sub Command1_Click()
Dim arrCabinetState
Dim lngRxTrxID As Integer
Dim bolFound  As Boolean
Dim intCount As Integer

arrCabinetState = Array(0)
ReDim arrCabinetState(7)

lngRxTrxID = lblInput.Text

arrCabinetState(1) = "a"
arrCabinetState(2) = "B"
arrCabinetState(3) = "C"
arrCabinetState(4) = lngRxTrxID

MyIndex = MyIndex + 1
If MyIndex = 1 Then
    ReDim MyArray(MyIndex)
Else
    ReDim Preserve MyArray(MyIndex)
End If

ReDim Preserve MyArray(MyIndex)

MyArray(MyIndex) = arrCabinetState

colCabinetState.Add Item:=MyArray(MyIndex), Key:=CStr(lngRxTrxID)





'ChangeOne CStr(lngRxTrxID)
'MsgBox MyArray(colCabinetState(CStr(lngRxTrxID)))(MyIndex)

End Sub

Private Sub Command2_Click()
 Dim bolFound As Boolean
 Dim intFindNumber As Integer
 Dim intCount As Integer
 
 intCount = colCabinetState.Count
 intFindNumber = lblFind.Text

 bolFound = myFunction(intCount, CStr(intFindNumber))

MsgBox bolFound
End Sub

Private Sub Form_Load()
'does nothing
End Sub

Private Function myFunction(ArrayCount As Variant, CabinetKey As String) As Boolean

    Dim localCabinetState As Variant
    Dim index As Long
    Dim strResult As Variant
   
    index = 1
    myFunction = True
   
    On Error GoTo noSuchItem
   
    While Not (index = ArrayCount)
       localCabinetState = colCabinetState.Item(CStr(CabinetKey))
       strResult = localCabinetState(index)
       index = index + 1
    Wend

    Exit Function
   
noSuchItem:
    myFunction = False ' or use some kind of sentinel value to indicate not found
End Function



0
LeeHenryAuthor Commented:
Command1 enters the value to collection
Command2 searches for the value in collection

Crashes @ strResult = localCabinetState(index)
in myFunction when ArrayCount is > 8

0
LeeHenryAuthor Commented:
Never mind..

I fixed it
0
Mike TomlinsonMiddle School Assistant TeacherCommented:
In answer to your question earlier about searching for a key:
 
If you search the collection for a key, then you are expecting an entire array back no?

If you add this function to the code I wrote earlier:

Private Function myFunction2(CabinetKey As Long, ByRef targetArray() As String) As Boolean
    On Error GoTo noSuchKey:
   
    targetArray = colCabinetState.Item(CStr(CabinetKey))
    myFunction2 = True
    Exit Function
   
noSuchKey:
    myFunction2 = False
End Function

Then call it like this: (Assuming you typed a key into a textbox called Text1)

Dim newArray() As String
Dim a As Integer

If myFunction2(Text1.Text, newArray) Then
   If UBound(newArray) >= LBound(newArray) Then
      For a = LBound(newArray) To UBound(newArray)
          Form1.Print "lngRxTrxID = " & lngRxTrxID & " Index " & a & "=" & newArray(a)
      Next a
   End If
Else
   MsgBox "No array found for key " & Text1.Text
End If
0
LeeHenryAuthor Commented:
inthedark,

I know this was posted a while ago, but I was looking over the code again, and I was just wondering why ReDim Preserve MyArray(MyIndex) was written in a certain part of the code. There is an If Else statement, and ReDim Preserve MyArray(MyIndex) is called in the else, then called again after the If condition. Is this correct?

**I put comments next to the code I'm talking about**

Dim arrCabinetState
Dim lngRxTrxID As Integer
Dim bolFound  As Boolean
Dim intCount As Integer

arrCabinetState = Array(0)
ReDim arrCabinetState(7)

lngRxTrxID = lblInput.Text

arrCabinetState(1) = "a"
arrCabinetState(2) = "B"
arrCabinetState(3) = "C"
arrCabinetState(4) = lngRxTrxID

MyIndex = MyIndex + 1
If MyIndex = 1 Then
    ReDim MyArray(MyIndex)
Else
    ReDim Preserve MyArray(MyIndex)  ' Called 1st Time Here**
End If

ReDim Preserve MyArray(MyIndex)  'Called again here**

'If in the conditional statement the else clause was met, it is called again after the statement.

MyArray(MyIndex) = arrCabinetState

colCabinetState.Add Item:=MyArray(MyIndex), Key:=CStr(lngRxTrxID)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.