SpecialCells method inside worksheet function?

[BobBridges]

I'm updating a worksheet function for a client, and I've run into a problem. The old version of the function looked for a particular section in the worksheet and extracted a value from it. He wants the new version to search not just the one section but that and the next one too, so I thought I'd "improve" the logic by using a class method that maps the worksheet, section by section, and pull the two sections I want with less trouble.

Instead I'm having trouble. When the mapping method invokes the SpecialCells(xlCellTypeLastCell) method, it returns Cell(1, 1); it should be the last used cell on the worksheet, for example Cell(25, 8). The next statement tries for an Offset value of that cell, and it triggers an error ("Application-defined or object-defined error").

When I call this logic in a Sub, it works fine. Is there something about the SpecialCells method that prevents it from working properly inside a worksheet function? If so, I don't recall knowing it before.

 

[Marc L]

Even if VBA SpecialCells method is somewhat limited and can be tricky the limitation does not come from this method …​

… but from using an UDF (user defined function) which has constraints.​

​

Like you can discover with this thread :​

Sumcolor in VBA gives me invalid argument + it won't load after it's saved and file is reopened

Hello! I'm trying to create a spreadsheet that automatically sums values in cells based on their colors. For example: Red cells should be summed together. Green cells should be summed together. Blue cells should be summed together. Yellow cells should be summed together. I want the sums to...

chandoo.org

 

[BobBridges]

Fine, it has constraints (which I half expected). But what are the constraints? Where do I find them documented?

 

[Marc L]

As usual with a web search, starting with the mother source like​

Description of limitations of custom functions in Excel - Microsoft Support

Lists the functionality limitations of user-defined functions in Excel. These functions are called by a formula in a worksheet cell and cannot change the environment of Excel. You must use the Visual Basic subroutine to change the environment.

support.microsoft.com

​

And to optimize :​

Making your VBA UDFs Efficient

Jeff keeps telling me that it is a hassle to find the posts in my series on writing efficient VBA UDFs. So Here you are Jeff: a headline page. There are currently 22 posts in this series. They sort…

fastexcel.wordpress.com