Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop (Excel 2002 (xp) SP2)

    I have written some code to loop through several values in a database and don't understand something. Any assistance on this would be greatly appreciated.
    Note the following code:
    <hr> With xlsheet
    .Range("A:A").Select
    With Selection
    .ColumnWidth = "18"
    .HorizontalAlignment = xlRight
    End With
    End With<hr>
    This code snipet is within a much larger loop. The first time through the loop, the code runs fine. The second time through (and I suppose all subsequent times, but I haven't gotten that far yet), I get a "With Block variable not set" on the ".ColumnWidth..." line. I think that this means that my selection of "A:A" is invalid. Why would it work the first time through, but not the 2nd???

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Loop (Excel 2002 (xp) SP2)

    Hi Randall,
    There is nothing technically wrong with that piece of code (although I don't know why you are assigning columnwidth a string value, Excel should convert it ok) so I would guess something else is bombing out - possibly the xlsheet variable is not being set correctly. Can you post the rest of the code?
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Loop (Excel 2002 (xp) SP2)

    What is xlsheet, how is it defined, and how is it set?

    If xlsheet is defined as a worksheet object variable, and it has been properly assigned a worksheet object, then it is not necessary to select the range in order to assign the properties. If it is not required to select a range to perform some operation, then it is always better and faster to not select it. Your code could be simplified to:

    <pre> With xlsheet.Range("A:A")
    .ColumnWidth = 18
    .HorizontalAlignment = xlRight
    End With
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop (Excel 2002 (xp) SP2)

    Legare and Rory--

    Thanks for your responses. First, xlsheet is defined as a worksheet object variable as follows:
    <hr>Set xlApp = New Excel.Application
    Set xlbook = xlApp.Workbooks.Add
    Set xlsheet = xlbook.ActiveSheet<hr>
    I have been able to work around the With... problem by getting rid of all my With... statements. I have begun to add them back one at a time, but that should not have been a problem. I'm sure this has significantly affected my running time, however.

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

    Re: Loop (Excel 2002 (xp) SP2)

    Was xlsheet actually DIMed as a worksheet object, or was it just assigned a value with the Set statement you showed (in which case it was a Variant that was assigned to a worksheet object)?

    It is vary hard to tell without seeing all of your code, but two possibilities. 1- If xlsheet was DIMed as a worksheet object, then somehow it was set to nothing before the second time through the loop, or the worksheet it was assigned to was deleted or in someother way the reference became invalid. If xlsheet is a variant, then it could have been changed to any value other than a worksheet object.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop (Excel 2002 (xp) SP2)

    Now that I've finally had some time to get back to this project and look/experiement with it further, I think I've ruled out the xlsheet variable being the problem--as far as I can tell. It is tripping up on the Selection word. Legare, I agree with you that I don't have to select a range in order to work with it. However, with some of the manipulation I am doing it is difficult to avoid it completely. But you are right, my example code could (should?) have been written more succinctly.

    I have been "Watching" the xlsheet (and related) variables and they appear to be set correctly throughout the running of the code. However, the second time through the loop I've created, I get the With block variable not set, as I reported. I've determined that Excel is somehow not "Selecting" the range. See the graphic. Selection is set to Nothing, even though the .Range("A2").Select line was already processed. Is there a reason why .Select would not, uh, select. Surely you can you have a With... statement nesting inside another With... statement. This is the case here.

    Again, thanks for your responses, Rory and Legare. Any other sugguestions.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop (Excel 2002 (xp) SP2)

    Try using xlApp.Selection instead of just Selection:

    With xlApp.Selection
    ...
    End With

    Since you are using Automation, you should specify explicitly what Selection belongs to.

Posting Permissions

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