Call VBA function from cell

Is it possible to call a VBA function from a cell on a worksheet?

For example:
- Cell A1 contains a date.
- I want Cell A2 to evaluate the date in A1 and then display the date
corresponding to the end of that financial quarter.
- So if A1 = February 23, 2005, I want A2 to return March 31, 2005.

I can write a Function in VBA that would return this value, I just don't
know how I would call it from within a cell on an Excel worksheet and pass
the value of A1.

Any help is appreciated.

Cheers,
Don

You need to set it up something like this:

In a VBA module you have your function:

Function GetQuarterEnd (RngDate as range) as Date
'Code to convert RngDate.Value to last day of Quarter
'the code should assign the last day of quarter to GetQuarterEnd e.g
GetQuarterEnd = myDateVariable
End Function

Then in A2 (formatted as date) you enter the formula =GetQuarterEnd(A1)

HTH
Rowan


"donesquire" wrote:

> Is it possible to call a VBA function from a cell on a worksheet?
>
> For example:
> - Cell A1 contains a date.
> - I want Cell A2 to evaluate the date in A1 and then display the date
> corresponding to the end of that financial quarter.
> - So if A1 = February 23, 2005, I want A2 to return March 31, 2005.
>
> I can write a Function in VBA that would return this value, I just don't
> know how I would call it from within a cell on an Excel worksheet and pass
> the value of A1.
>
> Any help is appreciated.
>
> Cheers,
> Don

Mmmm, I'm afraid this isn't working. When I enter the function name and
reference cell A1, I get the #NAME? error. Any thoughts?


"Rowan" wrote:

> You need to set it up something like this:
>
> In a VBA module you have your function:
>
> Function GetQuarterEnd (RngDate as range) as Date
> 'Code to convert RngDate.Value to last day of Quarter
> 'the code should assign the last day of quarter to GetQuarterEnd e.g
> GetQuarterEnd = myDateVariable
> End Function
>
> Then in A2 (formatted as date) you enter the formula =GetQuarterEnd(A1)
>
> HTH
> Rowan
>
>
> "donesquire" wrote:
>
> > Is it possible to call a VBA function from a cell on a worksheet?
> >
> > For example:
> > - Cell A1 contains a date.
> > - I want Cell A2 to evaluate the date in A1 and then display the date
> > corresponding to the end of that financial quarter.
> > - So if A1 = February 23, 2005, I want A2 to return March 31, 2005.
> >
> > I can write a Function in VBA that would return this value, I just don't
> > know how I would call it from within a cell on an Excel worksheet and pass
> > the value of A1.
> >
> > Any help is appreciated.
> >
> > Cheers,
> > Don

My mistake. I placed the code into "This Workbook" instead of into a Module.

Your sample code worked perfectly. Many thanks!


"donesquire" wrote:

> Mmmm, I'm afraid this isn't working. When I enter the function name and
> reference cell A1, I get the #NAME? error. Any thoughts?
>
>
> "Rowan" wrote:
>
> > You need to set it up something like this:
> >
> > In a VBA module you have your function:
> >
> > Function GetQuarterEnd (RngDate as range) as Date
> > 'Code to convert RngDate.Value to last day of Quarter
> > 'the code should assign the last day of quarter to GetQuarterEnd e.g
> > GetQuarterEnd = myDateVariable
> > End Function
> >
> > Then in A2 (formatted as date) you enter the formula =GetQuarterEnd(A1)
> >
> > HTH
> > Rowan
> >
> >
> > "donesquire" wrote:
> >
> > > Is it possible to call a VBA function from a cell on a worksheet?
> > >
> > > For example:
> > > - Cell A1 contains a date.
> > > - I want Cell A2 to evaluate the date in A1 and then display the date
> > > corresponding to the end of that financial quarter.
> > > - So if A1 = February 23, 2005, I want A2 to return March 31, 2005.
> > >
> > > I can write a Function in VBA that would return this value, I just don't
> > > know how I would call it from within a cell on an Excel worksheet and pass
> > > the value of A1.
> > >
> > > Any help is appreciated.
> > >
> > > Cheers,
> > > Don

You're welcome.

"donesquire" wrote:

> My mistake. I placed the code into "This Workbook" instead of into a Module.
>
> Your sample code worked perfectly. Many thanks!
>
>
> "donesquire" wrote:
>
> > Mmmm, I'm afraid this isn't working. When I enter the function name and
> > reference cell A1, I get the #NAME? error. Any thoughts?
> >
> >
> > "Rowan" wrote:
> >
> > > You need to set it up something like this:
> > >
> > > In a VBA module you have your function:
> > >
> > > Function GetQuarterEnd (RngDate as range) as Date
> > > 'Code to convert RngDate.Value to last day of Quarter
> > > 'the code should assign the last day of quarter to GetQuarterEnd e.g
> > > GetQuarterEnd = myDateVariable
> > > End Function
> > >
> > > Then in A2 (formatted as date) you enter the formula =GetQuarterEnd(A1)
> > >
> > > HTH
> > > Rowan
> > >
> > >
> > > "donesquire" wrote:
> > >
> > > > Is it possible to call a VBA function from a cell on a worksheet?
> > > >
> > > > For example:
> > > > - Cell A1 contains a date.
> > > > - I want Cell A2 to evaluate the date in A1 and then display the date
> > > > corresponding to the end of that financial quarter.
> > > > - So if A1 = February 23, 2005, I want A2 to return March 31, 2005.
> > > >
> > > > I can write a Function in VBA that would return this value, I just don't
> > > > know how I would call it from within a cell on an Excel worksheet and pass
> > > > the value of A1.
> > > >
> > > > Any help is appreciated.
> > > >
> > > > Cheers,
> > > > Don