Is defined array


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
'**********************************************************************************************************
' Name:         isDefinedArray
' Author:       mielk | 2012-03-27
'
' Description:  Function to check if the given parameter is an array with dimensions and sizes already
'               declared.
'
' Parameters:
'   arr         Parameter to be tested.
'
' Returns:
'   Boolean     True - if parameter [arr] is an array and its dimensions and sizes has been already
'                      defined.
'               False - if parameter [arr] is not an array or it is declared as a dynamic array but its
'                      dimension and size have not been defined yet.
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-03-27        mielk       Function created.
' 2014-08-10        mielk       Case about arrays with negative up bound instead of being not declared.
'**********************************************************************************************************
Public Function isDefinedArray(arr As Variant) As Boolean
    Const METHOD_NAME As String = "isDefinedArray"
    '------------------------------------------------------------------------------------------------------
    Dim upBound As Long
    Dim lowBound As Long
    '------------------------------------------------------------------------------------------------------


    'Try to assign bottom and top bound of the given parameter.
    'If it is not an array or is not declared yet, code will move to
    'the label NotArrayException and function will return False.
    On Error GoTo NotArrayException
    upBound = UBound(arr, 1)
    lowBound = LBound(arr, 1)

    'In some cases, it is possible to get LBound and UBound of a dynamic array
    'althought it is not declared yet (i.e. arrays returned as a result of VBA
    'built-in Split function if empty string is passed as a parameter),
    'however in this case UBound will be lower than LBound.
    isDefinedArray = (upBound >= lowBound)


'==========================================================================================================
ExitPoint:
    Exit Function

'----------------------------------------------------------------------------------------------------------
NotArrayException:
    GoTo ExitPoint

End Function