How can one use different element types in a multidimensional array?

[matonanjin]

Another newbie question from me. I did try and search but wasn't sure about search terms.

With a multidimensional array one might do something like:
Dim arrCartridges (1 to 8, 1 to 2) as String which, obviously, would dimension my array as a String.

But what if I want the 1 to 8 to be string format and the 1 to 2 to be integer (or long)? Do I use variant and just leave it as that? Or is there a way the first dimension as one format and the second a different format?

 

[Jeffrey Mahoney]

Here is an example of how to create your own type. The TYPE part has to outside of a SUB

Type Example
  aStr As String
  aLong As Long
  aVar As Variant
End Type


Sub test()
  Dim arrCartridges(1 To 8, 1 To 2) As Example
  
  arrCartridges(1, 1).aStr = "Hey"
  arrCartridges(1, 1).aLong = 1301
  arrCartridges(1, 1).aVar = Range("A1")
  
  
End Sub

 

[DanteAmor]

Hi @matonanjin

I hope you are well.​

I explain your doubts.

First:

Or is there a way the first dimension as one format and the second a different format?

That is not possible. When you declare an array, the entire array will be of one type.

Second:

if I want the 1 to 8 to be string format and the 1 to 2 to be integer (or long)?

Dim arrCartridges (1 to 8, 1 to 2) as String means that you will have 8 rows and each row will be made up of 2 columns. The content of that 8 x 2 array is what will be dimensioned to the String type.
It is not that one dimension can be of one type and the other dimension of another type.

Imagine the following table of 8 rows x 2 columns, each space is the one that will have the type string or long

Or Long type:

If you want 2 different types, then you could declare 2 arrays:

--------------
I hope it helps you with your doubts.
Cordially
Dante Amor
--------------​

 

[matonanjin]

@Jeffrey Mahoney and @DanteAmor thank you both for your thoughtful, helpful responses. I have to study these. I am very appreciative.

 

[matonanjin]

So maybe you guys can tell me why this won't work. I'm sure it has to do with scope but I cant figure it out. And I know it is going to be something embarrassingly obvious.
When the sheet loads it fills this array. And then the array is fed to the combo box.

Sub UserForm_Initialize()

Dim i As Integer
Dim arrCartridges(1 To 8, 1 To 2) As Variant

arrCartridges(1, 1) = "Magenta"
arrCartridges(1, 2) = Range("D16")
arrCartridges(2, 1) = "Photo Cyan"
arrCartridges(2, 2) = Range("E16")
arrCartridges(3, 1) = "Yellow"
arrCartridges(3, 2) = Range("F16")
arrCartridges(4, 1) = "Black"
arrCartridges(4, 2) = Range("G16")
arrCartridges(5, 1) = "Gray"
arrCartridges(5, 2) = Range("H16")
arrCartridges(6, 1) = "Photo Magenta"
arrCartridges(6, 2) = Range("I16")
arrCartridges(7, 1) = "Light Gray"
arrCartridges(7, 2) = Range("J16")
arrCartridges(8, 1) = "Cyan"
arrCartridges(8, 2) = Range("K16")


ComboBox1.ColumnCount = 2
ComboBox1.List = arrCartridges

ComboBox1.ListIndex = 1


End Sub

THEN picking the combobox and command button picks the choice and the message box confirms and then calls the sub where I want to decrement by 1 the cell:

Sub CommandButton1_Click()
Dim answer As Integer
Dim intCartridge As Integer

intCartridge = ComboBox1.ListIndex'MsgBox "So you ran out of " & ComboBox1.Column(0) & " ink?"

answer = MsgBox("Did you runout of " & ComboBox1.Column(0) & " ink?", vbQuestion + vbYesNo, "Canon Ink Cartridges")

If answer = vbYes Then
OutAink
Else

End If

End Sub

BUT I GET Compile Error. Sub of Function not Defined. It is considering my array a function or sub rather than an array.
The "arrCartridges" in the 3rd to last row is highlighted in blue.

Sub OutAink()

Dim intInk As Integer
Dim nC As Integer
intInk = ComboBox1.ListIndex

intInk = intInk + 1
nC=arrCartridges(intInk,2)
nC = nC - 1
Range("I16") = nC

End Sub

 

[DanteAmor]

nC=arrCartridges(intInk,2)

The arrCartridges variable is a local variable, to use it in other procedures, one option is to make it public:

Dim arrCartridges(1 To 8, 1 To 2) As Variant    '<-- At the start of all code

Sub UserForm_Initialize()
  Dim i As Integer
  ' ----- Here you should NO longer put the declaration of arrCartridges -----

  arrCartridges(1, 1) = "Magenta"
  arrCartridges(1, 2) = Range("D16")
  arrCartridges(2, 1) = "Photo Cyan"
  arrCartridges(2, 2) = Range("E16")
  arrCartridges(3, 1) = "Yellow"
  arrCartridges(3, 2) = Range("F16")
  arrCartridges(4, 1) = "Black"
  arrCartridges(4, 2) = Range("G16")
  arrCartridges(5, 1) = "Gray"
  arrCartridges(5, 2) = Range("H16")
  arrCartridges(6, 1) = "Photo Magenta"
  arrCartridges(6, 2) = Range("I16")
  arrCartridges(7, 1) = "Light Gray"
  arrCartridges(7, 2) = Range("J16")
  arrCartridges(8, 1) = "Cyan"
  arrCartridges(8, 2) = Range("K16")
 
  ComboBox1.ColumnCount = 2
  ComboBox1.List = arrCartridges
  ComboBox1.ListIndex = 1
End Sub

Sub CommandButton1_Click()
  Dim answer As Integer
  Dim intCartridge As Integer
 
  intCartridge = ComboBox1.ListIndex 'MsgBox "So you ran out of " & ComboBox1.Column(0) & " ink?"
 
  answer = MsgBox("Did you runout of " & ComboBox1.Column(0) & " ink?", vbQuestion + vbYesNo, "Canon Ink Cartridges")
 
  If answer = vbYes Then
    OutAink
  Else
 
  End If
 
End Sub

Sub OutAink()

  Dim intInk As Integer
  Dim nC As Integer
  intInk = ComboBox1.ListIndex
 
  intInk = intInk + 1
  nC = arrCartridges(intInk, 2)
  nC = nC - 1
  Range("I16") = nC

End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[matonanjin]

@DanteAmor thank you again for the help. And again, educational.