EXCEL: Why do I get #NAME? error on a function I've written?-CodePudding

So I have written this function in VBA EXCEL

    Public Function GIVENUM(InputRange As Range, numIndex As Integer) As Integer
    
    Dim RangeNonZeros As Integer
    RangeNonZeros = 0
    
    For Each Cell In InputRange
        If Not (Cell.Value = 0) Then
             RangeNonZeros = RangeNonZeros   1
        End If
        Next Cell
    
    Dim RowNumbers(RangeNonZeros) As Integer
    RowNumbers(0) = 0
    
    Dim Index As Integer
    MinIndex = 0
    
    For Each Cell2 In InputRange
        If Not (Cell2.Value = 0) Then
            RowNumbers(MinIndex) = Cell2.Value
            MinIndex = MinIndex   1
            End If
            Next Cell2
            
    GIVENUM = RowNumbers(numIndex)

    End Function

When I try type the funciton: =giveNum(U1:U9,R1)

In a cell it shows #NAME? error in the cell.

Why is that?

CodePudding user response：

From your code Dim RowNumbers(RangeNonZeros) As Integer is incorrect, RangeNonZeros needs to be constant expression. So try doing the following

Dim RowNumbers() As Integer
ReDim RowNumbers(RangeNonZeros)

You would have already seen the error if you had specified Option Explicit at the top of your module. Also try declaring all variables you use in your code.

CodePudding user response：

as milo5m noted, using Option Explicit as the first line removes potential for many errors, always use it.

revised code that compiles and returns a value under testing. i have added further variable declareations, but Cell and Cell2 could be combined, using Redim Preserve

Option Explicit
Public Function GIVENUM(InputRange As Range, numIndex As Integer) As Integer
    
    Dim RangeNonZeros As Integer, cell As Range, cell2 As Range
    RangeNonZeros = 0
    
    For Each cell In InputRange
        If Not (cell.Value = 0) Then
             RangeNonZeros = RangeNonZeros   1
        End If
    Next cell
    
    ReDim RowNumbers(RangeNonZeros) As Integer
    RowNumbers(0) = 0
    
    Dim Index As Integer, MinIndex As Integer
    MinIndex = 0
    
    For Each cell2 In InputRange
        If Not (cell2.Value = 0) Then
            RowNumbers(MinIndex) = cell2.Value
            MinIndex = MinIndex   1
        End If
    Next cell2
            
    GIVENUM = RowNumbers(numIndex)

End Function