Excel custom function does not work across sheets

Question

I'm quite new to writing excel custom functions, and I have encountered a strange behaviour that I can't seem to find explained when searching the net (although the amount of info is vast of course). Please bear with me.

Here is a demo which shows at least some of the problem:

Function mycustomfn_demo(rng As Range)

 Dim rngrows, rngcols, curcol, currow, i, j,firstcol As Integer
 Dim exitflag As Boolean

 firstcol = -1

 rngrows = rng.Rows.Count
 rngcols = rng.Columns.Count
 exitflag = False

 For i = 1 To rngcols

 For j = 1 To rngrows

   curcol = rng.Column + i - 1
   currow = rng.Row + j - 1

   If Cells(currow, curcol).Value <> "" Then
    firstcol = i
    exitflag = True
    Exit For
   End If

 Next j

 If exitflag = True Then
  Exit For
 End If

 Next i

 mycustomfn_demo = firstcol


End Function

This function finds the first column in a range with a nonblank cell (no nonblank cells in range gives a result of -1) and works fine afaics as long as the range rng is on the same sheet as the formula containing the mycustomfn_demo function. Here is Sheet 1 containing both the formula and the range:

However, if they are on different sheets, strange things happen This shows Sheet 2 (range still in Sheet 1):

In this case (but not in others) referencing the formula cell in Sheet 1 gives the correct result (again, Sheet 2):

Is this expected behaviour, or is it the result of a bug? I am using Office 2016 for Mac under OSX High Sierra 10.13.5 and the Excel version is 15.23.

I should add that in a more complex situation, referencing a custom formula result from another sheet erases the result from the formula cell itself. It can then be restored by deleting that cell and then choosing Undo.

Answer 1

The problem is with the following,

If Cells(currow, curcol).Value <> "" Then

Cells has no parent worksheet reference so although you are passing a row number and column number from a cell reference on Sheet1, it is just using those numbers to find a cell on the active sheet.

Add a reference to rng's parent worksheet.

with rng.parent

 For i = 1 To rngcols

 For j = 1 To rngrows

   curcol = rng.Column + i - 1
   currow = rng.Row + j - 1

   If .Cells(currow, curcol).Value <> "" Then
    firstcol = i
    exitflag = True
    Exit For
   End If

 Next j

 If exitflag = True Then
  Exit For
 End If

 Next i

end with

Note that Cells becomes .Cells to catch the parent worksheet reference in the With ... End With block.

With just a single reference that could have easily been rng.parent.Cells(currow, curcol).Value but the With ... End With block is more thorough for expanding calls to other cells on rng's worksheet.