CODEWITHSUNDEEP
vbaexceluser-defined-functions
JSP64
JSP64

Reputation: 1462

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in the active sheet when I hit the 'Calculate Now' button, and auto-calculation does not work.

What am I doing wrong?

The full function is below, not sure if it's needed to answer my question.

Option Explicit
Option Base 1
Option Compare Text

Function recordString(ByVal width As Integer, ByVal height As Integer, ByVal firstCell As Range) As String
    Application.Volatile

    Dim tempString As String
    tempString = ""
    Dim i As Integer
    Dim j As Integer
    For i = firstCell.Row To firstCell.Row + height - 1
        For j = firstCell.Column To firstCell.Column + width - 1
            If IsEmpty(Cells(i, j)) Then
                tempString = tempString & "0"
            Else
                tempString = tempString & "1"
            End If
        Next j
    Next i
    recordString = tempString
End Function

Upvotes: 1

Views: 1970

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19767

You need to use Application.Caller.

This will return the value in cell A1 of the sheet the function is entered to:

Public Function DisplayCaller() As String

    DisplayCaller = Application.Caller.Parent.Cells(1, 1)

End Function

This will return the name of the calling sheet:

Public Function DisplayCaller() As String

    DisplayCaller = Application.Caller.Parent.Name

End Function

For more info:
http://www.cpearson.com/excel/sheetref.htm

Upvotes: 1

Related Questions