Nested "With" Clauses?

[(PeteCresswell)]

I'm going over an application written by somebody else and have encountered what
looks to me like nested "With"s.

Is this something new with MS Access 2003 or am I losing it? Seems to me that
when I tried nesting "With" clauses in MS Access 2000 it was a no-no.

Some sample code - in which it looks like they're creating/populating a
..XLS spreadsheet. "Option Explicit" is not specified in the module...
------------------------------------------------------------------------------
Sub ptManagers()

Dim strSheetName As String
Dim oSheet As Object
Dim wksPivot As Object

Set oSheet = oBook.Sheets.Ad d
oSheet.Activate

strSheetName = "ptManagers "
oSheet.Name = strSheetName
Set wksPivot = oSheet

oBook.Worksheet s(oSourceSheet. Name).PivotTabl es("FlashPivotT able").PivotCac he.
_
CreatePivotTabl e TableDestinatio n:=wksPivot.Ran ge("A3"), _
tableName:="ptM anagersPivotTab le", DefaultVersion: =xlPivotTableVe rsion10

Set pvtTable = wksPivot.PivotT ables("ptManage rsPivotTable")

With pvtTable

' Specify page fields.

.PivotFields("G roup").Orientat ion = xlPageField
.PivotFields("G roup").CurrentP age = "Total"
.PivotFields("T ype").Orientati on = xlPageField

With .PivotFields("D ate")
.Orientation = xlPageField
.NumberFormat = "ddMMMyy"
End With

' Specify fields
.PivotFields("P roduct").Orient ation = xlRowField
.PivotFields("P ortfolio").Orie ntation = xlRowField

With .PivotFields("P ortfolio Base Rtn(%)")
.Orientation = xlDataField
.Position = 1
.NumberFormat = "0.0"
.Caption = ".Portfolio Base Rtn(%)"
End With
With .PivotFields("I ndex Base Rtn(%)")
.Orientation = xlDataField
.Position = 2
.NumberFormat = "0.0"
.Caption = ".Index Base Rtn(%)"
End With
With .PivotFields("S tock Select")
.Orientation = xlDataField
.Position = 3
.NumberFormat = "0.000"
.Caption = ".Stock Select"
End With
With .PivotFields("G roup Weight")
.Orientation = xlDataField
.Position = 4
.NumberFormat = "0.000"
.Caption = ".Group Weight"
End With
With .PivotFields("T otal")
.Orientation = xlDataField
.Position = 5
.NumberFormat = "0.000"
.Caption = (".Total")
End With
With .DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

.ColumnGrand = False
.HasAutoFormat = False
.RowGrand = False

.PivotFields("P ortfolio").Auto Sort xlDescending, ".Total"
.PivotFields("P roduct").Subtot als = Array(False, False, False, False,
False, False, False, False, False, False, False, False)
End With

With oSheet
.Range("5:5").E ntireRow.Hidden = True
.Rows("6:6").Ro wHeight = 56.25
With .Range("A6:G6")
.Font.Bold = True
.Interior.Color Index = 1
.Interior.Patte rn = xlSolid
.Font.ColorInde x = 2
.HorizontalAlig nment = xlCenter
.VerticalAlignm ent = xlBottom
.WrapText = True
End With

.Columns("B:F") .ColumnWidth = 8.5
.Columns("A:A") .ColumnWidth = 12
.Columns("B:B") .ColumnWidth = 18.5

With .PageSetup
.LeftFooter = "&F" & Chr(10) & "&A"
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
.Activate
.DisplayAutomat icPageBreaks = False
End With

oXL.ActiveWindo w.DisplayGridli nes = False
oBook.ShowPivot TableFieldList = False
End Sub

------------------------------------------------------------------------------
--
PeteCresswell

[Rick Brandt]

(PeteCresswell) wrote:

I'm going over an application written by somebody else and have
encountered what looks to me like nested "With"s.

Is this something new with MS Access 2003 or am I losing it? Seems
to me that when I tried nesting "With" clauses in MS Access 2000 it
was a no-no.

VBA help from Access 2003
*************** ********
You can nest With statements by placing one With block within another. However,
because members of outer With blocks are masked within the inner With blocks,
you must provide a fully qualified object reference in an inner With block to
any member of an object in an outer With block.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[(PeteCresswell)]

Per Rick Brandt:

you must provide a fully qualified object reference in an inner With block to
any member of an object in an outer With block.

I'll bet that's what got me off on the notion that they were a no-no.... i.e.
tried it, got my hand slapped, didn't think about it any more.
--
PeteCresswell

[David W. Fenton]

"(PeteCresswell )" <[e-mail]> wrote in
news:6e******** *************** *********@4ax.c om:

Per Rick Brandt:

you must provide a fully qualified object reference in an inner
With block to any member of an object in an outer With block.

I'll bet that's what got me off on the notion that they were a
no-no.... i.e. tried it, got my hand slapped, didn't think about
it any more.

I think it creates extremely bad code to nest WITH blocks. To me,
it's a sign of a bad programmer, as it's optimization that makes the
code much harder to read and understand.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bri]

(PeteCresswell) wrote:

I'm going over an application written by somebody else and have encountered what
looks to me like nested "With"s.

Is this something new with MS Access 2003 or am I losing it? Seems to me that
when I tried nesting "With" clauses in MS Access 2000 it was a no-no.

You can nest 'With' statements in AC97 as well.

It is only really useful in a few situations. It can make the code more
difficult to follow unless you use consistant indenting. Your example is
a good example of using it right, IMO, but it is easy to do it wrong,
particulatly when you need to refer to a 'parent' object within the
nest. 'With' allows the code to run more efficiently, but with today's
PCs its not likely to be a noticable increase in speed.

--
Bri

[(PeteCresswell)]

Per David W. Fenton:

I think it creates extremely bad code to nest WITH blocks. To me,
it's a sign of a bad programmer, as it's optimization that makes the
code much harder to read and understand.

Excellent. Now I feel better.... -)
--
PeteCresswell

[rkc]

David W. Fenton wrote:

"(PeteCresswell )" <[e-mail]> wrote in
news:6e******** *************** *********@4ax.c om:

Per Rick Brandt:

you must provide a fully qualified object reference in an inner
With block to any member of an object in an outer With block.

I'll bet that's what got me off on the notion that they were a
no-no.... i.e. tried it, got my hand slapped, didn't think about
it any more.

I think it creates extremely bad code to nest WITH blocks. To me,
it's a sign of a bad programmer, as it's optimization that makes the
code much harder to read and understand.

No more so than nested anything else.

[David W. Fenton]

rkc <rk*@rochester. yabba.dabba.do. rr.bomb> wrote in
news:I%******** **********@twis ter.nyroc.rr.co m:

David W. Fenton wrote:

"(PeteCresswell )" <[e-mail]> wrote in
news:6e******** *************** *********@4ax.c om:

Per Rick Brandt:

you must provide a fully qualified object reference in an inner
With block to any member of an object in an outer With block.

I'll bet that's what got me off on the notion that they were a
no-no.... i.e. tried it, got my hand slapped, didn't think about
it any more.

I think it creates extremely bad code to nest WITH blocks. To me,
it's a sign of a bad programmer, as it's optimization that makes
the code much harder to read and understand.

No more so than nested anything else.

But that is logically necessary.

And, of course, I try to avoid complex If/Then/Else nesting when
possible, in any case, as it seems to me to be a clue that there is
logic/design problem.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

"David W. Fenton" <XX*******@dfen ton.com.invalid > wrote in
news:Xn******** *************** ***********@127 .0.0.1:

I think it creates extremely bad code to nest WITH blocks. To me,
it's a sign of a bad programmer, as it's optimization that makes the
code much harder to read and understand.

I'm a bad programmer.

--
Lyle Fairfield

[Lyle Fairfield]

"(PeteCresswell )" <[e-mail]> wrote in
news:kq******** *************** *********@4ax.c om:

Per David W. Fenton:

I think it creates extremely bad code to nest WITH blocks. To me,
it's a sign of a bad programmer, as it's optimization that makes the
code much harder to read and understand.

Excellent. Now I feel better.... -)

I'm glad; but there is nothing wrong with nested withs. I have been using
them since before the turn of the century with zero problems.

--
Lyle Fairfield