Loop (Excel 2002 (xp) SP2)

Topic

New Reply

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:
[indent]

---

With xlsheet
.Range(“A:A”).Select
With Selection
.ColumnWidth = “18”
.HorizontalAlignment = xlRight
End With
End With

---

[/indent] 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???

Reply | Quote

Replies

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?

Reply | Quote

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?

Reply | Quote

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:

    With xlsheet.Range("A:A")
        .ColumnWidth = 18
        .HorizontalAlignment = xlRight
    End With

Reply | Quote

Legare and Rory–

Thanks for your responses. First, xlsheet is defined as a worksheet object variable as follows: [indent]

---

Set xlApp = New Excel.Application
Set xlbook = xlApp.Workbooks.Add
Set xlsheet = xlbook.ActiveSheet

---

[/indent] 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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Legare and Rory–

Thanks for your responses. First, xlsheet is defined as a worksheet object variable as follows: [indent]

---

Set xlApp = New Excel.Application
Set xlbook = xlApp.Workbooks.Add
Set xlsheet = xlbook.ActiveSheet

---

[/indent] 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.

Reply | Quote

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:

    With xlsheet.Range("A:A")
        .ColumnWidth = 18
        .HorizontalAlignment = xlRight
    End With

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote