Mimicking ROW() function using VBA

[petereddy]

Hi,

I'm trying to broaden my horizons. Is there a way to mimic Excel's ROW() function in VBA? The following code is close:

Function MYROW()
    MYROW = Selection.Row
End Function

But if you have a range that spans more than two rows and hit Ctrl + Enter, it will only output the top row of the range.

That's not its only bug. For example, if you use it on a single cell (say A10, in which case the result is correctly 10), then select a row and delete it (say 5:5), MYROW() will take on the row number of the deleted row (5, in this example).

If you're up for it, is there a way to design a formula that outputs the formula of a targeted cell? Like my ROW()-mimicking function above, the following code is a start, but it has similar drawbacks:

Function GETFORMULA(ByVal Address)
    Dim myformula, leftmostbracket, rightmostbracket, mytarget
    myformula = Selection.Formula
    leftmostbracket = Application.Find("(", myformula)
    rightmostbracket = Application.Find(")", myformula)
    mytarget = Mid(myformula, leftmostbracket + 1, _
        rightmostbracket - leftmostbracket - 1)
    GETFORMULA = Range(mytarget).Formula
End Function

Is there a way to do this?

Thanks for your help.

 

[Fluff]

I don't understand what you are trying to do with the ROW() function, but you can re-write your GetFormula UDF like

Function GETFORMULA(Cl As Range)
    GETFORMULA = Cl.Formula
End Function

 

[petereddy]

That's great. Thanks, Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.

If you are suing Xl 2013 or newer you can just use the FORMULATEXT function, which will do the same thing.

 

[petereddy]

Good to know. I'm currently on Office 2010. We're moving to 2016 soon, so I'll keep that in mind when the upgrade takes place.

 

[Fluff]

You're welcome.

 

[mikerickson]

Regarding MyRow. It is keyed with the Selection, which is odd. If you want the UDF to refer to the cell holding the formula, you could use Application.Caller

Function MyRow2(optional aRange as Range) As Long

    If aRange is Nothing Then
        Set aRange = Application.Caller
    End Ifr

    MyRow2 = aRange.Row
End Function

When passed a multi-row range, Excel''s ROW returns the row number of the top-left cell. It sounds like you want something different than that.

 

[petereddy]

Thanks for pointing out the Caller property. I was wondering if there was something like that. Thanks.