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).

VBA - Use RAII Design Pattern to tidy your code managing Excel's Status Bar

Summary: Use classes to manage Application state such as Application.StatusBar, Application.DisplayAlerts, Application.ScreenUpdating and Application.EnableEvents.

In this inaugural post we'll start simple with my favourite tip, using RAII design pattern

When working with Excel one will manipulate the Application.StatusBar to convey information to the user.  However, different subroutines may want to convey different information so either they must co-operate with each implementing its own save restore mechanism or alternatively we can use a class.

RAII stands for Resource Acquisition Is Initialization , don't worry it's just a fancy name for a simple technique.  When a class is instantiated its constructor is called and if the reference is stored in a local variable then when the code leaves the scope and is destroyed then the class's destructor is called.

Before we get to the best answer, let's show the less optimal answer.  In the following code, we see that each subroutine implements its own save and restore mechanism to help jointly manage the status bar.  If the status bar is showing the default text, usually "Ready" then capturing this value returns "FALSE".  To reset the status bar we call Application.StatusBar = False.  To set to anything else we just supply the string.  So in the code below the status bar's value is captured upon entering

'Module1.bas
Option Explicit

Sub A()
    Dim vSBSaved As Variant
    vSBSaved = Application.StatusBar

    Application.StatusBar = "Entered A"
    B
    
    
RestoreStatusBar:
    If vSBSaved = "FALSE" Then Application.StatusBar = False Else _
        Application.StatusBar = vSBSaved
    
End Sub

Sub B()
    Dim vSBSaved As Variant
    vSBSaved = Application.StatusBar
    
    Application.StatusBar = "Entered B"
    
RestoreStatusBar:
    If vSBSaved = "FALSE" Then Application.StatusBar = False Else _
        Application.StatusBar = vSBSaved
End Sub

Clearly nobody wants to repeat this code and some of it could be factored out into separate routines.  However, matters get complicated if one starts using Goto such as On Error Goto ErrorHandler and then one will have to jump around to the RestoreStatusBar label.   I'll spare you that bad example, instead let me show you a RAII solution.

'RAIIStatusBar.cls
Option Explicit

Dim vSBSaved As Variant

Sub Class_Initialize()
    vSBSaved = Application.StatusBar
End Sub

Private Sub Class_Terminate()
    If vSBSaved = "FALSE" Then Application.StatusBar = False _
        Else Application.StatusBar = vSBSaved
End Sub

In the above class, we have housed the save and restore code. In Excel VBA instead of constructors and destructors we have the Class_Initialize and Class_Terminate events respectively.  Now it is simple to use this class in your normal code.

'Module2.bas
Option Explicit

Sub A()
    Dim oSB As RAIIStatusBar
    Set oSB = New RAIIStatusBar

    Application.StatusBar = "Entered A"
    B
End Sub '* When oSB goes out of scope status bar is restored to "Ready"

Sub B()
    Dim oSB As RAIIStatusBar
    Set oSB = New RAIIStatusBar
    Application.StatusBar = "Entered B"
End Sub '* When oSB goes out of scope status bar is restored to "Entered A"

Feel free to step through the code so that you understand the sequence of events. This is a powerful technique known to any object orientated programmer working in C++,Java or C#. We can use it in VBA as well.

Can this used for other Application settings? Yes but the Status Bar is a special case because the setting is a string. The other Application states you'd probably handle are simply Booleans.

I give a class now for the Boolean Application.ScreenUpdating and it would be easy to copy across for the other Booleans Application.EnableEvents and Application.DisplayAlerts .

'RAIIScreenUpdating.cls
Option Explicit

Dim bSUSaved As Boolean

Sub Class_Initialize()
    bSUSaved = Application.ScreenUpdating
End Sub

Private Sub Class_Terminate()
    Application.ScreenUpdating = bSUSaved
End Sub

for It is possible to use this pattern to write similar classes for the great many of use cases where a setting has to be saved and restored, such Application.ScreenUpdating, Application.EnableEvents and Application.DisplayAlerts and I give a simple class for ScreenUpdating it would be easy to copy across for the others.