How to pass cell reference to VBA function?

[Guest]

I want to define something like:

function mycell(ref)
mycell = range(ref).something
end function

That works if I call it from a spreadsheet formula
in the form mycell("A1"). But I want to call it in
the form mycell(A1) -- no quotes -- so that mycell()
is recomputed if A1 changes.

How can I do that? That is, how do I declare "ref",
and/or how do I pass A1 so that it can be used in
the VBA range() function, without passing "A1" as
a string?

Note: I am using Office Excel 2003, if that matters.

 

[Zack Barresse]

Hi there,

Declare it as a Range variable..

Function MyCell(rngRef As Range) As Variant
MyCell = rngRef.Address
MyCell = rngRef.Formula
MyCell = rngRef.Value
End Function

Or use whatever parameter you want. You couuld use a Variant instead of a
Range which should use both. It gets a little trickier when trying to code
for it though, but is used that way quite a bit.

HTH

 

[Guest]

You need the double quotes because you are passing in a string. Something
like:

Function plus1(r As Range) As Long
plus1 = r.Value + 1
End Function

will allow you to use =plus1(A1) in the worksheet.

 

[Zack Barresse]

Gary's Student,

They are actually passing it as a Variant. But using it as
Range(ref).Something will assume it as a string. They could use
ref.Something and it would work the other way. Maybe a more prolific way
would be ...

Function MyCell(ref As Variant) As Variant
If TypeName(ref) = "String" Then
MyCell = Range(ref).Value
ElseIf TypeName(ref) = "Range" Then
MyCell = ref.Value
End If
End Function

HTH

 

[Guest]

Function MyCell(rngRef As Range) As Variant
[....]
MyCell = rngRef.Formula

Klunk! I had tried "ref as Range", but I thought it
did not work because "msgbox ref" displayed the
value instead of the address. I guess "ref" alone
defaults to ref.Value. Klunk!

Thanks for a clear and succinct explanation.

 

[Guest]

Good suggestion Zack, makes the code a little more bullet-proof.

 

[Zack Barresse]

Yeah, it defaults to Value. Just like ..

Range("A1")

Defaults to ..

ActiveSheet.Range("A1")

And the same goes for the workbook. If not specified (and not required)
Excel will *assume* for you. That's why it's best, if there is a
possibility of going to another worksheet/book, to always explicitly qualify
your references.

Glad it works for you.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM

Function MyCell(rngRef As Range) As Variant
[....]
MyCell = rngRef.Formula

Klunk! I had tried "ref as Range", but I thought it
did not work because "msgbox ref" displayed the
value instead of the address. I guess "ref" alone
defaults to ref.Value. Klunk!

Thanks for a clear and succinct explanation.