|
P: n/a
|
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.Add
oSheet.Activate
strSheetName = "ptManagers"
oSheet.Name = strSheetName
Set wksPivot = oSheet
oBook.Worksheets(oSourceSheet.Name).PivotTables("F lashPivotTable").PivotCache.
_
CreatePivotTable TableDestination:=wksPivot.Range("A3"), _
tableName:="ptManagersPivotTable", DefaultVersion:=xlPivotTableVersion10
Set pvtTable = wksPivot.PivotTables("ptManagersPivotTable")
With pvtTable
' Specify page fields.
.PivotFields("Group").Orientation = xlPageField
.PivotFields("Group").CurrentPage = "Total"
.PivotFields("Type").Orientation = xlPageField
With .PivotFields("Date")
.Orientation = xlPageField
.NumberFormat = "ddMMMyy"
End With
' Specify fields
.PivotFields("Product").Orientation = xlRowField
.PivotFields("Portfolio").Orientation = xlRowField
With .PivotFields("Portfolio Base Rtn(%)")
.Orientation = xlDataField
.Position = 1
.NumberFormat = "0.0"
.Caption = ".Portfolio Base Rtn(%)"
End With
With .PivotFields("Index Base Rtn(%)")
.Orientation = xlDataField
.Position = 2
.NumberFormat = "0.0"
.Caption = ".Index Base Rtn(%)"
End With
With .PivotFields("Stock Select")
.Orientation = xlDataField
.Position = 3
.NumberFormat = "0.000"
.Caption = ".Stock Select"
End With
With .PivotFields("Group Weight")
.Orientation = xlDataField
.Position = 4
.NumberFormat = "0.000"
.Caption = ".Group Weight"
End With
With .PivotFields("Total")
.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("Portfolio").AutoSort xlDescending, ".Total"
.PivotFields("Product").Subtotals = Array(False, False, False, False,
False, False, False, False, False, False, False, False)
End With
With oSheet
.Range("5:5").EntireRow.Hidden = True
.Rows("6:6").RowHeight = 56.25
With .Range("A6:G6")
.Font.Bold = True
.Interior.ColorIndex = 1
.Interior.Pattern = xlSolid
.Font.ColorIndex = 2
.HorizontalAlignment = xlCenter
.VerticalAlignment = 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
.DisplayAutomaticPageBreaks = False
End With
oXL.ActiveWindow.DisplayGridlines = False
oBook.ShowPivotTableFieldList = False
End Sub
------------------------------------------------------------------------------
--
PeteCresswell
| |
Share this Question
|
P: n/a
|
(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 | |
|
P: n/a
|
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 | |
|
P: n/a
|
"(PeteCresswell)" <x@y.Invalid> wrote in
news:6e********************************@4ax.com: 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/ | |
|
P: n/a
|
(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 | |
|
P: n/a
|
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 | |
|
P: n/a
|
David W. Fenton wrote: "(PeteCresswell)" <x@y.Invalid> wrote in
news:6e********************************@4ax.com:
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. | |
|
P: n/a
|
rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:I%******************@twister.nyroc.rr.com: David W. Fenton wrote: "(PeteCresswell)" <x@y.Invalid> wrote in
news:6e********************************@4ax.com:
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/ | |
|
P: n/a
|
"David W. Fenton" <XX*******@dfenton.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 | |
|
P: n/a
|
"(PeteCresswell)" <x@y.Invalid> wrote in
news:kq********************************@4ax.com: 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 | |
|
P: n/a
|
"Lyle Fairfield" wrote 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.
Turn of which century? :-)
And, of course, your perception and memory far outclasses the average. Thus
you can be an example to, but not representative of us of the "hoi polloi".
I'm with David on the subject of nesting withs -- it makes the code hard to
read for us "lesser mortals."
Larry | |
|
P: n/a
|
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119... "David W. Fenton" <XX*******@dfenton.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.
Yeh, man, don't nobody mess wid you cuz you de baddest dere is! :-) | |
|
P: n/a
|
I wouldn't agree with that at all. Nested with statements don't necessarily
lead to poor readability, the biggest cause of that is behemoth procedures.
In fact when I take over a project from someone else I frequently find
myself going through implementing nested withs in order to increase
readability as much as anything.
--
Terry Kreft
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "(PeteCresswell)" <x@y.Invalid> wrote in
news:6e********************************@4ax.com:
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/ | |
|
P: n/a
|
BAD BAD LYLE FAIRFIELD
Wih apologies to Jim Croce
Well the South side of Chicago
Is the baddest part of town
And if you go down there you better just beware
Of a man named Lyle Fairfield
Now Lyle more than trouble
You see he stand about six foot four
All the downtown ladies call him treetop lover
All the men just call him sir
....
--
Terry Kreft
"Larry Linson" <bo*****@localhost.not> wrote in message
news:3gg9g.1683$_B5.51@trnddc01... "Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119... "David W. Fenton" <XX*******@dfenton.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.
Yeh, man, don't nobody mess wid you cuz you de baddest dere is! :-)
| |
|
P: n/a
|
Lyle Fairfield <ly***********@aim.com> wrote in
news:Xn*********************************@216.221.8 1.119: "David W. Fenton" <XX*******@dfenton.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.
A regular participant in one of the other forums I participate in
regularly points out that code is going to be *read* more often that
it is *written*. Thus, optimizations of the *writing* of the code,
when they make the code more convoluted, are going to be much more
inefficient than any amount of verbosity.
Now, this is separate from the issue of performance. My feeling is
that WITH blocks can speed up certain kinds of looped operations,
since they create an implicit reference to a parent object rather
than having to resolve through both the parent object's collection
and that of whatever object you're looking for within the
collections of that parent object.
But, in general, the feeling of a need to nest WITHs seems to me to
be an erroneous compulsion, one that makes the code harder to
maintain.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/ | |
|
P: n/a
|
Per Larry Linson:
I'm with David on the subject of nesting withs -- it makes the code hard to
read for us "lesser mortals."
I must be among the "*Much* lesser..." because I even found myself stumbling
over the code in the "good" example in the OP.
--
PeteCresswell | |
|
P: n/a
|
Terry Kreft wrote: I wouldn't agree with that at all. Nested with statements don't necessarily
lead to poor readability, the biggest cause of that is behemoth procedures.
Amen. | |
|
P: n/a
|
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:JO********************@karoo.co.uk... I wouldn't agree with that at all. Nested with statements don't
necessarily
lead to poor readability, the biggest cause of that is behemoth
procedures.
In fact when I take over a project from someone else I frequently find
myself going through implementing nested withs in order to increase
readability as much as anything.
I may uh bin wrong. Youse may be de baddest ub all! Don' nobody mess wid
Terry now, cuz dat man think nestin' Withs make things easier.
Larry | |
|
P: n/a
|
<Officer Barbrady>
OK, Lets move along now folks, there's nothing to see here ...
<Officer Barbrady>
--
Terry Kreft
"Larry Linson" <bo*****@localhost.not> wrote in message
news:cpL9g.367$343.134@trnddc06... "Terry Kreft" <te*********@mps.co.uk> wrote in message
news:JO********************@karoo.co.uk...
I wouldn't agree with that at all. Nested with statements don't
necessarily
lead to poor readability, the biggest cause of that is behemoth
procedures.
In fact when I take over a project from someone else I frequently find
myself going through implementing nested withs in order to increase
readability as much as anything.
I may uh bin wrong. Youse may be de baddest ub all! Don' nobody mess wid
Terry now, cuz dat man think nestin' Withs make things easier.
Larry
| |
|
P: n/a
|
"Terry Kreft" <te*********@mps.co.uk> wrote <Officer Barbrady>
OK, Lets move along now folks, there's nothing to see here ...
<Officer Barbrady>
<MUTTER_GRUMBLE> "Nested WIFs." </MUTTER_GRUMBLE>
(Shuffles off, shoulders slumped.) | | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 20283
- replies: 19
- date asked: May 12 '06
|
