Use it or lose it. I lost nearly it.

It has been more than 4 years since I've done any VBA programming in Excel. I didn't realize how much I'd forgotten. A pro bono project I accepted took much longer than I expected. I had to go back to my manuals for even some basic syntax. It really discouraged me. The project shouldn't have taken more than 15 minutes. It took me no less than 5 hours. But the challenge was so mentally invigorating and enjoyable.

Some gent that is an instructor for a mid-west university apparently teaches what appeared to be French. In his mind, some analytics of language proficiency was the size of words students chose to write in sentences. After placing a paragraph of text in a cell, he wanted a formula that would count only words that were equal to or greater than (=>) 6 characters. I immediately realized that no inherent, intrinsic functions in Excel could accomplish that and even if it were possible, the resulting formula would be unwieldy.

So set about to create a UDF (User-Define Function) using VBA. I finished it last night and shipped it off. The instructor quickly responded that it was PERFECT. (Yes, he used all caps.) It really gave me a sense of accomplishment and being helpful to someone in need. Normally I would have asked for payment, but I was just happy to get my brain cells active again. Using the UDF I created, the user only needed to enter two variables, 1) the cell reference where the phrase was located, and 2) the shortest length of word to check for. It looked like this....

=WORDCOUNT([CellReference], [WordSize])

So, =WORDCOUNT(A1,6) would return the count of all words in cell A1 whose length was 6 characters or longer.

Why am I posting this here? Hey, its the Off-Topic forum and it was an accomplishment I wanted to share.

addendum: I just realized I transcribed words in the title. My brain has been doing funny things lately.

 

@JFB Regular Expressions was intellectually exciting to me. I studied it for a while and loved it. In my lifetime I've learned no less than 3 full-fledged programming languages (I even got certified as an RPG pgmr). I also learned some scripting languages for rudimentary DOS commands. I wrote a file transfer batch file for transferring data between a mainframe and a DOS-based PC.

But now, with all the huge dings on my brain (chemo, sleep apnea, chronic illness), it is amazing I can even remember how to tie my shoes (and sadly, its no exaggeration). I have days where I get lost in the middle of my sentence and completely forget what I was saying. I'm told it ain't Alzheimer's just yet but some days it sure feels like it. Then I have days like yesterday where I had such clarity of mind that allowed me to create a UDF. For most people this would be nothing to boast about. For me, it was truly an accomplishment.

 

@our3rotts One book I've used as a reference because of using quick lessons designed to be absorbed in about 15 minutes each is Read Less Learn More Excel Programming.