To what degree is my VBA Array size function safe, robust, and performant

Question

Coming from a Java/Scala background with a strong focus on OOP+FP, I recently started working with Excel+VBA to implement a front-end for a SaaS. It was like stepping way back in time, over two decades, in software engineering trying to understand how to use and exploit VBA.

In the ensuing 3 months of being immersed in VBA, I have ended up running into and then unique solving a number of problems using a more principled software engineering principled approach. This includes things like DRY (Don't Repeat Yourself which means eschew copy/pasta), DbC (Design by Contract which means define clear boundaries around types and functions), encapsulation, immutability, referential transparency, etc.

The first things I hit had to do with VBA's Array. The code smell of the imperative solutions I found on StackOverflow and the web, in general, was very frustrating. Additionally, it seemed all the proposed solutions I found were not only not DRY (Don't Repeat Yourself) whatsoever, almost all of them seemed to have subtle errors for which they didn't account. After hitting issue after issue with just trying to figure out if an Array was allocated, empty, or defined (non-empty), I finally set about trying to create an optimal solution.

Given my lack of VBA-specific experience (I have a year's worth of VBA 3.0 from back in 1994-1995), I'd like to understand to what degree the solution I am proposing is safe, robust, and performant.

And just as a reminder, I am desiring the critique to be more from a software engineering principles perspective. IOW, I am not focused on novice Excel macro programmers. Or nitpicking about particular VBA syntax and semantics (unless that specifically relates to DRY, DbC, etc.). The intention is to assist future Java/Scala/Python/etc. software engineers who must create and support VBA code bases. Like me.

Feedback is appreciated.

SIDENOTE: In this submission, to keep the discussion clean, I don't plan to discuss my unique VBA code formatting approach. If you are interested in a discussion around that, let me know in the comments below and I will start a separate review submission for that.

---

The main function, f_ua_lngSize, just focuses on obtaining the size. The function can be called on any of the Array's dimensions, and defaults to the first.

Public Const M_UA_SIZE_NOT_ARRAY As Long = -1
Public Const M_UA_SIZE_EMPTY As Long = 0

'Return Value:
'   -1 - Not an Array
'    0 - Empty
'  > 0 - Defined
Public Function f_ua_lngSize( _
    ByRef pr_avarValues As Variant _
  , Optional ByVal pv_lngDimensionOneBased As Long = 1 _
) As Long
  Dim lngSize As Long: lngSize = M_UA_SIZE_NOT_ARRAY 'Default to not an Array
  Dim lngLBound As Long
  Dim lngUBound As Long
  
  On Error GoTo Recovery
  
  If (IsArray(pr_avarValues) = True) Then
    lngSize = M_UA_SIZE_EMPTY 'Move default to Empty
    lngLBound = LBound(pr_avarValues, pv_lngDimensionOneBased)
    lngUBound = UBound(pr_avarValues, pv_lngDimensionOneBased)
    If (lngLBound <= lngUBound) Then
      lngSize = lngUBound - lngLBound + 1 'Non-Empty, so return size
    End If
  End If
  
NormalExit:
  f_ua_lngSize = lngSize
  Exit Function
  
Recovery:
  GoTo NormalExit
End Function

Then I've created two helper functions, f_ua_blnIsEmpty and f_ua_blnIsDefined, which just interpret calls to f_ua_lngSize above.

Public Function f_ua_blnIsEmpty( _
    ByRef pr_avarValues As Variant _
  , Optional ByVal pv_lngDimensionOneBased As Long = 1 _
) As Boolean
  f_ua_blnIsEmpty = f_ua_lngSize(pr_avarValues, pv_lngDimensionOneBased) = 0
End Function

Public Function f_ua_blnIsDefined( _
    ByRef pr_avarValues As Variant _
  , Optional ByVal pv_lngDimensionOneBased As Long = 1 _
) As Boolean
  f_ua_blnIsDefined = f_ua_lngSize(pr_avarValues, pv_lngDimensionOneBased) > 0
End Function

Answer 1

Once you know you have an array it looks to me that this comparison is unecessary:

If (lngLBound <= lngUBound) Then
  lngSize = lngUBound - lngLBound + 1 'Non-Empty, so return size
End If

The reason being that if you have an uninitialized array then

lngLBound = 0
lngUBound = -1

So your equation evaluates to:

lngSize = -1 - 0 + 1

Which means that lngSize = 0

So it's safe to simplify your main function like so:

Public Function f_ua_lngSize( _
    ByRef pr_avarValues As Variant _
  , Optional ByVal pv_lngDimensionOneBased As Long = 1 _
) As Long
  Dim lngSize As Long: lngSize = M_UA_SIZE_NOT_ARRAY 'Default to not an Array
  Dim lngLBound As Long
  Dim lngUBound As Long
  
  On Error GoTo Recovery
  
  If (IsArray(pr_avarValues) = True) Then
    lngLBound = LBound(pr_avarValues, pv_lngDimensionOneBased)
    lngUBound = UBound(pr_avarValues, pv_lngDimensionOneBased)
    lngSize = lngUBound - lngLBound + 1 ' return size
  End If
  
NormalExit:
  f_ua_lngSize = lngSize
  Exit Function
  
Recovery:
  GoTo NormalExit
End Function

This makes M_UA_SIZE_EMPTY unused.

To prove this out I recommend writing a unit test suite that tests all cases you expect to handle. You can have a test for at least your three states listed above the function.

You can use RubberDuckVBA to help you write and run unit tests. It will also give you some of the modern features you are missing in VBA. WARNING: It will tell you about all the style mistakes you are making that you said you don't want to hear about. So be prepared to be amazed and also prepare to have your feelings hurt.

NOTE: I have answered for performant by reducing an instruction. I have answered safe by suggesting that unit testing is your provable measure of safety. As for robustness I think it does what it says on the tin without side effect so it's good.

Answer 2

@Hackslash The assertion of

lngLBound = 0
lngUBound = -1

for an uninitialised array is incorrect. The code below demonstrates why

Public Sub ArrayTypeInfo()

    Dim myUbound As Long
    Dim myUboundMsg As String
    On Error Resume Next
    Debug.Print , , , "TypeName", "VarType", "IsArray", "IsNull", "IsEmpty", "Ubound"
    
    Dim myLongs() As Long
    myUbound = UBound(myLongs)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "Dim myLongs() As Long", , TypeName(myLongs), VarType(myLongs), IsArray(myLongs), IsNull(myLongs), IsEmpty(myLongs), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
'@Demonstrates nicely the dangers of assumption after on error resume next
'@ its not a Ubound error.  You can't assign an array created using array to an array not declared as variant holding array of x.
    myLongs = Array(1&, 2&, 3&, 4&, 5&)
    myUbound = UBound(myLongs)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "myLongs = Array(1&, 2&, 3&, 4&, 5&)", TypeName(myLongs), VarType(myLongs), IsArray(myLongs), IsNull(myLongs), IsEmpty(myLongs), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
    ReDim myLongs(1 To 5)
    myUbound = UBound(myLongs)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "ReDim myLongs(1 To 5)", , TypeName(myLongs), VarType(myLongs), IsArray(myLongs), IsNull(myLongs), IsEmpty(myLongs), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
    Debug.Print
    Dim myArray As Variant
    myUbound = UBound(myArray)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "Dim myArray As Variant", , TypeName(myArray), VarType(myArray), IsArray(myArray), IsNull(myArray), IsEmpty(myArray), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
    myArray = Array()
    myUbound = UBound(myArray)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "myArray = Array()", , TypeName(myArray), VarType(myArray), IsArray(myArray), IsNull(myArray), IsEmpty(myArray), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
    myArray = Array(1, 2, 3, 4, 5)
    myUbound = UBound(myArray)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "myArray = Array(1, 2, 3, 4, 5)", TypeName(myArray), VarType(myArray), IsArray(myArray), IsNull(myArray), IsEmpty(myArray), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
    myArray = myLongs
    myUbound = UBound(myArray)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "myArray = myLongs", , TypeName(myArray), VarType(myArray), IsArray(myArray), IsNull(myArray), IsEmpty(myArray), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
    Debug.Print
    Dim myArrayOfVar() As Variant
    myUbound = UBound(myArrayOfVar)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "Dim myArrayOfVar() As Variant", TypeName(myArrayOfVar), VarType(myArrayOfVar), IsArray(myArrayOfVar), IsNull(myArrayOfVar), IsEmpty(myArrayOfVar), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
    myArrayOfVar = Array()
    myUbound = UBound(myArrayOfVar)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "myArrayOfVar = Array()", , TypeName(myArrayOfVar), VarType(myArrayOfVar), IsArray(myArrayOfVar), IsNull(myArrayOfVar), IsEmpty(myArrayOfVar), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
    myArrayOfVar = Array(1&, 2&, 3&, 4&, 5&)
    myUbound = UBound(myArrayOfVar)
    myUboundMsg = IIf(Err.Number = 0, CStr(myUbound), "Ubound error")
    Debug.Print "myArrayOfVar = Array(1&, 2&, 3&, 4&, 5&)", TypeName(myArrayOfVar), VarType(myArrayOfVar), IsArray(myArrayOfVar), IsNull(myArrayOfVar), IsEmpty(myArrayOfVar), myUboundMsg
    On Error GoTo 0
    On Error Resume Next
    
End Sub

which gives the output

                                          TypeName      VarType       IsArray       IsNull        IsEmpty       Ubound
Dim myLongs() As Long                     Long()         8195         True          False         False         Ubound error
myLongs = Array(1&, 2&, 3&, 4&, 5&)       Long()         8195         True          False         False         Ubound error
ReDim myLongs(1 To 5)                     Long()         8195         True          False         False         5

Dim myArray As Variant                    Empty          0            False         False         True          Ubound error
myArray = Array()                         Variant()      8204         True          False         False         -1
myArray = Array(1, 2, 3, 4, 5)            Variant()      8204         True          False         False         4
myArray = myLongs                         Long()         8195         True          False         False         5

Dim myArrayOfVar() As Variant             Variant()      8204         True          False         False         Ubound error
myArrayOfVar = Array()                    Variant()      8204         True          False         False         -1
myArrayOfVar = Array(1&, 2&, 3&, 4&, 5&)  Variant()      8204         True          False         False         4

It should be noted that if Ubound gives a result of -1 it is also necessary to check Lbound for a valid result as the following definition is perfectly acceptable in VBA

Dim myArray(-10 to -1,-10 to -1, -10 to -1)

Update 24 July2021 - I made a mistake below and forgot about the 0th index so my assertion that when crossing from negative to positive you only need Ubound-Lbound is wrong, Its still Ubound-Lbound+1 and finally you also need to be aware that if Lbound and Ubound have the same sign then the size can be calculated using Ubound-Lbound+1 BUT if they have opposite signs then size is just Ubound-Lbound.

Answer 3

NOTE: Per the CodeReview rules, I am not allowed to modify my original question with suggestions and improvements. And instead, I'm required to post an answer showing any substantial adjustments and/or enhancements. Hence, the post below.

---

UPDATE 2021/Jul/31

Per deeply appreciated additional feedback from @Freeflow, I have updated the code to simplify the naming scheme (ex: removed the Hungarian Notation).

---

Thanks to both comments and feedback by @HackSlash and @FreeFlow, it appears the original overall implementation is correct.

However, in my continuous review, I discovered two things that can be changed to reduce the number of instructions executed leading to probably improved performance. A visual diff between the OP (above) and the new code (below) is here.

Public Const SIZE_NOT_ARRAY As Long = -1
Public Const SIZE_EMPTY As Long = 0

'Return Value:
'   -1 - Not an Array
'    0 - Empty
'  > 0 - Defined
Public Function size( _
    ByVal value As Variant _
  , Optional ByVal dimensionOneBased As Long = 1 _
) As Long
  Dim result As Long: result = SIZE_NOT_ARRAY 'Default to not an Array

  Dim lowerBound As Long
  Dim upperBound As Long
  
  On Error GoTo NormalExit
  
  If (IsArray(value) = True) Then
    result = SIZE_EMPTY 'Move default to Empty
    lowerBound = LBound(value, dimensionOneBased) 'Possibly generates error
    upperBound = UBound(value, dimensionOneBased) 'Possibly generates error
    If (lowerBound < upperBound) Then
      result = upperBound - lowerBound + 1 'Size greater than 1
    Else
      If (lowerBound = upperBound) Then
        result = 1 'Size equal to 1
      End If
    End If
  End If
  
NormalExit:
  size = result
End Function

Public Function isEmpty( _
    ByVal value As Variant _
  , Optional ByVal dimensionOneBased As Long = 1 _
) As Boolean
  isEmpty = size(value, dimensionOneBased) = 0
End Function

Public Function isDefined( _
    ByVal value As Variant _
  , Optional ByVal dimensionOneBased As Long = 1 _
) As Boolean
  isDefined = size(value, dimensionOneBased) > 0
End Function

Answer 4

Your code is still laced with very ugly, impenetrable prefixes and other 'hungation nototation' stuff.. Try comparing the readability of your code with my TryGetSize function

'@Description("Returns the count of items in an iterable")
Public Function TryGetSize(ByVal ipIterable As Variant, Optional ByRef iopResult As ResultLong, Optional ByVal ipRank As Long = 1) As ResultLong

    If iopResult Is Nothing Then Set iopResult = ResultLong.Deb.SetFailure
    Set TryGetSize = iopResult
    
    If Types.Group.IsNotIterable(ipIterable) Then Exit Function
    
    If Types.IsArray(ipIterable) Then
    
        Arrays.TryGetSize ipIterable, ipRank, iopResult
 
    Else
    
        iopResult.SetSuccess ipIterable.Count
        
    End If
     
End Function

Of course for the above code there is a fair bit of backing library code.