Hello all,

Has anybody created a listing of possible alternative options for the
various GET.CELL functions? Ideally, since it gives the annoying
warning message at startup, I would prefer not to use the outdated
GET.CELL function.

I am specifically interested in determining if a given cell is
currently visible (height=0). I can do this with GET.CELL or with a
very simple VB Macro - I am just wondering if there is a way to
accomplish the task just through built-in excel functions.

Thanks!


--
whitehurst
------------------------------------------------------------------------
whitehurst's Profile: http://www.excelforum.com/member.php...o&userid=34544
View this thread: http://www.excelforum.com/showthread...hreadid=543120

"whitehurst" wrote
in message ...

Hello all,

Has anybody created a listing of possible alternative options for the
various GET.CELL functions? Ideally, since it gives the annoying
warning message at startup, I would prefer not to use the outdated
GET.CELL function.

I am specifically interested in determining if a given cell is
currently visible (height=0). I can do this with GET.CELL or with a
very simple VB Macro - I am just wondering if there is a way to
accomplish the task just through built-in excel functions.

Thanks!

No built-in way to get height. There is a way to get width:
=CELL("width",A1)

Kind of makes you wonder why you can get width but not height!

it gives the annoying warning message at startup

I use GET.CELL on occasion and never get a warning message.

What does the message say?

Biff

Biff wrote...
....
No built-in way to get height. There is a way to get width:
=CELL("width",A1)

Kind of makes you wonder why you can get width but not height!
....

Because CELL goes way back, at least to XL2 if not all the way to XL1.
It was there for compatibility with Lotus 123 Release 2.x. Excel's CELL
hasn't changed in 19 years. 123's @CELL function, OTOH, can return row
height, text color, cell background color, whether the cell contains a
formula evaluating to a number, label or error, bold, italic,
underline, etc.

Lotus 123 Release 2.x didn't have different row heights (unless you
loaded the 3rd party Always add-in), so there was no point to having
@CELL return height. However, 123 Release 3.x included an add-in named
WYSIWYG which did change row heights, and SURPRISE! 123 Release 3
included height, text color, . . .

Why didn't Microsoft change/improve Excel's CELL function? When the
lemmings are paying you for doing nearly squat all, why work?

"whitehurst" skrev
i en meddelelse
...

Hello all,


I am specifically interested in determining if a given cell is
currently visible (height=0). I can do this with GET.CELL or with a
very simple VB Macro - I am just wondering if there is a way to
accomplish the task just through built-in excel functions.

Thanks!


--
whitehurst


Hi

If you have Excel 2003, you can use this formula:

=IF(SUBTOTAL(103,C2),"Visible","Hidden")

or just

=SUBTOTAL(103,C2)

Returning 1 for visible or 0 for hidden.

Before Excel 2003 you can only see if a cell
is hidden as a result of using filter with the formula

=IF(SUBTOTAL(3,C2),"Visible","Hidden")

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Leo Heuser Wrote:

=SUBTOTAL(103,C2)


Oh, that is a neat idea, it may work in many cases. Unfortunately,
that would only work if the cell is not empty.

--
Jason Whitehurst


--
whitehurst
------------------------------------------------------------------------
whitehurst's Profile: http://www.excelforum.com/member.php...o&userid=34544
View this thread: http://www.excelforum.com/showthread...hreadid=543120

Biff Wrote:

I use GET.CELL on occasion and never get a warning message.

What does the message say?

Biff


Oh, it is the warning that Excel 4.0 Macros are in use. You probably
just turn security to LOW so that the warning is ignored. I like to
know when spreadsheets I obtain from others are using old macros - so I
don't care to turn the warning off (unless it is my own worksheet!).


--
whitehurst
------------------------------------------------------------------------
whitehurst's Profile: http://www.excelforum.com/member.php...o&userid=34544
View this thread: http://www.excelforum.com/showthread...hreadid=543120

On Wed, 17 May 2006 17:45:06 -0500, whitehurst
wrote:


Hello all,

Has anybody created a listing of possible alternative options for the
various GET.CELL functions? Ideally, since it gives the annoying
warning message at startup, I would prefer not to use the outdated
GET.CELL function.

I am specifically interested in determining if a given cell is
currently visible (height=0). I can do this with GET.CELL or with a
very simple VB Macro - I am just wondering if there is a way to
accomplish the task just through built-in excel functions.

Thanks!

Try this:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=XLM.GET.CELL(17,cell_ref)


--ron

On Fri, 19 May 2006 21:59:41 -0400, Ron Rosenfeld
wrote:

On Wed, 17 May 2006 17:45:06 -0500, whitehurst
wrote:


Hello all,

Has anybody created a listing of possible alternative options for the
various GET.CELL functions? Ideally, since it gives the annoying
warning message at startup, I would prefer not to use the outdated
GET.CELL function.

I am specifically interested in determining if a given cell is
currently visible (height=0). I can do this with GET.CELL or with a
very simple VB Macro - I am just wondering if there is a way to
accomplish the task just through built-in excel functions.

Thanks!

Try this:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=XLM.GET.CELL(17,cell_ref)


--ron

Sorry, I did not see you wanted to do that just with built-in functions. But I
believe Longre's XLM.GET.CELL function has some advantages over the "built-in"
GET.CELL function.
--ron

Ron Rosenfeld wrote...
....
Sorry, I did not see you wanted to do that just with built-in functions. But I
believe Longre's XLM.GET.CELL function has some advantages over the "built-in"
GET.CELL function.

So what would these advantages be?

Don't get me wrong. I view the entire MOREFUNC.XLL add-in as essential,
but I don't need to share most of my workbooks with other users, so I
don't have the headache of making sure other potential users have it
installed on their PCs. As for embedding MOREFUNC.XLL, wouldn't that
change XLL calls to udf calls? If so, wouldn't that SLOW DOWN
recalculation?

On 22 May 2006 13:39:53 -0700, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
...
Sorry, I did not see you wanted to do that just with built-in functions. But I
believe Longre's XLM.GET.CELL function has some advantages over the "built-in"
GET.CELL function.

So what would these advantages be?

Don't get me wrong. I view the entire MOREFUNC.XLL add-in as essential,
but I don't need to share most of my workbooks with other users, so I
don't have the headache of making sure other potential users have it
installed on their PCs. As for embedding MOREFUNC.XLL, wouldn't that
change XLL calls to udf calls? If so, wouldn't that SLOW DOWN
recalculation?

Harlan,

I seem to recollect that there are some ways of causing Excel pre-XP versions
to crash when using the old Macros under certain circumstances. I seem to
think that it was you that wrote this and that the problem had been fixed in
XP.

Since I have XP, I have no way of checking to see if this is an issue with
Longre's add-in.

Excel 4.0 GET.CELL has 53 information types; Longre's XLM.GET.CELL has 66
types.

So far as whether or not embedding Morefunc.xll will change XLL calls to udf
calls, I'm not knowledgeable enough to be sure. Perhaps you can tell from
morefunc HELP regarding embedding:

==========================================
Embedding Morefunc in a workbook has the following consequences*:


· It adds a "very hidden" worksheet ("Morefunc Storage Sheet") to the
workbook. The add-in itself and the help file are stored in this sheet as
binary data.

· It adds a small standard module named modRestoreMorefunc to the VBA
project of the workbook.

· It inserts a call to the MorefuncTempInstall Sub in the Workbook_Open
event handler of the workbook.

None of these 3 items should be removed or altered, otherwise the new functions
won't work.

When the workbook is opened, the MorefuncTempInstall sub performs these tasks*:


· It checks if Morefunc is already installed (and loaded) in the current
Excel instance

· If Morefunc is already loaded, it compares its version number with the
one of the Morefunc add-in stored in the workbook.

· If the version of the workbook is more recent (or if Morefunc is not
installed), it reads the binary data stored in the hidden sheet, creates a
Morefunc.xll file in the temporary folder and opens it.
=============================================

Best wishes,
--ron