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