# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  How to pass cell reference to VBA function?

## joeu2004@hotmail.com

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

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


"joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com> wrote
in message news:9B7EBBB5-41F7-4B93-AD4B-1E42B88C62D1@microsoft.com...
>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

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


"joeu2004@hotmail.com" 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

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" <GarysStudent@discussions.microsoft.com> wrote in message
news:0489B8E6-AB8E-4155-B45E-07246196445F@microsoft.com...
> 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
>
>
> "joeu2004@hotmail.com" 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.

----------


## joeu2004@hotmail.com

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

----------


## Gary''s Student

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" <GarysStudent@discussions.microsoft.com> wrote in message
> news:0489B8E6-AB8E-4155-B45E-07246196445F@microsoft.com...
> > 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
> >
> >
> > "joeu2004@hotmail.com" 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

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

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


"joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com> wrote
in message news:5466B065-2CEE-4A14-A433-460BED5A0674@microsoft.com...
> "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.

----------

