SETATREF(Height) / EventXFMod Behavior

[MacGyver]

I'm attempting to run a macro when height of a shape is changed to dynamically adjust connection points.  I'm running into an issue that appears others have run into (similar topic)

I've attempted using CALLTHIS in a user cell like such:

Code Select Expand

=SETATREF(Height,CALLTHIS("ConnPtAdjust"))And the ConnPtAdjust macro gets executed anywhere from 4-20 times.  I've attempted moving the code to the EventXFMod Behavior as well and same result - code is executed multiple times. 

The real oddity is if i toss a break in there for debug purposes, code functionality works exactly as expected.  If no break, it executes between 4 and 20 times.  Even adjusted my code so that called routine only debug prints the time to verify it wasn't my code causing the error and same results.

I changed from CALLTHIS to use RUNMACRO (and forgot to remove the visShape as input) and it errored out multiple times.  I have added various wait times to my code which also doesn't fix the issue.  Attached the drawing with shape in question, and included code that gets executed.

Any thoughts/tips would be greatly appreciated!

[MacGyver]

Another weird characteristic I've noticed is that when my macro is run multiple times. The various routine's appear to be running in parallel. As in one the first run of the routine may not have finished running before the second instance of the routine is executed.

I added code to debug.print start/stop times of an otherwise empty macro.  I then added some code that throws an intentional error.  The debug window shows that the macro started 6 times, and only ended once.  If the intentional error is removed, the macro was started some random number of times and ended some random number of times.

[Yacine]

Your observation is right. When you drag something (shape or size handle) the event gets fired multiple times. You can observe this if you run the events manager.

Why not put the macro on an action command (right button menu, then deliberately run the command)

Other option, but slightly more complex. Let the macro know that you've finished the resizing.
Define a general variable to hold the time. You're macro checks first the time between the current call and the value stored. If difference is small (threshold), it stores the current time in the global variable and leaves, otherwise it runs the actually intended code.

'*******************************
Public lastcall As Double
'*******************************

Sub ConnPtAdjust(shp As Visio.Shape)
    ' this will add remove conection points from some shapes - Box, Device, EGSE after shape is resized
    ' this macro is called from the shapesheet of the resized shape
    ' shp is passed into this routine from when it is called within the shapesheet
   
    ' connections points for these shapes are defined from the top down (connection point 1 and 2 are top/bot mid; cp3,4 is upper most left/right; cp5,6 are cps below 3,4
    ' additional connection points req to be added if last connection point Y value is >= 0.875 inches
    ' connection points req to be deleted if last connection point Y values is < 0.875 inches
   
    Dim connPtRow As Integer    ' row identifier of last connection point
    Dim UndoScopeID1 As Long
    Dim lastCPYVal As Single    ' y value of last connection point (zero based)
    Dim rowsToAdd As Integer    ' number of connection point rows we need to add to vba formula is 0 based. for shapesheet formulas it is one-based
    Dim rowNew As Integer      ' the row we just added
    Dim i As Integer
       
    Dim currentTime As Date
    Dim milliseconds As Double
    currentTime = Now()
'*******************************
    If Timer - lastcall < 100 Then
      lastcall = Timer
      Exit Sub
    End If
'*******************************
....

[wapperdude]

Couple additional notes: 
>  Your usage of SETATREF is not correct.  The cell reference is intended to be the target for action, not the trigger.  Instead, use CALLTHIS("ConnPtAdjust")+DEPENDSON("Height").  For reference, see https://learn.microsoft.com/en-us/office/client-developer/visio/setatref-function and https://visguy.com/vgforum/index.php?topic=6383.msg26308#msg26308  This applies to all 3 of your SETATREF formulae.

>  I'm having trouble getting the code to run manually, but don't have time to investigate.  However, I recommend deconstructiong it, without the error stuff and without the auto-triggering.  Just add rows and delete.  You can step thru using <F8>, and you can set execution breakpoints.  There should not be any re-triggering.  Keep the control point count low.  I'm not convinced the code fully restricts adding / deleting.  Then, add the +Dependson statement in shapesheet.  Keep breakpoints in code.  See if behavior is as desired.  Finally, remove breakpoints and let code fully run.

>  As resizing the height is the trigger, moving should not be an issue.  Also, duplicating should not be an issue if the code is properly checking row count and connection position. There should not be any retriggering, or if there is, it's of no consequence.

[MacGyver]

Thanks guys!
@wrapperdude, I changed it up to CALLTHIS("ConnPtAdjust")+DEPENDSON("Height"), but code continues to get executed multiple times.
 

Also, duplicating should not be an issue if the code is properly checking row count and connection position. There should not be any retriggering, or if there is, it's of no consequence.

I'm finding this not to be the case.  With additional debug messaging, I'm finding out what is happening is 1st execution of macro Adds a new point and before it is able to set its properties, a 2nd macro might add another point, while the 3rd might delete one of those points.  so then when 1/2 macro goes to set the value it is not setting the intended row. 

Think I'll be resorting to @Yaccine's solution of making use of the shape actions.  Thanks all for the assistance!

[wapperdude]

I ran the macro from the shape with the Dependson syntax...confirmed the multiple executions.  Then, using Size and Position Window, typed in a value for height.  Execution was as expected for both increased and decreased heights.  The problem is dragging the height causes multiple triggers.

To eliminate the multiple triggers, the obvious is to disallow resizing by mouse drag.  Another approach might be to required mouse button up as necessary event.  Other approaches?

For the record, although I dont' recomme nd it, your SETATRREF syntax also works using keyboard value entry.

[Yacine]

The code with the global variable works. Why not try it?

[wapperdude]

The code with the global variable works. Why not try it?

How did you do this?  I tried and it made no difference,

[Yacine]

Did you add the two pieces of code marked red?

[wapperdude]

Oh.  I thought you were referencing the original code.  So, no, I did not add the red stuff.

[wapperdude]

Alrighty.  Copied / pasted the red code.  Variable declaration as global, the little IF timer is 1st executable in the macro.  Still doesn't execute properly.  I did tbhis with original code, located in Module1.,,

However, I did put a code break to pause the code before IF statement executed, then let code run, and all is well.  So, the If statement is being recognized.  Perhaps timing needs adjusting.

Seems to me, catching moluse button up event ought to be, perhaps less PC dependent, more reliable/predictable.