VBA - How to return the number of dimensions of a Variant array

Asking how to return the number of dimensions of a Variant array passed to it in VBA is a classic Excel VBA Developer interview question.  The simple obvious answer is to use On Error Resume Next whilst interrogating the array bounds for an increasing dimension.  A more complex answer is to copy over the array internal array descriptor using operating system level memory utility function.  This choice can broaden the question into a more essay question on programming style.  

This question has been asked on StackOverflow probably multiple times but a good representative question is this one  SO how-to-return-the-number-of-dimensions-of-a-variant-variable-passed-to-it-in-v.

The On Error Resume Next (OERN) Approach

So is the obvious On Error Resume Next (hereafter OERN) good enough and why would you not want to use it?  Well, one colleague once described OERN as the 'heroine of VBA coding' and I suppose it certainly is a painkiller and thus addictive.  I would prefer to steer clear of OERN where possible and I will explain why but first we must remind ourselves of some VBA development environment features.

In the Excel IDE, selecting from the main menu Tools->Options->General gets you to this screen

Looking at the Error Trapping frame we can see the radio button that controls error trapping behaviour.   I typically operate 98% of the time on Break on Unhandled Errors, circled in blue.  However, sometimes when chasing down a bug it is extremely useful to switch over to the Break on All Errors and stop dead on a problematic line.

If your code base has a ton of code using OERN to take away the pain then selecting Break on All Errors will stop you dead on every one of those lines.  For this reason I, as far as I can, I try to remove any errors and banish OERN.

If removing all OERN is not possible then you can always (i) use breakpoints to halt execution and switch the setting over when approaching problem area (ii) isolate the OERN code in a separate project and lock for viewing (VBA won't break on code that is locked).

The Memory Copy Of Array Descriptor Approach

Some other answers on the SO thread describe a different approach that takes advantage of knowledge about an arrays internal representation.  C++ programmers who program for Excel and who use datatypes compatible with VBA will tell you that a VBA array is in fact a C++ SAFEARRAY.

Here's a copy of Microsoft documentation describing the SAFEARRAY structure at https://msdn.microsoft.com/en-us/library/windows/desktop/ms221482(v=vs.85).aspx

C++
typedef struct tagSAFEARRAY {
  USHORT         cDims;
  USHORT         fFeatures;
  ULONG          cbElements;
  ULONG          cLocks;
  PVOID          pvData;
  SAFEARRAYBOUND rgsabound[1];
} SAFEARRAY, *LPSAFEARRAY;

The number of dimensions is stored in cDims.   To get the memory address of this structure one uses VarPtr and then to copy the structure over to a variable, either you can copy a portion of the structure to a Long as the SO answers do or you can copy the entire structure as this very good DevX example demonstrates.

Hiding the pain in a Compiled DLL

One final approach is to bury the code in a compiled DLL, be that a VB6, C++, or .NET dll.  One can use On Error Resume Next or its equivalent in those languages to hide the pain.

EDIT: In a later post I have actually deposited some VBA code, Get Array Dimensions and Bounds (REDUX).