On Jun 6, 7:04*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Rick,

I should have look for Chip Pearson's take on this first:

http://www.eggheadcafe.com/conversat...30597587&threa....

for an example of using a class module to do (possibly...) what you want.

Bernie

"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?

I haven't done a lot with classes in excel as I haven't run into items
of that complexity. However, I decided to experiment (learn) from this
exercise. So I created a class and still have the same problem of
getting the value to the worksheet cell. "the formula you typed
contains an error".

Even reading all of the excel examples, or so they would have you
believe, I find it most difficult to find anyone that says put this
formula in a cell to get the results.

I guess I will keep digging and hopefully learning. I will post when I
find an appropriate answer. Unlike 90% of the questions out there on
the internet that seem to have lots of questions with no real answers.
Makes Google and other search engines work overtime. How about a
search engine that only displays answers instead of all of the
questions that never got a response.

Thanks to all ... while I keep searching.