Excel custom function does not work across sheets

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.

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.