Solved: User defined in-parameters in a function

[danlu]

Hi,

How do you create a function where you leave it to the user of the excel sheet to define which values the in-parameters should take by letting the user state the cell references (which holds these values)?

For ex if I have created a very simple function which is called "mulitply" and which multiplies two values and I want to give the user the possibility to write (in the excelsheet) the formula:
=multiply(A1;B1)
so that this function goes into these cell references and look for values and then multiply the value in A1 with the value in B1, how do achieve this in my VBA custom Function code?

[Bob Phillips]

Hi,

How do you create a function where you leave it to the user of the excel sheet to define which values the in-parameters should take by letting the user state the cell references (which holds these values)?

For ex if I have created a very simple function which is called "mulitply" and which multiplies two values and I want to give the user the possibility to write (in the excelsheet) the formula:
=multiply(A1;B1)
so that this function goes into these cell references and look for values and then multiply the value in A1 with the value in B1, how do achieve this in my VBA custom Function code?

If you created a function like so

[vba]

Function multiply(val1, val2)
multiply = val1 * val2
End Function
[/vba]

which will handle cells or constants.

[Cyberdude]

Hey, danlu, welcome to VBAX!

I have a nitpicking comment:
You wrote your function reference as
=Multiply(A1;B1)

It's probably a typo, but the semicolon should be a comma:
=Multiply(A1,B1)

Just in case you weren't aware.

[Bob Phillips]

Hey, danlu, welcome to VBAX!

I have a nitpicking comment:
You wrote your function reference as
=Multiply(A1;B1)

It's probably a typo, but the semicolon should be a comma:
=Multiply(A1,B1)

Just in case you weren't aware.

Not necessarily. On the mainland continent (of Europe), the delimiter is a semi-colon, not comma. The comma is used as decimal separator in numbers, and so could throw the formula.

[Zack Barresse]

It's probably a typo, but the semicolon should be a comma..

Not in Sweden.

If it could be a cell reference or a value directly given, I generally use the TypeName to check while adding some error handling in case textual characters are presented...

[VBA]Function Multiply(val1 As Variant, val2 As Variant) As Variant
Multiply = "ERROR!"
On Error Resume Next
If TypeName(val1) = "Range" Then
If TypeName(val2) = "Range" Then
Multiply = val1.Value * val2.Value
Else
Multiply = val1.Value * val2
End If
Else
If TypeName(val2) = "Range" Then
Multiply = val1 * val2.Value
Else
Multiply = val1 * val2
End If
End If
End Function[/VBA]

[Bob Phillips]

Not in Sweden.

I don't consider Sweden (or Norway, or Finland) part of mainland Europe. Mainland Europe is the main central block IMO, FGrance, Germany, Spain, Belgium, Luxembourg, Liechenstein, Italy, etc.

Sweden is odd, they even drove on the left once.

[Cyberdude]

That's the place where they always misspell the word "color".

[Zack Barresse]

That's not the only place, my friend!

[Bob Phillips]

That's the place where they always misspell the word "color".

In Sweden they wouldn't spell colour, they have their own word.

You are in the place where they mis-spell colour, and where they mis-use momentarily, and where .... need I go on. Remember, it is called the English language because it originated in England.

[Zack Barresse]

English, blah, blah, England, blah, blah. Err.. I think it's.. Yadda, yadda, yadda..

[danlu]

Hi,

Is val1 and val2 some sort of predefined parameter (variable) or how can VBA treat both cells and constants (usually when you define variables you assign them some sort of format)?

If you created a function like so

[vba]

Function multiply(val1, val2)
multiply = val1 * val2
End Function
[/vba]

which will handle cells or constants.

[Bob Phillips]

Because they default to variant type, and they will take the sub-type of the parameter passed to it.

[danlu]

Hi,

Thanks very much, that made things a lot clearer!

Because they default to variant type, and they will take the sub-type of the parameter passed to it.

[geekgirlau]

Sweden is odd, they even drove on the left once.

What, before they came to their senses? Better take care down here before stepping out into traffic! Colour me embarrassed - next thing you know I'll be typing dates in dd/mm/yy format ...

[Bob Phillips]

What, before they came to their senses? Better take care down here before stepping out into traffic! Colour me embarrassed - next thing you know I'll be typing dates in dd/mm/yy format ...

http://www.hyperdictionary.com/search.aspx?define=irony

[TonyJollans]

Not in Sweden.

The list separator character is set under Regional Options and the default for Sweden is semicolon.

Sweden is odd, they even drove on the left once.

But they had left-hand drive cars even when they drove on the left. Odd indeed!

[geekgirlau]

Actually I get the irony xld, it's just that down here in Aus we have our hands full driving on the left and putting the "u" in colour, so don't have much opportunity to practice it ourselves ... (oh, and the image refers to those of us who insist on driving on the wrong side of the road when the rest of world does the reverse!)

[Bob Phillips]

Actually I get the irony xld, it's just that down here in Aus we have our hands full driving on the left and putting the "u" in colour, so don't have much opportunity to practice it ourselves ... (oh, and the image refers to those of us who insist on driving on the wrong side of the road when the rest of world does the reverse!)

So do we here in England, all those things inclduing the date, which is why I said that.

[mdmackillop]

So do we here in England, all those things including the date, which is why I said that.

...and North of the border too.