Actually, my two objections to VBA functions are that they slow down the spreadsheet, and you have to fire up the VBA editor if you want to see what it does.
The example I gave wasn’t the best, but it was on my mind and was business-related.
Another example would be something I have in a spreadsheet tracking information related to a hobby of mine:
Input is a string that, in its most complex configuration, is in the form of “a/b/c/d-e”.
a, b, c, d, and e are strings of variable length. a, c, and d are 1-3 characters long; b is 3+ characters long; e is 1-2 characters long.
“a/”, “/c”, “/d”, and “-e” are not always present. The input could be “b”, or “a/b”, or “b/c”, or “b/d”, or “b-e” or…
The results of working with this input are either b, or a portion of b consisting of all characters up-to and including the first numeral following the first letter.
I usually play with this data in a SQLite database…but sometimes I find it easier to explore something by dropping the data in Excel. I do this enough that I have a VBA function ready to go – =MyDescriptiveFunctionName(input) produces “b”.
While composing this comment, seeing if I can switch to using LAMBDA for this task has been added to my ever-growing project list.
See my post above about corporate IT being slow to upgrade Office. I think IFERROR was introduced with Office 2012 or Office 2013. Until that version was fully rolled out…
(I’ve been with my current company for over 20 years…although I was hired by company 4a, then transferred to company 4b when it bought 4a, and have since been loaned out to company 4c which was more recently acquired by 4b..)
I’d like to customize the autocomplete capability so that it does not autocomplete something that is consistent with a cell reference. It happens all the time that I am typing in a simple formula and excel pattern matches a cell reference to a function and autocompletes to the function. So annoying.
I created one and it looks like you can only do it at workbook level. You could save it to a template and create any files that might use your custom functions from that template, otherwise you’ll need to recreate it from scratch if you open a new workbook.
You can also copy the sheet to a new workbook and it will bring all the lambdas over from that sheet to the new workbook.
You place a battery of questionable status in between two contacts and that red needle moves to indicate how much juice the battery still has left.
How could I replicate that red needle and arc of ranges as an excel chart? Almost like an old fashioned gasoline gauge on a dashboard. There’s some calculation that ranges basically between 0% and 100% and the needle arc from left to right based on that calculation as an input.
The only way I can think of it to get it is to draw the lines, arcs, and the needle on an XY graph because I am so unclever that 100% of my excel charts are either XY or bar charts.. Is there another graph type in excel that can be modded to look like this?
