Skip to content
Excel
2012.07.16 16:20

[excel] GET.CELL사용법

조회 수 26573 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

[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

로그인 후 댓글쓰기가 가능합니다.

?

http://urin79.com

우린친구블로그

sketchbook5, 스케치북5

sketchbook5, 스케치북5

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소