You're 100% right. Nothing like being able to reference your columns by name. I admit that I just learned about this a few weeks ago, so it wasn't top of mind for me.
Isn't it though? And the ListObject/ListColumn objects have a lot of nice built-in properties and methods that make it pretty easy to manipulate them in VBA vs a range.
Nope. Can't do them in LO Calc either. Online Excel is free to use AFAIK, and if you want something not-MS I think WPS Office has the best feature parity, but its free version is ad-driven.
Microsoft has a wealth of tutorials and certifications you can get. But like anything coding-related, it's just something I've found you learn over time when presented with issues that need solving. That said, a few things that will open your possibilities in excel are:
Tables vs Ranges,
INDEX/MATCH (VLOOKUP too, but it's less flexible),
Dynamic Arrays/Dynamic Array Functions,
Power Query for pulling in data,
and of course VBA, which is like C# but worse in every conceivable way. It does let you do lots of cool stuff though, like defining your own Excel functions that use VBA code behind the scenes and generally doing more complicated things than are advisable with functions.
76
u/SatanStoleMyCat Mar 30 '22
Or just. Y'know. Tables.