Unlimited Parameters for Custom Functions

[andreacosta]

I just started fooling around w/ VBA yeseterday, so I'm still getting my bearings.

I know how to create functions in which the arguments are explicitly stated i.e.

Please Login or Register  to view this content.

But how would I modify the function to include any number of arguments? i.e.

Please Login or Register  to view this content.

I've seen ParamArray being applied in certain instances where the number of arguments can vary....not sure if that can be used in this case.

Any assistance is much appreciated.

[shg]

Welcome to the forum.

Please take a few minutes to read teh forum rules, and then edit your post to add code tags. Then we can talk about parameter arrays.

Thanks.

[andreacosta]

Thanks. I was wondering how everyone got their code to appear so nicely...

[shg]

A ParamArray is a 0-based variant array composed of up to 30 arguments. Does this do what you want?

Please Login or Register  to view this content.

=LINK3(1,2,3,4) returns 10.355024

If you're computing compound interest, there are easier ways with just a formula.

EDIT: If you are passing a multi-cell range (e.g., =LINK3(a1:a10)), vs individual values (=LINK3(A1, A5, B27)) this will need some adjustments.

[andreacosta]

shg,

Thanks very much. I realize there are easier ways to compute compound interest...This was more an exercise for learning purposes.

In your ParamArray argument, does av stand for something in particular? Can you also give me a brief explanation of how you're using i/Next i in your code? I glanced at Excel VBA Programming for Dummies and didn't find a reference to that.

[shg]

I have a naming convention for variables:

i for integers (Byte, Integer, Long)
n for integers that denumerate a count (nRow)
s for Strings
f for Singles (as in 'Float')
d for Doubles
v for Variants
r for Ranges
o for Objects other than ranges
u for user-defined types
.... and some others.

... preceded by m for module-level variables, g for global variables, nothing for local variables, and a for arrays. A module-level array of integers would be like maiVeryBriefDescriptiveText. So av is just my name for an array of Variants without further description.

Other people have other conventions; I like mine, and have used it for years -- it makes my code very readable to me after not looking at it for a while. I would highly commend you adopt one. Look at some of Excel chart god Andy Pope's code in the forum; he's religious about variable naming. His is more verbose than mine, but he probably types with all ten fingers too ...

how you're using i/Next i in your code?

It's just a for/next loop that replaces each value of the incoming array with 1+value/100. And it's really terrible practice as shown, because if it were being called from VBA (instead of the worksheet) it would be modifying the array it was passed. That's not intrinsically bad (and functions are frequently coded to do exactly this), but it's not the intent here. I should have declared a local array to do the same thing.