+ Reply to Thread
Results 1 to 3 of 3

DIMming / setting multiple ranges by iterating over an array of string elements

Hybrid View

  1. #1
    Registered User
    Join Date
    04-12-2013
    Location
    nowhere
    MS-Off Ver
    Office 2010
    Posts
    3

    DIMming / setting multiple ranges by iterating over an array of string elements

    Hi all -
    I've searched pretty extensively for this without luck. I will admit to not fully understanding the rules for arrays and data types in VBA, though. What I'm trying to do is this:

    INSTEAD of typing all this out....

    Dim abc as Range
    Set abc = Range(....)

    Dim def as Range
    Set def = Range(....)

    Dim ghi as Range
    Set ghi = Range(....)

    ...I'm simply trying to do it like this....

    rA = Array("abc", "def", "ghi")

    For i=0 to 2
    Dim rA(i) as Range '<------ (constant expression required ERROR OCCURS HERE)
    Set rA(i) = Range(....)
    MsgBox (rA(i)) '<------- (this works fine)
    Next i

    ... but I get the "Compile error: Constant expression required" problem - even though the loop is successfully grabbing each element of Array rA() as expected (e.g., if I put "MsgBox (rA(i))" inside the loop, it happily displays each member in turn).

    Thanks for any help.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,525

    Re: DIMming / setting multiple ranges by iterating over an array of string elements

    code tags would help us see the code blocks separate from your questions. Instructions for using code tags are in the forum rules link at the top of the page.

    1) You look like you are trying to use the same variable for the range array and for the string index array. I would probably assign two different arrays for this.
    2) You cannot use a variable in a dim statement to create a variable sized array. You accomplish this using a dynamic array with the Redim statement http://msdn.microsoft.com/en-us/libr.../gg251578.aspx But, it is not clear to me if you need a dynamic array. In this case, it looks more like you are trying to dim each element of array rA separately during each loop, which is not necessary. When an array is dimensioned, each element is also dimensioned, if that makes sense.

    This might look something like
    dim rA(2) as range
    stindex=array("a","b","c")
    for i=0 to 2
    set ra(i)=sheets(1).range(stindex(i))
    next i
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    12,996

    Re: DIMming / setting multiple ranges by iterating over an array of string elements

    stindex=array("a1","b1","c1") ?
    Last edited by protonLeah; 04-12-2013 at 08:29 PM.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1