Adding/Deleting Shapes

So I've got an electrical apparatus (switchgear) where there are many switches and circuit breakers. There is logic associated with each switch, but I'm trying to get just the basics working for now. I have 2 buttons assigned to their respective macros. The first button is supposed to make a switch line move from 1 position to another (3 positions). When the line moves, any other line that was added should be removed so that only 1 line exists at a time. The second button is the same as the first except that the switch only has 2 positions. The 2-position switch seems to work ok. The only glitches there seems like I need to declare an initial state, otherwise I get an error on the first execution, and the other glitch is allowing duplicate lines to be created which doesn't delete the original after executing a new position. The same glitches also exist for the first switch with the line addition/deletion not working as expected.

Where am I going wrong here?

Many thanks in advance.

Shannon

Hi there,

As someone who spent over 30 years designing and commissioning high-voltage substations, the content of your workbook interested me more than somewhat!

I haven't looked at your workbook or code in anything but a superficial way, but my initial thought is that your approach could probably be simplified considerably. For example, instead of creating and deleting shapes (lines) to represent the switchgear action, you could simply draw both shapes and then hide/display whichever one is appropriate. You could probably create a table which correlates the "Open/Closed" cells with their associated shapes, and this should allow you to take a "parameterised" (wonderful word! ) approach rather than having to write separate Open/Close routines for each piece of switchgear involved.

Let me know if you think this type of approach might be suitable and we can talk about it some more.

You mention three-position switches - are these disconnector/grounding switches?

Regards,

Greg M

Hi again,

Attached is a simplified workbook showing the approach I mentioned above. The useful aspect of it is that it uses only a single routine for opening/closing a switchgear item, regardless of how many such items the worksheet contains. The code is as follows:

Option Explicit


Sub OperateSwitch()

    Const sBUTTON_PREFIX    As String = "btn"
    Const sSHAPE_PREFIX     As String = "shp"

    Const lGREEN            As Long = 32768
    Const lRED              As Long = 255

    Const iROTATION         As Integer = -30
    Const sCLOSE            As String = "Close"
    Const sOPEN             As String = "Open"
    Const iLEFT             As Integer = 5
    Const iTOP              As Integer = -2

    Dim sSwitchName         As String
    Dim rSwitchCell         As Range
    Dim rDataTable          As Range
    Dim shpButton           As Shape
    Dim shpSwitch           As Shape
    Dim rState              As Range
    Dim sState              As String
    Dim iRowNo              As Integer

    Set rDataTable = ActiveSheet.Range("ptrDataTable")

'   Identify the button which called this routine - this in turn will identify
'   which switch to operate
    Set shpButton = ActiveSheet.Shapes(Application.Caller)

'   Determine the name of the associated switch shape
    sSwitchName = Replace(Application.Caller, sBUTTON_PREFIX, vbNullString)

'   Locate the above switch in the data table
    With rDataTable.Columns(1)

        Set rSwitchCell = Nothing
        Set rSwitchCell = .Cells.Find(What:=sSwitchName, _
                                      LookIn:=xlValues, LookAt:=xlWhole)

    End With

    If Not rSwitchCell Is Nothing Then

'         Create a reference to the required switch shape
          Set shpSwitch = ActiveSheet.Shapes(sSHAPE_PREFIX & sSwitchName)

'         Determine the next state of the switch - i.e. Open if the swicth is
'         currently closed, and Closed if the switch is currently open
          Set rState = rSwitchCell.Offset(0, 1)
              sState = rState.Value

'         Change the state of the required switch and the colour of the associated button
          With shpSwitch

              If sState = sOPEN Then

                    .IncrementLeft -iLEFT
                    .IncrementTop -iTOP
                    .Rotation = iROTATION
                    rState.Value = sCLOSE

                    shpButton.TextFrame.Characters.Font.Color = lGREEN

              Else: .IncrementLeft iLEFT
                    .IncrementTop iTOP
                    .Rotation = 0
                    rState.Value = sOPEN

                    shpButton.TextFrame.Characters.Font.Color = lRED

              End If

          End With

    Else: MsgBox "The table does not contain data for switch " & sSwitchName

    End If

End Sub

Hope this helps.

Regards,

Greg M

It doesn't look like my reply went through earlier, so please excuse if there is a duplicate...

Thank you Greg! The solution you suggested is along the lines of what I'm trying to accomplish with a couple of caveats... all of the switches in your worksheet are 2-way, and I'm having trouble with the 3-ways. Also, I've tried working inside of the workbook you shared, but I can't seem to figure out how to copy or make changes to the existing switches without causing errors in the code... For example, my setup doesn't have a switch named "F72Q2G". If I attempt to change the name of this to "F72Q1G", I get errors. If I copy a line, switch, and button, and attempt to rename them using the table, the button name changes, but the control still operates the copied switch, not the new. I've tried changing the switch name but I'm just not getting anywhere.

Yes, these are disconnector/grounding switches. I wasn't expecting to get a response from anyone who has experience in the electrical world -- very nice surprise! I'm a Sr. Field Service Eng. with about 21yrs. under my belt, so not quite as extensive as your background. I do enjoy wearing many hats from designing, testing, commissioning, and training, mostly in the protection and automation relaying; not so much with apparatus. What's the industry like in Ireland? Are you investing a lot in renewables these days? I don't imagine solar is very reliable, but perhaps wind? Maybe off-shore wind?

Thank you again for your insight and support!

Best Regards,

Shannon

Hi again Shannon,

Thanks for your feedback.

The following refers only to two-position switches - we can deal with three-position switches later.

To create a new switch called (e.g.) F99Q1:

Put switch F71Q1 into the closed position

Select the three "elements" (line shapes) of switch F71Q1 and also the button for operating switch F71Q1 to the clipboard

Paste the clipboard contents to wherever you wish to position your new switch

Rename the line shape which represents the main contact of your new switch from "shpF71Q1" to "shpF99Q1"

Rename the button associated with the new switch from "btnF71Q1" to "btnF99Q1"

Enter "F99Q1" on the next available row (e.g. row 10) in Column A of the data table

Enter "Open" (i.e. the Next State of the new switch) on the next available row in Column B of the data table

Copy the formula in Column C of the data table into the cell associated with the new switch - it should evaluate to "F99Q1 Open"

Select the button associated with the new switch - the formula displayed in the formula bar should read "=$D$4"

Change the "4" in the above formula to "10" (i.e. the row number associated with the data for the new switch) - the text displayed on the button for the new switch should read "F99Q1 Open"

As far as the disconnector/grounding switches are concerned, can I assume that the contact will be in the Vertical/Angled position to represent the Closed/Open states, and in the Angled/Horizontal position to represent the Open/Grounded states?

Also for the disconnector/grounding switches, do you want two buttons (one to toggle between the Open/Close states and the other to toggle between the Open/Grounded states), or do you want one button which moves the switch in the sequence Closed > Open > Grounded > Open > Closed? If two buttons are required, it will be necessary to enable/disable where appropriate to prevent the switches moving directly from the Closed state to the Grounded state and vice versa.


Ok on your experience in the area of electrical substations - I've dealt with most aspects, but my main area of expertise was control, interlocking and signalling. Yes, wind turbines are becoming more and more extensive over here, and as a country which has relatively few natural resources for energy generation, the interest in renewable sources is increasing all the time.


I'm in Dublin; whereabouts in the US are you located?


Keep me posted.

Regards,

Greg M

Thanks again Greg! Your assumptions are correct -- vertical/angled for Closed/Open, and angled/horizontal for Open/Grounded. This scheme gets even more complicated when I pull in the interlocks with the HV side... not sure if this is the best software for the application, but without having a budget to develop anything else, I figured I'd give it a go. Though I'm sure you can guess by now that my expertise in VBa does not extend to this depth of operation!

I'll attach another copy of the same sheet we've been discussing to show the overall layout including the HV system. The switches may be a bit difficult to see on the black lines, but there are essentially a 3-way on each side of each breaker in the ring bus, a 3-way from the ring bus to each feeder (3 transformers and 2 lines), and a 2-way high-speed ground switch just above those 3-ways to ground the high-side of the transformers or ends of line. That should make it about as clear as mud, but I think you'll get the picture :-)

I'm for whatever works best, using the least amount of coding. So if one button transitioning through the 3 states Closed - Open - Grounded - Open - Closed works best, then let's try that avenue. If integrating permissive logic is easier into separate buttons, then let's try that. I truly have no preference either way without understanding what's involved to develop each.

My wife and I just sold the farm, so to speak, so we're travelling nomads at the moment! We're originally from the South (SC to be exact) but have lived in Georgia and Southern California. We recently purchased an incredibly nice 5th wheel along with a new 1 ton truck to pull it with! So I'm a happy camper (pun intended) -- it's not as easy to keep the wife onboard with the notion, but she's being a really good sport so far (however, full disclosure: we've been hiding in the Caymans for work since 2 months after we got the rig LOL).

My in-laws used to visit Ballymena quite regularly -- my father-in-law retired from Michelin. The photos from their trips along with all the ancestry research my wife's found of our lineages there makes me want to visit badly. Eventually, we'll get there -- maybe for a project! That would be fun, and I'd definitely reach out to have a pint (or 2) with you, if you're up for it.

Thanks again for all your help! I've been wanting to learn more about this part of Excel for a while.

Best Regards,

Shannon