Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: Overloading custom VBA functions. Possible? Howto?

    Fairly simple question really...

    Is it possible to create overloaded versions of custom functions? Basically I would like to create optional parameters for some functions. For example, say I wanted to give the user an option to specify a length for a string returned by a function, but I don't want the argument to be mandatory. Can this be done?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Sure:

    Function MyFunction(Optional someInt as Integer) as String


    End Function

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    AWESOME!!

    That's been bothering me forever.

    Much obliged good sir!

    One further question, is it possible to allow an undefined number of arguments short of feeding an array?

    Say I wanted to make a coalesce function, basically returning the first non-null value of a series. Is there a way to pass an undetermined number of comma-deliminated parameters? Currently I can either feed it a string and use split(), or pass an array... any ideas?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I figured this out by the way...

    (ParamArray varname() As Variant)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Posts
    96
    you can declare arrays without declaring the size first.. once size is know... use "redim" so-call redeclare the array with size.. limitation is that you cannot return an array from an function..

    maybe passing the array as reference instead of value (for parameters) may help..

    Cyherus

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The solution I posted works flawlessly.

    Thank for your consideration though!!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Teddy, that is a good technique because up until now I have been using global variables.

    Lately I have implemented stacks instead of Arrays. I bet the same method can be used for those datatypes.

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I recommend you do not declare parameters as type Variant for convenience.

    1) Variants consumes unnecessary stack space.
    2) Your function is not type safe.
    Last edited by r123456; 06-18-04 at 11:57.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by r123456
    I recommend you do not declare parameters as type Variant for convenience.

    1) Variants consumes unnecessary stack space.
    2) Your function is not type safe.
    Unfortunately we don't get much of a choice:

    You use the ParamArray keyword to denote a parameter array. The array must be declared as an array of type Variant, and it must be the last argument in the procedure definition.
    *shrug*

    It doesn't really matter much for the COALESCE function that I was using as an example, as the array MUST be variant to accept any type of input. All it does is evaluate null.

    I agree though, I would much rather declare a defined type.

    To get around the issue, I place a CONVERT() function at the beginning of the sub/function with error trapping. Works fine.
    Last edited by Teddy; 06-18-04 at 12:24.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •