Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    St. Louis, Missouri, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a variable as Array() Argument (2000)

    I need to select several worksheets and group them for changes, moving, etc. This can be done with the Array function. For example, Sheets(Array("Sheet1", "Sheet2")).Select. However, I would like to build the argument for the Array function programatically, based on the user's input from a sheet. I have tried this, but I get a "subscript out of range" error. I checked that the argument variable was composed correctly. I have tried with both string and variant variables. I also tried appending a double-quote on both ends of the variable, so that Excel could take them off when the string is evaluated. Still no luck. Apparently, Excel is having trouble converting the string into a list of string values to be placed into the array. I even tried placing the sheet names into elements in an array variable. I am using a variation of this solution to move sheets (one at a time), but I'd really like to get this working. There are many variations of this for other functions that would be useful, if I can figure out the required technique. Does anyone have any suggestions?

    Here's some sample code:

    Sub Test()
    Dim strArray As String
    ThisWorkbook.Sheets(1).Select 'reset
    ' Make a string with value: "Sheet1", "Sheet2", "Sheet3"
    strArray = Chr(34) & "Sheet1" & Chr(34)
    strArray = strArray & ", " & Chr(34) & "Sheet2" & Chr(34)
    strArray = strArray & ", " & Chr(34) & "Sheet3" & Chr(34)
    ' Strip off quotes at each end (try it with and without this):
    strArray = Mid(strArray, 2, Len(strArray) - 2)

    ThisWorkbook.Sheets(Array(strArray)).Select 'the problem line
    ' This works (but it's "hard-coded"):
    ' ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    End Sub

    A sample file is attached.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    Try something like this:

    <pre>Dim strSheets(3) As String, I As Integer
    strSheets(0) = "Sheet1"
    strSheets(1) = "Sheet2"
    strSheets(2) = "Sheet3"
    For I = 0 To 2
    Sheets(strSheets(I)).Select (I = 0)
    Next I
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    St. Louis, Missouri, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    Thanks, this works. However, I don't understand why. I am not familiar with the additional "(I=0)" construct on the "Sheets" line. Do you know what this is called so that I can look it up, or can you explain what it is doing? It appears to be appending the select to the previous selects in the loop, or creating an array within the line, but I don't know why. Thanks.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    The Select method has a parameter that you can pass to it when used with sheets. If that parameter is True, then this selection replaces the previous selection. When it is False, the current selection is added to the previous selection. That (I=0) was my way of passing True to the select method the first time through the loop to replace any current selection with the first sheet in the loop, and then passing False for the remaining times so that the remaining sheets would be added to the selection. It is comparing the loop variable I to 0 which is True the first time through the loop and false every other time.
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Apr 2001
    Location
    St. Louis, Missouri, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    I didn't notice that on the Select method, but it's right there in the online help! Again, thanks. This will make my app easier and simpler to implement.

  6. #6
    Lounger
    Join Date
    Apr 2001
    Location
    St. Louis, Missouri, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    Sorry to come back to this, but part of the reason I wanted to select multiple sheets was to move them. However, I have found that the Move method does not work on Selection (or a group of selected sheets). I've tried this several ways with no success, but I think what I need to do is somehow turn the selected sheets into an array. Once I have an array of sheets, I should be able to use Sheets(array).Move.

    I've also developed a solution that moves one sheet at a time, which is not as fast or simple, but would work. However, I seem to have encountered some sort of bug in Excel 2000. After moving a certain number of sheets (I think around 40 or 50), it refuses to move any more sheets. Even after stopping the code, I can't move any sheets manually! Exiting Excel and restarting will reset things, but the error occurs again. So, I'm back to the array solution...

    Any suggestions would be helpful. Thanks.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    If .Move will not work on the Selection collection, then I do not think it would work on the array that you want to create if it were possible to do, and I do not think it is possible. The only possible work around to the problem you describe that I can think of would be to use something like OnTime to sequentially schedule a macro to move small groups of sheets. That might or might not get around the problem.
    Legare Coleman

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    Do you mean you want to create an array with names of worksheets and then have code that modes all of them at once? This works:

    Public Sub MoveSheets()
    Dim arySheets

    arySheets = Array("aaaa", "bbbb", "cccc")
    Worksheets(arySheets).Move after:=Worksheets("Sheet2")

    End Sub

    The trick is that you have to set up the array first you can't do:

    Worksheets("aaaa", "bbbb", "cccc")

    Does this help? Deb <img src=/S/clown.gif border=0 alt=clown width=15 height=15>

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    I tried one more thing, and it seems to work.

    <pre>Dim I As Long, strMySheets(1 To 2), vSheets As Variant
    For I = 1 To Worksheets.Count - 1
    strMySheets(I) = Worksheets(I).Name
    Next I
    vSheets = strMySheets
    Worksheets(vSheets).Move after:=Worksheets(Worksheets.Count)
    </pre>

    Legare Coleman

  10. #10
    Lounger
    Join Date
    Apr 2001
    Location
    St. Louis, Missouri, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    Thanks. Yes, this is the way we do it now, but there are many sheets and they change from month to month, and it's a pain to maintain. I have changed our code to programatically pull the proper sheet names into an array (loading each element of an array variable), but I can't get the Array function (or Worksheets or Sheets) to use my array variable as an input or qualifier. I've also tried it as a big string going into the Array function, but it doesn't like that either!

    Have you ever tried anything like this? See the attached file. Maybe I'm missing something obvious. Thanks.
    Attached Files Attached Files

  11. #11
    Lounger
    Join Date
    Apr 2001
    Location
    St. Louis, Missouri, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    I tried this (almost) exact thing and it wouldn't work. However, I was finally able to make it work like your example. The one thing that was different was that I had not defined the lower bound of my array variable to 1. When I made this one change, it magically began working! I suppose this is a good programming practice whenever using arrays.

    Thanks again for your help! This was getting very frustrating as a 1-2 hour project was dragging on for days. Once again, you've come through with the answer.

  12. #12
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable as Array() Argument (2000)

    Ok, I got it working. I too have rarely used the Array function and always thought it odd that the examples I've seen were always hard coded. The trick was that your array was sized to 100 elements but only 3 were stored. If you populate your array with 'n' sheets then resize it to 'n' sheets before calling the .Move command

    You can do it like this:
    dim strSheets(100) as String
    strSheets(0) = "Sheet1"
    strSheets(1) = "Sheet4"
    strSheets(2) = "Sheet12"

    redim preserve strSheets(ubound(strSheets)-1))

    I modified your spreadsheet and it's attached.

    Deb
    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Attached Files Attached Files

Posting Permissions

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