CODEWITHSUNDEEP
vbaexcel
JackTheFruit
JackTheFruit

Reputation: 110

SpecialCell(xlCellTypeLastCell) does not work in custom function in Excel

The custom function below entered in a cell with a reference to another cell does not work. I do not understand why. Does anybody know the answer?

Public Function SpecialCellBroken(rng As Range)
Debug.Print rng.Worksheet.Cells.Address
Debug.Print rng.Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Address

SpecialCellBroken = rng.Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Address
End Function

Return in debug window:

$1:$1048576
$1:$1048576



However, the following Sub works fine and returns the address of the last cell:

Sub SpecialCellWorksFine()
    Debug.Print Selection.Worksheet.Cells.Address
    Debug.Print Selection.Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Address

    Selection.Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Select
End Sub

Return in debug window:

$1:$1048576
$J$21

Also referencing from the range to the worksheet with Parent does not work. Tried ByVal on the parameter rng. Also no effect to the better. Any suggestions what is the issue here? Thank you.

Upvotes: 2

Views: 678

Answers (1)

Mr.Burns
Mr.Burns

Reputation: 700

.SpecialCells(xlCellTypeLastCell) Wont work in a UDF as UDFs cannot interact with Excel (Or the program you are using at that time). What you can do though is:

Sub SpecialCellWorksFine()
x = Selection.Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Address(False, False)

n = y(x)

End Sub

Public Function y(x As String)
'Code goes here
End Function

This has been simplified but the principal is the same, you assign what values you want to go into the UDF before you call it and then pull the ones you want into the UDF (The limit I believe is 30)

Upvotes: 2

Related Questions