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?

Return a variant

Function SeparateValueFromUnit(ByVal strInput As String) As Variant



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

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?

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?

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?

Type ValUnit
tValue As Double
tUnit As String
End Type

Sub test()
Dim i As ValUnit
Dim s As String
s = " 49.0000RLS"
i.tValue = dNum(s)
i.tUnit = sStr(s)
Debug.Print i.tUnit
Debug.Print i.tValue
End Sub

Function dNum(s As String) As Double
Dim re
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[0-9.]+"
If re.test(s) Then _
dNum = CDbl(re.Execute(s)(0))
End Function

Function sStr(t As String) As String
Dim re
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[A-z]+"
If re.test(t) Then _
sStr = CStr(re.Execute(t)(0))
End Function

A1=" 49.0000RLS")
B1=dNum(A1)
C1=sStr(A1)

regards
r

--
Come e dove incollare il codice:
http://www.rondebruin.nl/code.htm

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


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

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?

Rick,

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

http://www.eggheadcafe.com/conversat...did=3059 7482

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?

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.

On Jun 6, 8:11*pm, "Rick's nickname" wrote:
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.

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?

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