User-Defined Excel Functions

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

Anyone know where I can find info on creating user defined
excel fuctions?

Just as a rudimentary example:
---------------------
Public Function Test(ByVal dAmt As Double) As Double

Test = dAmt * 2

End Function
------------------
This returns the "#VALUE!" error message. Never done an
excel UDF, so I've no idea what I'm doing wrong (or even
right).

Thanks in advance,

Sean
 
Sean,

I think by definition a UDF has to be by reference. So I would delete hte
"ByVal" and see if it works.

Regards,
Kevin
 
Thanks for the help, but it doesn't work either (I had
tried it byref, byval, with nothing).

I have tried boilerplate examples from MS, but they return
the same error.

Is there an option or setting I need to adjust? I can find
nothing under Tools--Options.
 
Hi Sean

This works for me.

Option Explicit

Public Function Test(dAmt As Double) As Double

Test = dAmt * 2

End Function



In the spreadsheet, I entered in a cell, =Test(5)

The answer was 10.

So it works as planned.

If you have difficulty, I can send my sample spreadsheet.

Regards,
Kevin
 
GRRR!

I hate it when it turns out to be something silly.

I never put in "Option Explicit"

Thanks Kevin
 
Hi Sean:

While the use of Option Explicit is highly recommended, it should have had
no impact in this case, unless you misspelled a variable.

Regards,

Vasant.
 
Putting it in a general module rather than a worksheet module would be
important. It worked fine in a general/standard module for me (without
using Option Explicit).
 
Back
Top