I have a VBA function that is located in several Cells
[=myFunction(dataRange as Range)]. For the function to work during Excel
Recalculation process, I need to now the address that Excel is
recalculating. Say the formula is located in the 12 Cells, A1:B6. As Excel
works through the recalculation, my function needs the know the cell (A1:B6)
Excel is recalculating.

I tried

Set HomeCell = Range(ActiveCell.Address)

With this, I only get the cell where the cursor is located at the time Excel
starts the recalculation.

Thanks for the help

And you're calling the function from a formula in a worksheet cell?

If yes, look at Application.caller

Option Explicit
Function myFunc() As String
myFunc = Application.Caller.Address(external:=True)
End Function



"Bruce A. Julseth" wrote:

I have a VBA function that is located in several Cells
[=myFunction(dataRange as Range)]. For the function to work during Excel
Recalculation process, I need to now the address that Excel is
recalculating. Say the formula is located in the 12 Cells, A1:B6. As Excel
works through the recalculation, my function needs the know the cell (A1:B6)
Excel is recalculating.

I tried

Set HomeCell = Range(ActiveCell.Address)

With this, I only get the cell where the cursor is located at the time Excel
starts the recalculation.

Thanks for the help

--

Dave Peterson

Thank you, Thank you.

It worked like a charm..

Thanks again for the response


"Dave Peterson" wrote in message
...
And you're calling the function from a formula in a worksheet cell?

If yes, look at Application.caller

Option Explicit
Function myFunc() As String
myFunc = Application.Caller.Address(external:=True)
End Function



"Bruce A. Julseth" wrote:

I have a VBA function that is located in several Cells
[=myFunction(dataRange as Range)]. For the function to work during Excel
Recalculation process, I need to now the address that Excel is
recalculating. Say the formula is located in the 12 Cells, A1:B6. As
Excel
works through the recalculation, my function needs the know the cell
(A1:B6)
Excel is recalculating.

I tried

Set HomeCell = Range(ActiveCell.Address)

With this, I only get the cell where the cursor is located at the time
Excel
starts the recalculation.

Thanks for the help

--

Dave Peterson

Oops.. Just came across a problem with your code snippit..

I get a variable not defined(external) with Option Explicit set. Turn it off
and it runs..

I guess I turn it off with no problem. Do you have any other suggestion?

"Dave Peterson" wrote in message
...
And you're calling the function from a formula in a worksheet cell?

If yes, look at Application.caller

Option Explicit
Function myFunc() As String
myFunc = Application.Caller.Address(external:=True)
End Function



"Bruce A. Julseth" wrote:

I have a VBA function that is located in several Cells
[=myFunction(dataRange as Range)]. For the function to work during Excel
Recalculation process, I need to now the address that Excel is
recalculating. Say the formula is located in the 12 Cells, A1:B6. As
Excel
works through the recalculation, my function needs the know the cell
(A1:B6)
Excel is recalculating.

I tried

Set HomeCell = Range(ActiveCell.Address)

With this, I only get the cell where the cursor is located at the time
Excel
starts the recalculation.

Thanks for the help

--

Dave Peterson

"Option Explicit" at the top of a module means that you want VBA to check to
make sure each variable is declared (using Dim or Const or whatever)...

If you get a warning message that you have a variable that isn't defined, then
you should declare that variable.

Dim myVar as string 'or as long or data or whatever it is.

If you declare all your variables, you won't have to spend much time debugging a
line like:

ctrl = ctr1 + 1

Those two are separate variables--one ends with "ell" and one ends with "one".

At first, you may think that this is just a pain, but you'll soon find out that
it saves much more time than the alternative.

"Bruce A. Julseth" wrote:

Oops.. Just came across a problem with your code snippit..

I get a variable not defined(external) with Option Explicit set. Turn it off
and it runs..

I guess I turn it off with no problem. Do you have any other suggestion?

"Dave Peterson" wrote in message
...
And you're calling the function from a formula in a worksheet cell?

If yes, look at Application.caller

Option Explicit
Function myFunc() As String
myFunc = Application.Caller.Address(external:=True)
End Function



"Bruce A. Julseth" wrote:

I have a VBA function that is located in several Cells
[=myFunction(dataRange as Range)]. For the function to work during Excel
Recalculation process, I need to now the address that Excel is
recalculating. Say the formula is located in the 12 Cells, A1:B6. As
Excel
works through the recalculation, my function needs the know the cell
(A1:B6)
Excel is recalculating.

I tried

Set HomeCell = Range(ActiveCell.Address)

With this, I only get the cell where the cursor is located at the time
Excel
starts the recalculation.

Thanks for the help

--

Dave Peterson

--

Dave Peterson