Pass by ref?

[megaxoom]

Can I write a function that after calling a function with a parameter, the paramter value is upated?

For example:

Function test(startRow as integer)
startRow = startRow + 5
....
test = some_value
End Function

Function testByRef()
startRow = 2
someValue = test(startRow)
testByRef = startRow
End Function


And when I call =testByRef(), the result is 2, but I want it to be 7 (i.e I want the startRow value to be updated after calling the test(startRow) function.)

Thanks for any help.

[NateO]

The way you've written the function is somewhat circular, and to simplify, use one function:

e.g,

Function testByRef2(ByVal test As Integer)

startrow1 = 2

testByRef2 = startrow1 + test

End Function

Where

=testByRef2(5) equals 7

If you want to test a range's value from the offset, you can use the offset in a two parameter function to accomplish this in a single function. Application.Volitle tells the function to 'be on its toes,' it will recalc. when your spreadsheet calculates:

Function testByRef(strtrw As Range, ofst As Long) As Double

'strtrw is cell to reference, ofst is number of rows to check

Application.Volatile

testByRef = strtrw.Offset(ofst).Value

End Function

Use the following formula:

=testbyref(B5,4)

Hope this helps.

Edit: The last function is basically a rewrite of Excels built in =Offset() function, e.g., =OFFSET(B4,4,,,)
_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-19 16:52 ]