Active Cell problem

[Bruce A. Julseth]

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]

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

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

 

[Bruce A. Julseth]

Thank you, Thank you.

It worked like a charm..

Thanks again for the response

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

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

 

[Bruce A. Julseth]

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?

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

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.

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?

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

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

 

[Bruce A. Julseth]

I'm sorry. I guess my response wasn't worded correctly.

You gave me a code snippet to solve a probem I had.

This is that snippet:

Option Explicit
Now, in your snippet, you have "external=True" as the argument for
Application.Caller.Address. The VBA
compiler is complaining about the argument, external. Now, since it is in
your snippet, I didn't know
external was "MY" variable. So, I guess I need to do a Dim:
Dim external as boolean

Is that right?

Also, do I really need the argument of "external=True". I removed it and
everything seems to be running good. But,
since I'm new at VBA programming, maybe, by removing it, someday ????????

Thanks for the resonse.

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

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?

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



:

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]

No.

I passed True to the external parm. This means that I wanted to see the
complete drive, path, workbook, worksheet and address.

I'm guessing that you didn't spell external correctly when you created your
function.

I'm sorry. I guess my response wasn't worded correctly.

You gave me a code snippet to solve a probem I had.

This is that snippet:

Option Explicit
Now, in your snippet, you have "external=True" as the argument for
Application.Caller.Address. The VBA
compiler is complaining about the argument, external. Now, since it is in
your snippet, I didn't know
external was "MY" variable. So, I guess I need to do a Dim:
Dim external as boolean

Is that right?

Also, do I really need the argument of "external=True". I removed it and
everything seems to be running good. But,
since I'm new at VBA programming, maybe, by removing it, someday ????????

Thanks for the resonse.

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

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?

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



:

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

 

[Bruce A. Julseth]

Here is a copy/paste of what is in my code.

HomeAddress = Application.Caller.Address(external = True)

I'm still getting the "Variable not defined" on external, so it is spelled
correctly.

Appreciate any other suggestions you might have for me to try.

Thanks again for the response.

No.

I passed True to the external parm. This means that I wanted to see the
complete drive, path, workbook, worksheet and address.

I'm guessing that you didn't spell external correctly when you created
your
function.

I'm sorry. I guess my response wasn't worded correctly.

You gave me a code snippet to solve a probem I had.

This is that snippet:

Option Explicit

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

Now, in your snippet, you have "external=True" as the argument for
Application.Caller.Address. The VBA
compiler is complaining about the argument, external. Now, since it is in
your snippet, I didn't know
external was "MY" variable. So, I guess I need to do a Dim:
Dim external as boolean

Is that right?

Also, do I really need the argument of "external=True". I removed it and
everything seems to be running good. But,
since I'm new at VBA programming, maybe, by removing it, someday ????????

Thanks for the resonse.

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

:

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?

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



:

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]

Notice the : (colon) in the suggested line:

That's gonna make a lot(!) of difference.

ps. Lots of times when you're testing, it's good to just copy|Paste from the
message to the code window. Then you don't have to worry about making a typo.
(You only have to worry about the author of the suggestion making a typo <vbg>.)

Here is a copy/paste of what is in my code.

HomeAddress = Application.Caller.Address(external = True)

I'm still getting the "Variable not defined" on external, so it is spelled
correctly.

Appreciate any other suggestions you might have for me to try.

Thanks again for the response.

No.

I passed True to the external parm. This means that I wanted to see the
complete drive, path, workbook, worksheet and address.

I'm guessing that you didn't spell external correctly when you created
your
function.

I'm sorry. I guess my response wasn't worded correctly.

You gave me a code snippet to solve a probem I had.

This is that snippet:

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

Now, in your snippet, you have "external=True" as the argument for
Application.Caller.Address. The VBA
compiler is complaining about the argument, external. Now, since it is in
your snippet, I didn't know
external was "MY" variable. So, I guess I need to do a Dim:
Dim external as boolean

Is that right?

Also, do I really need the argument of "external=True". I removed it and
everything seems to be running good. But,
since I'm new at VBA programming, maybe, by removing it, someday ????????

Thanks for the resonse.

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

:

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?

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



:

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