How to paste values in a spreadsheet with a VBA function

[davidotero159]

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

 

[Jacob Skaria]

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

 

[davidotero159]

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

 

[Dave Peterson]

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.

 

[Jacob Skaria]

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