Detecting equipment U Position on rack

[carbarym]

I've been racking (pun intended) my brain on how to do this and have had no luck. Basically I want to make it so when I place a peice of equipment onto a rack, it will in real time update a shape data property with it's calculate position in RU's on the rack.

From what I've read I don't think this is possible via shapesheet. I tried finding someway of refering to a parent or glued object via shapesheet, I could then easily perform some simple geometric math to calculate the U Position but I couldn't find anything on that reference. I could easily refere to a static rack (Rack.39!) but this would only work on a case to case basis without needing to edit the shapesheet formula.

Now I have resorted to VBA, which I really didn't want to do for simplicity and transportability sake. I was thinking I could use CALLTHIS and a visio macro through the shapesheet of the shape. My only problem is I don't think this will automatically refresh when the object is moved, only when it is dropped.

I'm thinking I will need some kind of VBA code to detect when an object gets glued to the rack and then it will update the U Position property. Could anyone give me insight on which path I should be looking at?

[aledlund]

There are a couple of facets to the issue. The first of course is the recognition that something has been attached, and this is simply handled by intercepting a formula changed event. The good news is that when the event happens the BeginX and other fields get updated to someing similar to this - PAR(PNT('6183_1'!Connections.slot_28_1.X,'6183_1'!Connections.slot_28_1.Y)). The PNT data has the parent shape (6183_1) and connection point in the parent (slot_28_1) identified.
In this case I had modified the connection points to named connections to ease the problem when I wrote the necessary code. That was my resolution to the problem.
The reason I went with named connection points was because in my environment the racks came from numerous vendors for the racks and the associated Visio shapes. Some vendors counted RUs from the top and some counted from the bottom. Some shapes had connection points for the frame inserted before the connection points for the RUs. I manually created custom shapes by editing the connection point for the RUs using the format "slot" as an eye catcher, RU number, and left/right position (1 or 2) . I delimited the connection name with underscores. In my code I analyze the beginx/endx and beginy/endy of the connection to give me the RU. This also gives me the opportunity to make sure the user hasn't dropped the component into the rack skewed (one side higher than the other).

hth,
al

[carbarym]

Thanks for the replies on this and my other post. It turns out I have Parkers book but totally missed over that RU position code! I was one page away from it the whole time haha

The code is very nice but I am trying to consider using it in a different fashion. I'd like for someone to be able to create a rack from scratch and as they drop the equipment onto the rack it will update and list the RU position. I want to minimize the user interaction with buttons, etc.

I'm wondering if I could use a formula like this inside of a cell in the shapesheet of a piece of equipment..

=CALLTHIS(VBAMacroHere) +DEPENDSON(BeginX, BeginY)

I'm pretty sure the macro will be called only when BeginX or Y changes. However with Parker's current code it will scan EVERY shape and update their RU position, seems kind of inefficient. I'm starting to think of creating a function in which it will be called with a variable being the shapes name, kind of like showRU(shapename). I'm just ranting to myself at this point but will post back my results soon

[aledlund]

David's code works because it only has to test against a single known shape (the generic rack), and only tests for one side of the RU. As a point of input you might want to be aware that the format of the 1d cells can have two forms

                  Dim strHeader
                    strHeader = Left(Cell.FormulaU, 19)
                    If strHeader = "PNTX(LOCTOPAR(PNT('" Then
                        intHeader = 19
                    End If
                    strHeader = Left(Cell.FormulaU, 9)
                    If strHeader = "PAR(PNT('" Then intHeader = 9
 
which will alter the displacement into the connection information. The other point of interest is if you begin to start thinking of adding components not just to racks but to computers(servers/workstations) and network gear (routers/switches). The same set of issues arise in determining slots/ports/connections.
hth,
al

[vojo]

so why wouldnt you guys want to do this on format change of the rack

I believe if you added a shape to the rack connections, the format event fires.....this could fire the macro to update shapes involved or the page.

Could use the value in beginX/endX as a pointer into the rack connectors.

Ie

rack connections would be
    connections.1U
    connections.2U
    connections.3U
    etc

Shape1 beginx =  blah blah blah  connections.01U.x        begin y = blah blah blah...connections.01U.y
shape2 beginx = blah blah blah connections.18U.x         begin y = blah blah blah...connections.18U.y

shape1 user.rackslot = <parse beginX to find "01U" charactiers>
shape2 user.rackslot = <parse beginx to find "18U" characters>

Am I missing something here?

[aledlund]

No, you're fine with renaming the connection points using 1u, 2u, etc. The thing to remember is there are two connections for each 'slot' or RU, a left and a right. This brings the point in that you cannot name both connections the same (visio won't allow it), so your plan should include adjusting for it. Additionally you may have a requirement for 'vertical slots' if you plan to allow for vertically mounted power strips (APC has some very nice remote manageable ones for data centers). And as I mentioned above, it is possible for the user to (unintentionally) drop a shape onto the rack skewed where one connector is attached to one RU and the other is attached to a different one (one of the reasons for tracking both sides).
al

[carbarym]

You guys are definitely levels above me in Visio and VBA knowledge so bear with me as I try to keep up. In my test equipment shape I have 6 events, none seem to be a format event unless EventXFMod is the cell I'm looking for, any advice on that one?

[vojo]

if you do VBA then

eventfxmod = callthis(blah blah blah)

If you are trying to do this with shape cells

user.rackslot = blah blah blah + dependson(eventfxmod)

Below is a brief example
- Open shapeseet of the line with the number. 
- Note the endx/endy cells
- note the user.rackslot (this is where you would put the parsing of the endx to get "L2"
- note the test, value, driver cells....this shows you how to use eventxfmod to trigger recalc
(interestingly to all....changing color of the line does NOT trigger event....but changing position of line does....seems odd)
(at any rate, could also trigger off changes in endx or beginx as needed)

[carbarym]

Sigh,

I cannot seem to get CALLTHIS to work at all. I'm performing a simple test to rule out any other variables.

I have a stencil called "Telecommunications" with a copy of the visio "Cable tray/spacer" inside of it. I edited the EventDrop of the master to

CALLTHIS("ThisDocument.A",)

Inside VBA in the ThisDocument (Drawing3) class module I have a very simple sub

Sub A(shpObj As Visio.Shape)

Debug.Print "Hello"

MsgBox "Click OK."

End Sub

I tried this sub in the class module "ThisDocument" in both the Drawing and the stencil with no luck. No message box or debug print, what am I doing wrong? I took the code from the msdn website so you'd think it'd be correct

[Jumpy]

Make a new VBA-Modul, not ThisDocument. Place the Macro there.

I sometimes had trouble with calling macros in ThisDocument, too, because it is a somewhat special modul.

[carbarym]

I tried starting from scratch, new modules, ThisDocument, everything, only to find out you need to have a simple box checked under Tools->Options->Enable Automation Events. Now it works perfectly. I hope this is a option that saves with the document because I plan on sending this out as a template to many people

Here's the code I used incase anyone was curious

On the equipment stencil I have the following in the EventFXMod cell

=CALLTHIS("ThisDocument.A","Telecommunications")

This will fire whenever the shape is moved, resized, changed, etc

My VBA code (still has some test and debug params in it) is

Public Sub A(ByVal shpObj As Visio.Shape)
Dim ruLeft As String
Dim ruRight As String
Dim uLoc As Integer
Dim RU As Integer

ruLeft = shpObj.Cells("BeginX").Formula
ruRight = shpObj.Cells("EndX").Formula
'Debug.Print ruLeft
'Debug.Print ruRight

uLoc = InStr(1, ruLeft, "X")
'Debug.Print uLoc
RU = Right(Left(ruLeft, uLoc + 2), 2)
RU = (RU - 1) / 2
'Debug.Print RU

shpObj.Cells("Prop.RUPosition").Formula = "=""" & RU & """"

End Sub

Basicaly it'll search the BeginX cell for the letter X, which is followed directly by the connection point on the rack. This works flawlessly assuming the rack doesn't go above 50U or the right side of the equipment is not placed at a different RU position

I'm debating on adding the intelligence to compare both sides of the rack and make sure it isn't skewed or adding the abilitiy to check for the alternative 1d cell format. What parameters decide wether or not the cell will use PNTX(LOCTOPAR or PAR(PNT? Ideally I'd like to keep this cost fast and simple and not bloat it up so it takes seconds to run when the user moves the object. I want the users to not even know code is running in the background

I'll try some additions and post my final results

[aledlund]

Enable Automation Events is part of the security setup and is not the normal Visio default. Since you're sending the vba code as well you may have to also go to the Trust Center in Visio to allow macros, etc.
al

[carbarym]

I had those enabled but that other one got me 

I added a little to my code, right now it'll compare the RU's on each side and print in the shape data the RU or if the equipment is placed skewed. I started coding for when a rack is >50U and realized my method probably won't work, it relies on parsing out the rack name ID from the equipment connection string and then determining its height in RUs. My problem is that when the equipment is mounted below 50RU the connection point is 2 digits whereas when it is above 50U the connection point jumps up to 3 (99- vs 100+). I think it may be time to rename the connections like you all mentioned above but this would require recoding everything I've done so far haha

Public Sub ruPos(ByVal shpObj As Visio.Shape)
Dim ruLeft As String
Dim ruRight As String
Dim uLocL As Integer
Dim uLocR As Integer
Dim ruL As Integer
Dim ruR As Integer
Dim rackshp As Visio.Shape
Dim rackstr As String
Dim rackU As Boolean

'Obtain rack connection point references from shape
ruLeft = shpObj.Cells("BeginX").Formula
ruRight = shpObj.Cells("EndX").Formula

'Parse out rack shape ID from equipment connection
rackstr = Right(Left(ruLeft, (InStr(1, ruLeft, "!")) - 1), Len(Left(ruLeft, (InStr(1, ruLeft, "!") - 1))) - (InStr(1, ruLeft, "T(")) - 1)

Set rackshp = Visio.ActivePage.Shapes(rackstr)

'If rack is greater than 50U then set rackU=true
If rackshp.Cells("Prop.UCount").ResultInt(32, visRound) > 50 Then
    rackU = True
Else
    rackU = False
End If

'Determine RU left and right position through connection point by parsing string
uLocL = InStr(1, ruLeft, "X")
uLocR = InStr(1, ruRight, "X")

ruL = (Right(Left(ruLeft, uLocL + 2), 2) - 1) / 2
'ruL = (ruL - 1) / 2
ruR = (Right(Left(ruRight, uLocR + 2), 2) - 2) / 2
'ruR = (ruR - 2) / 2

'Compare left and right RU positions to ensure equipment is not placed skewed on rack
If ruL <> ruR Then
    shpObj.Cells("Prop.RUPosition").Formula = "=""Placement skewed in rack"""
Else
    shpObj.Cells("Prop.RUPosition").Formula = "=""" & ruL & """"
End If

End Sub

[carbarym]

Here is my final code, it encompasses either type of shape connection, 50U+ sized racks and skewed equipment placements in one compact, organized chunk of code. Hopefully this helps others in the future if they stumble upon this. With the new code I didn't need to rename my rack connection points.

Public Sub ruPos(ByVal shpObj As Visio.Shape)
Dim ruLeft As String
Dim ruRight As String
Dim ruL As Integer
Dim ruR As Integer

'Obtain rack connection point references from shape
ruLeft = shpObj.Cells("BeginX").Formula
ruRight = shpObj.Cells("EndX").Formula

'Determine RU left and right position through connection point by parsing string
ruL = (Mid(ruLeft, (InStr(1, ruLeft, "X") + 1), ((InStr(1, ruLeft, ",")) - (InStr(1, ruLeft, "X") + 1))) - 1) / 2
ruR = (Mid(ruRight, (InStr(1, ruRight, "X") + 1), ((InStr(1, ruRight, ",")) - (InStr(1, ruRight, "X") + 1))) - 2) / 2

'Compare left and right RU positions to ensure equipment is not placed skewed on rack
If ruL <> ruR Then
    shpObj.Cells("Prop.RUPosition").Formula = "=""Placement skewed in rack"""
Else
    shpObj.Cells("Prop.RUPosition").Formula = "=""" & ruL & """"
End If

End Sub