Sometimes Excel wants me to out "@" in front of UDF calls

[JenniferMurphy]

I have two UDFs in the same code module called from the same sheet in a .xlsm book. For one, Excel requires an @ sign in front of the function name. Otherwise, it returns a #Value error. For the other, it will run it with or without the @ sign.

Can someone explain?

Thanks

 

[RoryA]

That usually means your function is relying on implicit intersection to determine what to return: Implicit intersection operator: @

 

[JenniferMurphy]

That usually means your function is relying on implicit intersection to determine what to return: Implicit intersection operator: @

Thanks for that link. I did a little experimenting, but I still can't figure it out.

Using this test UDF:

'Test implicit intersection operator (@)
Function testIIO(p1)
testIIO = p1 + 1
End Function

I ran these tests:

I understand why D6 fails, but why does D8?

 

[JenniferMurphy]

Ooops. I forgot to say that ColC is a named range assigned to $C:$C.

Here's a more complete set of examples:

 

[RoryA]

It’s implicit intersection (best avoided IMO) whereby Excel decides that you meant to refer to the item in the same row, not the entire column.

 

[JenniferMurphy]

It’s implicit intersection (best avoided IMO) whereby Excel decides that you meant to refer to the item in the same row, not the entire column.

Right, but why do D8 & D12 fail? They are right out of the examples:

=@INDEX(A1:A10,B1) =@OFFSET(A1:A2,1,1)

 

[RoryA]

The @ needs to be in front of the part that requires implicit intersection. Your UDF doesn’t return an array, so it doesn’t need the @ but implicit intersection is required for the UDF to be passed just one cell, which is the only way it works (as you can’t use +1 on a multi-cell range), so you need the @ in front of the range name.

 

[JenniferMurphy]

The @ needs to be in front of the part that requires implicit intersection. Your UDF doesn’t return an array, so it doesn’t need the @ but implicit intersection is required for the UDF to be passed just one cell, which is the only way it works (as you can’t use +1 on a multi-cell range), so you need the @ in front of the range name.

So the @ on a parameter controls what get passed and the @ on the function name controls what gets returned?

And an extraneous @ is just ignored?

 

[RoryA]

I'm not sure about that last bit, to be honest. I wouldn't recommend just putting @ on everything for example.