When I try to use a VBA function that I've defined in an Excel spreadsheet, I get "That is not a valid function"

Question

I defined a function in VBA as follows

Public Fuction Distance(X, Y) as Double

Then in a cell i try to use the fuction. I type "@Dis" and get the drop down menu, select the function and then enter the parameters ending up with @Distance(A1, A2) in the cell.

When I press Enter I get the Error "That function is not valid".

I enabled macros throughout the system, tried saving it as the old format and as the macro enabled workbook format to no avail.

What's wrong with my usage of this function?

Answer 1

Try using:

=Distance(A1, A2)

Instead of

@Distance(A1, A2)

I've never seen @ as the correct character to call a function in Excel.

I tried the following in Excel, and it works like a charm:

In Module1:

Public Fuction Distance(X as Double, Y as Double) as Double
    Distance = 10
End Function

In a cell:

=Distance(A1, A2)

Which produces the result:

10

as expected.