Nested With

[Jac Tremblay]

Hi,
Is it possible to nest With... End With statements?
Here is an example:
With ActiveWorkbook.Sheets("Param")
' ...
Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _
.Range(strRange).Offset(2, 0).End(xlDown))
For Each rngCell In rng1stCol
With Me.lstTest
.AddItem rngCell.Value
.List(.ListCount - 1, 1) = _
Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd")
End With
Next rngCell
Set rng1stCol = Nothing
End With
I just need some confirmation.
Thanks.

 

[Dave Peterson]

Yep.

It worked when you tried it, right?

<vbg>

But you could have gotten away without nesting, too:

With ActiveWorkbook.Sheets("Param")
' ...
Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _
.Range(strRange).Offset(2, 0).End(xlDown))
End with

For Each rngCell In rng1stCol.Cells
With Me.lstTest
.AddItem rngCell.Value
.List(.ListCount - 1, 1) = _
Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd")
End With
Next rngCell

Set rng1stCol = Nothing

 

[Jac Tremblay]

Hi Dave,
Your answer was quick.
My code was only an example that I built on the fly.
Here is a new test I just made up.
Dim rngRange As Range
Dim rngCell As Range
Dim intI As Integer
With ActiveWorkbook.Sheets("Sheet1")
Set rngRange = .Range(.Range("A1").Offset(1, 0), _
.Range("A1").Offset(1, 0).End(xlDown))
For Each rngCell In rngRange
With ActiveWorkbook.Sheets("Sheet2").Range("C3")
.Offset(intI, 0).Value = rngCell.Value
intI = intI + 1
End With
Next rngCell
Set rngRange = Nothing
End With
It works fine.
I know that here too I could have gone without nesting the With statements
but that is not the point.
The reason I posted this question is that when you type a dot after an
object name in the inner With, nothing appears as if there was something
wrong. I guess that Excel cannot guess which object you are refering to.
Thank you Dave for your comment.

 

[Rick Rothstein]

You are missing the real benefit of being able to nest With blocks. Study
this off-the-top-of-my-head example to see how nesting can be useful. Go to
Sheet1, put some entries in Column A (they don't have to be contiguous and
any old text will do for example purposes) and then run this macro...

Sub Test()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
If .Cells(X, "A").Value <> "" Then
With .Cells(X, "A")
With .Font
.Size = 18
.Bold = True
.Italic = True
End With
.ColumnWidth = 10 + .ColumnWidth
With .Borders
.ColorIndex = 3
.LineStyle = xlDouble
End With
End With
End If
Next
End With
End Sub

--
Rick (MVP - Excel)

Hi Dave,
Your answer was quick.
My code was only an example that I built on the fly.
Here is a new test I just made up.
Dim rngRange As Range
Dim rngCell As Range
Dim intI As Integer
With ActiveWorkbook.Sheets("Sheet1")
Set rngRange = .Range(.Range("A1").Offset(1, 0), _
.Range("A1").Offset(1, 0).End(xlDown))
For Each rngCell In rngRange
With ActiveWorkbook.Sheets("Sheet2").Range("C3")
.Offset(intI, 0).Value = rngCell.Value
intI = intI + 1
End With
Next rngCell
Set rngRange = Nothing
End With
It works fine.
I know that here too I could have gone without nesting the With statements
but that is not the point.
The reason I posted this question is that when you type a dot after an
object name in the inner With, nothing appears as if there was something
wrong. I guess that Excel cannot guess which object you are refering to.
Thank you Dave for your comment.

 

[Dave Peterson]

It's not the nesting that's the problem. It's that excel/VBE won't use the
intellisense with "with worksheets(...)"

Try:

Dim wks as worksheet
set wks = ActiveWorkbook.Sheets("Sheet1")
....

With wks
set rngrange = .
and you'll see the intellisense.

If you declare your objects correctly (as Worksheet, as range, ...
and not As Variant, As Object), you'll see that helpful intellisense.

Hi Dave,
Your answer was quick.
My code was only an example that I built on the fly.
Here is a new test I just made up.
Dim rngRange As Range
Dim rngCell As Range
Dim intI As Integer
With ActiveWorkbook.Sheets("Sheet1")
Set rngRange = .Range(.Range("A1").Offset(1, 0), _
.Range("A1").Offset(1, 0).End(xlDown))
For Each rngCell In rngRange
With ActiveWorkbook.Sheets("Sheet2").Range("C3")
.Offset(intI, 0).Value = rngCell.Value
intI = intI + 1
End With
Next rngCell
Set rngRange = Nothing
End With
It works fine.
I know that here too I could have gone without nesting the With statements
but that is not the point.
The reason I posted this question is that when you type a dot after an
object name in the inner With, nothing appears as if there was something
wrong. I guess that Excel cannot guess which object you are refering to.
Thank you Dave for your comment.

 

[Dave Peterson]

Just to add...

I'll often use:

dim ActWks as worksheet
set ActWks = activesheet

with actwks
.range(...

Just so that I get the intellisense help. And I won't rely on unqualified
ranges being on the activesheet (ok for code in a general module), but can cause
trouble if you copy|paste code elsewhere.

Hi Dave,
Your answer was quick.
My code was only an example that I built on the fly.
Here is a new test I just made up.
Dim rngRange As Range
Dim rngCell As Range
Dim intI As Integer
With ActiveWorkbook.Sheets("Sheet1")
Set rngRange = .Range(.Range("A1").Offset(1, 0), _
.Range("A1").Offset(1, 0).End(xlDown))
For Each rngCell In rngRange
With ActiveWorkbook.Sheets("Sheet2").Range("C3")
.Offset(intI, 0).Value = rngCell.Value
intI = intI + 1
End With
Next rngCell
Set rngRange = Nothing
End With
It works fine.
I know that here too I could have gone without nesting the With statements
but that is not the point.
The reason I posted this question is that when you type a dot after an
object name in the inner With, nothing appears as if there was something
wrong. I guess that Excel cannot guess which object you are refering to.
Thank you Dave for your comment.

 

[Jac Tremblay]

Hi Rick,
What a great example. Thank you very much.
Have a good day.

 

[Jac Tremblay]

Hi Dave,
You are right. That is what I always do normally except for short examples
or tests.
By the way, is it any better to declare a Workbook object like this:
Dim Wbk as Excel.Workbook
or like this:
Dim Wbk as Workbook?
Is there any difference?
Thank you again and have a good week end.

 

[Jac Tremblay]

Hi again.
Thanks again.
--
Jac Tremblay

Just to add...

I'll often use:

dim ActWks as worksheet
set ActWks = activesheet

with actwks
.range(...

Just so that I get the intellisense help. And I won't rely on unqualified
ranges being on the activesheet (ok for code in a general module), but can cause
trouble if you copy|paste code elsewhere.

 

[Dave Peterson]

If I'm in Excel, I use:
dim wkb as workbook

I've read some posts from very smart people who always qualify every object. I
think that their reasoning is that it makes it easier when they copy the code to
a different application (like automating excel from word/access/powerpoint).

I'm not as careful as I should be!

Hi Dave,
You are right. That is what I always do normally except for short examples
or tests.
By the way, is it any better to declare a Workbook object like this:
Dim Wbk as Excel.Workbook
or like this:
Dim Wbk as Workbook?
Is there any difference?
Thank you again and have a good week end.

 

[Jac Tremblay]

Hi again Dave,
That is a good comment. I am trying to set standards for developpers where I
work and that is not always easy. One has to be aware of the environment. For
example, in VB.NET when I do automation, I declare a range differently for
Word and Excel
I use wdRng as a prefix for Word ranges and xlRng for Excel ranges. It helps
me a lot when reading my own code and developping new applications.
Thanks again for your time.