I am trying to get a function to return the values stored in a
user-defined type. The type has two fields: the first field is a Double
and the second field is an Integer.

I realize functions can return more than one value by declaring them as
arrays or by using the Array() function, but can UDT's be used in this
manner? I remember seeing code that returns the values of a UDT, so I
believe this IS possible.

Here is a simple example VBA:


Code:
--------------------

' DECLARE USER DEFINED TYPE
Private Type typDblInt

typDblInt0 As Double
typDblInt1 As Integer

End Type

' DECLARE SIMPLE FUNCTION USING USER DEFINED TYPE
Function UDFTest(dNum, iNum) As typDblInt

Dim typDblIntTmp As typDblInt

typDblIntTmp.typDblInt0 = dNum
typDblIntTmp.typDblInt1 = iNum


UDFTest = typDblIntTmp

End Function
--------------------

When I type this function into my spreadsheet I get #VALUE!. I've tried
entering the function into a single cell, two adjecent cells, and as a
normal function or as an array function (Ctrl+Shift+Enter). Still
#VALUE!. *sigh*

So is it possible to get a function to return the field values of a
user defined type? THANKS for the HELP!

P.S. To answer a few obvious ?'s you may be thinking: "Why do you want
to do this ?" B/c I want to return multiple values of different data
types (not just Double and Integer but also String and Boolean).

"Why not decalre a Variant array?" B/c I don't want to take the
performance hit of using Variant (the function will be used 100's of
times on a very slow pc). And I want to strongly type the inputs, which
variant doesn't allow.

"If you want to strongly type, why not use a Class Module?" B/c I don't
want my VBA code to create a COM interface if it doesn't have to, and I
believe there is a performance hit using a Class Module.

THANKS AGAIN VERY MUCH FOR THE HELP! :)


--
BobbyMurcerFan
------------------------------------------------------------------------
BobbyMurcerFan's Profile: http://www.excelforum.com/member.php...o&userid=11107
View this thread: http://www.excelforum.com/showthread...hreadid=526255

So is it possible to get a function to return the field values of a
user defined type? No. - neither as an array formula entered in two
cells or a single formula in a single cell.

--
Regards,
Tom Ogilvy

"BobbyMurcerFan"
<BobbyMurcerFan.25786n_1143245402.1466@excelforu m-nospam.com wrote in
message news:BobbyMurcerFan.25786n_1143245402.1466@excelfo rum-nospam.com...

I am trying to get a function to return the values stored in a
user-defined type. The type has two fields: the first field is a Double
and the second field is an Integer.

I realize functions can return more than one value by declaring them as
arrays or by using the Array() function, but can UDT's be used in this
manner? I remember seeing code that returns the values of a UDT, so I
believe this IS possible.

Here is a simple example VBA:


Code:
--------------------

' DECLARE USER DEFINED TYPE
Private Type typDblInt

typDblInt0 As Double
typDblInt1 As Integer

End Type

' DECLARE SIMPLE FUNCTION USING USER DEFINED TYPE
Function UDFTest(dNum, iNum) As typDblInt

Dim typDblIntTmp As typDblInt

typDblIntTmp.typDblInt0 = dNum
typDblIntTmp.typDblInt1 = iNum


UDFTest = typDblIntTmp

End Function
--------------------

When I type this function into my spreadsheet I get #VALUE!. I've tried
entering the function into a single cell, two adjecent cells, and as a
normal function or as an array function (Ctrl+Shift+Enter). Still
#VALUE!. *sigh*

So is it possible to get a function to return the field values of a
user defined type? THANKS for the HELP!

P.S. To answer a few obvious ?'s you may be thinking: "Why do you want
to do this ?" B/c I want to return multiple values of different data
types (not just Double and Integer but also String and Boolean).

"Why not decalre a Variant array?" B/c I don't want to take the
performance hit of using Variant (the function will be used 100's of
times on a very slow pc). And I want to strongly type the inputs, which
variant doesn't allow.

"If you want to strongly type, why not use a Class Module?" B/c I don't
want my VBA code to create a COM interface if it doesn't have to, and I
believe there is a performance hit using a Class Module.

THANKS AGAIN VERY MUCH FOR THE HELP! :)


--
BobbyMurcerFan
------------------------------------------------------------------------
BobbyMurcerFan's Profile:
http://www.excelforum.com/member.php...o&userid=11107
View this thread: http://www.excelforum.com/showthread...hreadid=526255

Thanks Tom. Not the answer I was hoping for, but I DO appreciate it. ;)


--
BobbyMurcerFan
------------------------------------------------------------------------
BobbyMurcerFan's Profile: http://www.excelforum.com/member.php...o&userid=11107
View this thread: http://www.excelforum.com/showthread...hreadid=526255