Info only - get.cell arguments

[PaddyD]

Get.cell (an old xl 4 macro function) can be used to return more info about the worksheet environment than is available with the cell() function. No VBA code (or skills) are required. One complication is that you cannot use get.cell directly in the worksheet. However, there is a work-around...

The method is as follows:

1) Determine which of the get.cell arguments you need - see the full list below.
2) Go to Insert | Name | Define.
3) Type a suitably descriptive name (e.g. CellHasFormula, or CellFont etc)
5) In the refers to box, type something of the following format:

=GET.CELL(48,INDIRECT("rc",FALSE))

This will return info about the cell the final formula is in. To use get.cell to return info about cells other than the ones the formulas are in, , you will need to use on offset, e.g.:

=GET.CELL(48,OFFSET(INDIRECT("RC",FALSE),0,1))

6) enter =CellHasFormula etc in the desired cells.

What follows is a full list of the get.cell arguments. The full help file for excel 4 macros is available here:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q128185&ID=KB;EN-US;Q128185&FR=1<pre>

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())</pre>
This message was edited by PaddyD on 2002-09-05 22:02

 

[Tazguy37]

Very nice work, guys!

I did notice that the fill color wasn't included, so I added that in with a Select Case almost exactly like that for the font color. Really great stuff!

 

[T. Valko]

See also:

http://makeashorterlink.com/?E27D155B1

Dead link

 

[Justinaschwarz]

Thank you for the tips, because it really worked on my iPad with the same problem! Maybe it is some kind of software issue but the solution is great.

 

[Zyg D]

Well, I have tried one thing which PaddyD has offered 11 years ago It seems that it is not working.

Particularly this thing:

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

I thought it would bold the text in a cell straight from another cell.
I have included the function into Name Manager as a function called Bolding. In a new module I have created the sub BoldCell() which actually bolds B4 cell's text (also tried to change sub to function). After this was done I've included =Bolding in a cell and got the result of 0 (in both cases). So text in a cell cannot be bolded using function in a worksheet. Maybe there's something wrong I've done? Another question - is there a way to use GET.CELL in VBA? I dont really get point of having this functionality in such a strange place - it is not a built-in spreadsheet's function nor it can be used in VBA. Maybe earlier it was possible to use it in VBA Editor? I'm using 2013 Office.

 

[RoryA]

You can't bold a cell using a formula, and there is generally no need to try and call GET.CELL from VBA as there are equivalents in the object model for most things.

 

[ipsyd]

PaddyD,

Your original post was referred to me by a MVP in another thread (http://www.mrexcel.com/forum/excel-...ins-formula-opposed-constant.html#post4618522 ), and it gave me the crucial information I needed to find the one-and-only possible solution to a problem that had been bugging me for months. Very very grateful for your 14-years-ago post!

Huge kudos!!

 

[haleba]

Very busy working on a project that required this exact solution, but dropping everything to reply to say Thank You!!!!

A Seventeen year old thread that is still getting replies because ... Excel ... why does the CELL function not return all the information about cell contents like the GET.CELL macro function???

Browsing the replies I noticed a lot of dead links, so to show my appreciation I wanted to add currently live (as of 1/17/2019) links concerning Excel macro functions.

The best resource I found is Excel Off the Grid's "Using Excel 4 Macro Functions" (https://exceloffthegrid.com/using-excel-4-macro-functions/) that gives excellent instructions on how to implement them as well as introducing me to the concept of relative named ranges, the subject of their link "Creating relative named ranges" (https://exceloffthegrid.com/creating-relative-named-ranges/).

The greatest bonus from this resource is a link to My Online Training Hub's Phil Treacy's monumental 653 page Excel 4.0 Macro Functions Reference: A Comprehensive List of Microsoft Excel 4.0 Macro Functions, a resource that I've been wanting to explain a couple of macro functions that I've found online that were given with any explanation.

A couple of the replies complained about workbook crashes copying these formulas between worksheets, so I tested on a blank workbook by creating a simple relative named range named OtherCell that simply refers to a cell two columns over from the cell where I defined the named range.

To be on the safe side I made name scope workbook level and deleted the sheet name part of the reference, leaving the sheet reference exclamation mark as illustrated in the example given in "Using Excel 4 Macro Functions" (Excel didn't complain about the reference formatting, so I'm interpreting this to signify the current sheet). Then copied it down and bingo! the two cells below had values 2 and 3 and contained the formula "=OtherCell". To check for crashing I copied the range with formulas to the next sheet without any issues

So cool

For my particular application I need to find the fill color of the other cell (GET.CELL(63,<ref>))

This also copied between sheets without problems, but did have an issue with recalculation: if the fill color is changed the formula value doesn't change even if the file is saved. Not sure why since the test formula and other macro formulas I've used recalculated just fine. Perhaps cell fill color isn't on the recalculation engine's "channel", but the workaround was simple enough:

1. Copy and paste back into the cell where the color was changed.
2. Do the equivalent by using Format Painter and paint the new color back into the cell.

Not a huge price to pay for a function Microsoft failed to supply and works perfectly for what I need.

For a VBA-based approach that probably would recalculate since VBA code is likely included in recalculations see Pearson Software Company's "Color Functions In Excel" (http://www.cpearson.com/excel/colors.aspx).

For the current Office color palette ColorIndex values: "ColorIndex property", Office VBA Reference (https://docs.microsoft.com/en-us/office/vba/api/excel.colorindex)

Huge thanks to MrExcel!

 

[fullerm]

Some additional information from the year 2020. I found this post very informative and tried to use the information but came across a problem using it in a function.
N.B.: I am using Excel Pro plus 2016
This comment is just here to save someone else the pain and lost time I have gone through.
After a lot of painful work I have discovered you cannot run ExecuteExcel4Macro within a function(UDF). Also worked out you cannot call a sub that runs ExecuteExcel4Macro form within a function(UDF). It just ended the function at the ExecuteExcel4Macro and returned #Value! in the cell. I did not have an error capture system running. This may be the issue with the above problem but I'm not sure if there is a different result on older excel.

Background Info:
I have a cell I wanted to test if the user had changed the colour or if the conditional formatting had changed the colour. I wanted to know what colour was showing on the screen.
I tried a function (UDF) but had an issue with .displayformat. You cannot use .displayformat in a function.
So I thought I could use the Get.Cell(63,cell). To do that I needed to use ExecuteExcel4Macro but it would not work in a function(UDF). It would crash the function with Error2015. However it did work in a macro by itself.
I was using a named range with a formula of =GET.CELL(63,INDIRECT("rc",FALSE)) for the conditional formatting and this worked without incident.
Some of the code I was using to test in a function(UDF). After TempA was defined I would jump to Temp = ExecuteExcel4...

Public Function IdentifyTypeOfBackgroundColourOfCell(ByRef MyCell As Range) As Integer
Dim ColouredCell As Boolean, ColouredByCondFormat As Integer, ColouredByUser As Integer, ColourDisplayed As Integer, ConditionalFormattingTriggered As Boolean, CounterB As Integer
Dim ConditionalFormat As FormatCondition
    'if the cell has: no colour and not conditional formatted colour then function = 0
    'if the cell has: no colour and has conditional formatted color then function = -1
    'if the cell has: colour and not showing  conditional formatted colour then function = 1
If MyCell.Count > 1 Then
    MyCell = MyCell.Resize(1, 1)
End If
ConditionalFormattingTriggered = False

TempA = Application.ExecuteExcel4Macro("Sqrt(4)")
TempA = Application.ExecuteExcel4Macro("GET.CELL(42)")
TempA = "GET.CELL(63,HistorySheet!" & MyCell.Address(ReferenceStyle:=xlR1C1) & ")"

Temp = ExecuteExcel4Macro(TempA)

'function would never get to this point, so there was no need to finish the code
'do more here to check which colour is being displayed
    'if the cell has: no colour and not conditional formatted colour then function = 0
         IdentifyTypeOfBackgroundColourOfCell  = 0
    'if the cell has: no colour and has conditional formatted color then function = -1
        IdentifyTypeOfBackgroundColourOfCell  = -1
    'if the cell has: colour and not showing  conditional formatted colour then function = 1
        IdentifyTypeOfBackgroundColourOfCell = 1
End Function

This maybe issue of ExecuteExcel4Macro in a function(UDF) may be obvious to some programmers but I didn't know nor could I find anything on the internet let me know of the problem/restriction.
Edit: posted before I finished