VBA counts non-empty array elements

Question

Noob question: I want to count non-empty elements of an array?

My attempt:

Dim Arr(1 To 15) As Double
'populating some of the elements of Arr
'...

Dim nonEmptyElements As Integer, i As Integer
nonEmptyElements = 0: i = 0
For i = LBound(Arr) To UBound(Arr)
    If Not Arr(i) = "" Then
        nonEmptyElements = nonEmptyElements + 1
    End If
Next

With this program, I get the error message: Type mismatch on if.

If we try to change the if condition to If Not IsEmpty(Arr(i)) Then, and as a result we get nonEmptyElements = 15.

Any suggestions on how to fill in the code?

Answer 1

    Dim Arr(0 To 15) As Double
    Arr(6) = 1.2
    Arr(3) = 7
    Dim nonEmptyElements As Integer, i As Integer
    nonEmptyElements = 0 : i = 0
    For i = LBound(Arr) To UBound(Arr)
        If Not Arr(i) = 0 Then
            nonEmptyElements = nonEmptyElements + 1
        End If
    Next

The default double value is 0.0, so check:

Arr(i) = 0

Answer 2

Application.CountA(myarray)

CountA is a worksheet function for counting non-empty values.

Applies only to VBA6, does not work in VBA7.