CODEWITHSUNDEEP
excelvba
Andres Perez
Andres Perez

Reputation: 25

Function returns an error #Value! when I try to use it

What is the reason an error appears when I want to return the value of my function?

Public Function Alphabet_SEF() As Integer
Dim AllAreAlphabetic As Boolean
Dim ReturnVal As Integer
    AllAreAlphabetic = True
    Sheets("BlaBla").Activate
    For i = 1 To Sheets("BlaBla").Range("E1", Range("E1").End(xlDown)).Rows.Count
       If (VarType(Range("E1")) <> 8) Then
          AllAreAlphabetic = False
          Exit For
       End If
    Next
    Sheets("CdM").Activate
    If (AllAreAlphabetic) Then
        ReturnVal = 1
    Else
        ReturnVal = 0
    End If
    Alphabet_SEF = ReturnVal
End Function

When I put in my Excel book "=Alphabet_SEF()" appear #value!

Upvotes: 2

Views: 58

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Try this - it does not rely on BlaBla being active

Public Function Alphabet_SEF() As Boolean
    Dim rng As Range, c As Range

    Application.Volatile 'forces recalculation: use when you have no parameters for
                         '  Excel to use to determine when it needs to be recalculated
    With Sheets("BlaBla")
        Set rng = .Range(.Range("E1"), .Cells(.Rows.Count, "E").End(xlUp))
    End With
    
    For Each c In rng.Cells
       If VarType(c) <> 8 Then
          Alphabet_SEF = False 'set to false and exit function
          Exit Function
       End If
    Next
    
    Alphabet_SEF = True 'if got here then all values are type 8
End Function

Upvotes: 1

Related Questions