Robert Baer wrote:

Hell, macros seem to be almost unknown, and what functionality exists
is rather limited to say the least.

You haven't looked. I've seen an entire accounting business app,
including JIT ordering, payroll, and payables built on VBA in Excel.
Their program gave no visual hint that it was based on Excel as it had
its own GUI (also programmed in VBA). You can build your own interfaces
for input. When I saw it, I thought "That's based on Excel?" Then I
found out that VBA can make queries to read or write to an SQL database,
so the much faster database could handle faster huge amounts of data
isntead of trying to pile it all into a spreadsheet; i.e., Excel+VBA was
a frontend to the database. This is no different than any other program
that uses a CLI or API to a database. In fact, I've read reports
claiming Excel can become unstable after reaching a 1.5GB dataset size,
so using a database makes a lot of sense for big data.

Such use of embedded Excel is no different than resellers building their
own turnkey systems based on whatever OS, software, and specialty
programs they bundle together with their choice of hardware to provide a
solution to the customer. The customer is buying a washine machine and
doesn't care which brand and model of motor is used inside. With
"programs" based on Excel, the reseller just bundles Excel with their
product. The customer may not even know they have Excel. This is like
other companies that incorporate Quicken into their turnkey program, and
the customer gets everything, including the Quicken license(s).

However, to me, that limits their "program" to just Excel, so if Excel
fades away then so does the demand for their vertical market software.
That was a very long time ago, like maybe 25 years back. Excel is
Windows only, so any turnkey solution built on Excel will also be a
Windows platform only solution. That doesn't concern many users, since
they're already chosen the OS and then look for what to use on it.

I've read where VBA was used to break the passwords on other
spreadsheets. I've seen financial programs, like stock analysis,
written in VBA for Excel. Although you can write VBA to access a
database, sometimes a macro is better positioned in the database instead
of inside of Excel. VBA is an interpreted script language: no compile,
no p-code. VBA is slower than a compiled program, as is any interpreted
scripting language.

You can define Interfaces for multi-stacked projects, and classes that
support run-time enumeration. A project can be designed in 3 layers:
presentation + business + data. THere is no multi-threading in VBA. It
wasn't designed for that environment. However, you can link VBS
programs to run outside of Excel and use signal/semafore to sync the
processes. You can develop C# and Python COM libraries that can be used
and distributed with your Excel-VBA solution.

VBA even has hardware access. For example, it can open or close the
tray of your CD drive.

Declare Sub mciSendStringA Lib "winmm.dll" (ByVal lpstrCommand As String, _
ByVal lpstrReturnString As Any, ByVal uReturnLength As Long, _
ByVal hWndCallback As Long)

Sub OpenCDTray()
mciSendStringA "Set CDAudio Door Open", 0&, 0, 0
End Sub

Sub CloseCDTray()
mciSendStringA "Set CDAudio Door Closed", 0&, 0, 0
End Sub

You can code a VBA script to enter keystrokes, and move the mouse, like
to the taskbar's Start button, click it, and shutdown your PC. Someone
claimed an Excel Team got Excel to control CAD/CAM hardware. You can
use Microsoft's text-to-voice function with a VBA library.

There is no Undo in VBA scripts. If the script deletes data, it's gone
forever. A good VBA programmer will temporarily cache or version the
old data before deleting it.

There is no version control in VBA. However, there's no version control
in any other programming language you use. Version control is something
added atop of managing your code.

VBA is not a standardized programming language. It is a Microsoft-ism.
It's not like you write VBA for anywhere outside of Office apps, and
changes to VBA are at Microsoft's whim.

You can even have VBA make you a sandwich.

Private Sub makeSammich()

Dim iFoot as Long
Dim iMeatBall as Integer
Dim iProvolone as Variant
Dim strSauce as String
Dim wsSlice as Variant

For Each wsSlice in Application.ThisLoaf.Slices
For iFoot = 1 to 12
With wsSlice
.Add iMeatBall
.Add iProvolone
.Add strSauce
End With
Next iFoot
Next wsSlice

End Sub

Okay, I'm just kidding.