If I go to the immediate window and enter:
? separatevaluefromunit(" 49.0000RLS").tunit
I get the correct response.

The Immediate window evaluates VBA code, not worksheet functions. In
VBA, you can get one element from a Type; you cannot do that in an
Excel formula. The best way is create a simple class with the data
variables and then test Application.Caller to see if the function was
called by a worksheet cell or by other VBA, and return the appropriate
result. As a simple example, consider the following.

Insert a class module namedCUser. In that module, enter

Public UserName As String
Public UserID As Long

Then in a regular code module, use the following code:

Function GetUser() As Variant
Dim User As New CUser
User.UserName = "Joe Smith" '<<< CHANGE
User.UserID = 12345 '<<< CHANGE
With Application
If IsObject(.Caller) Then
If TypeOf .Caller Is Excel.Range Then
' Called from a worksheet cell
If .Caller.Columns.Count = 1 Then
' called from one column in two rows
GetUser = .Transpose( _
Array(User.UserName, User.UserID))
Else
' called from two columns on one row
GetUser = Array(User.UserName, User.UserID)
End If
End If
Else
' Not called from a worksheet cell.
Set GetUser = User
End If
End With
End Function


To call the function from a worksheet, select two adjacent cells,
enter =GetUser() and press CTRL SHIFT ENTER. This will return the user
name to the first cell and the user id to the second cell. The code
automatically adjusts for the case when the two cells are in one
column and two rows or in two columns on one row.

You can also call this function from other VBA code. Since VBA can get
the properties of the User object, you can use

Dim User As New CUser
Set User = GetUser()
Debug.Print User.UserName, User.UserID

I've never really found any reason to use a Type. Classes are much
more flexible. See http://www.cpearson.com/excel/Classes.aspx for an
in-depth introduction to classes. They are not as complicated as you
might think.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)






On Sat, 6 Jun 2009 11:59:41 -0700 (PDT), "Rick's nickname"
wrote:

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?