Yes, xlwings (one of the packagess covered by the book, and the one on whose site the page is hosted) absolutely allows this via an Excel add-in: https://docs.xlwings.org/en/stable/addin.html
Those that only use VBA only have themselves to blame.
- lambda and JavaScript, out of the box in latest versions
- any .NET or COM aware language, PowerShell fits the bill, there is even a mini-IDE installed by default
Not every company uses the latest versions of everything. We finally switched everyone to Windows 10 this year. We were paying for Windows 7 support while making the transition.
The main reason VBA is horrible is because it allows dynamic typing by default and Python would not help there at all.
VB syntax is actually nicer than Python syntax in at least 3 aspects: 1) VB is not case sensitive 2) VB allows specifying data types 3) VB uses If Then Else End If style syntax that makes it clear where things start and stop.
If Excel actually had the SQL based query interface inside the spreadsheet, it would make most of VBA unnecessary and probably improve the performance of Excel macros by several orders of magnitude.
VB is not case sensitive, but case insensitivity isn't nice; it reduces clarity and forces circumlocution in cases where having case distinctions would not.
> 2) VB allows specifying data types
Python allows specifying data types, and the major python typecheckers support a more robust type system than VBA.
> 3) VB uses If Then Else End If style syntax that makes it clear where things start and stop
Python uses indentation-based syntax that makes it more immediately visually clear where things start and stop with less visible noise.
The result is that if you declare a variable named "count" anywhere in your project, every property (and every unrelated variable) everywhere gets renamed from "Count" to "count". Makes for annoying diffs.
Also case insensitivity is a terrible idea. Everywhere it is used it causes problems.