Offset in VBA
Posted by Jack on December 17, 2001 11:09 AM
Tanks in advance, im trying to help a pal, with offset, and am struggling with a custom function, wondered if anyone can help, best i explain, offset is complex and all i need to do is reurn a diff cell to the function cell, so if =OFFSET(A3,0,-3,1,1) would reyurn the contence of A1, its that simple, but i would like to have so all he ned to do is type =NANA(C3) in C3 and return the text or number in A1 so i can add text from B1 and so on, by & : : & B1
Any help with custon Function to take care of this will sanme me BIG headaces, many many thanks
Posted by Gary on December 17, 2001 11:22 AM
The following function takes no arguments and returns the value in the cell 3 columns to the left.
Function NANA() As Variant
Application.Volatile
NANA = Application.Caller.Offset(0, -3).Value
End Function
So typing =NANA() in C3 will give the contents of C1 for example.
Posted by Jack on December 17, 2001 11:48 AM
How can i make argument say one for loaction offset to fix cell, that code woks perfect, just imtrested a big thanks
Posted by Gary on December 17, 2001 11:57 AM
If you change it to
Function NANA(Offset As Integer) As Variant
Application.Volatile
NANA = Application.Caller.Offset(0, Offset).Value
End Function
then the function accepts one argument - how many columns to offset by.
Posted by Juan Pablo G. on December 17, 2001 11:58 AM
As i've read on http://www.decisionmodels.com/ in the UDF's chapter, it's a very good thing to put every variable that the formula uses as an argument, and avoid, when possible, the use of Application.Volatile, because, this too, force Excel to recalculate the formula each time any cell changes, and not the one that specifically is used by the UDF, taking more time and resources.
That said, i would change the proposed UDF to this:
Public Function NANA(Rng as Range) as Variant
Dim DRow as Long
Dim DCol as Single
DRow = Application.Max(1,Rng.Row - 2)
DCol = Rng.Column
NANA = Cells(DRow,DCol)
End Function
The example isn't clear if the expected result is two rows up and two columns to the left, or only two rows up.
Juan Pablo G.
Posted by jack on December 17, 2001 12:30 PM
What do i say guys all good stuff, and i have loads to try, i really enjoy the challenge but my skills suffer sometimes, now to make all pretty with comments of waht to do in that box like MS foruuls, and so on, i must thanks you again,
Jack in UK
Posted by jack on December 17, 2001 1:04 PM
What do i say guys all good stuff, and i have loads to try, i really enjoy the challenge but my skills suffer sometimes, now to make all pretty with comments of waht to do in that box like MS foruuls, and so on, i must thanks you again,
Jack in UK
