I'm an absolute noob at Excel VBA and trying to create a function that checks how many values are bigger then a given value in a column

Function Dontes2(a As Range, b)
    Dim cell As Range
    result = 0
    For Each cell In a
        If cell > b Then
            result = result + 1
    Next cell
Dontes2 = result
    
End Function

here is the code but it keeps giving back 0 as a result when I try to run it in excel

I give the function a range of 35 numbers from 1 to 35 and check how many of them are bigger then 10. I would expect it to be 25 but the function keeps giving back 0.

I'm just starting to learn VBA and the solution might be really obvious. Thanks for any answers in advance.

The code proposed by @VBasic2008 is great but because you are learning I'm going to use your original code:

Firt of all, make sure you indent properly:

> VBA
> indentation

Second, your code should be like this at least:

Function Dontes2(a As Range, b)

Dim cell As Range

result = 0

For Each cell In a
    If cell > b Then result = result + 1
Next cell

Dontes2 = result
    
End Function

The code works perfectly when invoked using cells or even numbers as arguments.

Note the red case returns 0. It's because I'm passing the second argument as a string so because it's not numeric, it returns 0: Check the formulas please:

This is the only way I can obtain 0 as result because your code looks good. Make sure you use the right inputs.

To avoid this, try always to declare all the variables you are going to use, even the arguments of yur Function. So a nice version of your code should look like this:

Function Dontes2(ByVal a As Range, ByVal b As Long) As Long

Dim cell As Range
Dim Result As Long 'no need of result = 0 because all numeric variables are initialized as 0

For Each cell In a
    If cell > b Then Result = Result + 1
Next cell

Dontes2 = Result

End Function

Check this:

> Data type
> summary

Notice how noe even the red case that failed before works properly:

This is because VBA is forcing "10" to be read as a number so the comparison in cell > b Then result = result + 1 works perfectly.

Your output was 0 before because your second argument b was not declared so by default all non declared variables are treated as a Variant type, which can hold almost anything. So "10" was being read as a text, not a number, and then cell > b will always be False

Hope this throws some light

Practicing Loops and If-s: Count Values Greater Than (COUNTIF)

• Always use Option Explicit at the top of the module. It will require you to declare all variables, but it will help you to detect typos and other mistakes at compile time (before run-time).

A Quick Fix

<!-- language: lang-vb -->

Option Explicit

Function Dontes2(ByVal SourceRange As Range, ByVal Value As Double) As Long
    Dim cell As Range, Result As Long
    For Each cell In SourceRange.Cells
        If VarType(cell.Value) = vbDouble Then ' is a number
            If cell.Value > Value Then ' is greater than
                Result = Result + 1
            'Else ' is not greater than; do nothing
            End If
        'Else ' is not a number; do nothing
        End If
    Next cell
    Dontes2 = Result
End Function