Active cell referencing question

  • Thread starter Thread starter jwlabno
  • Start date Start date
J

jwlabno

Hello,
I would like to be able to reference active cell in another cell.
For example, I would like cell A1 to display the row number and cell B1 to
display the column of currently selected cell.
Ie. after selecting cell C5 the value in A1 should change to 5 and B1 should
change to 3 (or C) whichever is easier.

I wrote a macro

Function XRow
XRow=ActiveCell.Row
End Function

and entered A1
=XRow()

but this does not update as I select different cell.
Please Help
Jack
 
The active cell is the cell the function is in (A1), not the reference cell
(C5).


Try this:
In A1 put the formula: =row(c5)
In B1 put the formula:=column(c5)


Don Pistulka
 
Does it update if you hit "Enter" after selecting the 2nd cell?
Richard Choate

Hello,
I would like to be able to reference active cell in another cell.
For example, I would like cell A1 to display the row number and cell B1 to
display the column of currently selected cell.
Ie. after selecting cell C5 the value in A1 should change to 5 and B1 should
change to 3 (or C) whichever is easier.

I wrote a macro

Function XRow
XRow=ActiveCell.Row
End Function

and entered A1
=XRow()

but this does not update as I select different cell.
Please Help
Jack
 
Unless I have totally misinterpreted the OP's problem,
The active cell is the cell the function is in (A1), not the reference cell (C5).
Click to expand...

The cell the function is in would be the cell returned by Application.Caller. That may or may
not be the active cell.
In A1 put the formula: =row(c5)
In B1 put the formula: =column(c5)
Click to expand...

With these formulas, A1 will *always* contain 5 and B1 will contain 3. He wants what you call
the reference cell -- C5 was just his example -- to change every time he moves the cell cursor.

There's nothing wrong with the code he wrote. The problem is how to force that function to
recalculate when he changes the selection.
 
I wouldn't expect it to. Changing the selection doesn't cause a recalculation, and even if it
did, this function has no changing arguments and there's no Application.Volatile statement that
would cause it to recalculate. But see my other post for perhaps an easier way to force
recalculation.
 
Myrna, you are correct, his code is basically OK. I'm thinking he probably
will have to hit ENTER to make the function update unless he changes his
code.
Richard

Unless I have totally misinterpreted the OP's problem,
The active cell is the cell the function is in (A1), not the reference cell
Click to expand...
(C5).

The cell the function is in would be the cell returned by
Application.Caller. That may or may
not be the active cell.
In A1 put the formula: =row(c5)
In B1 put the formula: =column(c5)
Click to expand...

With these formulas, A1 will *always* contain 5 and B1 will contain 3. He
wants what you call
the reference cell -- C5 was just his example -- to change every time he
moves the cell cursor.

There's nothing wrong with the code he wrote. The problem is how to force
that function to
recalculate when he changes the selection.
 
Don,
Will your code cause the worksheet to recalculate every time he clicks on a
cell and sometimes make the worksheet behave poorly if you need to make a
lot of selection changes? I know that this type of code has had that effect
when I've seen it used in the past.
Richard

I took another look at your question. Put this in the sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("a1") = ActiveCell.Row
Range("b1") = ActiveCell.Column
End Sub

Don Pistulka
 
I've just had another thought:

Rewrite the function as

Function XRow(Dummy As Double)
XRow = ActiveCell.Row
End Function

And call it like this:

=XRow(Now())

If calculation is set to automatic, the function will now be called whenever the clock changes,
so it should recalculate perhaps every millisecond (I don't know what the resolution of the NOW
function is).

In theory I guess you could move the cursor faster than the clock changes, but try it and see if
that does the trick.

But I have another question: why do you want to do this??? Seems to me it will slow your
worksheet quite a bit.
 
Richard,

Obviously the target range can be limited if we knew what it was. If the
target range was column C, then we would include the line second line.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
Range("a1") = ActiveCell.Row
Range("b1") = ActiveCell.Column
End Sub

Don Pistulka
 
1. Formulas do not recalculate unless one of their arguments change. Your function has no
arguments.

2. Putting Application.Volatile at the top of the function will force this function to
recalculate, but only when Excel does a calculation because some other cell has changed.

3. Changing the selection doesn't, per se, force a recalculation.

If this function normally is used in only a single cell, use that event macro to put the row
into a cell, say A1. The code would look like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Row
End Sub

The code goes in the module for the particular worksheet where you want it to operate.

If you want to see the row number in more than one cell, you could use your existing code (with
an Application.Volatile instruction added at the top) plus the Worksheet_SelectionChange event
macro to force a recalculation,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub
 
Jack,
I'm guessing we can solve your problem in another manner, possibly without a
macro at all, if you will tell us what the bigger goal is.
Richard Choate

Hello,
I would like to be able to reference active cell in another cell.
For example, I would like cell A1 to display the row number and cell B1 to
display the column of currently selected cell.
Ie. after selecting cell C5 the value in A1 should change to 5 and B1 should
change to 3 (or C) whichever is easier.

I wrote a macro

Function XRow
XRow=ActiveCell.Row
End Function

and entered A1
=XRow()

but this does not update as I select different cell.
Please Help
Jack
 
Thank you all for replying. I tried to add NOW() as parameter to the macro
functions but the cell value does not change - obviously, simply changing
cell selection is not enough to force recalculation. I thought there is a
variable or
object that can be used in worksheet formula as reference to active cell
just like ActiveCell object is in macros.

I used SelectionChange event before and found it good but I wanted to avoid
it for a couple of reasons.
1. When I used it before, the sheet that contained event or my macro modules
often got corrupted and the whole sheet needed rebuilding on a regular
basis. It was rather painful so I hoped there is another way. The code
corruption problems seemed to have stopped after I removed event processing
from my sheets (I used SelectionChange and WorksheetChange).

2.Ideally, this sheet using activecell references, would be often deleted
and recreated by other macros, which means the event coding would be lost in
the process. The macro that deletes and recreates the sheet can place
formulas in cell A1, A2 but it can not (or at least I don't know how to),
put the event code back in the sheet module.

3. Performance consideration. Even if using selective processing (If
Target.Row=3 then FunctionName=ActiveCell.Row) I thought it might still slow
down the processing.

The big picture is, I copy and paste stock option information from my
broker's web site and process this information.
I make it look nice and clear, and add additional static information kept
elsewhere in the spreadsheet, about the stock and also produce some values
for each option (gamma, delta, theta, rho, vega, implied volatility and fair
value)

I do it by displaying all options for particular stock on my broker's site,
the Ctrl-A, Ctrl-C - highlight the whole screen, copy to clipboard, and
switch to excel, and invoke a macro by pressing a button placed on the
toolbar. The macro clears the temporary sheet by deleting and recreating
it, then using ClipboardPaste places the information copied from the web on
it and depending on what information was pasted, it invokes a series of
macros to format sheet, process information after formatting, and update
other sheets if required. I have found that Delete and Add on the temporary
sheet is better than ClearContents/ClearFormats - it keeps the worksheet
clean and lean. So this is why I wanted to avoid SelectionChange event - the
code will be lost each time the sheet is deleted and added.

It appears I can not do have both, automatic active cell reference and sheet
delete add. I think the solution will be to have a permanent sheet,
preformatted etc with the selection change code in it, and have the
information transferred from the temporary sheet after pasting, rather than
doing it directly on the temporary sheet.

This will keep selectionchange code in, and the performance issues can be
addressed by avoiding using Select method in macros that run on the sheet,
which will limit the invocation of the code to only when I click on cells
manually. It is slightly better than using custom function (ROWX) in
formulas, these also can slow down processing considerably (and are also
pain in the neck when debugging, as each time macro causes recalc the
debugger jumps to the function used in the cell formula)

The top 6 rows of the option display is the information related to all
options - that is, the underlying stock information, the bottom part
contains options, listed one line per option. Each line has option bid, ask,
price, expiry, strike, etc. I would like to able to click on the option name
(say cell A20) and cause the sheet to automatically display some more
information about that option in the top (static) part of the display. There
is several reasons for doing it this way, rather than listing these values
in each row alongside with the rest of the option-specific information.

1. I am running out of screen space, adding 7 more columns to the display
would either go off screen or if zoom was used, make all text too small and
hard to read.
2. These 7 values are not easily created. They are complicated and recursive
formulas and I wouldn't even try to attempt to calculate them for each row.
If each line contained 7 formulas to calculate them the recalc would
effectively kill it. So I resolved to have them in the top part of the
screen and calculate them on demand.

By "demand" I mean when I want to - and sure, I can have a button to invoke
macro, read the inputs, and display the values. But I am too lazy - this
would be a two step operation, I would have to first click on the selected
option row, or "trigger cell" then press the button to display - and I am
not happy with it. One step operation is much better - simple selecting the
trigger cell(s) should cause the values to appear on top. Two step operation
also requires one to remember to press the button, and if you forget to do
it, the values displayed on top could be interpreted as calculated for the
option currently selected where in fact they weren't.

Excel has a lot of "magic names" - predefined objects, variables and special
methods. One of those "magic names" is ActiveCell object in macros. But they
are not documented very well in the online help. For example, there is no
"ActiveCell" reference in my macro help file. Strange, but even database
search returns nothing. So I used to have a handy reference file I found on
the net which had them all listed, but I lost it.

The ideal solution would be such "magic name" for active cell that can be
referenced in workesheet formula without the use of any additional macro
coding but if there was, with the number of ideas I got in response, one of
you would mention it by now. So it looks like I have to settle for second
choice, selective use of SelectionChange event and avoid triggering it with
my macros

I would like to thank you all for taking time to read and solve my problem.
regards,
Jack
 
There's no need for any VBA, just use:

=CELL("ROW")

and

=CELL("COL")

Press F9 to update. (updates all formulae in sheet).
 
Back
Top