Hi, I want to know how to use the instruction Range("RangeName").Value
= Output in a VBA function to paste in a spreadsheet a value which is
not the principal output of the function.

Thanks

You need to passback the output to the function. Refer the example below
Getword accepts two arguments and return a string. Note the last line in the
code where the output is assigned back to the function name.

A1 = "This is a test"
B1 = GetWord(A1,3)

Function GetWord(strTemp As String, lngPos As Long) as String
'Function to return the nth word from a text string..from a cell
If InStr(Trim(strTemp), " ") = 0 Then Exit Function
lngPos = lngPos - 1
arrTemp = Split(Trim(strTemp), " ")
If lngPos UBound(arrTemp) Or lngPos < 0 Then Exit Function
GetWord = arrTemp(lngPos)
End Function


--
If this post helps click Yes
---------------
Jacob Skaria


" wrote:

Hi, I want to know how to use the instruction Range("RangeName").Value
= Output in a VBA function to paste in a spreadsheet a value which is
not the principal output of the function.

Thanks

Thanks Jacob. The value I need to paste in a cell is not the output
of the function. I want to paste a second value that the function
computes in a cell different from the one I typed the function. Is
this possible using a VBA function?

Thanks

On May 3, 12:08*am, Jacob Skaria
wrote:
You need to passback the output to the function. Refer the example below
Getword accepts two arguments and return a string. Note the last line in the
code where the output is assigned back to the function name.

A1 = "This is a test"
B1 = GetWord(A1,3)

Function GetWord(strTemp As String, lngPos As Long) as String
'Function to return the nth word from a text string..from a cell
If InStr(Trim(strTemp), " ") = 0 Then Exit Function
lngPos = lngPos - 1
arrTemp = Split(Trim(strTemp), " ")
If lngPos UBound(arrTemp) Or lngPos < 0 Then Exit Function
GetWord = arrTemp(lngPos)
End Function

--
If this post helps click Yes
---------------
Jacob Skaria

" wrote:
Hi, I want to know how to use the instruction Range("RangeName").Value
= Output in a VBA function to paste in a spreadsheet a value which is
not the principal output of the function.

Thanks

UDF's can't stuff values into other cells. They return values to the cells that
contain the UDF call.

On the other hand, you could have a multicell array function that receives
values from the function. But that means you're entering an array formula into
that multicell range.

wrote:

Thanks Jacob. The value I need to paste in a cell is not the output
of the function. I want to paste a second value that the function
computes in a cell different from the one I typed the function. Is
this possible using a VBA function?

Thanks

On May 3, 12:08 am, Jacob Skaria
wrote:
You need to passback the output to the function. Refer the example below
Getword accepts two arguments and return a string. Note the last line in the
code where the output is assigned back to the function name.

A1 = "This is a test"
B1 = GetWord(A1,3)

Function GetWord(strTemp As String, lngPos As Long) as String
'Function to return the nth word from a text string..from a cell
If InStr(Trim(strTemp), " ") = 0 Then Exit Function
lngPos = lngPos - 1
arrTemp = Split(Trim(strTemp), " ")
If lngPos UBound(arrTemp) Or lngPos < 0 Then Exit Function
GetWord = arrTemp(lngPos)
End Function

--
If this post helps click Yes
---------------
Jacob Skaria

" wrote:
Hi, I want to know how to use the instruction Range("RangeName").Value
= Output in a VBA function to paste in a spreadsheet a value which is
not the principal output of the function.

Thanks

--

Dave Peterson

Try this. You can get the required value to a global variable within the
function...and have another function to retrieve that value..Something like
the below

A1 = "This is a test"
B1 = GetWord(A1,1)
C1 = GetWordLength()

Keep on changing the first word in A1 and see...

Insert a new module in VBE and copy the below code.


Dim intWordLength As Integer 'General Declarations
'------------------------------------
Function GetWordLength()
GetWordLength = intWordLength
Application.Volatile
End Function
'-----------------------------------------------
Function GetWord(strTemp As String, lngPos As Long) As String
'Function to return the nth word from a text string..from a cell
If InStr(Trim(strTemp), " ") = 0 Then Exit Function
lngPos = lngPos - 1
arrTemp = Split(Trim(strTemp), " ")
If lngPos UBound(arrTemp) Or lngPos < 0 Then Exit Function
intWordLength = Len(arrTemp(lngPos))
GetWord = arrTemp(lngPos)
End Function


If this post helps click Yes
---------------
Jacob Skaria


" wrote:

Thanks Jacob. The value I need to paste in a cell is not the output
of the function. I want to paste a second value that the function
computes in a cell different from the one I typed the function. Is
this possible using a VBA function?

Thanks

On May 3, 12:08 am, Jacob Skaria
wrote:
You need to passback the output to the function. Refer the example below
Getword accepts two arguments and return a string. Note the last line in the
code where the output is assigned back to the function name.

A1 = "This is a test"
B1 = GetWord(A1,3)

Function GetWord(strTemp As String, lngPos As Long) as String
'Function to return the nth word from a text string..from a cell
If InStr(Trim(strTemp), " ") = 0 Then Exit Function
lngPos = lngPos - 1
arrTemp = Split(Trim(strTemp), " ")
If lngPos UBound(arrTemp) Or lngPos < 0 Then Exit Function
GetWord = arrTemp(lngPos)
End Function

--
If this post helps click Yes
---------------
Jacob Skaria

" wrote:
Hi, I want to know how to use the instruction Range("RangeName").Value
= Output in a VBA function to paste in a spreadsheet a value which is
not the principal output of the function.

Thanks