Solved

What is =GET.CELL()? What is Excel4?

Posted on 2014-01-31
4
3,735 Views
Last Modified: 2014-02-28
Hello,

Some time ago, I posted a question asking if in Excel, there was any way to simultaneously view both the formula and the resulting value in a given cell. I was aware of the option to view only values (which is the default setting) or formulas* but I was hoping to find a way to view both simultaneously.

The solution provided by one of the experts (imnorie) works great and I use it all the time. However, it involves an unusual process and syntax I had not seen before or since:

    1) enter a formula into a cell (say A1)
    2) select another cell (say B1) where you would like A1's formula to be displayed
    3) open the Name Manager
    4) click "New"
    5) enter any name in the "Name:" box, eg "ShowFormula"
    6) in the "Refers to:" box, enter =GET.CELL(6,A1)
    7) click OK
    8) close the Name Manager
    9) in B1 enter: =ShowFormula

This is really a great trick and as mentioned, I use it all the time — especially when building formulas which are large and/or confusing. It's made me wonder what other great tricks may be available using a similar format.

By the way, in the original thread there were a couple of comments which mentioned something called "Excel4".

Questions:
What type of formula is this?
What is the significance of the number 6 as the first argument?
Was Excel4 a previous version or does it refer to something else?
Does anyone have a list of similar formulas or know of a good place to find similar useful formulas?


Thanks

*In Excel 2013: File > Options > Advanced > scroll to
"Display options for this worksheet:" > then tick
"Show formulas in cells instead of their characteristic results"
0
Comment
Question by:Steve_Brady
4 Comments
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 166 total points
ID: 39825514
The 6 means:

Formula in reference, as text, in either A1 or R1C1 style depending on the workspace setting.

Excel4 refers to Excel 4.0 (XLM) macros - these are the macro functions of Excel 4
0
 
LVL 19

Assisted Solution

by:Ken Butters
Ken Butters earned 167 total points
ID: 39825531
Very interesting ... Took some digging to figure this out.

All of these are called XLM macro's ... meaning that they are an old version of macros predating VBA.

I run Windows 7, and Windows 7 does not have a way to read the help files in this download, so you also need to download a "reader" for the help file if you want to view it.

You can download and install the help file "reader" for your version of your OS here:
http://support.microsoft.com/kb/917607

You can download and install the actual XLM HELP file here:
http://support.microsoft.com/kb/128185

Once you finally get navigated to the right help file for get.cell here is the info provided in the Microsoft help: (it is formatted nicer than here, as I copied and pasted it into this window, so I have plain text as a result.  

NOTE : I am surrounding it with 'Code' markers... hopefully that will make it easier to read.

But following the above steps should get you to be able to display this info for yourself in it's original format.


Macro Sheets Only
Returns information about the formatting, location, or contents of a cell. Use GET.CELL in a macro whose behavior is determined by the status of a particular cell.

Syntax

GET.CELL(type_num, reference)
Type_num    is a number that specifies what type of cell information you want. The following list shows the possible values of type_num and the corresponding results.

Type_num	Returns

1	Absolute reference of the upper-left cell in reference, as text in the current workspace reference style.
2	Row number of the top cell in reference.
3	Column number of the leftmost cell in reference.
4	Same as TYPE(reference).
5	Contents of reference.
6	Formula in reference, as text, in either A1 or R1C1 style depending on the workspace setting.
7	Number format of the cell, as text (for example, "m/d/yy" or "General").
8	Number indicating the cell's horizontal alignment:
1 = General 
2 = Left 
3 = Center 
4 = Right 
5 = Fill
6 = Justify
7 = Center across cells
9	Number indicating the left-border style assigned to the cell:
0 = No border
1 = Thin line
2 = Medium line
3 = Dashed line
4 = Dotted line
5 = Thick line
6 = Double line
7 = Hairline

10	Number indicating the right-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.
11	Number indicating the top-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.
12	Number indicating the bottom-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.
13	Number from 0 to 18, indicating the pattern of the selected cell as displayed in the Patterns tab of the Format Cells dialog box, which appears when you choose the Cells command from the Format menu. If no pattern is selected, returns 0.
14	If the cell is locked, returns TRUE; otherwise, returns FALSE.
15	If the cell's formula is hidden, returns TRUE; otherwise, returns FALSE.
16	A two-item horizontal array containing the width of the active cell and a logical value indicating whether the cell's width is set to change as the standard width changes (TRUE) or is a custom width (FALSE).
17	Row height of cell, in points.
18	Name of font, as text.
19	Size of font, in points.

20	If all the characters in the cell, or only the first character, are bold, returns TRUE; otherwise, returns FALSE.
21	If all the characters in the cell, or only the first character, are italic, returns TRUE; otherwise, returns FALSE.
22	If all the characters in the cell, or only the first character, are underlined, returns TRUE; otherwise, returns FALSE.
23	If all the characters in the cell, or only the first character, are struck through, returns TRUE; otherwise, returns FALSE.
24	Font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0.
25	If all the characters in the cell, or only the first character, are outlined, returns TRUE; otherwise, returns FALSE. Outline font format is not supported by Microsoft Excel for Windows.
26	If all the characters in the cell, or only the first character, are shadowed, returns TRUE; otherwise, returns FALSE. Shadow font format is not supported by Microsoft Excel for Windows.

27	Number indicating whether a manual page break occurs at the cell:
0 = No break
1 = Row
2 = Column
3 = Both row and column
28	Row level (outline).
29	Column level (outline).
30	If the row containing the active cell is a summary row, returns TRUE; otherwise, returns FALSE.
31	If the column containing the active cell is a summary column, returns TRUE; otherwise, returns FALSE.
32	Name of the workbook and sheet containing the cell If the window contains only a single sheet that has the same name as the workbook without its extension, returns only the name of the book, in the form BOOK1.XLS. Otherwise, returns the name of the sheet in the form "[Book1]Sheet1".
33	If the cell is formatted to wrap, returns TRUE; otherwise, returns FALSE.
34	Left-border color as a number in the range 1 to 56. If color is automatic, returns 0.
35	Right-border color as a number in the range 1 to 56. If color is automatic, returns 0.

36	Top-border color as a number in the range 1 to 56. If color is automatic, returns 0.
37	Bottom-border color as a number in the range 1 to 56. If color is automatic, returns 0.
38	Shade foreground color as a number in the range 1 to 56. If color is automatic, returns 0.
39	Shade background color as a number in the range 1 to 56. If color is automatic, returns 0.
40	Style of the cell, as text.
41	Returns the formula in the active cell without translating it (useful for international macro sheets).
42	The horizontal distance, measured in points, from the left edge of the active window to the left edge of the cell. May be a negative number if the window is scrolled beyond the cell.
43	The vertical distance, measured in points, from the top edge of the active window to the top edge of the cell. May be a negative number if the window is scrolled beyond the cell.

44	The horizontal distance, measured in points, from the left edge of the active window to the right edge of the cell. May be a negative number if the window is scrolled beyond the cell.
45	The vertical distance, measured in points, from the top edge of the active window to the bottom edge of the cell. May be a negative number if the window is scrolled beyond the cell.
46	If the cell contains a text note, returns TRUE; otherwise, returns FALSE.
47	If the cell contains a sound note, returns TRUE; otherwise, returns FALSE.
48	If the cells contains a formula, returns TRUE; if a constant, returns FALSE.
49	If the cell is part of an array, returns TRUE; otherwise, returns FALSE.
50	Number indicating the cell's vertical alignment:
1 = Top 
2 = Center 
3 = Bottom 
4 = Justified
51	Number indicating the cell's vertical orientation:
0 = Horizontal
1 = Vertical
2 = Upward
3 = Downward
52	The cell prefix (or text alignment) character, or empty text ("") if the cell does not contain one.

53	Contents of the cell as it is currently displayed, as text, including any additional numbers or symbols resulting from the cell's formatting.
54	Returns the name of the PivotTable view containing the active cell.
55	Returns the position of a cell within the PivotTableView.
56	Returns the name of the field containing the active cell reference if inside a PivotTable view.
57	Returns TRUE if all the characters in the cell, or only the first character, are  formatted with a superscript font; otherwise, returns FALSE.
58	Returns the font style as text of all the characters in the cell, or only the first character as displayed in the Font tab of the Format Cells dialog box: for example, "Bold Italic".
59	Returns the number for the underline style:
	1 = none
2 = single
3 = double
4 = single accounting
5 = double accounting

60	Returns TRUE if all the characters in the cell, or only the first characrter, are formatted with a subscript font; otherwise, it returns FALSE.
61	Returns the name of the PivotTable item for the active cell, as text.
62	Returns the name of the workbook and the current sheet in the form "[book1]sheet1".
63	Returns the fill (background) color of the cell.
64	Returns the pattern (foreground) color of the cell.
65	Returns TRUE if the Add Indent alignment option is on (Far East versions of Microsoft Excel only); otherwise, it returns FALSE.
66	Returns the book name of the workbook containing the cell in the form BOOK1.XLS.

Reference    is a cell or a range of cells from which you want information.

If reference is a range of cells, the cell in the upper-left corner of the first range in reference is used.
	If reference is omitted, the active cell is assumed.

Tip	Use GET.CELL(17) to determine the height of a cell and GET.CELL(44) - GET.CELL(42) to determine the width.

Examples

The following macro formula returns TRUE if cell B4 on sheet Sheet1 is bold:

GET.CELL(20, Sheet1!$B$4)


You can use the information returned by GET.CELL to initiate an action. The following macro formula runs a custom function named BoldCell if the GET.CELL formula returns FALSE:

IF(GET.CELL(20, Sheet1!$B$4), , BoldCell())

Open in new window

0
 
LVL 80

Accepted Solution

by:
byundt earned 167 total points
ID: 39825688
Excel 4 came out in 1992. It had a macro language so it could compete with Lotus 1-2-3, which also had a macro language. Although the Excel 4 macro language was sufficiently powerful for professional developers to produce a number of macros for large companies, the technology was not a mainstream success.

For Excel 5, which came out in 1993, Microsoft tried a different tack. They took a modified version of their Visual Basic programming language and added objects, methods and properties appropriate for the worksheet user interface. They also added a macro recorder, so ordinary users could record a macro for future use. The so-called Visual Basic for Applications (VBA) macro language proved enormously more popular than Excel 4 macros.

It didn't take long for Microsoft to realize that they needed to support Excel 4 macros for their enterprise customers who had paid good money for them to be written. But for everybody else, Excel 4 macros were of little use. To discourage the use of Excel 4 macros, Microsoft removed the Help with Excel 2000 and the ability to create new Excel 4 module sheets with Excel 2007.

Obviously, Microsoft has a long-term interest of avoiding the expense of continuing to support Excel 4 macros. A few years back, they asked the Excel MVPs what things could be done in Excel 4 macros that either couldn't be done or were inefficient in VBA. These tasks included macros using PageSetup (much slower in VBA than Excel 4 macros) and getting the page number that a cell will print out on. They then added features to the VBA object model so you wouldn't need to resort to Excel 4 macros.

It's fair to say that Excel 4 macro compatibility is on borrowed time. It's remarkable that it is still supported (partially) in Excel 2013, over 20 years after having been superseded by VBA. If you want to future-proof your workbooks, don't use Excel 4 macros. Replace them instead with VBA subs and user-defined functions that offers similar functionality. Here is one that returns the formula (as text) of the cell to its immediate left:
=GetFormula()
Function GetFormula() As String
Dim cel As Range
On Error Resume Next
Set cel = Application.Caller
If Not cel Is Nothing Then GetFormula = cel.Offset(0, -1).Formula
On Error GoTo 0
End Function

Open in new window

0
 

Author Closing Comment

by:Steve_Brady
ID: 39896375
Great comments. Thanks for the feedback.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now