Alternatives to GET.CELL and VB?
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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> skrev
i en meddelelse
news:[email protected]...
>
> 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.
Oh, that is a neat idea, it may work in many cases. Unfortunately, that would only work if the cell is not empty.Originally Posted by Leo Heuser
--
Jason Whitehurst
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!).
On Wed, 17 May 2006 17:45:06 -0500, whitehurst
<[email protected]> 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 <[email protected]>
wrote:
>On Wed, 17 May 2006 17:45:06 -0500, whitehurst
><[email protected]> 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" <[email protected]> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks