Chip Pearson's IsArrayAllDefault function issue...

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
358
Office Version
  1. 2003 or older
Platform
  1. Windows
The function is supposed to return TRUE or FALSE indicating whether all the elements in the array have the default value for the particular data type. Depending on the data type of the array, the default value may be vbNullString, 0, Empty, or Nothing.

But, i am getting a False with, for e.g., vArr(1 to 3) As Variant or with dArr(1 to 3) As Double with Arr(1)=3, Arr(2)=2, Arr(3)=1. Any reason why?

VBA Code:
Public Function IsArrayAllDefault(InputArray As Variant) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsArrayAllEmpty
' Returns True if the array contains all default values for its
' data type:
'   Variable Type           Value
'   -------------           -------------------
'   Variant                 Empty
'   String                  vbNullString
'   Numeric                 0
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Ndx As Long
Dim DefaultValue As Variant

'''''''''''''''''''''''''''''''
' Set the default return value.
'''''''''''''''''''''''''''''''
IsArrayAllDefault = False

'''''''''''''''''''''''''''''''''''
' Ensure InputArray is an array.
'''''''''''''''''''''''''''''''''''
If IsArray(InputArray) = False Then
    IsArrayAllDefault = False
    Exit Function
End If

''''''''''''''''''''''''''''''''''
' Ensure array is allocated. An
' unallocated is considered to be
' all the same type. Return True.
''''''''''''''''''''''''''''''''''
If IsArrayAllocated(Arr:=InputArray) = False Then
    IsArrayAllDefault = True
    Exit Function
End If
  
''''''''''''''''''''''''''''''''''
' Test the type of variable
''''''''''''''''''''''''''''''''''
Select Case VarType(InputArray)
    Case vbArray + vbVariant
        DefaultValue = Empty
    Case vbArray + vbString
        DefaultValue = vbNullString
    Case Is > vbArray
        DefaultValue = 0
End Select
For Ndx = LBound(InputArray) To UBound(InputArray)
    If IsObject(InputArray(Ndx)) Then
        If Not InputArray(Ndx) Is Nothing Then
            Exit Function
        Else
          
        End If
    Else
        If VarType(InputArray(Ndx)) <> vbEmpty Then
            If InputArray(Ndx) <> DefaultValue Then
                Exit Function
            End If
        End If
    End If
Next Ndx

'''''''''''''''''''''''''''''''
' If we make it out of the loop,
' the array is all defaults.
' Return True.
'''''''''''''''''''''''''''''''
IsArrayAllDefault = True


End Function
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Pretty sure it is not Chip Pearson's code. Why don't you post some test code that calls Pearson's routine and returns a result so we can evaluate?
 
Upvote 0
Firstly thank you for the link, your code calls another function that you did not include namely- IsArrayAllocated
You seem to be misinterpreting what the function does.
You are loading your test array with values. This means that the vArr is not filled with empty and dArr is not filled with zero.
This produces a False. To get a true vArr would need to be all empty and dArr would need to be all set to 0.

Rich (BB code):
Sub testAllDefaultFunction()

    Dim vArr(1 To 3) As Variant
    Dim dArr(1 To 3) As Double
    Dim i As Long
   
    For i = 1 To 3
        'vArr(i) = 3 - i + 1    ' This loads the array with a value and therefore will produce a false
        'dArr(i) = 3 - i + 1    ' This loads the array with a value and therefore will produce a false
        vArr(i) = Empty         ' Just not doing anything with vArr would also have resulted in it being empty
        dArr(i) = 0
    Next i
   
    Debug.Print IsArrayAllDefault(vArr), "vArr"
    Debug.Print IsArrayAllDefault(dArr), "dArr"
   
End Sub

Result:
True vArr
True dArr
 
Upvote 0
@Alex Blakenburg; Further, in your test example if vArr(i) = 0 then the output should be false... but with vArr(i) = 0 or vArr(i) = vbNullString the output is still True. Same holds true for dArr(i) = Empty.
 
Upvote 0
@Alex Blakenburg; Further, in your test example if vArr(i) = 0 then the output should be false... but with vArr(i) = 0 or vArr(i) = vbNullString the output is still True. Same holds true for dArr(i) = Empty.
I tested the other scenarios and you are correct. The function seems to be described incorrectly.
vArr will show as True if it is filled with all or a combination of the 3 options given in the remarks, Empty, 0 or vbNullString.
dArr will show as True if it is filled with all or a combination of the 2 options, Empty, or 0 (vbNullString will cause a type mismatch)
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top