or “Stupid Excel Tricks”, Part 1
So, the CPS (programming software) for both the Anytone 878 and Connect Systems radios is atrocious… well.. in my opinion. I’m used to Astro and APX series software, which, at it’s core, is really a database. Everything is referenced and linked.
In the Motorola (P25) world, a “mode” (usually called a “Channel” by the lay-person) is really a name assigned to a link between RF channel or system, Personality (how the radio acts) and some network parameters (Talkgroup, usually). Items such as signaling and encryption are also included in the personality. Programming a zone with a dozen different talkgroups on a single system is often as easy as just selecting the personality and then a talkgroup associated with that personality, and making sure the RF parameters are entered for each of the modes. The nice thing about this zone creation is the fact that each zone is entered in a table – so the entire zone can be viewed easily.
Some older CPS had a more “individual channel” based programming scheme, where each mode was entered in a user form with fields for RX freq, TX freq, RX PL, TX PL, TOT, etc. this was fine for 16 or 32 channel radios, but in the XTS and APX realm, each radio has the capability of hundreds of zone and channel combinations..
As far as Anytone and CS go – they seem to have adopted the “old” paradigm of a window capturing all of the data for a channel in one spot, and then hitting a “next” button to go to the next channel. In MY humble opinion, this is painful, when you consider the amount of data this radio can handle in a codeplug (THOUSANDS of channels).
This is fine if you only have a few channels to enter, but given the number of possible talkgroups, and the potential for someone to travel, this is a MAJOR pain to enter. Now, they DO provide the capability to import and export to CSV or XLS, which is where this really begins.
While it is easy enough to manipulate an excel file to copy things, this still has some drawbacks, given the number of talkgroups, and the fact that there is no simple way to link things together – I will give “their” software that much – talkgroups are entered, and then easily selected (per channel) from a dropdown.
So what to do… In comes excel and VBA. I will admit, knowing that the Astro software is a database, I originally thought about using access for this, but it because very difficult to quickly experiment and try things out, not to mention visualizing all of the channel and talkgroup linking.
So here’s where I started…
First, I want to define a zone. Generally speaking, a zone in the amateur DMR world seems to be (As I’ve said before) a single repeater (physical zone in the area) with the various talkgroups of interest. This isn’t always the case, but GENERALLY.
so… my zone woudl have the following characteristics, when defining:
- A zone Name – something programmed, but not visible on the radio
- the possibility of a zone “tag” – an identifier that shows up as part of the programmed channel names. This is similar to how the XTS radios handled the zone name… for example, the N0ERH repeater is in a zone with “ERH” as the zone tag.
- A Channel name – this may or may not reflect the RF channel or talkgroup… whatever the user wants to call it
- A Talkgroup selection
- an RF Channel selection (like from a pool of frequencies)
- A timeslot selection
- A personality selection
- (additionally, since I have my hotspot set up to work multiple networks, based on a talkgroup prefix, a network and prefix selection)
The image below is what I came up with for a zone definitions:
Note the greyed out cells are either lookups, or calculated.
The overall tool has a LOT of functionality built in that isn’t shown above…. though you can see the fact that the talkgroup is a dropdown menu.
This tool has a number of tabs that are used for calculations and lookups:
- “Zonebuilder” Tab – the data entry page shown above
- Network tabs – individual tabs for each DMR network (Brandmeister, TGIF, etc.)
- Zone tabs – auto-generated, storage for each defined zone
- RF Channels – a channel lookup page. TX/RX Freqs, Color Code, Channel Name, power level and transmit admit selections
- Personalities – a lookup page with all of the radio personality functions…. encryption, TOT, lone worker, emergency signaling, etc.
so obviously, i have a LOT of functionality – I have even added userforms to handle creating the network prefix list for each RF channel, add new RD channels, duplicate zones (useful if you want a zone with the same talkgroup list, but maybe want a different RF channel), and create or delete a zone. There’s another button not shown which will allow the user to export data to an XLS file, which can then be imported to the CPS and uploaded into the radio.
I’m still working through bugs and concepts, and will probably ramble about them here…. one of the bigger challenges is the linking / lookups for large data sets, like a talkgroup – adding all of the Brandmeister talkgroups, for example, to a single string as a formula result makes the lookup too bulky for excel to handle, so using ranges as validation formulas is critical, but I’m still working through this detail.
Stay tuned for more developments on this one….