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

Question

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

Community · Accepted Answer · 2018-05-08 21:49:10Z

1

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

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Jun 23, 2015 at 16:25

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JSP64 Over a year ago

Perfect, thanks. That's what I get for thinking the Cells function would work intuitively.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related