Jagged array in VBA 7.0 - syntax error

Question

I'm using Excel 2010, and I wonder why the below code doesn't work for me? I've got compile error: syntax error.

Sub test()
Dim myStudents(,) As String = _       
   {{"Dick", "Jane", "Tom", "Sam"}, _
   {"Sue", "Bill", "Mary", ""}}    
End Sub

Example link and code :

Sub xyz()
    Dim xyz()()() As Byte
End Sub

This also produced compile error: syntax error when ran.

I get 'compile error:syntax error' because of 'myStydents(,)' structure — Qbik
– Qbik, Commented Jan 9, 2013 at 13:53
I don't think jagged arrays are available in VBA (VBA <> VB). You will need to use an array of arrays. — assylias
– assylias, Commented Jan 9, 2013 at 14:01

Xophmeister · Accepted Answer · 2013-01-09 14:06:32Z

2

This is VB.net syntax. VB.net is not the same as VBA, which is what is used in Excel, etc. To do this in VBA you can do something like:

Public Sub test()
  Dim myStudents() As Variant

  myStudents = Array( _
                 Array("Dick", "Jane", "Tom", "Sam"), _
                 Array("Sue", "Bill", "Mary") _
               )

  Debug.Print myStudents(0)(1) ' Jane
  Debug.Print myStudents(1)(2) ' Mary
End Sub

i.e., We create an array of arrays.

answered Jan 9, 2013 at 14:06

Xophmeister

9,2495 gold badges53 silver badges99 bronze badges

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

Collectives™ on Stack Overflow

Jagged array in VBA 7.0 - syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related