[VBA] 간단한 소개

VBA에 대한 간단한 소개 (p.30)

·         VBA module은 procedure들로 구성

Sub Test()

    Sum = 1 + 1

    MsgBox "The answer is " & Sum

End Sub

·         함수 procedure

Function AddTwo(arg1, arg2)

AddTwo= arg1 + arg2

End Function

·         100개 이상의 조작가능한 클래스 개체를 제공

·         개체 클래스들은 계층화되어 있음 - object model

Application

Workbook

Worksheet

Range

PivotTable

Chart

CommandBar

·         유사한 개체들은 collection을 구성

·         개체참조

Application.Workbooks("Book1.xls")

Application.Workbooks("Book1.xls").Worksheets("Sheet1")

Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1")

·         활성개체(active object)

Worksheets("Sheet1").Range("A1")

Range("A1")

·         개체들은 속성(property)를 갖는다

Worksheets("Sheet1").Range("A1").Value

·         변수에 값을 할당

Interest= Worksheets("Sheet1").Range("A1").Value

·         개체는 method를 갖는다

o    Range("A1").ClearContents

 

 

Visual Basic Editor - VBE (p.33)

·         <Alt+F11>

·         <Ctrl+G> 직접실행창

o    프로시저를 만들지 않고 결과를 확인가능

o    ? Range("A1").Value

o    Print Range("A1").Value

·         (문법적으로 필요하지 않으나) 들여쓰기는 좋은 습관

·         긴 VBA코드를 여러줄에 나눠쓰기: 빈칸과 밑줄 문자 입력후 줄바꿈

·         <F5> 실행

·         문자열 합치기- & 연산자 이용

 

 

개체와 컬렉션 (p.56)

·         개체 계층

o    Application

·         Workbooks

§  Worksheets

·         ChartObjects

·         Range

·         PageSetup

·         PivotTables

§  Charts

§  Names

·         Windows

·         AddIns

·         AutoCorrect

 

·         컬렉션(Collection)

o    동일한 클래스에 속해있는 개체들의 집합

o    컬렉션 자체가 하나의 개체

 

속성과 메서드 (p.58)

·         개체 속성

Sub ShowValue()

    Answer = Worksheets("Sheet1").Range("A1").Value

    MsgBox Answer

End Sub

·         개체 메서드

Sub ZapRange()

    Worksheets("Sheet1").Range("A1:C3").Clear

End Sub

·         인수사용

o    속성

·         반드시 인수를 괄호 안에 둔다.

·         MsgBox Range("A1").Address(False)

·         Named argument사용 예

§  MsgBox Range("A1").Address(rowAbsolute:=False)

o    메서드

·         인수를 쉼표로 구분하여 메서드 이름 뒤에 둔다

·         Workbooks("Mybook.xls").Protet "xyzzy", True, True

·         Named argument 사용 예

§  Workbooks("MyBook.xls").Protect Structure:=True, Windows:=True

 

Comment 개체: 사례분석 (p.61)

·         Comment object

Methods

	Name	Description
	Delete	Deletes the object.
	Next	Returns a Comment object that represents the next comment.
	Previous	Returns a Comment object that represents the previous comment.
	Text	Sets comment text.

Properties

	Name	Description
	Application	When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Application object that represents the creator of the specified object (you can use this property with an OLE Automation object to return the application of that object). Read-only.
	Author	Returns or sets the author of the comment. Read-only String.
	Creator	Returns a 32-bit integer that indicates the application in which this object was created. Read-only Long.
	Parent	Returns the parent object for the specified object. Read-only.
	Shape	Returns a Shape object that represents the shape attached to the specified comment.
	Visible	Returns or sets a Boolean value that determines whether the object is visible. Read/write.

 

·         Comments collection

Methods

	Name	Description
	Item	Returns a single object from a collection.

Properties

	Name	Description
	Application	When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Application object that represents the creator of the specified object (you can use this property with an OLE Automation object to return the application of that object). Read-only.
	Count	Returns a Long value that represents the number of objects in the collection.
	Creator	Returns a 32-bit integer that indicates the application in which this object was created. Read-only Long.
	Parent	Returns the parent object for the specified object. Read-only.

 

o    Worksheets("Sheet1").Cmments(1)

o    Worksheets("Sheet1").Cmments(1).Text

o    ActiveSheet.Comments.Count

o    ActiveSheet.Comments(1).Parent.Address

·          

·         Range("A1").Comment.Text

o    Comment 는 Range의 속성, 개체(Comment)를 반환한다

o    Comment.Text로 사용가능

·         Comment 개체내의 개체들 (확인방법?)

o    Application

·         Workbook

§  Worksheet

·         Comment

·         Shape

·         FillFormat

·         ColorFormat

o    Worksheets("Sheet1").Comments(1).Shape.Fill.ForeColor.RGB = RGB(0, 255, 0)

 

·         유용한 Application속성들

	Name	Description
	ActiveCell	Returns a Range object that represents the active cell in the active window (the window on top) or in the specified window. If the window isn't displaying a worksheet, this property fails. Read-only.
	ActiveSheet	Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active.
	ActiveWindow	Returns a Window object that represents the active window (the window on top). Read-only. Returns Nothing if there are no windows open.
	ActiveWorkbook	Returns a Workbook object that represents the workbook in the active window (the window on top). Read-only. Returns Nothing if there are no windows open or if either the Info window or the Clipboard window is the active window.
	Selection	Returns the selected object in the active window for an Application object.
	ThisWorkbook	Returns a Workbook object that represents the workbook where the current macro code is running. Read-only.

 

 

Range 개체 (p.67)

·         Range개체를 참조하는 3가지 방법

o    Worksheet나 Range클래스 개체의 Range 속성

·         문법

§  Object.Range(cell)

§  Object.Range(cell1, cell2)

·         Worksheets("Sheet1").Range("A1").Value = 1

·         Worksheets("Sheet1").Range("Input").Value = 1

·         Worksheets("Sheet1").Range("A1:B10").Value = 2

§  Range("A1", "B10") = 2

·         Range("C1:C10 A6:E6")= 3

·         공통 구역 연산자(빈칸)

·         Range("A1, A3, A5, A7") = 4

·         AcitveCell.Range("B2") = 5

§  활성 셀보다 한 줄 아래 오른 쪽에 있는 셀에 5를 입력

o    Worksheet 개체의 Cells 속성

·         문법

§  Object.Cells(rowIndex, columnIndex)

§  Object.Cells(rowIndex)

§  Object.Cells

·         rowIndex = 1~65536

·         columnIndex = 1~256

·         ActiveSheet.Cells(3, 4) = 7

§  활성셀을 A1으로 가정

§  D3 (3행, 4열)에 7이 입력됨

·         ActiveSheet.Cells(1, 1) = 7

§  A1에 입력

·         ActiveSheet.Cells(2, 1) = 7

§  활성 셀 바로 아래 셀에 입력

o    Range 개체의 Offset 속성

·         Offset속성은 오직 Range개체에만 적용됨

·         문법

§  Object.Offset(rowOffset, columnOffset)

§  양수 (우측 또는 하단 방향)

§  음수 (좌측 또는 상당 방향)

§  0

·         ActiveCell.Offset(1, 0).Value = 12

·         ActiveCell.Offset(-1, 0).Value = 12