Link to home
Start Free TrialLog in
Avatar of cri
criFlag for Switzerland

asked on

Excel 97 / VBA: Minimal distance between shape nodes ?

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

I seem to have stumbled over a bug ..er.. feature 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 ?

b) 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 version/OS as written into the active cell
- Re-run macros with diminishing values for 'a' until you get an application error
- Try with the last 'a', in my case it will not work, I need a restart of Excel, merely resetting is not sufficient

Sub MinimalNodeDistance()
 
  Const a As Single = 8 'will crash if < 8, 'souring' VBA. Excel _restart_ is required
  Const x0 As Single = 50
  Const y0 As Single = 50
  Dim sWhere As String
 
  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



Avatar of cri
cri
Flag of Switzerland image

ASKER

My work PC:

Excel 8.0e (Build 5618),  running on Windows (32-bit) NT 4.00
Hi Cri, interesting never done this stuff

Excel 8.0a (Build 4307),  running on Windows (32-bit) 4.10

and i got an runtime error 1004 at a=7 and lower but nothing crashed at least not excel

:O)Bruintje
windows 98 btw
Avatar of cri

ASKER

bruintje, thanks for tackling this question.

I must be more precise: It does not crash Excel, but apparently VBA / ConvertToShape method is 'soured', i.e. even if you reset VBA and change the value to >=8 it will not work until you restart Excel.

Another interesting fact is that you can resize the shape to make it real small and the connection nodes are all functional.
well saw the crashing at 7.36

first time you get the 1004 error
then after two times F5 excel crashes

this seems like a bug to me and there's nothing on the web or i'm looking wrong at it but who's using freefrombuilder anyway ;)

it's xl97 can't test till later for 2k or xp
Avatar of Elmo_
I have never done this stuff either but I thought I may as well post, here goes:

Excel 8.0a (Build 4307),  running on Windows (32-bit) 4.00

- I got a runtime error 1004 at a=7 and lower
- Hit Debug
- Hit F8
- I got a runtime error 1004 at a=7 and lower
- Hit Debug again
- Hit F8 again
- This time Excel Crashed

I also noticed what you mentioned about being able to resize the shape - Strange!

Go there just before me!! :-)
Hi cri,

I tested it on 2000 (Excel 9.0 (Build 4430),  running on Windows (32-bit) NT 5.00) and I do not have the problem that after the error 1004 I have to restart. Everything is still working without any problem.
I tried what bruintje was doing and what elmo did, still no problems....
My "crashing point" lays at 7,38, below it crashes.

Regards
Avatar of cri

ASKER

Thank for all responses.

In my real macro the minimal distance seems is something between 8 and 9 points, i.e. it does not seem to be constant as the (bloody) offset of 11.8 points when duplicating a shape.

How shall I proceed with the points ? I suggest to the person which manages to get it into the Microsoft Knowledge Base... And double points for a bugfix ? >8o)

Just kidding, I tried this once and the lady explained me for half an hour (no kidding) why she was not going to click on a demo macro button as not _directly_ related to
a bug report... No wonder you find this kind of stuff in version 8.
well we can have this made a PAQ and a refund of your points, since it was no problem only a bug mention

put it on the MS site as a feedback item but i wonder if i ever hear back from them. i even mentioned that it is difficult to talk to a governement like institution as they seem to be

maybe that will get something back, a crashing PC for example :)
They should at least send a photo of Bill Gate$$$

:-)

regards
Avatar of cri

ASKER

yep, as a crashing PC is the default
Avatar of cri

ASKER

Time to PAQ this question.

Thank for all which tackled the question. I want to award Bruintje. Besides being the first to respond he gave feedback to Microsoft.

Bruintje As the "Accept Comment As Answer" seems to be brocken _again_, please post as answer.
ASKER CERTIFIED SOLUTION
Avatar of Brian Mulder
Brian Mulder
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cri

ASKER

Bruintje, finally. Do you _really_ expect Microsoft to give feedback ? Probably it needs umpteen $upport calls to get something into the MSKB...

Again: Thanks for all who tackled this question.
yeah sorry, but first i don't wanted theses points and second your last comment here went down under the load in my mailbox, cleaning up here and everywhere on this site gets me tons of notifs every day

have to rebuild my forwarder to further classify the incomming notifs now it sorts and forwards only on a notif state like good answer, accepted etc....it has to sort on TA and state that would be nice

but first work

btw i'm going to try another route for this bug maybe getting somewhere ?;)
Avatar of cri

ASKER

FYI:
Using the .AddPolyline instead of the .BuildFreeform method seems to be simplest work-around.