How to pass cell reference to VBA function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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.
 
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
 
Zack Barresse said:
Function MyCell(rngRef As Range) As Variant
[....]
MyCell = rngRef.Formula
Click to expand...

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.
 
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


[email protected] said:
Zack Barresse said:
Function MyCell(rngRef As Range) As Variant
[....]
MyCell = rngRef.Formula
Click to expand...

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.
Click to expand...
 
Back
Top