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:

Please Login or Register  to view this content.

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