cri
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.BuildFr eeform(mso EditingCor ner, 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
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.BuildFr
.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
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
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
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.
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
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
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!
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
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
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.
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 :)
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
:-)
regards
ASKER
yep, as a crashing PC is the default
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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 ?;)
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 ?;)
ASKER
FYI:
Using the .AddPolyline instead of the .BuildFreeform method seems to be simplest work-around.
Using the .AddPolyline instead of the .BuildFreeform method seems to be simplest work-around.
ASKER
Excel 8.0e (Build 5618), running on Windows (32-bit) NT 4.00