return UDT element to spreadsheet cell
Rick,
I rarely if ever use defined types (I prefer arrays or Excel ranges, since
that is what I work with), so I don't know. I'm guessing that a UDF cannot
return a UDT to a worksheet, based on your experiences - but you could use a
separate function to return the value - used like:
=ReturnValue(H2,"tValue")
coded like:
Type ValUnit
tValue As Double
tUnit As String
End Type
'=ReturnValue(H2,"tValue")
Function ReturnValue(ByVal myStrInput As String, myVal As String) As Variant
Dim myValUnit As ValUnit
myValUnit = SeparateValueFromUnit(myStrInput)
ReturnValue = IIf(myVal = "tUnit", myValUnit.tUnit, myValUnit.tValue)
End Function
Function SeparateValueFromUnit(ByVal strInput As String) As ValUnit
Dim fromwhere As Integer
fromwhere = InStr(Trim(strInput), ".") + 4
SeparateValueFromUnit.tValue = Left(Trim(strInput), fromwhere)
SeparateValueFromUnit.tUnit = Mid(Trim(strInput), fromwhere + 1)
End Function
Bernie
MS Excel MVP
"Rick's nickname" wrote in message
...
On Jun 6, 2:21 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Rick,
The usual method is to return an array from your UDF:
Function SeparateValueFromUnit(ByVal strInput As String) As Variant
Dim fromwhere As Integer
fromwhere = InStr(Trim(strInput), ".") + 4
Dim myVal(0 To 1) As Variant
myVal(0) = CDbl(Left(Trim(strInput), fromwhere))
myVal(1) = Mid(Trim(strInput), fromwhere + 1)
SeparateValueFromUnit = IIf(Application.Caller.Columns.Count 1, _
myVal, Application.Transpose(myVal))
End Function
Select two cells (either in one row or one column), then array enter
(enter
using Ctrl-Shift-Enter)
=SeparateValueFromUnit(H2)
HTH,
Bernie
MS Excel MVP
"Rick's nickname" wrote in message
...
Using Excel 2003.
I have a module with the following code:
Type ValUnit
tValue As Double
tUnit As String
End Type
Function SeparateValueFromUnit(ByVal strInput As String) As ValUnit
Dim fromwhere As Integer
fromwhere = InStr(trim(strInput), ".") + 4
SeparateValueFromUnit.tValue = Left(trim(strInput), fromwhere)
SeparateValueFromUnit.tUnit = Mid(trim(strInput), fromwhere + 1)
End Function
If I go to the immediate window and enter:
? separatevaluefromunit(" 49.0000RLS").tunit
I get the correct response.
If I go to a cell in the worksheet and enter: (H2 Holds "
49.0000RLS")
=SeparateValueFromUnit(H2).tUnit
I get an error: "The formula you typed contains an error".
How do I return just one element (tUnit, or tValue) to this cell?
I will try that, but I don't understand why I can execute the function
from the intermediate window and it works just as I want it to work,
but when I enter that same line into a cell I get the formula error.
Isn't that what a UDF in combination with a UDT is supposed to be used
for?
|