[excel] GET.CELL사용법
http://www.sulprobil.com/html/get_cell.html
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 this UDF:
Function sbGetCell(r As Range, s As String) As Variant
'Reverse("moc.LiborPlus.www") V0.11 PB 29-Jan-2011
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 "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
