Forum Discussion
Is now the time to abandon the concept of relative referencing?
Thank you for taking the time to read my reply and respond.
I do believe the introduction of dynamic arrays marked a turning point in the history of Excel - a major change in the calculation engine for the better. I can only speak for myself. Colleagues of mine that have used Excel since the 80s and 90 have not completely bought into Excel-based-on-arrays for lack of time to learn new skills, being content in one's current skill set, not being interested, etc. - whatever the reason may be. I still hear of columns being re-arranged to accommodate the limits of a base VLOOKUP, Advanced Filter being used instead of UNIQUE, helper columns used instead of LET, and plenty of other examples. The awareness of functions like FILTER, XMATCH, LET, CHOOSECOLS, CHOOSEROWS, TAKE, DROP is close to 0.
Clients are usually very open to dynamic arrays. Lambdas are usually received very well. Though I still have to talk people off the VBA ledge. There's a certain mindset that if someone doesn't know how to do it in the application that VBA must be the answer. I think anyone beginning their studies of Excel in 2024 would be wise to find a course specifically geared towards Excel 365 and stay away from macros!
"a course specifically geared towards Excel 365"
Have you identified such a course? In some ways that is what this discussion was intended to cover. It is always possible to bolt Dynamic Arrays, LET and LAMBDA on to an existing course as 'new', 'advanced' topics. Then one might need to debate whether Lambda should wait until Power Query and Pivot Tables have been covered.
What I would argue for is that DA, LET and LAMBDA should not be left to the final steps of training as advanced topics but, rather, treated up front as the basis of modern Excel; it is traditional spreadsheet techniques that should be left to the end for those who need to address issues of backward compatibility.
I'm mostly an auto-didactic when it comes to Excel. I learn through creating challenges for myself and have learned from participating in this community.
I haven't actively searched for decent Excel 365 courses online so it's difficult to recommend one. I've seen some videos from some of the popular instructors and there's usually something that puts me off about their methods. Mostly it's the lack of LET being taught. Someone had linked a VSTACK video on this forum a few weeks ago. The instructor was stepping viewers through a 3D stack. The finished formula contained explicit references repeated a few times and no use of LET. To top it off, he was charging a few dollars for the template with the formula!What I would argue for is that DA, LET and LAMBDA should not be left to the final steps of training as advanced topics but, rather, treated up front as the basis of modern Excel; it is traditional spreadsheet techniques that should be left to the end for those who need to address issues of backward compatibility.
I agree with DA and I've come around on LET. In the 40 hour Excel 365 introduction course I teach within my organization I debated including LET and had left it out the last time I ran the course. Next time I teach it I'm going to include it.
Patrick2788 I vaguely remember seeing something in the Excel 2003 menus referring to "Name Labels" but didn't know what it meant or how to use it, as I was still pretty green at the time. I wonder how dynamic that feature actually was... did it auto-expand the range references as the dataset increased? If so, then yes, that seems like a great feature for Microsoft to revamp/revisit in MS365. Current features like "Create from Selection" can be used to quickly define column names, for example, based on values in the top row, but the resulting range references are static, making it a useless feature for dynamic arrays.
Just out of curiosity, and in the spirit of additional discussion, what is the main source of your aversion to VBA? Is it primarily security concerns? I get it... macro-enabled workbooks can contain malicious code, and bad actors can use macro viruses to inject malicious code into existing documents. However, malware and viruses like these don't just appear on their own. They are typically "let in" unwittingly by the end user via phishing emails, network shares, downloading files from untrusted sources, etc. Much of the risk can be minimized by adopting secure network protocols (ie: firewalls, client endpoint protection, etc.) and safe document handling practices. If the general recommendation is to avoid VBA at all costs because of the security risks, one might argue that you should also stop using email altogether, and/or stop browsing the internet (go completely off grid), as the same risks apply to non-VBA specific malware and viruses.
Personally, I don't view VBA as a crutch to lean on, but rather as an enhancement tool if the situation calls for it. Newer tools such as Power Query and Power Automate may be more secure and more appropriate in certain situations, but from what I've read/experienced, can be limited in their scope and availability. VBA is a far more versatile and flexible option than the current alternatives. I'd be hard pressed to recreate many of my macro-enabled workbooks with the same level of automation and efficiency via alternative tools, if Microsoft were to remove VBA as an option in the future (and I don't think I'm alone in that respect... I think it would cause more of an uproar than making dynamic arrays the "default" behavior, lol).
Lastly, on the topic of self-education, some of the YouTube channels I would recommend to anyone stumbling across this discussion include ExcelIsFun (Mike Girvin), Leila Gharani (xelplus), MyOnlineTrainingHub (Mynda Treacy) and Excel Macro Mastery (Paul Kelly). There a plenty of other great channels out there, but these ones are at the top of my list for the quality of the content they put out. Cheers!
"Just out of curiosity, and in the spirit of additional discussion, what is the main source of your aversion to VBA?"
I realise the question wasn't addressed to me but some thoughts nonetheless.
If possible, I stay within the Excel formulas functional programming environment. Heterogeneous programming environments can provide solutions, but I would only adopt such if there were no good alternative available. The same goes for PowerQuery. I would use it to access data from external sources and to clean the data before loading to Excel. From that point on, I try to stay within the formula environment, even to preferring PIVOTBY over a 'proper pivot table'.
Returning to VBA, I do use it despite my being painfully slow at writing code. An example where I needed both event handlers and shape properties is to be found at
2048 game version for Excel (spreadsheet1.com)
The enhancement to the code made by Douglas Bliss is pretty good too.
Of course, if you are happy to have your mid truly blown, there is always Craig's site!
Beyond Excel - VBA Coding (google.com)
I can't even find his central error checking procedures that I once used but it is something of an eye-opener to see what happens to Excel when an IT professional sets out to deliver efficient and robust solutions.
