Calling Cell

  • I have a function called afn_CHECK, which basically checks to the total of a column against that of a row. If the totals differ it calls afn_ERROR which tags a comment onto that cell showing the difference. The problem I have is getting hold of the cell that has called the function. Code is as follows: -


    Private Function afn_ERROR(ByVal msg As String, Optional ByVal CellName As Name) As String
    CellName.RefersToRange.ClearComments
    CellName.RefersToRange.AddComment
    CellName.RefersToRange.Comment.Text Text:=msg
    End Function


    Public Function afn_CHECK(v0 As Variant, v1 As Variant) As Variant
    Dim CallingCell As Name
    Dim CellName As String


    CellName = Application.Caller.Name
    If v0 <> v1 Then
    Set CallingCell = ThisWorkbook.Names(RefersTo:="='" & ActiveSheet.Name & "'!" & Right$(CellName, 5))
    Call afn_ERROR("Cross check error expected " & v0 & " found " & v1, CallingCell)
    afn_CHECK = v0
    Else
    afn_CHECK = v0
    End If
    End Function


    The problem seems to be that Application.Caller.Name doesn't return a value in all cases. Any ideas? Any clarification needed let me know.


    Cheers
    Kieran

  • Hi Kieran,


    This revised code worked for me.


    Private Function afn_ERROR(ByVal msg As String, CallCell As Range) As String
    With CallCell
    .ClearComments
    .AddComment
    .Comment.Text Text:=msg
    End With
    End Function


    Public Function afn_CHECK(v0 As Variant, v1 As Variant) As Variant

    Dim rngCallCell As Range

    Set rngCallCell = Range(Application.Caller.Address)

    If v0 <> v1 Then
    Call afn_ERROR("Cross check error expected " & v0 & " found " & v1, rngCallCell)
    Else
    rngCallCell.Comment.Delete
    End If
    afn_CHECK = v0


    Set rngCallCell = Nothing

    End Function


    Assumption that v0 and v1 where the total from row and column. If the values matched the comment will be removed.


    If this doesn't work for you or I mis understood, please post back. Maybe include details of the circumstances that don't work.


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]