none
how declare / assign array? RRS feed

  • Question

  • I need help declaring / assigning an array.  Here is what does not work.

    Dim Tbl2ColsAry As Variant
    Tbl2ColsAry=new integer(27){1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45}

    Can someone please tell me what I'm doing wrong?  I've tried changing these two lines several different ways and still don't have it right.

    Friday, November 6, 2015 3:34 PM

Answers

  • I need help declaring / assigning an array.  Here is what does not work.

    Dim Tbl2ColsAry As Variant
    Tbl2ColsAry=new integer(27){1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45}


    Have you tried:

    Tbl2ColsAry = New Integer(26) {1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45}
    

    or

    Tbl2ColsAry = New Integer() {1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45}
    

    Some Basic examples:

    Dim Tbl2ColsAry() As Integer = {1, 2, 3, 4, 5, 12, 14, 15, 16, 19}

    The size of this array will be determined by the number of initializers.

    Dim Tbl2ColsAry2() As Integer = New Integer(4) {1, 2, 3, 4, 5}

    Array size will be 5 and you must supply that exact number of initializers.

    Dim Tbl2ColsAry2() As Integer = New Integer(4) {1, 2, 3, 4} ' error - too few

    Dim Tbl2ColsAry2() As Integer = New Integer(4) {1, 2, 3, 4, 5, 6} ' error - too many

    See:

    Arrays in Visual Basic
    https://msdn.microsoft.com/en-us/library/vstudio/wak0wfyt%28v=vs.140%29.aspx

    - Wayne

    • Marked as answer by VBApractice Monday, November 9, 2015 2:15 PM
    Friday, November 6, 2015 4:48 PM
  • Hi,

    It seems that you were developing VBA. We need to declare arrays before using it. Here is the code to declare the arrays for your reference:

    Dim arry1  As Variant
    arry1 = Array(1, 2, 3)
    In addition, here are some helpful links for your reference:

    Declaring Arrays

    Using Arrays

    If you still have any other problem about VBA, I suggest that you reopen a new thread in forum below:
    Msdn forums - Visual Basic for Applications (VBA)

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by VBApractice Monday, November 9, 2015 2:14 PM
    Monday, November 9, 2015 2:30 AM
    Moderator

All replies

  • Dim Tbl2ColsAry = {1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45}

    Success
    Cor

    • Marked as answer by VBApractice Monday, November 9, 2015 2:13 PM
    • Unmarked as answer by VBApractice Monday, November 9, 2015 2:13 PM
    Friday, November 6, 2015 3:45 PM
  • That gives me a Compile error: Syntax error. Could there be something else wrong?
    Friday, November 6, 2015 4:06 PM
  • That gives me a Compile error: Syntax error. Could there be something else wrong?

    Works for me? Show your code maybe.
    Friday, November 6, 2015 4:08 PM
  • Hello,

    Is this VB6 and if so use this forum as the current forum is for VB.NET or if for Excel I can move your question to the Excel for developers forum


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.
    Microsoft Developer tools
    Developer’s Guide to Windows 10 video series


    Friday, November 6, 2015 4:11 PM
  • Thanks for the quick reply.  Here is my code.

                            

    Sub GenDataArray()
        ' Generate an array of the values in the Data tab.

        Dim sht1 As Worksheet
        Dim LastCell As String
        Dim LastRow As Integer
        Dim DataArray() As Variant

        Set sht1 = Sheets("Data")
        LastCell = sht1.UsedRange.SpecialCells(xlLastCell).Address
        LastRow = sht1.UsedRange.SpecialCells(xlLastCell).Row

        ' Copy data from data tab.
        ' cell(2,1) includes the column headings in the Data tab.
        DataArray = sht1.Range(Cells(2, 1), LastCell).Value

        Dim sht2 As Worksheet
        Dim AryRowN As Integer
        Dim indx As Integer
        Dim Tbl2Array() As Variant

        ' These are the columns from the Data tab that are used in "Table 2" in this order.
        Dim Tbl2ColsAry = {1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45}

        Set sht2 = Sheets("Sheet1")

        AryRowN = 0

    End Sub

    Friday, November 6, 2015 4:11 PM
  • VB for Apps 7.1 in Excel 2013
    • Edited by VBApractice Friday, November 6, 2015 4:17 PM
    Friday, November 6, 2015 4:15 PM
  • I need help declaring / assigning an array.  Here is what does not work.

    Dim Tbl2ColsAry As Variant
    Tbl2ColsAry=new integer(27){1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45}


    Have you tried:

    Tbl2ColsAry = New Integer(26) {1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45}
    

    or

    Tbl2ColsAry = New Integer() {1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45}
    

    Some Basic examples:

    Dim Tbl2ColsAry() As Integer = {1, 2, 3, 4, 5, 12, 14, 15, 16, 19}

    The size of this array will be determined by the number of initializers.

    Dim Tbl2ColsAry2() As Integer = New Integer(4) {1, 2, 3, 4, 5}

    Array size will be 5 and you must supply that exact number of initializers.

    Dim Tbl2ColsAry2() As Integer = New Integer(4) {1, 2, 3, 4} ' error - too few

    Dim Tbl2ColsAry2() As Integer = New Integer(4) {1, 2, 3, 4, 5, 6} ' error - too many

    See:

    Arrays in Visual Basic
    https://msdn.microsoft.com/en-us/library/vstudio/wak0wfyt%28v=vs.140%29.aspx

    - Wayne

    • Marked as answer by VBApractice Monday, November 9, 2015 2:15 PM
    Friday, November 6, 2015 4:48 PM
  • Hi,

    It seems that you were developing VBA. We need to declare arrays before using it. Here is the code to declare the arrays for your reference:

    Dim arry1  As Variant
    arry1 = Array(1, 2, 3)
    In addition, here are some helpful links for your reference:

    Declaring Arrays

    Using Arrays

    If you still have any other problem about VBA, I suggest that you reopen a new thread in forum below:
    Msdn forums - Visual Basic for Applications (VBA)

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by VBApractice Monday, November 9, 2015 2:14 PM
    Monday, November 9, 2015 2:30 AM
    Moderator
  • Thank you.  It works.  I'll post new questions in the forum you linked.  Thanks again.
    Monday, November 9, 2015 2:18 PM