return UDT element to spreadsheet cell

[Rick's nickname]

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?

 

[Joel]

Return a variant

Function SeparateValueFromUnit(ByVal strInput As String) As Variant

 

[Bernie Deitrick]

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]

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

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?

 

[r]

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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Bernie Deitrick]

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,

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

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?

 

[Bernie Deitrick]

Rick,

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

http://www.eggheadcafe.com/conversation.aspx?messageid=30597587&threadid=30597482

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

Bernie

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

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's nickname]

Rick,

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

http://www.eggheadcafe.com/conversation.aspx?messageid=30597587&threa....

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

Bernie









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.

 

[Rick's nickname]

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.

Until I figure out the class approach or a proper method to use Types,
I have opted to use an additional parameter sent to the function to
tell it which part to return to the cell.

Function SVFU(strInput As String, sPart As String) As Variant
Dim fromwhere As Integer

fromwhere = InStr(trim(strInput), ".") + 4
Select Case sPart
Case "Value"
SVFU = CDbl(Left(trim(strInput), fromwhere))
Case "Unit"
SVFU = Mid(trim(strInput), fromwhere + 1)
End Select
End Function


In a cell, I can now enter =SVFU(H2,"Value") and I get the left hand
side, or I enter =SVFU(H2,"Unit") and I get the right hand side.

 

[Chip Pearson]

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)

 

[Rick's nickname]

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)

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