Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Different Parameters in Excel UDF

  Asked By: Bill    Date: Sep 20    Category: MS Office    Views: 1113
  

I got
a problem with my Excel UDF. I will just give an overview or summary
of my problem because I felt that maybe you guys can "get" me
immediately.

I created a function that accepts two parameters, one is a String and
the other one is a Double. This is the format:

Function tax(status As String, income As Currency)
'some code
End Function

This function must return tax in Double or Currency data type.

We all know that UDF errors are hard to debug or that there may be no
error messages that will pop out if there are any. Most UDF samples
posted in the web have samples with parameters of the same data type.
I just felt that having two or many parameters of different data
types could be a factor. I hope I am wrong with this one.

Would you help me guys?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Nahal Malik     Answered On: Sep 20

Can't actually tell what you want to know. Please ask a more specific
question. And show your code and any error messages.

Function parameters  can be (and often are) of many different data  types.
What you have in your parameter string  looks legal, although I do prefer to
see "ByRef" or "ByVal" for each parameter.

What is missing is a definition of the return  type of the function. This
means that the return type  will be whatever type you assign to the function
name inside the function. This can give  a run-time error if the return
value is assigned to a variable of an incompatible type. Give the function
a return type, and the compiler can check compatibility for you at compile
time.


BTW your second parameter is currency, not double. I haven't looked at how
VBA stores currency  variables, but it's certainly not the same as doubles -
more precision and less decimal places. I don't know whether this could be
causing your problems

> We all know that UDF errors  are hard  to debug  ...

UDF (I assume you mean "User Data Form" - actually just called User Form in
Excel) errors are very easy to debug in Excel, due to the availability of
break-points, code stepping, an immediates window, and a watch window. Plus
the fact that an uncaught error offers a "debug" option to allow you to go
straight to the line that's causing the problem  - and, in many cases, simply
to fix it and re-execute it.

 
Answer #2    Answered By: Billy Evans     Answered On: Sep 20

Not much information here to go on, but a couple of things stick out.

One, the function  declaration should probably be:

public function tax(status as string, income as currency) as currency

This would insure the return  value is of the type  currency.

Lastly, there should be a line executed before leaving the function that sets
the return value,

tax = (value)

Be aware that 'status' is a property used several places in VB, so you might
want to name it 'TaxStatus' instead.

 
Answer #3    Answered By: Isam Bashara     Answered On: Sep 20

I take it that you're getting unpredictable results?
Without seeing your code, it's going to be hard  to help.

I'd start with making sure that you're using "Option Explicit" in
your code module.

Also, you can use:

Function tax(status As String, income As Currency) As Currency

to force "tax" to return  currency...
then, at least if the code results in something else, it should
produce an error before it leaves the function.

Let us know what kind of results you're getting.
We should be able to help.

 
Answer #4    Answered By: Volker Fischer     Answered On: Sep 20

Thanks for your very insightful help  and yes, you guys  helped me big
time.

My first error that I have found out was this:

Function tax(status As String, income As Currency)
'some code
End Function

the "status" variable, according to sir Bob:
'Be aware that 'status' is a property used several places in VB, so
you might want to name it 'TaxStatus' instead.'

and second and significant error is that all of you guys spotted is
obviously, I never had a return  type with my code.

And, sir Dave is right, according to him:

'BTW your second parameter is currency, not double. I haven't looked
at how VBA stores currency  variables, but it's certainly not the same
as doubles - more precision and less decimal places. I don't know
whether this could be causing your problems'

You got that right sir!

Currently, my code is working and yet I got undesirable results in
terms of the output but it's ok! This is up to me now to debug  the
formulas.

Thank you all of you guys! I really really appreciate it.

 
Didn't find what you were looking for? Find more on Different Parameters in Excel UDF Or get search suggestion and latest updates.




Tagged: