B
BobbyMurcerFan
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!
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!
