Passing parameters to UDF
 

Hi,

There is a silly issue I got stuck for a while.

I used to write mostly procedures rather than functions. In order t
make them clear and easy to read when I called them I have formatte
the text like that:

procedure1 _
parameter1:=value1, _
parameter2:=value2, _
parameter3:=value3, etc.

now I wanted to do the same with UDF, but it doesn't work. The workin
format is:

function1(value1, value2, value3, etc)

but it is harder to read afterwards.

It seems to me silly, but there is no direct example on MSDN or thi
forum, so I decided to ask.


Thanks in advance,


Mbobr

--
Message posted from http://www.ExcelForum.com

Passing parameters to UDF
 

What doesn't work?

A UDF stands for user defined function and is a function that can be
used from an XL worksheet.

It is not possible that the procedure you are calling is from an XL
worksheet.

So, I am not sure what you are trying to do. If you are trying to use
a function in a worksheet, yes, you cannot specify named arguments.
They all have to be specified by position. On the other hand, from
within code, you can use something like:

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

There is a silly issue I got stuck for a while.

I used to write mostly procedures rather than functions. In order to
make them clear and easy to read when I called them I have formatted
the text like that:

procedure1 _
parameter1:=value1, _
parameter2:=value2, _
parameter3:=value3, etc.

now I wanted to do the same with UDF, but it doesn't work. The working
format is:

function1(value1, value2, value3, etc)

but it is harder to read afterwards.

It seems to me silly, but there is no direct example on MSDN or this
forum, so I decided to ask.


Thanks in advance,


Mbobro


---
Message posted from http://www.ExcelForum.com/

Passing parameters to UDF
 

Hi

How to code and what's easy to read is just a question of style, habit and taste.
Personally I use Call with all my procedures, so all passed variables are embraced with
parentheses. But no matter which choice, all variables and functions should be declared
with sesible data types and named reasonably. Here's a sample of the different ways I know
to pass stuff to a Sub and a Function. To your question, yes I think you must put
parentheses around variables to functions, and I think you should do that to subs too.

Sub test()
Dim dtBorn As Date
Dim Msg As String
dtBorn = DateSerial(1956, 12, 31)

Msg = BirthdayAlert(strFirstName:="Joe", _
strLastName:="Doe", _
dtDayOfBirth:=dtBorn)
MsgBox Msg

' I use this style for functions:
Msg = BirthdayAlert("Joe", _
"Doe", _
dtBorn)
MsgBox Msg

ShowBornInfo "Joe", _
"Doe", _
dtBorn

ShowBornInfo _
strFirstName:="Joe", _
strLastName:="Doe", _
dtDayOfBirth:=dtBorn

Call ShowBornInfo( _
strFirstName:="Joe", _
strLastName:="Doe", _
dtDayOfBirth:=dtBorn)

' I use this style for Subs:
Call ShowBornInfo("Joe", _
"Doe", _
dtBorn)

End Sub

Function BirthdayAlert( _
strFirstName As String, _
strLastName As String, _
dtDayOfBirth As Date) As String

Dim dtNext As Date
dtNext = DateSerial(Year(Date), _
Month(dtDayOfBirth), _
Day(dtDayOfBirth))

If dtNext = Date Then
BirthdayAlert = strFirstName & " " & _
strLastName & " is " & _
Year(Date) - Year(dtDayOfBirth) & _
" today ! Hooray !"
Else
If dtNext < Date Then _
dtNext = DateSerial(Year(Date) + 1, _
Month(dtDayOfBirth), _
Day(dtDayOfBirth))
BirthdayAlert = strFirstName & " " & _
strLastName & " is " & _
Year(Date) - Year(dtDayOfBirth) & _
" on " & Format(dtNext, "dddd mmmm d. yyyy")
End If
End Function

Sub ShowBornInfo( _
strFirstName As String, _
strLastName As String, _
dtDayOfBirth As Date)
MsgBox strFirstName & " " & _
strLastName & " is born " & _
Format(dtDayOfBirth, "dddd mmmm d. yyyy")
End Sub

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"mbobro" wrote in message
...
Hi,

There is a silly issue I got stuck for a while.

I used to write mostly procedures rather than functions. In order to
make them clear and easy to read when I called them I have formatted
the text like that:

procedure1 _
parameter1:=value1, _
parameter2:=value2, _
parameter3:=value3, etc.

now I wanted to do the same with UDF, but it doesn't work. The working
format is:

function1( value1, value2, value3, etc)

but it is harder to read afterwards.

It seems to me silly, but there is no direct example on MSDN or this
forum, so I decided to ask.


Thanks in advance,


Mbobro


---
Message posted from http://www.ExcelForum.com/

Passing parameters to UDF
 

What doesn't work?

A UDF stands for user defined function and is a function that can be
used from an XL worksheet.

It is not possible that the procedure you are calling is from an XL
worksheet.

So, I am not sure what you are trying to do. If you are trying to use
a function in a worksheet, yes, you cannot specify named arguments.
They all have to be specified by position. On the other hand, from
within code, you can use something like:

Option Explicit

Function aFunc(x, y)
aFunc = x ^ 2 + y ^ 3
End Function
Sub testIt()
MsgBox aFunc(y:=2, x:=3)
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

There is a silly issue I got stuck for a while.

I used to write mostly procedures rather than functions. In order to
make them clear and easy to read when I called them I have formatted
the text like that:

procedure1 _
parameter1:=value1, _
parameter2:=value2, _
parameter3:=value3, etc.

now I wanted to do the same with UDF, but it doesn't work. The working
format is:

function1(value1, value2, value3, etc)

but it is harder to read afterwards.

It seems to me silly, but there is no direct example on MSDN or this
forum, so I decided to ask.


Thanks in advance,


Mbobro


---
Message posted from http://www.ExcelForum.com/

Passing parameters to UDF
 

You can't have named parameters in an Excel function.

What you can do is use the tooltip, or the function wizard.
Also, you could start typing the function and press CTRL+SHIFT+A.
eg. Type =VLOOKUP( then press those keys for the names of the arguments.


"mbobro" wrote in message
...
Hi,

There is a silly issue I got stuck for a while.

I used to write mostly procedures rather than functions. In order to
make them clear and easy to read when I called them I have formatted
the text like that:

procedure1 _
parameter1:=value1, _
parameter2:=value2, _
parameter3:=value3, etc.

now I wanted to do the same with UDF, but it doesn't work. The working
format is:

function1(value1, value2, value3, etc)

but it is harder to read afterwards.

It seems to me silly, but there is no direct example on MSDN or this
forum, so I decided to ask.


Thanks in advance,


Mbobro


---
Message posted from http://www.ExcelForum.com/

Passing parameters to UDF
 

After a small Christmas break I'm back to my code.

Thanks everyone for help, in a way a kind of Christmas gift for me!

Michal


---
Message posted from http://www.ExcelForum.com/