Assigning Value to Two Dimension Array

Question

I am trying to assign values to a 2D array in VBA but its not working. Here is what I have tried:

Sub UpdateCustomName()
    Dim CellTags() As String
    Dim temp() As String
    Dim ControlName As String
    Dim CellValue As String
    Dim CustomName(1 To 2, 1 To 2) As String

    For Each Cell In ActiveSheet.UsedRange.Cells
        If Not Cell.Value = "" Then
            CellTags() = Split(Cell.Value, "*")
            ' here in CellTags(2) value is like ABC_E34
            CustomName() = Split(CellTags(2), "_")
            MsgBox CustomName(1, 2)
        End If
    Next

End Sub

Dim CustomName(1 To 2) As String is a 2D array ? I think CustomName(1 To 2,1 To 2) is 2D array — Mailkov
– Mailkov, Commented Nov 11, 2014 at 7:58
@Mailkov it says at cannot assign to array at this line CustomName() = Split(CellTags(2), "_") — AddyProg
– AddyProg, Commented Nov 11, 2014 at 8:01

gembird · Accepted Answer · 2014-11-11 09:03:36Z

1

Sub UpdateCustomName()
    Dim myCell As Range
    Dim CellTags() As String
    Dim temp() As String
    Dim ControlName As String
    Dim CellValue As String
    Dim CustomName(1 To 2, 1 To 2) As String

    For Each myCell In ActiveSheet.UsedRange.Cells
        If Not myCell.Value = "" Then
            CellTags() = Split(myCell.Value, "*")
            ' here in CellTags(2) value is like ABC_E34
            CustomName(1, 1) = Split(CellTags(2), "_")(0)
            CustomName(1, 2) = Split(CellTags(2), "_")(1)
            MsgBox CustomName(1, 2)
        End If
    Next
End Sub

If the cell contains value '1*2*ABC_E34' then:

enter image description here

Info about 2D arrays in VBA:

http://www.homeandlearn.org/multidimensional_arrays.html

answered Nov 11, 2014 at 9:03

gembird

14.1k5 gold badges39 silver badges54 bronze badges

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

Comments

Jean-François Corbett · Accepted Answer · 2014-11-11 08:21:45Z

1

Declare CustomName as a dynamic array instead:

Dim CustomName() As String

Note that Split returns a one-dimensional (not a two-dimensional) array. So MsgBox CustomName(1, 2) will not work. Instead, try, for example: MsgBox CustomName(0) & " and " & CustomName(1)

answered Nov 11, 2014 at 8:21

Jean-François Corbett

38.7k30 gold badges146 silver badges192 bronze badges

2 Comments

AddyProg Over a year ago

actually I want to save all controls names and cells in customName array like for each cell I will get some value like ABC_E44 I want to save these values for all cells in Custom name array by spliting them with "_"

Jean-François Corbett Over a year ago

That sounds like a new question. Please post a new question, and show some example input, desired output, current output, and point out how the current output differs from the desired. A Minimal, Complete, and Verifiable example always helps.

Daniel Powell · Accepted Answer · 2014-11-11 09:02:56Z

Split returns an array and I don't think you can set the value of an array to an array in VBA. Does this help you at all?

Sub UpdateCustomName()
    Dim CellTags() As String
    Dim temp() As String
    Dim ControlName As String
    Dim CellValue As String
    Dim CustomName() As String
    Dim iCount As Long

    ' Redimension the array here, we cannot use a variable to dimension it in a Dim statement,
    'and it needs to be big enough to hold the data but not so big it wastes memory.
    ReDim CustomName(0 To ActiveSheet.UsedRange.Cells.Count - 1, 0 To 1) As String

    For Each Cell In ActiveSheet.UsedRange.Cells
        If Not Cell.Value = "" Then
            CellTags() = Split(Cell.Value, "*")
            CustomName(iCount, 0) = Split(CellTags(1), "_")(0)
            CustomName(iCount, 1) = Split(CellTags(1), "_")(1)
            MsgBox CustomName(iCount, 0) & " : " & CustomName(iCount, 1)
            iCount = iCount + 1
        End If
    Next
End Sub

Collectives™ on Stack Overflow

Assigning Value to Two Dimension Array

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related