How to pass cell reference to VBA function?

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

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


"[email protected]" <[email protected]> wrote
in message news:[email protected]...
>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.

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


"[email protected]" wrote:

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

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

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



"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "[email protected]" wrote:
>
>> 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" wrote:
> 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.

Good suggestion Zack, makes the code a little more bullet-proof.
--
Gary''s Student


"Zack Barresse" wrote:

> 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
>
> --
> Regards,
> Zack Barresse, aka firefytr
> To email, remove NOSPAM
>
>
>
> "Gary''s Student" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "[email protected]" wrote:
> >
> >> 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.
>
>
>

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]" <[email protected]> wrote
in message news:[email protected]...
> "Zack Barresse" wrote:
>> 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.