CODEWITHSUNDEEP
vbaexcel
dezimar
dezimar

Reputation: 45

VBA check if array has a value on certain position

I have an array called alterArray() which gets filled automatically with values out of outlook (which works fine). The array has a maximum of 6 values, but sometimes the array has just 3 values (so alterArray(0-2) is filled with values but alterArray(3-5) is empty).

How can I check, if, for example, alterArray(5) has a value?

Thank you very much!

Upvotes: 0

Views: 195

Answers (2)

DisplayName
DisplayName

Reputation: 13386

to get its actual dimension you could use a helper function:

Function GetActualDimension(arr As Variant) As Long
    Dim i As Long
    If IsEmpty(arr) Then Exit Function
    For i = LBound(arr) To UBound(arr)
        If IsEmpty(arr(i)) Then Exit For
    Next
    GetActualDimension = i - 1
End Function

that you could exploit in your main code as follows:

Sub main()
    Dim alterArray As Variant

    MsgBox GetActualDimension(alterArray) '-> returns zero
    alterArray = Application.Transpose(Range("A1:A4").Value) ' fill your array
    MsgBox GetActualDimension(alterArray) '-> returns "actual" size
End Sub

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96791

Consider:

Sub HowEmptyIsIt()
    Dim alterArray(0 To 5) As Variant
    alterArray(0) = "X"
    MsgBox IsEmpty(alterArray(5))
End Sub

enter image description here

Upvotes: 3

Related Questions