PDA

View Full Version : Solved: User defined in-parameters in a function



danlu
08-27-2006, 03:30 AM
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
08-27-2006, 04:34 AM
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



Function multiply(val1, val2)
multiply = val1 * val2
End Function


which will handle cells or constants.

Cyberdude
08-27-2006, 01:11 PM
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
08-27-2006, 03:08 PM
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
08-29-2006, 09:19 AM
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...

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

Bob Phillips
08-29-2006, 09:25 AM
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
08-30-2006, 01:30 PM
That's the place where they always misspell the word "color". :devil2:

Zack Barresse
08-30-2006, 02:24 PM
That's not the only place, my friend! ;)

Bob Phillips
08-30-2006, 02:58 PM
That's the place where they always misspell the word "color". :devil2:

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
08-30-2006, 03:05 PM
English, blah, blah, England, blah, blah. Err.. I think it's.. Yadda, yadda, yadda.. :devil2:

danlu
09-01-2006, 06:28 AM
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



Function multiply(val1, val2)
multiply = val1 * val2
End Function


which will handle cells or constants.

Bob Phillips
09-01-2006, 07:26 AM
Because they default to variant type, and they will take the sub-type of the parameter passed to it.

danlu
09-02-2006, 02:01 AM
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
09-04-2006, 04:13 AM
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 ... :cuckoo:

Bob Phillips
09-04-2006, 04:28 AM
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 ... :cuckoo:

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

TonyJollans
09-04-2006, 04:36 AM
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
09-04-2006, 04:43 AM
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
09-04-2006, 05:10 AM
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
09-04-2006, 03:46 PM
So do we here in England, all those things including the date, which is why I said that.
...and North of the border too.:beerchug: