Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 97 VBA: Min Distance between Shape Nodes (SR-2)

    Posted same question in http://www.experts-exchange.com/jsp/qManag...mp;qid=20336203

    I am creating some Shapes as even Visio 2000 does not have them.

    I seem to have stumbled over a bug ..er.. feature for which I do not find a reference in whole (accessible) internet:
    If the distance between the nodes is below approx. 8 points the VBA is soured to the point an Excel restart is needed.

    Here my questions:

    a) Did you ever encounter this ?

    [img]/forums/images/smilies/cool.gif[/img] Please test the following code on your machine:
    - Make sure no open _work_ is left unsaved
    - Please run my sub first with a=8
    - Paste the string here containing the Excel version / Operating System as written into the active cell of your test worksheet
    - Re-run macros with diminishing values for 'a' until you get an application error
    - Try with the _previous_ last 'a'
    in my case the ConvertToShape method will not work anymore, I need to restart Excel, merely resetting is not sufficient

    <pre>
    Sub MinimalNodeDistance()

    Const a As Single = 8 'will crash if < 8, 'souring' the ConvertToShape method
    Const x0 As Single = 50
    Const y0 As Single = 50

    With Application
    ActiveCell = "Excel " & .Version & " (Build " & .Build & "), _
    running on " & .OperatingSystem
    End With

    With ActiveSheet.Shapes.BuildFreeform(msoEditingCorner, x0, y0)
    .AddNodes msoSegmentLine, msoEditingAuto, x0 + a, y0
    .AddNodes msoSegmentLine, msoEditingAuto, x0 + a, y0 + a

    .AddNodes msoSegmentLine, msoEditingAuto, x0 + 2 * a, y0 + a
    .AddNodes msoSegmentLine, msoEditingAuto, x0 + 2 * a, y0 + 2 * a
    .AddNodes msoSegmentLine, msoEditingAuto, x0 + 3 * a, y0 + 2 * a
    .ConvertToShape.Select
    End With
    End Sub
    </pre>


  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 VBA: Min Distance between Shape Nodes (SR-2)

    Interesting is that a shape created at a=8 points can be resized w/o any apparent problems.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 97 VBA: Min Distance between Shape Nodes (SR-2)

    I hadn't tried this myself, but now I have the same experience as you.

    Excel 8.0e (Build 5618), running on Windows (32-bit) NT 4.00
    Additional info: I use the Dutch language version of Excel 97 SR2, and the English (don't know whether US or UK) version of NT4 SP6 (build 1381)

    a>=8 runs fine
    a<8 errors at ConvertToShape
    a>=8 then errors at ConvertToShape too, making a restart of Excel necessary.

    Regards,
    Hans

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel 97 VBA: Min Distance between Shape Nodes (SR-2)

    Using Excel 9.0 (Build 3822), running on Windows (32-bit) NT 5.01 I get the error if a<8 but can then rerun with a>=8 without having to restart. Some sort of progress I guess!
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 VBA: Min Distance between Shape Nodes (SR-2)

    Thank for all the replies up to now.

    In my real macro the minimal distance is somewhat bigger, around 8 to 9 points, i.e. it does not seem to be constant as the offset of 11.8 points when duplicating a shape.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 VBA: Min Distance between Shape Nodes (SR-2)

    FYI: The simplest work-around seems use the .AddPolyline instead of the .BuildFreeform method.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •