On Jun 7, 2:47*pm, Chip Pearson wrote:
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. Seehttp://www.cpearson.com/excel/Classes.aspxfor 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, LLCwww.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?

Chip,

Thank you for the explanation, I will certainly learn from it. I
haven't used many type instances, but inadvertently I have used a few
classes because that seems inherent in Visual Studio .Net programming.
I just don't do enough of it to fully understand how it all ties
together. I'm an old C programmer that has tried to keep up with new
techniques and as you can tell, I am a few years behind.

I do appreciate you taking the time for this final answer and I will
spend some time with the aspects of classes and continue educating
myself on those techniques.

Rick