Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Basic help with Arrays in VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Basic help with Arrays in VBA

    I'm trying to learn arrays in VBA and am testing some basic concepts...
    i have the following code in which the TestArrayCol sub is working but the TestArrayRow is not....getting a subscipt out of range error on the msgbox myarrayrow(j) line...i'm sure i'm overlooking something simple...any help would be appreciated...

    Code:
    Sub TestArrayCol()
    Dim myArrayCol As Variant, i As Integer
    myArrayCol = WorksheetFunction.Transpose(Range("A1:A5"))
    For i = LBound(myArrayCol) To UBound(myArrayCol)
    MsgBox myArrayCol(i)
    Next i
    End Sub
    
    Sub TestArrayRow()
    Dim myArrayRow As Variant, j As Integer
    myArrayRow = Range("B1:G1")
    For j = LBound(myArrayRow) To UBound(myArrayRow)
    MsgBox myArrayRow(j)
    Next j
    End Sub

  2. #2
    Board Regular mortgageman's Avatar
    Join Date
    Jun 2005
    Posts
    2,015
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This may help. When I use MSgBox to find out the value of your upper bound, it seems that it is 1 (and not 5). I don't know why, but you can run this macro and see for yourself.

    Edit: I meant 6 and not 5 (which is what I assumed you were thinking)

    Gene Klein


    Sub TestArrayRow()
    Dim myArrayRow As Variant, j As Integer
    Dim mylbound As Integer
    Dim myubound As Integer

    myArrayRow = Range("B1:G1")
    mylbound = LBound(myArrayRow)
    MsgBox "mylbound is " & mylbound
    myubound = UBound(myArrayRow)
    MsgBox "myubound is " & myubound
    For j = LBound(myArrayRow) To UBound(myArrayRow)
    MsgBox myArrayRow(j)
    Next j
    End Sub

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    One tip - when working with Row variables you should use a Long data type rather than an Integer data type because the latter may not always be sufficient. See if changing j to a Long helps.
    Richie

  4. #4
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Gene,
    in my original code i stepped through and noticed that too...not sure why either...


    Richie, changing it to long still did not fix it...


    any other ideas?

  5. #5
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,999
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Nooch,

    Use your view locals window to study the structure of your arrays. In your second case your array is double dimensioned.


    Sub TestArrayRow()
    Dim myArrayRow As Variant, j As Integer
    myArrayRow = Range("B1:G1")
    For j = LBound(myArrayRow, 2) To UBound(myArrayRow, 2)
    MsgBox myArrayRow(1, j)
    Next j
    End Sub
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

  6. #6
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Greg that absolutely did the trick...
    now i'm confused though...
    i thought columns were two dimensional ...thus the TRANSPOSE....

    PS...just for fun...how would i change hte testarrayCol one not to use transpose then? or is that the way i should do it?

    Thanks again Greg...

  7. #7
    Board Regular mdavide's Avatar
    Join Date
    Oct 2004
    Posts
    211
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    to declare array:
    if you know the size use

    dim test_array(n) as ... where n =integrer
    dim test_array(n,m) as ... where n =integrer and m =integrer

    in order to refer to it you must use the index from 0 to n-1,so
    test_array(0) is the first element and test_array(n-1) is the last one

    also you can write in the module this line :option base 1
    then you refer to it using the index from 1 to n

    also you can declare the array:
    dim test_array(a to b) where a then you refer to it using the index from a to b

    if you want to use a variable array you must define
    dim test_array()
    and then
    redim test_array(n) or
    redim preserve test_array(n)

    ciao

    DM
    -------
    FLETTO I MUSCOLI E SONO NEL VUOTO
    -------

  8. #8
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    TheNoocH,

    When you substitute the range value, the array will be multi-dimentional, no exception even in one row or one column.
    And the data type must be variant.

    If you have
    A1=a, B1="B"
    A2="c", B2="d"
    then a=Range("a1:a2").Value means
    a(1,1)="a",a(2,1)="c"

    When a=Range("a1:b1").Value means
    a(1,1)="a", a(1,2)="b"

    as you know, lower bound begin from 1, not 0 for this type of substitution.

    Whereas the array created by the functions like
    Filter, Array, Split..
    lower bound always begin from 0, even if you state Option Base 1, NO EXCEPTION.

    hope this helps

  9. #9
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    mdavide and jindon...thanks for the clarifications...

    for fun i redid the column sub and got rid of the TRANSPOSE part...
    this is what i came up with...

    Code:
    Sub TestArrayCol1()
    Dim myArrayCol1 As Variant, i1 As Integer
    myArrayCol1 = Range("A1:A5")
    For i1 = LBound(myArrayCol1) To UBound(myArrayCol1)
    MsgBox myArrayCol1(i1, 1)
    Next i1
    End Sub
    in the lbound/ubound section if i did lbound(myarraycol1,2) to ubound(myarraycol1, 2)
    it only returned the first element...
    especially weird since in the msgbox line i needed it to look like a 2 dimensional array...

    any thoughts?

  10. #10
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Quote Originally Posted by jindon
    Code:
    Sub TestArrayCol1() 
    Dim myArrayCol1 As Variant, i1 As Integer 
    myArrayCol1 = Range("A1:A5") 
    For i1 = LBound(myArrayCol1,2) To UBound(myArrayCol1,2) 
    MsgBox myArrayCol1(1, i1) 
    Next i1 
    End Sub
    Jindon,
    when i run that it only returns the first element...
    eg...i have a, b, c, d, e in A1:A5
    it only returns a?

    It always helps to post some data and your expected results!!!
    To show your sheet on the board use Excel Jeanie or Beta HTML Maker from RichardSchollar

    When posting VBA Code use CODE tags ... eg. [CODE] <your vba code here> [ /CODE]
    To install code --> ALT-F11, Insert, Module...Paste the code from Sub <---> End Sub into the blank area

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •