I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

GET.CELL

You can retrieve interesting information about worksheet cells by using the Excel4 macro function GET.CELL. Define the name HasFormula with the value
=GET.CELL(48,INDIRECT("RC[-1]",FALSE))
for example. If you now insert =HasFormula next right to a cell, you will be shown whether the cell has a formula (“True”) or not (“False”).

Another example for GET.CELL you can find here.

An overview over some arguments for GET.CELL:

Proposed Name

Arg #

What =GET.CELL(Arg #,INDIRECT("RC[-1]",)) will return

AbsReference

1

Absolute style reference like [Book1.xls]Sheet1!$A$1

ShowValue

5

Cell value

ShowFormula

6

Cell formula

NumFormat

7

Number format of cell

IsLocked

14

True if cell is locked

FormulaHidden

15

True if cell formula is hidden

ShowWidth

16

Cell width. If array-entered into two cells of a row, second value is true if width is standard

ShowHeight

17

Cell height

WorkbookName

32

Workbook name like [Book1.xls]Sheet1 or Book1.xls if workbook and single sheet have identical names

ShowFormulaWOT

41

Cell formula without translation into language of workspace

HasNote

46

True if cell has a text note

HasFormula

48

True if cell contains a formula

IsArray

49

True if cell is part of an array formula

IsStringConst

52

Text alignment char “'” if cell is a string constant, empty string “” if not

AsText

53

Cell displayed as text with numbers formatted and symbols included

WorksheetName

62

Worksheet name like [Book1.xls]Sheet1

WorkbookName

66

Workbook name like Book1.xls

IsHidden

 

VBA only: True if cell is hidden (the entire row or column, actually)

If you want to achieve similar results with VBA use a UDF little this:

Function sbGetCell(r As Range, s As String) As Variant
'Reverse("moc.LiborPlus.www") V0.21 PB 15-Jan-2016
Application.Volatile
Select Case s
Case "AbsReference", "1"
    'Absolute style reference like [Book1.xls]Sheet1!$A$1
    If Application.Caller.Parent.Parent.Name = r.Worksheet.Parent.Name And _
        Application.Caller.Parent.Name = r.Worksheet.Name Then
        sbGetCell = r.Address
    Else
        If InStr(r.Worksheet.Parent.Name & r.Worksheet.Name, " ") > 0 Then
            sbGetCell = "'[" & r.Worksheet.Parent.Name & "]" & r.Worksheet.Name & "'!" & r.Address
        Else
            sbGetCell = "[" & r.Worksheet.Parent.Name & "]" & r.Worksheet.Name & "!" & r.Address
        End If
    End If
Case "ShowValue", "5"
    'Cell value
    sbGetCell = r.Value
Case "ShowFormula", "6"
    'Cell formula
    sbGetCell = r.FormulaLocal
Case "NumFormat", "7"
    'Number format of cell
    sbGetCell = r.NumberFormatLocal
Case "HorizontalAlignment", "8"
    'Number indicating the cell's horizontal alignment
    Select Case r.HorizontalAlignment
    Case xlGeneral
        sbGetCell = 1
    Case xlLeft
        sbGetCell = 2
    Case xlCenter
        sbGetCell = 3
    Case xlRight
        sbGetCell = 4
    Case xlFill
        sbGetCell = 5
    Case xlJustify
        sbGetCell = 6
    Case xlCenterAcrossSelection
        sbGetCell = 7
    Case xlDistributed
        sbGetCell = 8
    Case Else
        Debug.Assert False 'Should not get here
    End Select
'Please note that 9,10,12 behave differently from GET.CELL
Case "LeftBorderStyle", "9"
    'Number indicating the left-border style assigned to the cell
    sbGetCell = r.Borders(xlEdgeLeft).LineStyle
Case "RightBorderStyle", "10"
    'Number indicating the right-border style assigned to the cell
    sbGetCell = r.Borders(xlEdgeRight).LineStyle
Case "BottomBorderStyle", "12"
    'Number indicating the bottom-border style assigned to the cell
    sbGetCell = r.Borders(xlEdgeBottom).LineStyle
Case "IsLocked", "14"
    'True if cell is locked
    sbGetCell = r.Locked
Case "FormulaHidden", "15"
    'True if cell formula is hidden
    sbGetCell = r.FormulaHidden
Case "ShowWidth", "16"
    'Cell width. If array-entered into two cells of a row, second value is true if width is standard
    sbGetCell = r.ColumnWidth 'Not width!
Case "ShowHeight", "17"
    'Cell height
    sbGetCell = r.RowHeight
Case "WorkbooksheetName", "32"
    'Workbook name like [Book1.xls]Sheet1 or Book1.xls if workbook and single sheet have
    'identical names
    If r.Worksheet.Parent.Name = r.Worksheet.Name & ".xls" And _
        Application.Worksheets.Count = 1 Then
        sbGetCell = r.Worksheet.Parent.Name
    Else
        sbGetCell = "[" & r.Worksheet.Parent.Name & "]" & r.Worksheet.Name
    End If
Case "ShowFormulaWOT", "41"
    'Cell formula without translation into language of workspace
    sbGetCell = r.Formula
Case "HasNote", "46"
    'True if cell has a text note
    sbGetCell = Len(r.NoteText) > 0
Case "HasFormula", "48"
    'True if cell contains a formula
    sbGetCell = r.HasFormula
Case "IsArray", "49"
    'True if cell is part of an array formula
    sbGetCell = r.HasArray
Case "IsStringConst", "52"
    'Text alignment char "'" if cell is a string constant, empty string "" if not
    sbGetCell = r.PrefixCharacter
Case "AsText", "53"
    'Cell displayed as text with numbers formatted and symbols included
    sbGetCell = Format(r.Value, r.NumberFormatLocal)
Case "WorksheetName", "62"
    'Worksheet name like [Book1.xls]Sheet1
        sbGetCell = "[" & r.Worksheet.Parent.Name & "]" & r.Worksheet.Name
Case "WorkbookName", "66"
    'Workbook name like Book1.xls
    sbGetCell = r.Worksheet.Parent.Name
Case "IsHidden"
    'Cell hidden?
    sbGetCell = r.EntireRow.Hidden Or r.EntireColumn.Hidden
Case Else
    sbGetCell = CVErr(xlErrValue)
End Select

End Function

Sulprobil   Get it done   Contact   Disclaimer   Download