Matching index items in an array or list??

I have three sets of data: a list of Ampacity numbers, a list of "set" quantities, and a list of maximum amperages.

What I'm trying to do is provide a form where a user can select the ampacity from a list box and have the routine return the number of sets of max amps.
Example:

"Apacity = " 510 'User selects 510
"Requires" 2 "sets of" 255 "amp wire" 'routine returns 2 & 255

I believe this can be done with a multidimesional array - but I'm having trouble getting my head around it. In the past in VB I have bypassed the need for an array by creating "invisible lists". I'd really like to do this right, but I need some help.

Can anyone give me a short example of creating and using a 3D array? Or am I totally off base and this is not the way to do it?

Thanks in advance for any help!

 

Researching arrays a bit more, it seems I may be off in my description - maybe this will help, maybe make it worse - but here goes:

Simplfied example:
I have a name list: "Bob, Joe, Sue"
a department list: "CAD, Accounting, Marketing"
and a number list: "1, 2, 3"

If the user selects "Bob" from a combobox, how can I get the routine to match "Bob" with "CAD" & "1" which would be the matching index/array/list items?


I have three sets of data: a list of Ampacity numbers, a list of "set" quantities, and a list of maximum amperages.

What I'm trying to do is provide a form where a user can select the ampacity from a list box and have the routine return the number of sets of max amps.
Example:

"Apacity = " 510 'User selects 510
"Requires" 2 "sets of" 255 "amp wire" 'routine returns 2 & 255

I believe this can be done with a multidimesional array - but I'm having trouble getting my head around it. In the past in VB I have bypassed the need for an array by creating "invisible lists". I'd really like to do this right, but I need some help.

Can anyone give me a short example of creating and using a 3D array? Or am I totally off base and this is not the way to do it?

Thanks in advance for any help!

 

hey pkirill,
Where are you getting the list from? It looks like you'd have a table/spreadsheet with three (or more) columns:
Name, Dept, Number List.

Why not use this source?

You could also use a collection of user defined types or classes as well...

The problem with the later two is keeping your source up to date.
If Bob, Joe, or Sue changes departments, or has a new number, how will you update those? If they're in a table or spreadsheet, it'll be easy for you or someone else to maintain....

 

you made it a little easier, I think. Here's one example...

Sub array_example()
Dim nam(0 To 2) As String, dept(0 To 2) As String
Dim num(0 To 2) As String, i As Integer
nam(0) = "Bob": dept(0) = "CAD": num(0) = 1
nam(1) = "Joe": dept(1) = "Accounting": num(1) = 2
nam(2) = "Sue": dept(2) = "Marketing": num(2) = 3
i = 1
MsgBox nam(i) & ", " & dept(i) & ", " & num(i)
End Sub

Many people in the NG would probably tell you to use a Collection (search
the NG for more on collections) and fill it with strings composed of the 3
values separated by a unique symbol. Such as ...

colData.AddItem ("Bob;CAD;1")
colData.AddItem ("Joe;Accounting;2")
colData.AddItem ("Sue;Marketing;3")

You'd use the SPLIT function to break the strings into your 3 values.
Collections give you some built-in tools over arrays. I just haven't used
them as much, so I'm not the expert.

Good luck,
James
Researching arrays a bit more, it seems I may be off in my description -
maybe this will help, maybe make it worse - but here goes:

Simplfied example:
I have a name list: "Bob, Joe, Sue"
a department list: "CAD, Accounting, Marketing"
and a number list: "1, 2, 3"

If the user selects "Bob" from a combobox, how can I get the routine to
match "Bob" with "CAD" & "1" which would be the matching index/array/list
items?


I have three sets of data: a list of Ampacity numbers, a list of "set"
quantities, and a list of maximum amperages.

What I'm trying to do is provide a form where a user can select the ampacity
from a list box and have the routine return the number of sets of max amps.
Example:

"Apacity = " 510 'User selects 510
"Requires" 2 "sets of" 255 "amp wire" 'routine returns 2 & 255

I believe this can be done with a multidimesional array - but I'm having
trouble getting my head around it. In the past in VB I have bypassed the
need for an array by creating "invisible lists". I'd really like to do this
right, but I need some help.

Can anyone give me a short example of creating and using a 3D array? Or am I
totally off base and this is not the way to do it?

Thanks in advance for any help!

 

Something like this maybe???

'***Start Module
Option Explicit

Public Enum JoeSimith
Department = 0
EmployeeStatus = 1
End Enum

Sub test()
Debug.Print "Joe Simith" & " " & _
fDepartments(JoeSimith.Department) & _
" " & fEmployeeStatus(JoeSimith.EmployeeStatus)
End Sub

Function fDepartments(inVal)
Select Case fDepartments
Case 0: fDepartments = "Marketing"
Case 1: fDepartments = "CAD"
End Select
End Function

Function fEmployeeStatus(inVal)
Select Case fEmployeeStatus
Case 0: fEmployeeStatus = "Good"
Case 1: fEmployeeStatus = "Bad"
End Select
End Function

'***End Module

gl

Paul
Researching arrays a bit more, it seems I may be off in my description - maybe this will help, maybe make it worse - but here goes:

Simplfied example:
I have a name list: "Bob, Joe, Sue"
a department list: "CAD, Accounting, Marketing"
and a number list: "1, 2, 3"

If the user selects "Bob" from a combobox, how can I get the routine to match "Bob" with "CAD" & "1" which would be the matching index/array/list items?


I have three sets of data: a list of Ampacity numbers, a list of "set" quantities, and a list of maximum amperages.

What I'm trying to do is provide a form where a user can select the ampacity from a list box and have the routine return the number of sets of max amps.
Example:

"Apacity = " 510 'User selects 510
"Requires" 2 "sets of" 255 "amp wire" 'routine returns 2 & 255

I believe this can be done with a multidimesional array - but I'm having trouble getting my head around it. In the past in VB I have bypassed the need for an array by creating "invisible lists". I'd really like to do this right, but I need some help.

Can anyone give me a short example of creating and using a 3D array? Or am I totally off base and this is not the way to do it?

Thanks in advance for any help!

 

oops... Fixed Select Case in both Functions...

'***Start Module
Option Explicit

Public Enum JoeSimith
Department = 0
EmployeeStatus = 1
End Enum

Sub test()
Debug.Print "Joe Simith" & " " & _
fDepartments(JoeSimith.Department) & _
" " & fEmployeeStatus(JoeSimith.EmployeeStatus)
End Sub

Function fDepartments(inVal)
Select Case inVal
Case 0: fDepartments = "Marketing"
Case 1: fDepartments = "CAD"
End Select
End Function

Function fEmployeeStatus(inVal)
Select Case inVal
Case 0: fEmployeeStatus = "Bad"
Case 1: fEmployeeStatus = "Good"
End Select
End Function
'***End Module
Something like this maybe???

'***Start Module
Option Explicit

Public Enum JoeSimith
Department = 0
EmployeeStatus = 1
End Enum

Sub test()
Debug.Print "Joe Simith" & " " & _
fDepartments(JoeSimith.Department) & _
" " & fEmployeeStatus(JoeSimith.EmployeeStatus)
End Sub

Function fDepartments(inVal)
Select Case fDepartments
Case 0: fDepartments = "Marketing"
Case 1: fDepartments = "CAD"
End Select
End Function

Function fEmployeeStatus(inVal)
Select Case fEmployeeStatus
Case 0: fEmployeeStatus = "Good"
Case 1: fEmployeeStatus = "Bad"
End Select
End Function

'***End Module

gl

Paul
Researching arrays a bit more, it seems I may be off in my description - maybe this will help, maybe make it worse - but here goes:

Simplfied example:
I have a name list: "Bob, Joe, Sue"
a department list: "CAD, Accounting, Marketing"
and a number list: "1, 2, 3"

If the user selects "Bob" from a combobox, how can I get the routine to match "Bob" with "CAD" & "1" which would be the matching index/array/list items?


I have three sets of data: a list of Ampacity numbers, a list of "set" quantities, and a list of maximum amperages.

What I'm trying to do is provide a form where a user can select the ampacity from a list box and have the routine return the number of sets of max amps.
Example:

"Apacity = " 510 'User selects 510
"Requires" 2 "sets of" 255 "amp wire" 'routine returns 2 & 255

I believe this can be done with a multidimesional array - but I'm having trouble getting my head around it. In the past in VB I have bypassed the need for an array by creating "invisible lists". I'd really like to do this right, but I need some help.

Can anyone give me a short example of creating and using a 3D array? Or am I totally off base and this is not the way to do it?

Thanks in advance for any help!

 

These are all very helpful and I'm certainly learning a lot and I thank you all!

But, isn't there a way to have three lists of data (ListA, ListB, & ListC) and to be able to say "If the item selected from ListA is listindex item 5, then select listindex item 5 from ListB and ListC".

Something like this:

intItemA = cboListA.listindex

strItemA = ListA.item(intItemA)
strItemB = ListB.item(intItemA)
strItemC = ListC.item(intItemA)

Other than creating invisible List Boxes, I don't know how to do it. Can you pinpoint an item location in an array like you can in a list box?
oops... Fixed Select Case in both Functions...

'***Start Module
Option Explicit

Public Enum JoeSimith
Department = 0
EmployeeStatus = 1
End Enum

Sub test()
Debug.Print "Joe Simith" & " " & _
fDepartments(JoeSimith.Department) & _
" " & fEmployeeStatus(JoeSimith.EmployeeStatus)
End Sub

Function fDepartments(inVal)
Select Case inVal
Case 0: fDepartments = "Marketing"
Case 1: fDepartments = "CAD"
End Select
End Function

Function fEmployeeStatus(inVal)
Select Case inVal
Case 0: fEmployeeStatus = "Bad"
Case 1: fEmployeeStatus = "Good"
End Select
End Function
'***End Module
Something like this maybe???

'***Start Module
Option Explicit

Public Enum JoeSimith
Department = 0
EmployeeStatus = 1
End Enum

Sub test()
Debug.Print "Joe Simith" & " " & _
fDepartments(JoeSimith.Department) & _
" " & fEmployeeStatus(JoeSimith.EmployeeStatus)
End Sub

Function fDepartments(inVal)
Select Case fDepartments
Case 0: fDepartments = "Marketing"
Case 1: fDepartments = "CAD"
End Select
End Function

Function fEmployeeStatus(inVal)
Select Case fEmployeeStatus
Case 0: fEmployeeStatus = "Good"
Case 1: fEmployeeStatus = "Bad"
End Select
End Function

'***End Module

gl

Paul
Researching arrays a bit more, it seems I may be off in my description - maybe this will help, maybe make it worse - but here goes:

Simplfied example:
I have a name list: "Bob, Joe, Sue"
a department list: "CAD, Accounting, Marketing"
and a number list: "1, 2, 3"

If the user selects "Bob" from a combobox, how can I get the routine to match "Bob" with "CAD" & "1" which would be the matching index/array/list items?


I have three sets of data: a list of Ampacity numbers, a list of "set" quantities, and a list of maximum amperages.

What I'm trying to do is provide a form where a user can select the ampacity from a list box and have the routine return the number of sets of max amps.
Example:

"Apacity = " 510 'User selects 510
"Requires" 2 "sets of" 255 "amp wire" 'routine returns 2 & 255

I believe this can be done with a multidimesional array - but I'm having trouble getting my head around it. In the past in VB I have bypassed the need for an array by creating "invisible lists". I'd really like to do this right, but I need some help.

Can anyone give me a short example of creating and using a 3D array? Or am I totally off base and this is not the way to do it?

Thanks in advance for any help!

 

there is pkirill, but i think folks are trying to help you realize that you may NOT want three seperate arrays to store the data. you're really looking at a user defined type or perhaps a class. either way, when you think about it, each person has at least two other related pieces of info.

I want to ask you again, how are you planning on creating these associations in the first place??

using user defined types or classes would allow you to create an array or a collection.

(module code)

Code:

Option Explicit

Public Type EMP
Name As String
Dept As String
Product As String
End Type

(code in form)

Code:

Private Sub test()
Dim udtEmp As EMP

udtEmp.Name = "Joe Bishop"
udtEmp.Dept = "101"
udtEmp.Product = "005"

End Sub

 

am I missing something? are you looking for something more complicated than...
Sub TestArray()
'Ampacity numbers
'"set" quantities
'maximum amperages

Dim Ampacity() As Variant
Dim Quantities() As Variant
Dim Maximum() As Variant
Dim sTarget As String
'sTarget = "120"
'sTarget = "240"
'sTarget = "510"
'sTarget = "10000"

'obviously bogus values since I have no idea what you're doing
'
Ampacity = Array("120", "240", "510", "10000")
Quantities = Array(1, 2, 2, 10)
Maximum = Array(120, 120, 255, 1000)

Dim iIdx As Integer
'as example to see all values
'in your program sTarget is set by choice from combobox
For iIdx = 0 To 3
sTarget = Ampacity(iIdx)
Select Case sTarget
Case Is = Ampacity(iIdx)
Debug.Print "For ampacity : " & Ampacity(iIdx)
Debug.Print "Required: " & Quantities(iIdx) & " conductors at " & Maximum(iIdx)
End Select
Next

End Sub



These are all very helpful and I'm certainly learning a lot and I thank you all!

But, isn't there a way to have three lists of data (ListA, ListB, & ListC) and to be able to say "If the item selected from ListA is listindex item 5, then select listindex item 5 from ListB and ListC".

Something like this:

intItemA = cboListA.listindex

strItemA = ListA.item(intItemA)
strItemB = ListB.item(intItemA)
strItemC = ListC.item(intItemA)

Other than creating invisible List Boxes, I don't know how to do it. Can you pinpoint an item location in an array like you can in a list box?

 

However, I agree with the other answers that suggest having 3 separate arrays is a problematic way to store related information.
A collection would be one way to group the values more closely together for ease of maintenance and code clarity (imho)
Sub TestArray2()
'Ampacity numbers
'quantities
'maximum amperages

Dim AmpVals As Collection
Set AmpVals = New Collection
Dim AmpVal(0 To 2) As String

Dim sTarget As String
'create association lists as needed and put into collection
AmpVal(0) = "120"
AmpVal(1) = "1"
AmpVal(2) = "120"
AmpVals.Add AmpVal

AmpVal(0) = "240"
AmpVal(1) = "2"
AmpVal(2) = "120"
AmpVals.Add AmpVal

AmpVal(0) = "510"
AmpVal(1) = "2"
AmpVal(2) = "255"
AmpVals.Add AmpVal

AmpVal(0) = "10000"
AmpVal(1) = "10"
AmpVal(2) = "100"
AmpVals.Add AmpVal



Dim iIdx As Integer
'as example to see all values
'in your program sTarget is set by choice from combobox
For iIdx = 1 To AmpVals.Count
sTarget = AmpVals(iIdx)(0)
Select Case sTarget
Case Is = AmpVals(iIdx)(0)
Debug.Print "For ampacity : " & AmpVals(iIdx)(0)
Debug.Print "Required: " & AmpVals(iIdx)(1) & " conductors at " & AmpVals(iIdx)(2)
End Select
Next

Set AmpVals = Nothing
End Sub

 

and another approach might be the class method mentioned above

I have three sets of data: a list of Ampacity numbers, a list of "set"
quantities, and a list of maximum amperages.


one possible version:

put this code in classmodule and name it clsLoadVal (or change references
and name to your liking)
'begin class code
Option Explicit
Private mlNumCond As Long
Private mlAmpVal As Long

'here the class encapsulates the data
'hard code what ever value combinations are meaningful in your context
Property Let Load(lLoad As Long)
Select Case lLoad
Case Is = 120
mlNumCond = 1
mlAmpVal = 120
Case Is = 240
mlNumCond = 2
mlAmpVal = 120
Case Is = 510
mlNumCond = 2
mlAmpVal = 255
Case Is = 10000
mlNumCond = 10
mlAmpVal = 1000
Case Else
Err.Raise vbError + 1, "clsLoadVal", "Out of Range Value"
End Select
End Property

'read only property
Property Get Conductors() As Long
Conductors = mlNumCond
End Property

'read only property
Property Get AmpVal() As Long
AmpVal = mlAmpVal
End Property

'end class code


then in standard module put this code
'begin module code
Sub TestClass()
Dim cLoad As clsLoadVal
Set cLoad = New clsLoadVal
Dim lLoad As Long

lLoad = 120
cLoad.Load = lLoad
Debug.Print "For load of " & lLoad
Debug.Print "Require " & cLoad.Conductors & " conductors at " & cLoad.AmpVal

lLoad = 240
cLoad.Load = lLoad
Debug.Print "For load of " & lLoad
Debug.Print "Require " & cLoad.Conductors & " conductors at " & cLoad.AmpVal

lLoad = 510
cLoad.Load = lLoad
Debug.Print "For load of " & lLoad
Debug.Print "Require " & cLoad.Conductors & " conductors at " & cLoad.AmpVal

lLoad = 10000
cLoad.Load = lLoad
Debug.Print "For load of " & lLoad
Debug.Print "Require " & cLoad.Conductors & " conductors at " & cLoad.AmpVal

On Error GoTo Errhand

lLoad = 100000
cLoad.Load = lLoad
Debug.Print "For load of " & lLoad
Debug.Print "Require " & cLoad.Conductors & " conductors at " & cLoad.AmpVal


Errhand:
If Err Then '
MsgBox "Error in: " & Err.Source & vbCrLf & Err.Description
End If


Set cLoad = Nothing
End Sub

'begin module code

if running from ide,
to see error trapping set Tools|Options|General|Error Trapping to "break on
unhandled errors"

 

Or try this

Dim Ampacitiy(500,3) as string
Ampacity(1,0)="510"
Ampacity(1,1)="2"
Ampacity(1,2)="255"

The above lines so on and so forth. Next

For I =1 to 500
If Ampacity(i,0)="500" then
CmdString=Ampacity(i,1)+Ampacity(i,2)
endif
next

 

I asked earlier:

You can see what's been suggested above and why I keep asking. You've got to have this data sitting somewhere already (perhaps your accounting system?). If you don't, you'll have to create the relationship(s). Would you prefer to do this with code or a spreadsheet / table? the latter would be much easier to update & maintain

 

THAT'S IT! Mostly...

First, let me say thanks for all the input and the patience with my evident
inability to grasp the "array" concept. (I have a similar blockage with
databases...)

What I have been given is a list of *55* possible Ampacities - 30A thru
5025A. Each value needs to be associated with a number of wire sets and the
max amps for each set - so an ampacity of 1900 amps requires 5 sets of 380
amp wire.

The user will pick the desired ampacity from a pull down (in this
example1900) and the routine will return "5" and "380" as text values to
concatenate in a string along with selections from two other combo boxes (#
of Poles and type of Neutral).

imagine these are combo boxes:

| # Poles | | Ampacity | | Neutral |
1P 30 None
2P 310 Single
* 3P* *1900* * Double*

If the users selects the items noted with *item*, the string returned will
be:

5-3P380DN

 

pkirill,
Glad you were able to get the help you're after.

If you've managed to dig in this far, you'd do yourself a favor by learning some basic DAO and table skills. At the moment, you have 55 choices. What if you suddenly need to deal with 500+?

Sounds like what you have is working just fine for you now, enjoy

 

What if you suddenly need to deal with 500+?

I know! That was my original fear, but it was whittled down to a mere 55. I
had hoped to get educated in the finer points before I had to produce this
little tool. I didn't all the way there but I am much farther along thanks
to you guys...