Dear all,

I have a function which return string like this:

Function strReturn(byval inputstr as string) as string

strReturn="XXX"


End Function.


How can I make this function to look like other function such
as(HLookup,Vlookup,.....) which user can use when they need.

Example = strReturn(A1)

Many thanks
Thanh Nguyen


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=510475

You already have. Just make sure that it is in a standard code module.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"thanhnguyen"
wrote in message
...

Dear all,

I have a function which return string like this:

Function strReturn(byval inputstr as string) as string

strReturn="XXX"


End Function.


How can I make this function to look like other function such
as(HLookup,Vlookup,.....) which user can use when they need.

Example = strReturn(A1)

Many thanks
Thanh Nguyen


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile:
http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=510475

Hi Bob Phillips
But when i go to worksheet and try to use that function it return
#NAME?

Please give me your email address I will send to you my funtion.
my email address is

Thanks


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=510475

Try this:

http://www.vertex42.com/ExcelArticle...functions.html

:) !
Doc ky o muc:
"How to Create Excel User Defined Functions"

Chuc may man!


--
Rock


------------------------------------------------------------------------
Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
View this thread: http://www.excelforum.com/showthread...hreadid=510475

Put it in a general module, not a sheet module

Right click on the sheet tab and select view code === No, not here

go to the VBE, insert=Module ========= place it there.



--
Regards,
Tom Ogilvy


"thanhnguyen"
wrote in message
...

Hi Bob Phillips
But when i go to worksheet and try to use that function it return
#NAME?

Please give me your email address I will send to you my funtion.
my email address is

Thanks


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile:
http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=510475

Hi all,

Regards to follow the guides of you. I put my function in Global Module
not worksheet or workbook module, but still now slove my problem.

below is my function (I copy it from MSDN)

Function TrimSpace(ByVal CellInput As Range) As String
' This procedure trims extra space from any part of a string

Dim astrInput() As String
Dim astrText() As String
Dim strElement As String
Dim lngCount As Long
Dim lngIncr As Long
Dim returnstr As String
Dim mystr As String
Dim myspecialstr() As String

If Trim(CellInput.Value) = "" Then Exit Function
' Split passed-in string.
astrInput = Split(Trim(CellInput.Value))

' Resize second array to be same size.
ReDim astrText(UBound(astrInput))



' Initialize counter variable for second array.

lngIncr = LBound(astrInput)
' Loop through split array, looking for
' non-zero-length strings.
For lngCount = LBound(astrInput) To UBound(astrInput)
strElement = astrInput(lngCount)
If Len(strElement) 0 Then
' Store in second array.
astrText(lngIncr) = strElement
lngIncr = lngIncr + 1
End If
Next

' Resize new array.
ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)

' Join new array to return string.
returnstr = Join(astrText)

End Function


When I tried with formular =TrimSpace(A1) but nothing happen.

Best regards,

Thanh Nguyen


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=510475

Sorry, not solve my problem!


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=510475

If you were able to put

=TrimSpace(A1)

in a cell and it returned the values in A1, then that did solve your problem
in terms of getting the function to work.

If it returned #Value, then there is a problem either internal to the
function or in the argument

If it said something like unkown name, then the problem isn't solved.

If it just returned the string in cell A1, then did the string in A1 have
extra spaces in it.

In A1 put in

=" the big dog "

the function should then return

the big dog

--
Regards,
Tom Ogilvy


"thanhnguyen"
wrote in message
...

Sorry, not solve my problem!


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile:
http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=510475

But when I put =TrimSpace(A1) then nothing happen.

B1=TrimSpace(A1)

I tried to press Shift+F3 and insert user function but the result is
same.

"Bótay.com" is in vietnamese:)

Thanh Nguyen


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=510475