0

Is it possible to create multi dimensional array with different element types (string and integer)? I tried like this but wan't work

BT = Range("A12")
ReDim IT(BT) As String
ReDim RBT(BT) As Integer
ReDim IT_RBT(IT, RBT) as ???? how to create multi dim array with different variables type

Range("B2").Select
i = 0
Do
    i = i + 1
    IT(i) = ActiveCell
    RBT(i) = i
    IT_RBT(i, i) = ????  how to enter values in such array ????
    ActiveCell.Offset(1, 0).Select
Loop While ActiveCell <> ""

Thank you

4
  • 2
    Dim myArray As Variant Commented Feb 20, 2020 at 17:06
  • Think you'll need ubound too. It's not clear what you're trying to achieve. Why do you need a third array? Commented Feb 20, 2020 at 17:08
  • I already tried "As Variant". In that case RBT is highlighted and "type mismatch" error appears. Third array should keep track about connection between elements from first two arrays. Commented Feb 20, 2020 at 17:27
  • 1
    We still don't know why you need 3 arrays, or what you're trying to achieve. Commented Feb 20, 2020 at 17:28

3 Answers 3

2

Use a Variant array.

Dim values() As Variant

Now, your code is making assumptions that should be removed.

BT = Range("A12") '<~ implicit: ActiveSheet.Range("A12").Value

If you mean to pull the value of A12 from a particular specific worksheet, then you should qualify that Range member call with a proper Worksheet object. See CodeName: Sheet1 for more info, but long story short if that sheet is in ThisWorkbook you can do this:

BT = Sheet1.Range("A12").Value

And now assumptions are gone. Right? Wrong. BT isn't declared (at least not here). If it's declared and it's not a Variant, then there's a potential type mismatch error with that assignment. In fact, the only data type that can accept any cell value, is Variant:

Dim BT As Variant
BT = Sheet1.Range("A12").Value

Here, we're assuming BT is a numeric value:

ReDim IT(BT) As String

That's another assumption. We don't know that BT is numeric. We don't even know that it's a value that can be coerced into a numeric data type: we should bail out if that's not the case:

If Not IsNumeric(BT) Then
    MsgBox "Cell A12 contains a non-numeric value; please fix & try again."
    Exit Sub
End If

ReDim IT(BT) As String

Now that will work... but then, only the upper bound is explicit; is this a 0-based or a 1-based array? If the module says Option Base 1, then it's 1-based. Otherwise, it's 0-based - implicit array lower bounds are an easy source of "off-by-one" bugs (like how you're populating the arrays starting at index 1, leaving index 0 empty). Always make array bounds explicit:

ReDim IT(1 To BT) As String

Unclear why you need 3 arrays at all, and why you're only populating (i,i) in the 3rd one - you cannot populate a 2D array with a Do...Loop structure; you need every value of y for each value of x, and unless you hard-code the width of the array, that's a nested loop.

Moreover, looping on the ActiveCell and Selecting an Offset is making the code 1) very hard to follow, and 2) incredibly inefficient.

Consider:

Dim lastRow As Long
lastRow = Sheet1.Range("B" & Sheet1.Rows).End(xlUp).Row

ReDim values(1 To lastRow, 1 To 2) As Variant

Dim currentRow As Long
For currentRow = 2 To lastRow
    Dim currentColumn As Long
    For currentColumn = 1 To 2
        values(currentRow, currentColumn) = Sheet1.Cells(currentRow, currentColumn).Value
    Next
Next

Now, if we don't need any kind of logic in that loop and all we want is to grab a 2D variant array that contains every cell in B2:B???, then we don't need any loops:

Dim values As Variant
values = Sheet1.Range("A2:B" & lastRow).Value

And done: values is a 1-based (because it came from a Range), 2D variant array that contains the values of every cell in A2:B{lastRow}.

Note, code that consumes this array will need to avoid assumptions about the data types in it.

Sign up to request clarification or add additional context in comments.

2 Comments

Cell A12 has numeric value. First array is list of items. Second array should be position of item in the list. Third array should keep connection between item and position. Like: item1 position 5, item 3 position 4 and ....so on. Third array is actually one on one.
"Cell A12 has numeric value" - You know that; your code doesn't, and it will blow up when (not if - especially with an implicit reference to whatever the ActiveSheet is) it doesn't. Again, not clear why you'd need to have an array to hold the position of the items of another array - that's what the array index is! As for "connection between item and position", that's unclear as well. In any case the answer to your question is "use a Variant array".
1

As @SJR has said, variant will allow for this. The below example is a easy example how to add different types to an array. Instead of x or y you can have a cell on a worksheet.

Dim array1() As Variant, i As Long
Dim x As String, y As Long

    x = "5"
    y = 1

    For i = 1 To 10

        ReDim Preserve array1(1 To 2, 1 To i)
        array1(1, i) = x
        array1(2, i) = y
        y = y + 1
        Debug.Print array1(1, i) & "," & array1(2, i) ' This is where you insert output

    Next

1 Comment

Note: ReDim Preserve inside a loop is very inefficient, since the entire array is being copied over, at every single iteration. Consider sizing the array before you start looping.
0

You can do this:

BT = Range("A12")
ReDim IT(BT) As String
ReDim RBT(BT) As Integer

Dim IT_RBT(1 to 2) 'variant
IT_RBT(1) = IT   'add String array
IT_RBT(2) = RBT  'add Integer array

... this will keep your typed arrays functional but it's not a 2D array and you'd need to use notation like

IT_RBT(1)(1)  'String type
IT_RBT(2)(1)  'Integer type

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.