VBA,VBS 프로그래밍 일기
[Concept]
위 그림과 같이 하나의 오브젝트(객체 : 클래스에 의해 인스턴스화된 것)는 property(속성)과 메서드를 갖는다. property는 property 프로시저에 의해 생성되고, 메서드는 function 프로시저에 의해 생성된다.(개체찾아보기에서 각 클래스를 클릭해보면 어떤 property와 method로 구성되어있는지 알 수 있다)
엑셀에서 오브젝트는 가장 상위개념인 엑셀 어플리케이션 - 워크북 - 워크시트 - 셀 등으로 내려간다. 이때 중요한 것은 하위의 오브젝트는 상위의 오브젝트가 존재하지 않으면 존재할 수 없다. 가령, 엑셀 워크시트가 존재하지 않는데, 셀이 존재할 수는 없는 것이다.
2.collection : 엑셀의 북이나 시트와 같이 동일한 오브젝트가 복수개 존재하는 경우 그것을 모아서 하나로 처리하는 것이 가능한데, 그러한 집합을 collection이라고 부른다. workbook 오브젝트의 집합은 workbooks collection이며, worksheet 오브젝트의 집합은 worksheets collection이다.
3.property : 프로퍼티(속성)은 오브젝트가 가지고 있는 특성이나 설정등에 붙은 이름이다. 오브젝트의 프로퍼티는 반드시 어떤 값을 리턴하는데, 그 리턴값이 타입이 정해져 있음에 유념하자.(각 클래스와 속성과 메서드의 리턴 타입은 개체 찾아보기에서 확인할 수 있다)
4.method : 메서드는 오브젝트의 행동을 결정하는 것이다. 오브젝트의 메서드는 반드시 어떤 값을 리턴하는데, 그 리턴값이 타입이 정해져 있음에 유념하자.(각 클래스와 속성과 메서드의 리턴 타입은 개체 찾아보기에서 확인할 수 있다)
Dim month, day
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set addedWorkBook = ExcelApp.Application.Workbooks.Open("C:/Users/daseul.kim/Desktop/Book1.xlsx")
Set objSheets = addedWorkBook.Sheets
Set newSheet = objSheets.Add(, objSheets(objSheets.Count))
month = Mid(Now - 1, 6, 2)
day = Mid(Now - 1, 9, 2)
newSheet.Name = month & "." & day
addedWorkBook.Save
End Sub
시트의 가장 끝에 새로운 시트를 추가하는 위와 같은 코딩에서, Set objSheets = addedWorkBook.Sheets에 주목해보자. new를 사용하지 않았다. 왜냐하면, addedWorkBook객체를 생성할 때 sheets객체가 이미 생성되었기 때문에, 초기화할 필요없이 이미 있는 객체값의 주소를 참조만 시키면되기 때문이다!
▶ Dim x As New Collection과 Dim x As Collection; Set x = New Collection 차이
프로그램의 성능차이가 존재할뿐 의미상의 차이는 존재하지 않는다.
Dim x As Collection 'Declare ;타입의 선언부로써 인스턴스를 생성한다.
'자바로 따지자면 Collection x 의 부분. 변수 x의 타입을 설정한다. 자바와 다른 점은 굳이 타입설정을 해주지 않아도 된다는 점인데, 이와같은 경우 바인딩이 런타임중 진행되므로 프로그램의 성능이 떨어진다.
Set x = New Collection 'Initiate ; 초기화 단계로 New Collection(); 객체 어사인
'변수 x에 Collection 클래스 내용이 담긴 객체 주소를 참조시킨다.
▶ VBA코드 작성과 VBS코드 작성의 차이점
VBA는 엑셀 실행 후 작성하는 코딩이기 때문에, 기본적으로 application, workbooks, sheets, cells등의 객체가 생성되어 있는 상태지만, VBS는 말그대로 0에서 시작하기 때문에 하나하나의 객체를 모두 생성해 주어야 한다.
[Technic]
▶엑셀함수의 Row,Rows와 VBA의 Rows는 역할이 다르다.
함수의 Row() : 참조 셀의 행 번호를 반환한다.
함수의 Rows() : 참조 영역의 행의 갯수를 반환한다.
vba의 Rows() : ()안의 행을 가르킨다.
▶개체를 변수로 받을 때는 변수 앞에 Set 사용을 잊지말 것
set은 변수에 개체를 참조하도록 할당하는 것이다. 스택에 저장되는 기본형 변수는 = 으로 값을 할당하고( A = 23), 힙에 저장되는 객체의 참조형 변수에는 SET으로 주소값을 해서 할당해주어야 한다.(Set A = range("A3"))
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim area As Range
Set area = Intersect(Target, Range("N1:N4"))
If area Is Nothing Then
Exit Sub
Else
MsgBox "수정권한없음"
End If
End Sub
▶프로시저 창에서 F8을 잘 이용할 것
프로시저 창 내에서 F8키는 코딩을 한줄 한줄 실행시켜, 디버그가 발생했을 시 원인을 파악하기 쉽게 해준다.
▶중단점 설정/해제
중단점(Break Point)이란 프로시저가 실행되는 도중에 자동으로 실행을 중지하도록 사용자가 지정해 놓은 위치를 말합니다. 개념적으로는 Stop 문과 비슷한데 마우스 클릭만으로 설정할 수 있다는 것과 워크북을 저장한 다음 다시 열 경우, 중단점은 저장되지 않는다는 점에서 차이가 있습니다. 중단점을 설정하는 방법은 네 가지 정도가 있습니다. 중단점을 설정하려는 행을 선택한 다음,
코드 왼쪽에 있는 여백 표시줄(gray margin)을 클릭한다.
<F9> 키를 누른다.
'디버그-중단점 설정/해제' 메뉴를 선택한다.
마우스 오른쪽 버튼을 클릭하고 '설정/해제-중단점' 메뉴를 선택한다(이것은 토글 키로, 중단점이 이미 설정되어 있다면 해제합니다).
어느 방법이든 중단점을 설정하면 위의 그림과 같이 여백 표시줄에 커다란 동그라미가 생깁니다. 중단점은 여러 곳에 설정할 수 있으며 설정을 해제하려면 중단점을 클릭하면 됩니다. 설정된 중단점들을 한꺼번에 모두 제거하려면 <Ctrl+Shift+F9> 키를 사용합니다.
▶'지역' 창 활용
앞에서 '한 단계씩 코드 실행'할 때 <F8> 키를 사용한다고 설명드렸지요? '보기-지역 창' 메뉴를 선택하면 '지역' 창이 표시됩니다. 이 상태에서 <F8> 키를 계속 눌러보면 각 변수에 값들이 어떻게 변하는지 알 수 있습니다.
예전에 순환문이 잘 이해되지 않으면 종이를 한 장 꺼내놓고 몇 번 로직을 돌려보라고 조언해 드렸었지요? '지역' 창을 사용하면 프로시저 내의 모든 변수값이 자동으로 나타나므로 그런 고생을 하지 않아도 됩니다.
▶Debug.Print 를 이용하여 부분적인 output확인하기
debug.Print 는 자바의 system.out.println과 동일한 기능이다. debug.print의 결과값은 콘솔창에 표시된다.
▶메크로 락으로 메크로 보호하기
도구 → VBAproject속성 → 보호
▶VBA의 사용자 함수의 return 값
VBA에서 사용자 함수의 return값은 따로 return을 써주는 형식이 아니라, 메서드의 이름을 다시한번 언급하는 형식으로 return값을 선언한다.단! 리턴값이 오브젝트일 경우에는 Set을 붙일 것
Function split_h(text As String, parameter As String, num As Integer)
Dim arr As Variant
arr = Split(text, parameter)
split_h = arr(num - 1) //사용자 함수의 리턴값
'Set split_h = arr(num - 1) //리턴 값이 오브젝트인 경우에는 Set을 쓰고 리턴값을 받는다.
End Function
SUM(sheet2.cells(1,1),sheet2.cells(5,1))
Worksheets("Sheet1").Cells(1, 1).Formula = "=SUM(Sheet2!" & Cells(1, 1).Address & ":" &Cells(5, 1).Address & ")"
abc"d"e 라는 문자열을 출력하기 위해서는 어떻게 해야할까? str = "abc"d"e 를 입력하면 오류가 발생한다. 따라서 이와같은 경우는 아스키 코드를 사용한다 Chr(34)는 큰따옴표, Chr(39)는 작은따옴표이다.
Dim str As String
str = "abc" & Chr(34) & "d" & chr(34) & "e"
MsgBox str
>>>abc"d"e
개발창 F1키를 이용하여 함수 둘러보기
→autofield 1,"<>",,,True
Sheets("Sheet1").Range("D1").PasteSpecial(-4122)
Sheets("Sheet1").Range("D1").PasteSpecial(-4163)
VBS에서는 WScript.Quit
Dim done = False
For Each item In itemList
For Each item1 In itemList1
If item1.Text = "bla bla bla" Then
done = True
Exit For
End If
Next
If done Then Exit For
NextFor Each item In itemList
For Each item1 In itemList1
If item1.Text = "bla bla bla" Then
Goto end_of_for
End If
Next
Next
end_of_for:'1.
Call workbook.Save
Call workbook.CloseCall ExcelApp.Quit
Set ExcelApp = nothing
'2.
Call workbook.Close(True)
Call ExcelApp.Quit
Set ExcelApp = nothing
property
Set workbook = excelApp.workbook1.Open(dic,,,,,,1)
Dim objStream, strData Set objStream = CreateObject("ADODB.Stream") objStream.CharSet = "utf-8" objStream.Open objStream.LoadFromFile("C:\Users\admin\Desktop\ArtistCG\folder.txt") Do until objStream.EOS strData = objStream.ReadText(-2) 'ReadText함수는 -1일경우 전체를, -2일경우 한줄을 반환 Wscript.Echo(strData) Loop objStream.Close Set objStream = Nothing
Dim global_var As Integer
'
Sub doA()
global_var = global_var + 1
Debug.Print global_var
End Sub
Sub doB()
global_var = global_var + 10
Debug.Print global_var
End Sub
Sub main()
doA
doB
doA
End Subsub Main()
Nc As integer
Dim kij(1 To Nc, 1 To Nc), xi(1 to Nc), a1 As Double
'I assign values to my variables from the excelsheet e.g. Nc=Cells(1,1) etc.
Call CalculateA(Nc,kij, xi, a1, a)
Cells(5,5)=a
end sub
sub CalculateA(Nc as integer,kij as matrix double, xi as array double, a as Double)
a=0
For i=1 To Nc
For j=1 To Nc
a = a + kij(i,j)*x(i)*x(j)
Next j
Next i
a = a*a1
end sub- object
- WshShell オブジェクトです。
- title
- アクティブにするアプリケーションを指定します。この引数には、アプリケーションのタイトル バーに表示されるタイトル文字列か、アプリケーションのプロセス ID を指定できます。
解説
AppActivate メソッドは、プロシージャ コールが正常終了したかどうかを示すブール値を返します。このメソッドを呼び出すと、指定されたアプリケーションまたはウィンドウにフォーカスが移りますが、最大化と最小化には影響がありません。ユーザーがフォーカスを切り替えたりウィンドウを閉じたりすると、アクティブなアプリケーション ウィンドウからフォーカスが移ります。
実行中の各アプリケーションのタイトル文字列を title と比較することで、どのアプリケーションがアクティブになるかが決まります。完全に一致するタイトルが見つからない場合、タイトル文字列の先頭が title と一致するアプリケーションがアクティブになります。そのようなアプリケーションが見つからない場合、タイトル文字列の最後が title と一致するアプリケーションがアクティブになります。名前が title と一致するアプリケーションのインスタンスがいくつかある場合、アクティブになるインスタンスは不定です。
objSheet.Range(param2).AutoFilter param3,Array("派遣"),7,,True
1 2 3 | If (fso.FileExists(excelPath))=false Then fso.CopyFile excelPath, param1&"\" '파일 패스 마지막에 \찍는 것에 매우 주의할 것 End If | cs |
좀더 쉽게 비유하기 위해 c:/program and file/dev/program.exe 라는 1기가짜리 실행파일이 있다고 해보자.
참조 : 75kb짜리 바로가기 아이콘을 만들어서, program.exe의 주소값을 전달하고, 클릭할 때마다 주소값을 통해 c:/program and file/dev/program.exe로 이동 후 프로그램을 실행. 실행하면 기존의 c:/program and file/dev/program.exe에도 영향을 미침)
복제 : 1기가짜리 program.exe을 통째로 복사해서 사용함. 실행해도 기존의 c:/program and file/dev/program.exe에는 영향을 미치지 않고 별도로 움직임
VBSには外部ファイルをインクルード・インポートする仕組みがないのですが、
ExecuteGlobalを使えばそれっぽいことができます。Include "Library.vbs" ' 外部ファイル「Library.vbs」を取込み
Sub Include(ByVal strFile)
Dim objFSO , objStream , strDir
Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
strDir = objFSO.GetFile(WScript.ScriptFullName).ParentFolder
Set objStream = objFSO.OpenTextFile(strDir & "\" & strFile, 1)
ExecuteGlobal objStream.ReadAll()
objStream.Close
Set objStream = Nothing
Set objFSO = Nothing
End Sub
ExecuteGlobalに渡した文字列をスクリプトとして解釈して実行してくれる模様。
1.selection과 activate의 차이는 유연성이다.
Activate make the specified sheet the active sheet, and may only be applied to a single sheet
Select allow for optionally extending the currently selected sheets to include the specified sheet, eg
Worksheets("Sheet2").Select Replace:=Falseand also allow for selecting an array of sheets
Sheets(Array("Sheet3", "Sheet2")).Select3. activate는 스크린, 키보드 조작에 필수적이다.
위에서 언급했던 바와 같이, activate는 객체를 클릭하여 스크린의 윈도우를 active하는 것이다. 따라서, 스크린에서 가장 외부에 있는 객체를 조작하는 스크린, 키보드 조작에는 일단 activate로 조작 대상인 객체를 가장 외부로 active시키는 과정이 필요하다.
1 2 3 4 5 6 7 8 | Dim excelApp Dim wb Dim ws set excelApp = GetObject(,"Excel.Application") Set wb = excelApp.activeWorkbook Set ws = wb.Sheets(1) ws.Range("A1").Value = "hahahahahaa" | cs |
2. 현재 열려있는 excel객체로부터 특정 이름의 workbook 가져오기
현재 2개의 workbook이 열려있다. 하나의 workbook이름은 book1.xlsx이며, 다른 workbook이름은 boo2.xlsx일 경우를 가정한다.
1 2 3 4 5 6 7 8 | Dim excelApp Dim wb Dim ws set excelApp = GetObject(,"Excel.Application") set wbs = excelApp.Workbooks.Item("book1.xlsx") set ws = wbs.Sheets(1) ws.Range("A1").value = "hahahaha" | cs |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | Dim ExcelApp Dim objWorkbook, objSheets Dim args, param Dim result Set ExcelApp = GetObject(,"Excel.Application") For Each objWorkbook in ExcelApp.Workbooks Wscript.Echo(objWorkBook.Name) If objWorkbook.Name = "book3.xlsx" Then Exit For ' This is the workbook we wanted End If Next If objWorkbook.Name = "book3.xlsx" Then 'objWorkbook is set to book3, so do whatever you like Else ' No book3 found, throw up a message box to warn the user MsgBox "No Book3.xlsx file found" End If | cs |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Sub UnitGroupShowTest() '//모든 셀의 아웃라인 삭제. 특정 셀의 아웃라인 삭제도 가능함. ActiveSheet.Rows.ClearOutline '//특정 행을 그룹화 Range("A2:A8").Rows.Group Range("A15:A19").Rows.Group '//특정 행의 그룹화를 감추기 ActiveSheet.Outline.ShowLevels rowlevels:=1 '//특정 행의 그룹화 Range("A11:A13").Rows.Group End Sub | cs |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | Function findLastCell(arg1 As String, arg2 As String, arg3 As Date) As range Dim ws As Worksheet Dim startCell As range Dim tgCell As range Dim i As Integer Set ws = Sheets(arg1) Set startCell = ws.range(arg2) Debug.Print startCell.Value i = 1 Do While startCell(i, 1).Value <> arg3 i = i + 1 Loop Set tgCell = startCell(i, 1) Set findLastCell = tgCell End Function | cs |
1 2 3 4 5 6 7 8 9 10 11 | Option Explicit Sub MoveTodoFromDashToTodoList() Dim tgday As Date tgday = Date Call findLastCell("Dashboard", "A1", tgday) End Sub | cs |
1 2 3 4 5 6 7 8 9 10 11 | Option Explicit Sub MoveTodoFromDashToTodoList() Dim tgday As Date Dim tgCell As Range tgday = Date CfindLastCell "Dashboard", "A1", tgday End Sub | cs) |
1 2 3 4 5 6 7 8 9 10 11 | Option Explicit Sub MoveTodoFromDashToTodoList() Dim tgday As Date Dim tgCell As Range tgday = Date Set tgCell = CfindLastCell("Dashboard", "A1", tgday) End Sub | cs) |