VBA count non empty elements of array

Question

Noob question: I want to count the 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: Type mismatch on If statement.

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

Any suggestions on how to complete 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

A double value by default is 0.0, so check if:

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.