가치관제작소

[Concept]

▶ 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 & ")"

Sheets("Sheet1").Range("D1").PasteSpecial(-4122)
Sheets("Sheet1").Range("D1").PasteSpecial(-4163)  

▶VBA에서는 도중에 태스크를 멈추기 위해서 Exit sub
 VBS에서는 WScript.Quit

▶nest for or next while을 강제중지하는 방법

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
Next

For 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:

▶VBS로 엑셀 가동 후 올바른 종료 방법

'1.
Call workbook.Save
Call workbook.Close

Call ExcelApp.Quit

Set ExcelApp = nothing

'2.

Call workbook.Close(True)

Call ExcelApp.Quit

Set ExcelApp = nothing 

▶System.Collections.ArrayList

ArrayList Methods (System.Collections)

ArrayList Methods .NET Framework (current version) Other Versions Namespace: System.Collections Assembly:  mscorlib (in msco...

msdn.microsoft.com

▶Scripting.FileSystemObject

property

https://msdn.microsoft.com/en-us/library/ea5ht6ax(v=vs.84).aspx

method

https://msdn.microsoft.com/en-us/library/6tkce7xa(v=vs.84).aspx

▶読み取り専用으로 읽고 싶지 않을 때

Workbooks.Open은 정말 다양한 파라미터를 갖는데, 읽기 전용으로 읽기 위한 인수가 3번째이고 읽기전용으로 읽지 않기위한 인수가 7번째이다. 

Set workbook =  excelApp.workbook1.Open(dic,,,,,,1)

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbooks-open-method-excel

▶utf8로 텍스트를 읽고 싶을 때 : ADODB.Stream Object

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

 

Stream Object Properties, Methods, and Events

Stream Object Properties, Methods, and Events 2017-1-19 1 minutes to read Contributors Properties Charset Property EOS Prope...

docs.microsoft.com

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/stream-object-properties-methods-and-events

▶한 sub에서 다른 sub 불러오기 : 변수를 Global로 선언

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 Sub

▶한 sub에서 다른 sub 불러오기 : call

sub 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

▶"대용량 파일이 클립보드에 있습니다" 경고창 닫는 방법

:Excel.Application.CutCopyMode = False

▶외부에서 copy한 내용을 키보드조작으로 엑셀 안에 paste 하기 : SendKeys

Dim excelApp, objBook, objSheet

Dim wshShell

 

'Set Objects

Set excelApp = CreateObject("Excel.Application")

Set wshShell = CreateObject("WScript.Shell")

 

'키보드 조작이기 때문에 UI가 보여야함.

excelApp.Visible = True

 

Set objBook = excelApp.WorkBooks.Open("filePath.xlsx",,,,,,1)

Set objSheet = objBook.Sheets("sheetName")

objSheet.Range("A4").Select

 

'실행하고 있는 어떤 어플리케이션에 키보드 조작을 적용해야하는지 모르기 때문에

'캡션 이름으로 활성화할 대상을 지정

wshShell.AppActivate(excelApp.Caption)

 

'키보드 조작에 대한 더 많은 정보는 msdn정보 참고

Wscript.Sleep(500)

wshShell.SendKeys("^v")

Wscript.Sleep(500)

 

Set wshShell = Nothing

objBook.Close(True)

Call excelApp.Quit

Set excelApp = Nothing

※wshShell.AppActivate(title)

object

WshShell オブジェクトです。

title

アクティブにするアプリケーションを指定します。この引数には、アプリケーションのタイトル バーに表示されるタイトル文字列か、アプリケーションのプロセス ID を指定できます。

解説

AppActivate メソッドは、プロシージャ コールが正常終了したかどうかを示すブール値を返します。このメソッドを呼び出すと、指定されたアプリケーションまたはウィンドウにフォーカスが移りますが、最大化と最小化には影響がありません。ユーザーがフォーカスを切り替えたりウィンドウを閉じたりすると、アクティブなアプリケーション ウィンドウからフォーカスが移ります。

実行中の各アプリケーションのタイトル文字列を title と比較することで、どのアプリケーションがアクティブになるかが決まります。完全に一致するタイトルが見つからない場合、タイトル文字列の先頭が title と一致するアプリケーションがアクティブになります。そのようなアプリケーションが見つからない場合、タイトル文字列の最後が title と一致するアプリケーションがアクティブになります。名前が title と一致するアプリケーションのインスタンスがいくつかある場合、アクティブになるインスタンスは不定です。

▶sheet.range.autofilter param1,param2,param3,param4,param5

objSheet.Range(param2).AutoFilter param3,Array("派遣"),7,,True

1. Field 첫번째 파라미터 : autofilter의 대상이 되는 range의 행중 몇번째 열을 autofilter할 것인지를 선택한다.

2. Criteria1두번째 파라미터 : 첫번째 필터 대상이 되는 값을 선정한다.만약 multi value를 선택할 시에는

array("first", "second", "third", ..)로 적어준다.

3. Operator세번째 파라미터 :첫번째 파라미터로 array를 지정할 시에는, operator값을 xlFilterValues

7 -> Filter values으로 지정해야한다. 더 다양한 파라미터는 https://msdn.microsoft.com/en-us/vba/excelvba/articles/xlautofilteroperator-enumeration-excel에서 참고

4. Criteria2네번째 파라미터

5. VisibleDropDown다섯번째 파라미터 : 필터 드롭 표시를 할것이냐 안할 것이냐라는 것

6. autofilter는 sub이므로 괄호로 파라미터를 전달할시 can not pass value in parenthese라는 오류가 뜬다.

▶GetBaseName을 사용하면, 확장자를 제외한 파일의 순수한 이름만 뽑아낸다.

▶VBS에서 copyfile 메서드를 사용할 때 주의할 점

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

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:=False

and also allow for selecting an array of sheets

Sheets(Array("Sheet3", "Sheet2")).Select