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

Call VBA function from cell
 

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

Call VBA function from cell
 

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

Call VBA function from cell
 

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

Call VBA function from cell
 

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