VBA Function Returns #VALUE!, Works When Ran Via Editor.

[Troy1]

Hello,


I am trying to run the below function, the code itself executes without errors when I manually run it via the VBA editor. When I enter the function into the cell =EXSMSNG() it returns #VALUE!. Any clue what I am doing wrong? I am new to functions.

Function EXSMSNG()

Dim relCell As String


relCell = ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Address(RowAbsolute:=False, ColumnAbsolute:=False)


ActiveCell.Formula = "=IF(COUNTIFS(Sheet2!B:B," & relCell & ",Sheet2!F:F,""N"")=1,""No appointment requested on BOL."",IF(COUNTIFS(Sheet2!B:B," & relCell & ",Sheet2!F:F,""Y"",Sheet2!AC:AC,0)=1,""Operations did not set appointment."",""Missed appointment.""))"




End Function

 

[Marcelo Branco]

A funnction cannot return a formula - take a look at
Writing Your Own Functions In VBA

Where in Simple User Defined Function you can see
"A UDF an only return a value..."

As an alternative you can use Evaluate (observe the double-quotes in red - they are necessary because relCell is a String)

Function EXSMSNG()
Dim relCell As String

relCell = ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

EXSMSNG = Evaluate( _
    "=IF(COUNTIFS(Sheet2!B:B,[COLOR=#ff0000]""[/COLOR]" & relCell & _
        [COLOR=#ff0000]""[/COLOR]",Sheet2!F:F,""N"")=1,""No appointment requested on BOL."",IF(COUNTIFS(Sheet2!B:B,[COLOR=#ff0000]""[/COLOR]" & _
        relCell & "[COLOR=#ff0000]""[/COLOR],Sheet2!F:F,""Y"",Sheet2!AC:AC,0)=1,""Operations did not set appointment."",""Missed appointment.""))")

End Function

Hope this helps

M.