Hi there. For the last few months I’ve been working on my main add-in, AET Excel Utilities.
Update
So far I’m in the process of setting up some partners, had a translation offer, and downloads are happening as I write this. Thanks very much to everybody for your help. Don’t be shy if you are interested!
I first started working on it in 2005, as a hobby, and a way to learn VBA. Over time it’s grown from having a handful of very simple tools, to what it is now – well over a hundred utilities (more like over two hundred), and some of them quite complex, even if I do say so myself. Useful? I like to think so. Not a day goes by that I don’t use it, and I can honestly say it saves me lots of time.
But there’s a problem. Even though I like these utilities, I’m not very good at selling myself, letting alone anything I’ve made. And in the world of Excel, most folk have either never heard of me or think I’ve retired if they have. That’s been fine until now, with me plugging away in a corner, tinkering away, but it’s always bothered me that my tools could be so much more.
So, I’d ask all of you for some help. I’m making the tools shareware. And I’m looking for people to help sell them. Do you have a site? If so, are you willing to become a partner or an affiliate? Like I say, I’m not great at sales so any assistance would be appreciated. Translations? Great! Let’s talk about a percentage. I guess the main thing is making people aware of them. Apart from making a bit of pocket money, serious interest will give me incentive to improve them and maybe even try to give my site a bit of an overhaul. (Please contact me using aengwirda [at] gmail.com if you are interested).
Here’s a few screenshots to whet your interest. (Well maybe more than a few…). Look to the left, the AET UTILITIES tab shares both my main utilities and free add-ins (which you can download here).
Worksheet Tools
Rows And Columns
Formula Tools
Deletion Tools
Object Tools
Export Tools
Text Tools
Number Tools
Time And Date
Chart Tools
Path And Folder
Workbook Tools
Developer Tools
Fun And Games
Other Utilities
Cell Menu
Row Menu
Column Menu
Sheet Menu
Here’s the download page link. On the same webpage, you can also download a copy of the Help files for more details on the individual tools, plus the password to see how the code works.
In addition to adding more tools over the next few weeks, I’ll be working on my free utilities too. More details on them, and also some new code samples, that I’m looking forward to posting about in the near future.
I often develop worksheets by putting simple formulas in many separate cells, then when I’m ready to roll out the finished products, I copy and paste the formulas into the cells that refer to them, making (within reason) one big megaformula. But I have to do that by hand. I also have to be careful to avoid missing any parentheses I should have included.
I see that F9 evaluates a partial formula in a cell. Do your utilities contain a tool that can do something similar for me, but with the formula instead?
e.g. I have “=A1+A2” in cell A3, and “=A3*A4” in cell A5. Can I select the “A3” reference in cell A5, and convert it into “(A1+A2)” to make cell A5 read “=(A1+A2)*A4”, making cell A3 redundant so I can clear it?
Do you have a demo version of the AET Utilities add-in for us to try? Or are you hoping that we will sell it for you on the basis of a few screen shots?
Sorry derek, I don’t have something like that, but it is an interesting idea. Just removing the equals sign, then adding brackets is doable. Perhaps add the “partial” formulas from cells into a userform textbox, then copy to the clipboard later? (I’d prefer this way because if something goes amiss, whatever is on the clipboard may be lost)
I can have a think about it :-)
@Derek
Is this what you are after ?
snb,
I like how you do that. I was thinking of something to manually select which cell’s formulas to concatenate, but looping through precedents is interesting.
Hi James,
I guess I can put together a demo version. In the meantime, I can give you something to try if you are interested in becoming an affiliate.
Send me an email and let me know ;-)
Hi Andrew,
I would definately be interested in road testing it. I only endorse products I have personally used.
I couldn’t see an email address for you on your website but mine is jbrown124 at hotmail dot com.
Thanks.
@Andrew
Instead of
use
Avoid interference with ‘reserved terms’ by the use of F_ (for functions), M_ (for macros), n_ (for arguments).
Alas:
@Andrew
Instead of
use
Avoid interference with ‘reserved terms’ by the use of F_ (for functions), M_ (for macros), n_ (for arguments).
James,
Thanks. I’ll get in touch. (Email address is on my About page but I’ll make it easier to find on the main part of the blog itself)
snb,
On my main page? I should update those… at least use rCell instead of cell :-0
Think I wrote them about 10 years ago, my naming conventions have improved since :-)
snb: note that if you use the Precedents collection, you’ll run through all levels of precedents, which might be more than one wants.
So if A1 uses B1 and B1 uses C1, both B1 and C1 are in the Precedents collection for A1.
Hi,
I’m ready to translate this add-in in French. I’ll be able to begin in 2 weeks.
snb, thank you for that, that’s awesome. Unfortunately it doesn’t work for me at the moment: something about the UsedRange.SpecialCells(-4123) is causing “Run-time error 424: Object required”. I’ll look into it later when I have more time.
Also, I don’t want to complain about the free ice cream, it’s delicious–but I was hoping for a tool I could selectively apply to just those formula cells with one and only one dependent formula. A formula cell with two or more dependents could plausibly stay separate, where they can save on calculation time by only calculating once. It’s the ones that will only calculate a single time anyway that I would want to roll up into a big formula.
Derek, please can you contact me – I’d like to discuss your ideas further, with an aim of adding this feature to FormulaDesk. Just click ‘Support’ on the top menu of the FormulaDesk website: http://www.formuladesk.com
Hi Fred,
Send me an email when you have time :-)
Derek, I’ve added the feature to FormulaDesk. To use it: Click ‘Formula Editor’ on the FormulaDesk ribbon menu. In the displayed formula, right-click any referenced range (or named-range, or data-table structured reference). A context-menu will pop-up. If the clicked range reference contains a single cell, the the ‘Merge range into formula’ menu-item will be enabled. Click it to merge. If the referenced range has multiple cells then the menu-item will be disabled. To copy the new formula, copy the text from the top version of the displayed formula. Please let me know if you have any other suggestions. Download it here: http://www.formuladesk.com
Some interesting tools there Gareth!
Andrew: thanks! Your add-in also looks amazing, and very comprehensive. I can’t find a trial download. Is one available?
Gareth, I’m in the process of making one. I’ll add it to the top of this post, and post about it on my own site also, when it’s finished.
Gareth, sorry I’m so slow, work is crazy. I saw your first message, downloaded FormulaDesk to look at it and it looked good (I particulary liked the facility to split out megaformulae into cells for debugging) but didn’t get round to telling you so. I will download the latest version to see what you’ve done to glom multicell formula cascades (for development) into compiled megaformulae (for release).
As I said, I’m particularly interested in targeting those formulae with one and only one descendent, as formulae with multiple descendents should arguably stay as they are.
Derek, I’m interested to know if I hit the mark with regards to your request ;-) If you have any other wishes or requests, no matter how crazy, please let me know (I specialize in crazy…)