Excel VBA and named ranges

DiscoZombie

So in Excel, say you name column A "MyRange" and fill it with numbers. Then, if in a cell in column B, you put "=MyRange * 2". It's smart enough to know that you only want to multiply the cell in same row you typed that formula in by 2, not the entire named range.

How do I get my custom function in VBA to use this same logic?

If I create a function like so:

function MyMult(x)
MyMult = x * 2
end function

and then try to call it in a cell: "=MyMult(MyRange)"

it bombs out because MyRange gets passed as the entire range, not as just the cell in the range that shares a row with the cell I'm typing the formula in.

How can I pass to a formula the cell in a named range that shares the same row with the cell that contains the formula, as opposed to passing the entire range?

I guess I could add a second parameter to the function that accepts a row number, but it would be annoying/ugly to have to put something like "=MyMult(MyRange,row())" everywhere I want to use my custom function...

Pheezer

Tell it which cell in MyRange you want to reference. You can probably do MyRange.Cells(y,x).Value
So you'd do something like
function MyMult(x)
MyMult = x.cells(y,x).value * 2
end function

I don't have Excel in front of me, but there's a way to get the column and row numbers of the ActiveCell, which is what you'd put in for y and x. Probably something like ActiveCell.Address.Column() and ActiveCell.Address.Row(). So like x.Cells(ActiveCell.Address.Column(), ActiveCell.Address.Row()).Value * 2