Re: Border Mystery?
From: Josh Sale (jsale_at_tril)
Date: 03/16/05
- Next message: Gixxer_J_97: "on error goto"
- Previous message: quartz: "ADO cloned recordset doesn't return field names"
- In reply to: Peter T: "Re: Border Mystery?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 16 Mar 2005 13:03:20 -0700
Thanks again Peter. Armed with my new understanding, I've got thinks all
worked out now (famous last words!).
josh
"Peter T" <peter_t@discussions> wrote in message
news:%23V5DvmlKFHA.3500@TK2MSFTNGP14.phx.gbl...
> Josh - Indeed I do have to modify my assertion (actually it wasn't quite
> as
> strong as that). I really thought I had tested your code in both xl97 &
> xl2k
> and it had failed in both. Just had another look, it still fails in xl2k
> but
> strangely not in xl97. Also as you said, the inside vertical property for
> linestyle of the single cell shows as 11 in xl97 and 10 in xl2k, despite
> there being no such documented values for linestyle.
>
> I'm surprised, normally with such things xl97 is more likely to fail than
> later versions, I'm also surprised that it "can" work in xl97 (ie doesn't
> fail) - logically it should fail. Apologies for casting doubt on what you
> said. But it doesn't change my advice don't do it that way!
>
> Regards,
> Peter T
>
> "Josh Sale" <jsale@tril dot cod> wrote in message
> news:OQu61skKFHA.3272@TK2MSFTNGP10.phx.gbl...
>> Peter thanks for your persistence! The lost response remains lost!
>>
>> I added the suggested Debug.Print to the code and ran it under XL97 and
>> XL2003 and the Debug.Print results were identical. I think XL97 is just
>> more forgiving in this case.
>>
>> So I would modify your assertion to: Sending both single cell and
>> xlInsideVertical to your original function will fail in any version of XL
>> after XL2000.
>>
>>
>> The bottom line is I need to rethink how I'm going to handle the two
> inside
>> borders. I haven't bored you with the whole scope of my application but
> its
>> pretty big and complicated. I think you've given me the background on
>> borders I need to sort this out now.
>>
>> Much appreciation!!!
>>
>> josh
>>
>>
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:u7LAGcjKFHA.3420@tk2msftngp13.phx.gbl...
>> > This is second attempt to reply, what I posted a few hours ago seems to
>> > have
>> > got lost in the stratosphere - annoying! If it turns up you want want
>> > to
>> > read both. Following from memory of what I previously wrote:
>> >
>> > Josh - this doesn't add up. Sending both single cell and
> xlInsideVertical
>> > to
>> > your original function will fail in any version of XL.
>> >
>> >> Both versions are definitely running against the identical data. The
>> > source
>> >> range is a single cell when testing with XL97 and XL2003.
>> >
>> > Near the top of your function add
>> > Debug.print borderType, source.address(0,0)
>> > And compare failing and working versions
>> >
>> >> So given that my source will always be a single cell, I may as well
> just
>> >> skip trying to copy the two inside borders because by defintion they
> can
>> >> never be there.
>> >>
>> >> Is that right?
>> >
>> > Certainly re copying inside borders of a single cell. But what do you
> want
>> > to do about the inside borders of target multicell range - apply
>> > attributes
>> > from one of source's edges to insides, perhaps. Also what if user
> selects
>> > a
>> > multicell source with inside borders - then do you want to copy those.
>> >
>> >> The reason for the two Border object variables was (hopefully)
>> >> improved
>> >> performance. Since I expect this routine to be called quite a bit, I
> was
>> >> trying to (in effect) have two With's going at once. I have to admit
> to
>> > not
>> >> timing the code so I don't know if it is in fact any faster.
>> >
>> > When you say "called quite a bit" I assume you mean many times by the
> user
>> > when he wants to copy borders. Wouldn't think even inefficient code
> would
>> > be
>> > noticeably slow. FWIW reading cells is significantly faster than
> writing,
>> > so
>> > I wouldn't worry too much about source. Possibly setting a reference to
>> > target borders might speed up - I haven't tested.
>> >
>> > If you want to copy individual borders, each with their own attributes,
> to
>> > many areas at the same time you could do something like this:
>> >
>> > Sub test()
>> > Dim source As Range, target As Range, ra As Range
>> > Dim arrBorders(7 To 10, 0 To 2)
>> > Dim i As Long
>> > Set source = Range("B3")
>> > Set target = Range("C4:F6, H4:J10")
>> >
>> > With source.Borders
>> > For i = 7 To 10
>> > arrBorders(i, 0) = .Item(i).LineStyle
>> > arrBorders(i, 1) = .Item(i).Weight
>> > arrBorders(i, 2) = .Item(i).ColorIndex
>> > Next
>> > End With
>> >
>> > For Each ra In target.Areas
>> > With ra
>> > .Borders.LineStyle = arrBorders(7, 0)
>> > .Borders.Weight = arrBorders(7, 1)
>> > .Borders.ColorIndex = arrBorders(7, 2)
>> > .Borders.LineStyle = xlNone
>> > For i = 7 To 10
>> > .Borders(i).LineStyle = arrBorders(i, 0)
>> > .Borders(i).Weight = arrBorders(i, 1)
>> > .Borders(i).ColorIndex = arrBorders(i, 2)
>> > Next
>> > If .Columns.Count > 1 Then
>> > 'copy source Left to inside verticals ?
>> > .Borders(11&).LineStyle = arrBorders(7, 0)
>> > .Borders(11&).Weight = arrBorders(7, 1)
>> > .Borders(11&).ColorIndex = arrBorders(7, 2)
>> > End If
>> >
>> > If .Rows.Count > 1 Then
>> > .Borders(12&).LineStyle = arrBorders(8, 0)
>> > .Borders(12&).Weight = arrBorders(8, 1)
>> > .Borders(12&).ColorIndex = arrBorders(8, 2)
>> > End If
>> > End With
>> > Next
>> > End Sub
>> >
>> > As I said before - involves a loop unless you want to code individually
>> > for
>> > each border, which amounts the to the same thing but with more code.
>> >
>> > If you want to copy the same attributes from say source left border, to
>> > all
>> > borders in target, then maybe:
>> >
>> > with target
>> > .borders.linestyle = source.borders(xledgeleft).linestyle
>> > ditto weight & colorindex
>> > end with
>> >
>> > Another way might be to brute force your way through all borders,
> whether
>> > or
>> > not they exist in either source or target
>> >
>> > on error resume next
>> > for i = 7 to 12
>> > target.borders(i).linestyle = source.borders(xledgeleft).linestyle
>> > etc
>> > next
>> > on error goto 0
>> >
>> > Forcing like this is probably frowned on around here but short code to
>> > cater
>> > for all scenarios, should be OK
>> >
>> > Regards,
>> > Peter T
>> >
>> > "Josh Sale" <jsale@tril dot cod> wrote in message
>> > news:ukzxaUcKFHA.2852@TK2MSFTNGP14.phx.gbl...
>> >> Peter thanks for your followup. Let me try to respond ...
>> >>
>> >> "> Josh - On the basis of what you added you've reinforced my first
> guess
>> >> "you
>> >> > are trying to copy the inside vertical from your single cell source,
>> > which
>> >> > does not exist " and I'll add a second:
>> >> >
>> >> > In the versions in which your code works I "guess" your source is
>> >> > NOT
> a
>> >> > single cell whereas in XL2003, where it fails, source IS a single
> cell.
>> >>
>> >> Both versions are definitely running against the identical data. The
>> > source
>> >> range is a single cell when testing with XL97 and XL2003.
>> >>
>> >>
>> >> > Effectively you are doing this
>> >> >
>> >> > Dim SourceBorder As Border
>> >> > Dim source as range
>> >> > Set source = mySingleCell
>> >> > Set SourceBorder = source.borders(xlInsideVertical)
>> >> >
>> >> > But a single cell doesn't have an inside vertical border, so when
>> >> > you
>> > try
>> >> > and copy to your multi column target it will fail.
>> >> >
>> >> > Think you need to apply same check that "source" has inside borders
> as
>> > you
>> >> > have with "target"
>> >>
>> >> I think you've put your finger on it here. I think I have an
>> >> incorrect
>> >> mental model on how these inside borders work. I assumed that if the
>> >> user
>> >> selects A1:C1 and formats those cells with inside vertical borders,
> that
>> > if
>> >> you then selected just cell B1 it would have an inside vertical
>> >> border.
>> >>
>> >> Experimenting right now I see that's not the case. B1 in fact has
>> >> left
>> > and
>> >> right vertical borders and no inside border.
>> >>
>> >> So given that my source will always be a single cell, I may as well
> just
>> >> skip trying to copy the two inside borders because by defintion they
> can
>> >> never be there.
>> >>
>> >> Is that right?
>> >>
>> >>
>> >> >
>> >> > Not sure why you are even using the Borders object variable. Having
>> >> > done
>> >> > your checks for Insides in both source and target, simply:
>> >> >
>> >> > target.Borders(borderType).Weight =
>> >> > source.Borders(borderType).Weight
>> >>
>> >> The reason for the two Border object variables was (hopefully)
>> >> improved
>> >> performance. Since I expect this routine to be called quite a bit, I
> was
>> >> trying to (in effect) have two With's going at once. I have to admit
> to
>> > not
>> >> timing the code so I don't know if it is in fact any faster.
>> >>
>> >>
>> >> >
>> >> > Afraid my OE has taken upon itself to remove all attachments from
> posts
>> > in
>> >> > ng's deeming them unsafe, so I can't see your screenshot of those
>> > strange
>> >> > constants!
>> >> >
>> >> > Regards,
>> >> > Peter T
>> >> >
>> >> > "Josh Sale" <jsale@tril dot cod> wrote in message
>> >> > news:uzXDJKbKFHA.2800@TK2MSFTNGP10.phx.gbl...
>> >> >> Peter,
>> >> >>
>> >> >> Please see my replied in context:
>> >> >>
>> >> >> > Are you sure you are correctly applying your border properties,
>> >> >> > ie
>> > how
>> >> > is
>> >> >> > borderType defined in the calling procedure.
>> >> >>
>> >> >> The calling procedure uses the Excel constants. For example:
>> >> >> CopyBorder xlInsideVertical, target, source
>> >> >> so they should be valid.
>> >> >>
>> >> >> > Also are you sure you are reading those constants correctly,
>> >> >> > AFAIK
>> >> >> > there
>> >> >> > is
>> >> >> > no value 11 for any of the LineStyles, in any version. In Object
>> >> >> > browser
>> >> >> > search xllinestyle and examine all the corresponding constants My
>> > guess
>> >> > is
>> >> >> > you are trying to copy the inside vertical from your single cell
>> >> >> > source,
>> >> >> > which does not exist.
>> >> >> > XlInsideVertical = 11 !!
>> >> >>
>> >> >> I agree neither 11 or 241 correspond to any of the defined
>> >> >> LineStyle
>> >> >> constants. However, as you (hopefully the NG can handle
> attachments)
>> > can
>> >> >> see from the attached screen shot (Excel 2003) the LineStyle is 241
> (I
>> >> >> got
>> >> >> the 11 under Excel 97 ... where the code works fine!). I can only
>> > assume
>> >> >> its just a conincidence that xlInsideVertical resolves to the same
>> > value
>> >> > as
>> >> >> I get under Excel 97.
>> >> >>
>> >> >>
>> >> >> > You will need to loop properties for each border, unless all the
>> >> >> > properties
>> >> >> > for each border are the same and, you source from a single cell
>> >> >> > or
>> >> > similar
>> >> >> > size range, in which case you could do something like:
>> >> >> >
>> >> >> > [d2:f6].Borders.LineStyle = [b2].Borders.LineStyle 'Weight &
>> > ColorIndex
>> >> >> >
>> >> >> > If looping borders and applying same to each you could do:
>> >> >> >
>> >> >> > bDoInsideVert = False
>> >> >> > bDoInsideHoriz = False
>> >> >> >
>> >> >> > For i = 7 To 12
>> >> >> > If i = 11 And bDoInsideVert = False Then
>> >> >> > ElseIf i = 12 And bDoInsideHoriz = False Then
>> >> >> > Else
>> >> >> > [d2:f6].Borders(i).LineStyle = [b2].Borders.LineStyle
>> >> >> > [d2:f6].Borders(i).Weight = [b2].Borders(i).Weight
>> >> >> > [d2:f6].Borders(i).ColorIndex = [b2].Borders(i).ColorIndex
>> >> >> > End If
>> >> >> > Next
>> >> >>
>> >> >> The routine is always called with the Source range set to a single
>> > cell.
>> >> > So
>> >> >> I don't think looping should be necessary.
>> >> >>
>> >> >>
>> >> >> > To be ultra picky, you should change
>> >> >> > borderType As Integer
>> >> >> > to
>> >> >> > borderType As Long
>> >> >>
>> >> >> I suppose. However the range of all defined BorderIndex constants
> (5
>> > to
>> >> > 12)
>> >> >> are easily contained in an integer. I think if this were the
> problem,
>> >> >> the
>> >> >> code would fail 100% of the time. However, the caller of this
> routine
>> >> >> successively calls it (xlEdgeLeft, xlEdgeRight, etc) and all of
> those
>> >> > calls
>> >> >> work. So far its only having trouble on xlInsideVertical.
>> >> >>
>> >> >> Any other thoughts? Thanks.
>> >> >>
>> >> >> josh
>> >> >>
>> >> >>
>> >> >> > "Josh Sale" <jsale@tril dot cod> wrote in message
>> >> >> > news:ekYnIbQKFHA.3332@TK2MSFTNGP15.phx.gbl...
>> >> >> >> I have code that copies border formatting from one cell to a
> range.
>> >> > Its
>> >> >> >> been working fine. But I'm now getting a runtime error "Unable
>> >> >> >> to
>> > set
>> >> > the
>> >> >> >> Weight property of the Border class" trying to copy an
>> >> >> >> xlInsideVertical
>> >> >> >> border. The same error occurs trying to copy the LineStyle
>> > property.
>> >> >> >>
>> >> >> >> I know that a common problem with the xlInsideVertical border is
>> > that
>> >> > it
>> >> >> > can
>> >> >> >> only be applied to a multi-column range. My code checks for
>> >> >> >> this
>> >> >> > condition.
>> >> >> >> However in this case, the target range is B20:E20.
>> >> >> >>
>> >> >> >> I went back and retested this code under XL97, XL2000, XL2002
>> >> >> >> and
>> >> > XL2003
>> >> >> >> (all versions include all of the latest MS updates). It works
>> >> >> >> under
>> >> > XL97
>> >> >> >> but fails under all of the later releases.
>> >> >> >>
>> >> >> >> If I manually open the workbook under XL97 and then examine the
>> > source
>> >> >> >> cell's border in a watch window it has a LineStyle of 11 and a
>> > Weight
>> >> >> >> of -4138 (this is the case where the copy works). If I repeat
> this
>> >> >> >> procedure under XL2003, LineStyle is 241 and Weight is 225!
>> >> >> >>
>> >> >> >> I presume this difference is what's causing it to work under
>> >> >> >> XL97
>> > and
>> >> >> >> fail
>> >> >> >> under later versions.
>> >> >> >>
>> >> >> >> I've looked at all of the LineStyle constants and neither 11 or
> 241
>> > is
>> >> >> >> defined! I don't know what to make of that?
>> >> >> >>
>> >> >> >> Here's the code in question:
>> >> >> >>
>> >> >> >> Private Sub CopyBorder(borderType As Integer, Target As Range,
>> > source
>> >> > As
>> >> >> >> Range)
>> >> >> >> Dim TargetBorder As Border
>> >> >> >> Dim SourceBorder As Border
>> >> >> >>
>> >> >> >> If Not (borderType = xlInsideHorizontal And Target.Rows.Count
> <
>> >> >> >> 2
>> >> >> >> Or
>> >> > _
>> >> >> >> borderType = xlInsideVertical And
>> >> >> >> Target.Columns.Count
>> > <
>> >> > 2)
>> >> >> >> Then
>> >> >> >> Set TargetBorder = Target.Borders(borderType)
>> >> >> >> Set SourceBorder = source.Borders(borderType)
>> >> >> >>
>> >> >> >> TargetBorder.Weight = SourceBorder.Weight
>> >> >> >> TargetBorder.LineStyle = SourceBorder.LineStyle
>> >> >> >> TargetBorder.color = SourceBorder.color
>> >> >> >> TargetBorder.ColorIndex = SourceBorder.ColorIndex
>> >> >> >> End If
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >> Again, its the assignment of Weight and LineStyle that fail.
>> >> >> >> The
>> >> >> > assignment
>> >> >> >> of Color and ColorIndex work fine.
>> >> >> >>
>> >> >> >> Any thoughts?
>> >> >> >>
>> >> >> >> TIA,
>> >> >> >>
>> >> >> >> josh
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>
- Next message: Gixxer_J_97: "on error goto"
- Previous message: quartz: "ADO cloned recordset doesn't return field names"
- In reply to: Peter T: "Re: Border Mystery?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
